.NET Junkie

The DbTransactionWrapper wraps a DbTransaction object. This means, it creates and opens a DbConnection and creates a transaction within that connection. Within the scope of the DbTransactionWrapper you can execute your commands and in the end call DbTransactionWrapper.Commit. But in the case of an exception it will rollback the transaction for you. The user code could look like this:

using (DbTransactionWrapper transaction =
    new DbTransactionWrapper("connection string"))
{
    DbHelper.ExecuteNonQuery(transaction, 
        "UPDATE SOMETHING");
    DbHelper.ExecuteNonQuery(transaction,
        "DELETE SOMETHING");

    transaction.Commit();
}

Right now you might think, “That’s nice, but what about the .NET 2.0 TransactionScope? It allows the above type of coding, but is much more powerful”. The TransactionScope indeed is very powerful, but it needs the Microsoft Distributed Transaction Coordinator Service, which sometimes makes it a little bit too powerful in certain situations. See the DbTransactionWrapper as a lightweight version of the TransactionScope without the overhead of the MSDTC and without the 'distributed' part :-).

Let me explain a bit about the wrapper. The DbTransactionWrapper implements IDisposable which allows us to write the using statement. In case of an exception, the wrapper will detect -during disposal- that Commit was never called and rolls back the transaction.

The DbTransactionWrapper is an abstract class (like the DbCommand and DbConnection), so the code above will in fact not compile. You will need to use a derived class. The nice thing about creating it as an abstract class is that it allows us to easily implement different flavors of the transaction wrapper. This is completely in line with ADO.NET 2.0. Here's the code you need for a transaction wrapper that works on a SQL Server database.

using System.Data;
using System.Data.SqlClient;

public class SqlTransactionWrapper :
    DbTransactionWrapper
{
    public SqlTransactionWrapper(string connstr)
        : base(new SqlConnection(connstr)) { }
 
    public SqlTransactionWrapper(string connstr,
        IsolationLevel iso)
        : base(new SqlConnection(connstr), iso) { }
}

An Oracle implementation looks like this:

using System.Data;
using System.Data.OracleClient;

public class OracleTransactionWrapper :
    DbTransactionWrapper
{
    public OracleTransactionWrapper(string connstr)
        : base(new OracleConnection(connstr)) { }

    public OracleTransactionWrapper(string connstr,
        IsolationLevel iso)
        : base(new OracleConnection(connstr), iso)
    { }
}

As you can see, the only thing you'll have to do is to create a class that derives from DbConnection (like SqlConnection and OracleConnection) and use it as an argument in the base constructor. Optionally you can build a factory class that returns a transaction wrapper based on some configuration settings. (The Microsoft Patterns & Practices group has build the Enterprise Library which contains such factory class in the Data Access Application Block).

But enough talking here, let's look at the code for the DbTransactionWrapper.

using System;
using System.Data;
using System.Data.Common;

public abstract class DbTransactionWrapper : IDisposable
{
    private DbConnection connection;
    private DbTransaction transaction;
    private bool isCommitted;
    private bool isDisposed;
    private bool mustCloseConnection = true;
    private IsolationLevel isolationLevel;

    // Constructor 
    protected DbTransactionWrapper(DbConnection con)
        : this(con, IsolationLevel.Unspecified)
    {
    }

    // Constructor
    protected DbTransactionWrapper(DbConnection con,
        IsolationLevel iso)
    {
        if (con == null)
        {
            throw new ArgumentNullException("con");
        }

        this.isolationLevel = iso;

        this.mustCloseConnection =
            (con.State != ConnectionState.Open);

        this.connection = con;
    }

    public DbTransaction Transaction
    {
        get
        {
            if (this.transaction == null)
            {
                this.transaction = 
                    this.Connection.BeginTransaction(
                    this.isolationLevel);
            }

            return this.transaction;
        }
    }

