两个带有链接字段的插入查询 [英] Two insert queries with linked fields
问题描述
我有以下两个 MySQL 表:
I have the following two MySQL tables:
questions:
question_id (PK, AI), module_id (FK), author_id (FK), approved, question, correct_answer_id (FK)
answers:
answer_id (PK, AI), question_id (FK), answer
我希望能够在问题"表中插入一个新行，并在答案"表中插入多行.
I want to be able to insert a new row in the 'questions' table and multiple rows in the 'answers' tables.
'answers' 表中的新行应与 'questions' 行中新生成的 'question_id' 值具有相同的 'question_id'.此外，questions"表中的correct_answer_id"字段应等于answers"表中插入的第一行的answer_id".
The new rows in the 'answers' table should have the same 'question_id' as the newly generated 'question_id' value in the 'questions' row. Also, the 'correct_answer_id' field in the 'questions' table should equal the 'answer_id' of the first row inserted in the 'answers' table.
是否有比以下步骤更有效的方法?:
Is there a more efficiently way to do this than the following steps?:
- 插入值(module_id、author_id、approved、question)'问题'
- 获取questions"中的最后一个question_id"
- 在answers"中插入值(question_id、answer)
- 更新问题"中的值 (correct_answer_id)
代码:
string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
MySqlConnection conn = new MySqlConnection(connStr);
string queryUpdateQuestions = "INSERT INTO questions (module_id, author_id, approved, question) VALUES (@module_id, @author_id, @approved, @question)";
MySqlCommand cmdUpdateQuestions = new MySqlCommand(queryUpdateQuestions, conn);
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;
try
{
conn.Open();
cmdUpdateQuestions.ExecuteNonQuery();
}
catch
{
lblError.Text="Unable to add question.";
}
finally
{
conn.Close();
}
//????? = get last question_id in 'questions'
int a = Convert.ToInt32(ddlNoOfAnswers.SelectedValue.ToString());
for (int b=1; b <= a; b++)
{
string queryUpdateAnswers = "INSERT INTO answers (question_id, answer) VALUES (@question_id, @answer)";
MySqlCommand cmdUpdateAnswers = new MySqlCommand(queryUpdateAnswers, conn);
cmdUpdateAnswers.Parameters.Add("@answer", MySqlDbType.VarChar);
cmdUpdateAnswers.Parameters["@answer"].Value = ((TextBox)this.FindControl("txtAnswer" + b)).Text;
cmdUpdateAnswers.Parameters.Add("@question_id", MySqlDbType.VarChar);
cmdUpdateAnswers.Parameters["@question_id"].Value = ?????;
try
{
conn.Open();
cmdUpdateAnswers.ExecuteNonQuery();
}
catch
{
lblError.Text="Unable to add answer.";
}
finally
{
conn.Close();
}
}
//update 'correct_answer_id' in 'questions'
推荐答案
可以进行一些简化.首先，您需要将所有命令包含在一个事务中，因为这是插入的记录之间存在严格关系的典型情况，并且拥有一些部分完成的记录集是没有意义的.
Some simplification is possible. First of all you need to enclose all of your commands inside a transaction because this is the classical case where the records inserted are in strictly relationships and it doesn't make sense to have some partially completed set of records.
using(MySqlConnection conn = new MySqlConnection(connStr))
{
conn.Open();
using(MySqlTransaction tr = conn.BeginTransaction())
{
...
// MySqlCommand code goes here
...
tr.Commit();
}
}
现在，您可以更改插入问题 sql 以添加第二条语句，该语句返回最后插入的 id
Now, you could change your insert question sql to add a second statement that returns the last id inserted
string queryUpdateQuestions = @"INSERT INTO questions (.....);
SELECT LAST_INSERT_ID()";
using(MySqlCommand cmdUpdateQuestions = new MySqlCommand(queryUpdateQuestions, conn, tr))
{
// build the parameters for the question record
......
// Instead of ExecuteNonQuery, run ExecuteScalar to get back the result of the last SELECT
int lastQuestionID = Convert.ToInt32(cmdUpdateQuestions.ExecuteScalar());
..
}
注意在 MySqlCommand 构造函数中如何传递对当前事务的引用.这是处理已打开事务的连接所必需的.
Notice how, at the MySqlCommand constructor, is passed the reference to the current transaction. This is required to work with an connection that has a transaction opened.
第二部分的情况有点复杂.添加第二个 sql 语句的相同技巧也可以应用于插入答案的循环，但如果第一个问题是正确的，则需要向后循环
Things are a bit more complex for the second part. The same trick to add a second sql statement could be applied also to the loop that insert the answers, but you need to loop backward if the first question is the correct one
string queryUpdateAnswers = @"INSERT INTO answers (question_id, answer)
VALUES (@question_id, @answer);
SELECT LAST_INSERT_ID()";
using(MySqlCommand cmdUpdateAnswers = new MySqlCommand(queryUpdateAnswers, conn, tr))
{
// next move the loop inside the using and prepare the parameter before looping to
// to avoid unnecessary rebuild of the parameters and the command
cmdUpdateAnswers.Parameters.Add("@answer", MySqlDbType.VarChar);
cmdUpdateAnswers.Parameters.Add("@question_id", MySqlDbType.Int32);
int lastAnswerID = 0;
// Loop backward so the last answer inserted is the 'correct' one and we could get its ID
for (int b=a; b >= 1; b--)
{
cmdUpdateAnswers.Parameters["@answer"].Value = ((TextBox)this.FindControl("txtAnswer" + b)).Text;
cmdUpdateAnswers.Parameters["@question_id"].Value = lastQuestionID;
lastAnswerID = Convert.ToInt32(cmdUpdateAnswers.ExecuteScalar());
}
....
}
现在您可以运行最后一个命令，使用 lastAnswerID 更新问题
Now you could run the last command that update the question with the lastAnswerID
(最后一点，我假设字段 question_id 和 answer_id 是数字类型，而不是 varchar，这要求这些字段的参数是 Int32 而不是 varchar)
(A last note, I suppose that the fields question_id and answer_id are of type numeric, not varchar, this requires that the parameters for these fields will be an Int32 not a varchar)
这篇关于两个带有链接字段的插入查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
