Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- 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'
- using(MySqlConnection conn = new MySqlConnection(connStr))
- {
- conn.Open();
- using(MySqlTransaction tr = conn.BeginTransaction())
- {
- ...
- // MySqlCommand code goes here
- ...
- tr.Commit();
- }
- }
- 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());
- ..
- }
- 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.VarChar);
- 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());
- }
- ....
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement