Easier Database Transactions - Extending the Using Statement to Perform Automatic Database Transactions

• Download source files - 18.1 Kb

Overview

Any user who works with database updates uses transactions. Transactions in ADO.NET are done using a transaction object, and a try..catch, but there is an easier, one might even say a more C# way to handle database transactions.

Visual Basic .NET Users

Because this technique relies on the using statement which is C# specific, this technique cannot be applied in Visual Basic .NET. However, rumors are that Visual Studio .NET 2005 will include a similar statement for Visual Basic.

Transactions the Normal Way

Here is an example of a database transaction the normal way. This is a condensed version of the example on MSDN.

using (OleDbTransaction xTx = _DB.BeginTransaction()) {
    try {
        OleDbCommand xCmd = _DB.CreateCommand();
        xCmd.Transaction = xTx;
        xCmd.CommandText = "Insert into Person (Name, Telephone)" + 
                           " values ('Normal One', '(412) 555-1212')";
        xCmd.ExecuteNonQuery();
        if (chckThrow.Checked) {
            throw new Exception("Test Exception");
        }
        xCmd.CommandText = "Insert into Person (Name, Telephone)" + 
                           " values ('Normal Two', '(423) 555-1212')";
        xCmd.ExecuteNonQuery();
        xTx.Commit();
    }
    catch (Exception xException) {
        xTx.Rollback();
        throw xException;
    }
}

Transactions the Easy Way

With my method, the code does the same, but becomes a bit shorter, cleaner, and even a bit more C# like.

using (Transaction xTx = new Transaction(_DB)) {
    OleDbCommand xCmd = _DB.CreateCommand();
    ((IDbCommand)xCmd).Transaction = xTx.DBTransaction;
    xCmd.CommandText = "Insert into Person (Name, Telephone)" + 
                       " values ('Object One', '(412) 555-1212')";
    xCmd.ExecuteNonQuery();
    if (chckThrow.Checked) {
        throw new Exception("Test Exception");
    }
    xCmd.CommandText = "Insert into Person (Name, Telephone)" + 
                       " values ('Object Two', '(423) 555-1212')";    
    xCmd.ExecuteNonQuery();
    xTx.Commit();
}

Demo Project

The demo project demonstrates this and allows simulated exceptions. The demo project uses the included Access database file for simplicity. However, the included class works with any ADO.NET data source.

How it Works

The using statement in C# causes the dispose to be called when the block is exited. By implementing a Dispose, and checking to see if the transaction has been committed, the transaction class can then determine whether or not to call rollback. There is no need to rethrow any exception that may be in progress, because C# automatically continues the exception path as part of the handling of the using block.

Transaction Class Source

public class Transaction : IDisposable {

    private IDbConnection _DB;
    private IDbTransaction _DBTransaction;

    public IDbTransaction DBTransaction {
      get { return _DBTransaction; }
    }

    public Transaction(IDbConnection aDB) : this(aDB, true) {
    }

    public Transaction(IDbConnection aDB, bool aHandle) {
      if (aHandle) {
        _DB = aDB;
        _DBTransaction = _DB.BeginTransaction();
      }
    }

    public void Commit() {
      if (_DB != null) {
        _DBTransaction.Commit();
        _DBTransaction.Dispose();
        _DB = null;
      }
    }

    public void Dispose() {
      if (_DB != null) {
        _DBTransaction.Rollback();
        _DBTransaction.Dispose();
        _DB = null;
      }
    }
}

Conclusion

This class purely adds syntactic sugar to your code and makes performing transactions a little bit easier. The functionality is the same. But when working with transactions and having to manually create this structure each time, the shorter version is a lot easier to type, as well as read later when working in the code again.