ADO.net Transactions

.NET Transactions

Within .NET, transactions are managed with the System.Data.SqlClient.SqlTransaction class. Again, a transaction exists over a SqlConnection object – and thus all the SqlCommand objects you create using that connection. Let's look at a quick example:

public class TransactionDemo

{

   public TransactionDemo()

   {

   }

   [STAThread]

   public static void Main()

   {

      Demo1();

   }

   private static void Demo1()

   {

      SqlConnection db = new SqlConnection("connstringhere");

      SqlTransaction transaction;

      db.Open();

      transaction = db.BeginTransaction();

      try

      {

         new SqlCommand("INSERT INTO TransactionDemo " +

            "(Text) VALUES ('Row1');", db, transaction)

            .ExecuteNonQuery();

         new SqlCommand("INSERT INTO TransactionDemo " +

            "(Text) VALUES ('Row2');", db, transaction)

            .ExecuteNonQuery();

         new SqlCommand("INSERT INTO CrashMeNow VALUES " +

            "('Die', 'Die', 'Die');", db, transaction)

            .ExecuteNonQuery();

         transaction.Commit();

      }

      catch (SqlException sqlError)

      {

         transaction.Rollback();

      }

      db.Close();

   }

}

As you can see from this example, we first open a connection to the SQL database. We then call the BeginTransaction method on the connection, keeping a reference to the object that it returns. At this point, the connection is bound to the SqlTransaction object that was returned. This means that any SqlCommand executed on that connection will be within the transaction. You can see, within the try block, we create and execute three SqlCommand objects. You’ll notice, though, that in this case we’re using the strings, SqlConnection, SqlTransaction, overload of the SqlCommand constructor. This is because the SqlCommand object requires passing in the transaction bound to a connection – failing to do so will cause an exception to be thrown. This is, in my opinion, a weakness of the model – since a transaction is bound on a per-connection basis, and a SqlCommand object should be able to simply pull the SqlTransaction object out of the supplied SqlConnection.

In the example above, the first two SqlCommand executes are perfectly valid – TransactionDemo exists in the database. The CrashMeNow table, however, does not. Since the table does not exist, a SqlException object will be thrown on ExecuteNonQuery object. It is important to realize that having a transaction "does not replace the standard exception handling mechanism". If you think your statements might not execute, you have to catch SqlException and, within your catch block, rollback the transaction.

There are essentially, two operations you can use on the SqlTransaction object. Rollback will cancel your transaction, undoing all the changes that have been made. Commit will cause the transaction to be written to the database permanently. Either case will end the transaction.

If you execute the code above and look at the TransactionDemo table, you’ll see that no rows have been added – the transaction was rolled back after the exception was thrown. If you remove the offending line of SQL, though, and run the program and look again, you’ll see that two rows have been added. That is, essentially, Transactions in action.