ASP.Net C＃ - MySQLTransaction多个插入 [英] ASP.Net C# - MySQLTransaction multiple inserts

问题描述

这张贴在这里我的previous问题继（<一href=\"http://stackoverflow.com/questions/24739695/two-insert-queries-with-linked-fields/24740224\">Two插入带有链接字段）查询，为什么下面的code将在question_m'表4的新行，当我想只插入1列？此外，下面的code。在答案表中插入16个新行，而不是4行？

下面是表screencaps被执行此code后：

questions_m'表 - http://i.imgur.com/tMeGAyt.png

答案表 - http://i.imgur.com/z3nVYAe.png

 字符串connStr = ConfigurationManager.ConnectionStrings [myConnectionString]的ConnectionString。
    使用（康涅狄格州的MySqlConnection =新的MySqlConnection（connStr））
    {
        conn.Open（）;
        使用（MySqlTransaction TR = conn.BeginTransaction（））
        {
            字符串queryUpdateQuestions = @INSERT INTO questions_m（是模块，AUTHOR_ID，批准问题，类型）VALUES（@module_id，@author_id，@approved，@question，@type）;
            SELECT LAST_INSERT_ID（）;            使用（MySqlCommand的cmdUpdateQuestions =新的MySqlCommand（queryUpdateQuestions，康涅狄格州，TR））
            {
                cmdUpdateQuestions.Parameters.Add（@是模块，MySqlDbType.VarChar）;
                cmdUpdateQuestions.Parameters [@是模块]值= ddlModules.SelectedValue.ToString（）。
                cmdUpdateQuestions.Parameters.Add（@ AUTHOR_ID，MySqlDbType.VarChar）;
                cmdUpdateQuestions.Parameters [@ AUTHOR_ID]值=会话[用户名]的ToString（）。
                cmdUpdateQuestions.Parameters.Add（@认可，MySqlDbType.VarChar）;
                cmdUpdateQuestions.Parameters [@批准]值='N'。
                cmdUpdateQuestions.Parameters.Add（@问题MySqlDbType.VarChar）;
                。cmdUpdateQuestions.Parameters [@问题]值= txtQuestion.Text;
                cmdUpdateQuestions.Parameters.Add（@型，MySqlDbType.VarChar）;
                cmdUpdateQuestions.Parameters [@型]值= ddlQuestionType.SelectedValue.ToString（）。                INT lastQuestionID = Convert.ToInt32（cmdUpdateQuestions.ExecuteScalar（））;
                的ViewState [lastQuestionID] = lastQuestionID;
            }            字符串queryUpdateAnswers = @INSERT INTO答案（question_id，答案，正确的）VALUES（@question_id，@answer，@correct）;
                                    SELECT LAST_INSERT_ID（）;            使用（MySqlCommand的cmdUpdateAnswers =新的MySqlCommand（queryUpdateAnswers，康涅狄格州，TR））
            {
                cmdUpdateAnswers.Parameters.Add（@回答，MySqlDbType.VarChar）;
                cmdUpdateAnswers.Parameters.Add（@ question_id，MySqlDbType.Int32）;
                cmdUpdateAnswers.Parameters.Add（@正确的，MySqlDbType.VarChar）;                INT lastAnswerID = 0;
                INT A = Convert.ToInt32（ddlNoOfAnswers.SelectedValue.ToString（））;                对于（INT B = 1; B＆LT = A; B ++）
                {
                    。cmdUpdateAnswers.Parameters [@回答]值=（（文本框）this.FindControl（txtAnswer+ B））文本;
                    cmdUpdateAnswers.Parameters [@ question_id]值= Convert.ToInt32（的ViewState [lastQuestionID]）。
                    如果（B == 1）
                    {
                        。cmdUpdateAnswers.Parameters [@正确]值=Y;
                    }
                    其他
                    {
                        cmdUpdateAnswers.Parameters [@正确]值=空。
                    }
                    lastAnswerID = Convert.ToInt32（cmdUpdateAnswers.ExecuteScalar（））;
                }
            }            tr.Commit（）;
        }
    }
 

