Two insert queries with linked fields

Question

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.

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?:

• insert values (module_id, author_id, approved, question) in 'questions'
• get last 'question_id' in 'questions'
• insert values (question_id, answer) in 'answers'
• update value (correct_answer_id) in 'questions'

code:

    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'

Answer 1

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();
   }
}

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());

    ..

 }

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.

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());
    }
    ....
}

Now you could run the last command that update the question with the lastAnswerID

(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)

Answer 2

Yes, the approach you outline is the most efficient. You will need to retrieve the value assigned to the AUTO_INCREMENT column of each row INSERTED. But be careful how you retrieve that value.

• insert a row into 'questions' table
• retrieve last_insert_id value assigned to AUTO_INCREMENT column
• insert row to 'answers' table, using retrieved value for 'question_id' column
• retrieve last_insert_id value immediately following insert of "correct answer" row
• update row in 'questions' to set 'correct_answer_id' column

MySQL provides the LAST_INSERT_ID() function. That's the mechanism that MySQL provides to retrieve the value assigned to an AUTO_INCREMENT column, following the successful execution of an INSERT statement. (With singleton inserts, it's very straightforward; it just has to be called immediately following the insert.)

Ref: http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

A lot of client libraries provide a builtin function to do this, so it's not necessary to prepare and execute a separate SELECT statement. (For example, with PHP, PDO provides lastInsertId, mysqli provides $insertid. It's likely that the C# connector for MySQL has a similar function.)