SQL Transactions in .NET

In my last blog post, I talked about performing SQL transactions as part of the SQL statement itself.  Now I want to talk about an alternative method for implementing SQL transactions within .NET code.  Basically, the SqlTransaction object can perform the function of grouping together the execution of multiple SQL statements within .NET code.

Below is an example of a SqlTransaction object in action:

 //Create and open a connection to the datase
 using (SqlConnection conn = new SqlConnection(connStr))
 {
      conn.Open();

      //Next, create a sql transaction using our current connection.
      using (SqlTransaction transact = conn.BeginTransaction())
      {
           try
           {
                //Execute the first sql command
                string sqlStr1 =
                    "INSERT INTO TestTable1 (Field1) values ('This is a sample value.')";
                using (SqlCommand cmd1 = new SqlCommand(sqlStr1, conn, transact))
                {
                     cmd1.ExecuteNonQuery();
                }

                //Execute the second sql command
                string sqlStr2 =
                     "INSERT INTO TestTable2 (Field1, Field2) values ('Performing a test insert', 'Into SQL')";
                using (SqlCommand cmd2 = new SqlCommand(sqlStr2, conn, transact))
                {
                     cmd2.ExecuteNonQuery();
                }                //Execute the third sql command
                string sqlStr3 =
                     "INSERT INTO TestTable3 (Field1) values ('All of these inserts should be successful.')";
                using (SqlCommand cmd3 = new SqlCommand(sqlStr3, conn, transact))
                {
                     cmd3.ExecuteNonQuery();
                }                //Finally, make sure to commit the transaction
                transact.Commit();
           }
           catch (Exception)
           {
                //If there is a sql exception, we need to back out the transaction
                transact.Rollback();
                throw;
           }
      }
  }

As you can see, the SqlTransaction object is created through a SqlConnection object’s BeginTransaction method.   It must be included with all of the SqlCommand objects for all of the commands to function as part of the same transaction.  The Commit method must be called on the SqlTransaction object in order to complete the transaction.  If there is an exception thrown, it must be caught and dealt with using the Rollback method in the SqlTransaction object.  I hope that helps with any future SQL coding.

Richard Franzen
Sr. Developer
ImageSource, Inc. 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s