    public DbConnection Connection
    {
        get
        {
            DbConnection con = this.connection;

            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }

            return con;
        }
    }

    // Implement an implicit cast operator so we
    // can use our class as a DbTransaction
    public static implicit operator DbTransaction(
        DbTransactionWrapper m)
    {
        return m.Transaction;
    }

    public void Commit()
    {
        if (this.isDisposed == true)
        {
            throw new ObjectDisposedException(this.GetType().Name);
        }

        if (this.isCommitted == true)
        {
            throw new InvalidOperationException(String.Format(
                "{0} has already been committed.",
                this.GetType().Name));
        }

        if (this.transaction != null)
        {
            this.transaction.Commit();
        }

        this.isCommitted = true;
    }

    public void Dispose()
    {
        this.Dispose(true);
        GC.SuppressFinalize(this);
    }

    protected virtual void Dispose(bool disposing)
    {
        if (disposing == false)
        {
            return;
        }

        if (this.isDisposed == true)
        {
            return;
        }

        this.isDisposed = true;

        Exception e = null;

        if (this.transaction != null &&
            this.isCommitted == false)
        {
            // Rollback the transaction.
            try
            {
                this.transaction.Rollback();
            }
            catch (Exception ex)
            {
                e = ex;
            }
        }

        // Dispose the Transaction
        try
        {
            if (this.transaction != null)
            {
                this.transaction.Dispose();
            }
        }
        catch (Exception ex)
        { 
            e = ex; 
        }

        this.transaction = null;

        // Dispose the Connection
        try
        {
            if (this.connection != null &&
                this.mustCloseConnection == true)
            {
                this.connection.Dispose();
            }
        }
        catch (Exception ex)
        { 
            e = ex; 
        }

        this.connection = null;

        if (e != null)
        {
            throw new InvalidOperationException(
                "Exception thrown while disposing", e);
        }
    }
}

I think the Dispose method needs some explanation. The trick here is to catch the exceptions that can be thrown on rolling back, closing and disposing the DbTransaction and DbConnection objects (as a sort of VBScript On Error Resume Next statement). This way we can continue disposing objects when for instance rolling back has failed.

There is however a small problem with this code. It is possible that multiple exceptions are thrown, while we currently only rethrow the last stored exception. Instead you might try rethrowing the first exception, because this little bugger could gives us more information about what went wrong.

Note that the exception e is encapsulated in an InvalidOperationException. We don't simply want to rethrow e, because this would truncate the stack trace (see Matt Gollob's blog or Bart de Smet's blog for more information).

Note also that we return immediately when the disposing argument is false. This argument will be false when the Dispose method is called by a finalizer method. During finalization, there is no guaranty that our managed objects (that we want to dispose) still exist, so in that case we simply can't dispose (or rollback) anything. That we didn't implement a finalizer method in our DbTransactionWrapper doesn't matter, because we also didn't seal our class! This means someone else could still implement a finalizer in a class that inherits from our wrapper. (more information about the dispose pattern can be found here).

UPDATE [2007-02-03]: I changed two things in the DbTransactionWrapper's code. I changed the code in such a way that given connection won't get opened within the constructor methods. This not only makes the code more readable, but as Joe Duffy points out in this blog, 'prefer acquisition of resources inside discrete methods' instead of acquiring them in constructors. Second, on disposal, the wrapped DbConnection will now be disposed instead of closed. The DbConnection's base class, Component, implements a finalize method. Not calling Dispose guaranteed the object to survive a garbage collection and it was therefore always promoted to the next generation. This leaded to to a larger memory footprint but more importantly a costly and most of all unnecessary finalization of the object, which of course is undesirable

UPDATE [2009-10-22]: It has been a couple of years since I posted this article and the knowledge I gained these years leads me to a different design of the DbTransactionWrapper class. Especially around the Dispose(bool) method. The Dispose method shouldn't do more than just dispose it's internal resources. It should not try to rollback the transaction as the given implementation does. There is actually no good reason to rollback, because a transaction will get rolled back, when it's not explicitly committed. Also, according to the Framework Design Guidelines, a dispose method should not throw an exception. This dispose method (or any dispose method for that matter) is also called in the case of a catastrophic error and in that situation it is well possible that the Rollback method will throw an exception. Throwing exceptions from Dispose method will make us loose the originally thrown exception, which is a bad thing.

That's about it for now. I hope I inspired some of you.

The code samples on my weblog are colorized using javascript, but you disabled javascript (for my website) on your browser. If you're interested in viewing the posted code snippets in color, please enable javascript.