解决方案

的ExecuteScalar（）通常用于从数据库中返回/选择一些数据。当你执行任何DDL查询使用的ExecuteNonQuery来代替。

Following on from my previous question posted here (Two insert queries with linked fields), why does the following code insert 4 new rows in the 'question_m' table when I want to insert only 1 row? Also, the following code inserts 16 new rows in the 'answers' table instead of 4 rows?

Here are screencaps of the tables after this code was executed:

'questions_m' table - http://i.imgur.com/tMeGAyt.png

'answers' table - http://i.imgur.com/z3nVYAe.png

    string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
    using (MySqlConnection conn = new MySqlConnection(connStr))
    {
        conn.Open();
        using (MySqlTransaction tr = conn.BeginTransaction())
        {
            string queryUpdateQuestions = @"INSERT INTO questions_m (module_id, author_id, approved, question, type) VALUES (@module_id, @author_id, @approved, @question, @type);
            SELECT LAST_INSERT_ID()";

            using (MySqlCommand cmdUpdateQuestions = new MySqlCommand(queryUpdateQuestions, conn, tr))
            {
                cmdUpdateQuestions.Parameters.Add("@module_id", MySqlDbType.VarChar);
                cmdUpdateQuestions.Parameters["@module_id"].Value = ddlModules.SelectedValue.ToString();
                cmdUpdateQuestions.Parameters.Add("@author_id", MySqlDbType.VarChar);
                cmdUpdateQuestions.Parameters["@author_id"].Value = Session["UserID"].ToString();
                cmdUpdateQuestions.Parameters.Add("@approved", MySqlDbType.VarChar);
                cmdUpdateQuestions.Parameters["@approved"].Value = 'N';
                cmdUpdateQuestions.Parameters.Add("@question", MySqlDbType.VarChar);
                cmdUpdateQuestions.Parameters["@question"].Value = txtQuestion.Text;
                cmdUpdateQuestions.Parameters.Add("@type", MySqlDbType.VarChar);
                cmdUpdateQuestions.Parameters["@type"].Value = ddlQuestionType.SelectedValue.ToString();

                int lastQuestionID = Convert.ToInt32(cmdUpdateQuestions.ExecuteScalar());
                ViewState["lastQuestionID"] = lastQuestionID;
            }

            string queryUpdateAnswers = @"INSERT INTO answers (question_id, answer, correct) VALUES (@question_id, @answer, @correct);
                                    SELECT LAST_INSERT_ID()";

            using (MySqlCommand cmdUpdateAnswers = new MySqlCommand(queryUpdateAnswers, conn, tr))
            {
                cmdUpdateAnswers.Parameters.Add("@answer", MySqlDbType.VarChar);
                cmdUpdateAnswers.Parameters.Add("@question_id", MySqlDbType.Int32);
                cmdUpdateAnswers.Parameters.Add("@correct", MySqlDbType.VarChar);

                int lastAnswerID = 0;
                int a = Convert.ToInt32(ddlNoOfAnswers.SelectedValue.ToString());

                for (int b = 1; b <= a; b++)
                {
                    cmdUpdateAnswers.Parameters["@answer"].Value = ((TextBox)this.FindControl("txtAnswer" + b)).Text;
                    cmdUpdateAnswers.Parameters["@question_id"].Value = Convert.ToInt32(ViewState["lastQuestionID"]);
                    if (b == 1)
                    {
                        cmdUpdateAnswers.Parameters["@correct"].Value = "Y";
                    }
                    else
                    {
                        cmdUpdateAnswers.Parameters["@correct"].Value = null;
                    }
                    lastAnswerID = Convert.ToInt32(cmdUpdateAnswers.ExecuteScalar());
                }
            }

            tr.Commit();
        }
    }

解决方案

ExecuteScalar() is usually used to return/select some data from database. Use ExecuteNonQuery instead when you are executing any DDL queries.

这篇关于ASP.Net C＃ - MySQLTransaction多个插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！