1

How can i use transaction in single function for multiple insert and update queries? for example:

private void button1_Click(object sender, EventArgs e)
    {
        // transaction
        SqlConnection objConnection = new SqlConnection(annual_examination_system.Properties.Settings.Default.connString);
        SqlTransaction objTransaction = null;

        int count = 0;

        try 
        {
          objConnection.Open();
          objTransaction = objConnection.BeginTransaction();

          string q1 = "update query"
          SqlCommand objCommand1 = new SqlCommand(q1, objConnection, objTransaction);
          objCommand1.ExecuteNonQuery();
          objTransaction.Commit();

          string q2 = "insert query"
          SqlCommand objCommand1 = new SqlCommand(q2, objConnection, objTransaction);
          objCommand1.ExecuteNonQuery();
          objTransaction.Commit();
        }

        catch (Exception ex)
        {
           objTransaction.Rollback();
            MessageBox.Show(ex.Message);
            MessageBox.Show("Exception, Row :" + count);
            MessageBox.Show("Transaction Rollback.");
        }

        finally
        {
            // Close the connection.
            if (objConnection.State == ConnectionState.Open)
            {
                objConnection.Close();
            }
        }

now there are two queries one is for update and one is for insert. so do i have to apply transaction separately on them or one transaction can be applied on both?

Umama Khalid
  • 93
  • 1
  • 11
  • If you need both `update` and `insert` to be commited or rolled back as a single transaction then use only `commit` after the last `ExecuteNonQuery`. – Serg Aug 11 '16 at 07:19
  • i have tried that but it gives error : timeout expired. – Umama Khalid Aug 11 '16 at 10:04

1 Answers1

2

You can use same transaction for both queries . Here is an example :

SqlTransaction tran;

Now using this for both the queries 

using(SqlConnection connection=new SqlConnection(connection_string))
{
    connection.Open();
    tran = connection.BeginTransaction();
    cmd = new SqlCommand(query1, connection, tran);
    cmd1 = new SqlCommand(query2, connection, tran);
    count = cmd.ExecuteNonQuery();
    count = count + cmd1.ExecuteNonQuery();
    tran.Commit();
}

Update : connection is not closing that's the problem. I have edited the code. Please see the update.

lakshmi prakasan
  • 330
  • 4
  • 12