With the introduction of the System.Transactions namespace in .Net 2.0, working with transactions became dead easy. All you need to do is wrap the section of code you want to be transacted in a TransactionScope, and the framework will take care of the rest. There’s one problem though – if you open more than one database connection during the scope of the transaction, then the transaction escalates to a two-phase transaction, which means enlisting the help of the the Microsoft Distributed Transaction Coordinator service. Apart from the obvious overhead of communicating with this unmanaged service on your database server, there’s another reason for wanting to avoid this – it might not be running. If you’re hosting your application on a shared web host for instance, chances are their database server will have the MDTC disabled, and chances are they won’t enable it for you either. So what can a poor programmer do then?

He can be clever about reusing the connections when working inside a transaction. This article will show how we can write a custom DbProviderFactory proxy that intercepts any CreateConnection() calls and reuses connections while inside a transaction scope. The beauty of this implementation is that, as long as you’re using the DbProviderFactories (which you really should!) to create your connections, it will be totally transparent to your code. This is important because a transaction might be a high level concept in your code, far removed from the data access layer.

DbProviderFactory, Please

By implementing our data access layer so that it always queries the current DbProviderFactory for a connection, then there is one central place where connections are created, vastly simplifying what we want to do. Imagine then, that we have a DataStore that looks something like this:

public class DataStore
{
private string _connectionString;
private DbProviderFactory _dbProviderFactory;
public static readonly DataStore Instance = new DataStore();
private DataStore()
{
_dbProviderFactory = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings.ProviderName);
_connectionString = ConfigurationManager.ConnectionStrings.ConnectionString;
}
// datastore methods here...
}

This is in fact the constructor from my DataStore API (simplified for clarity), which you can read more about here. Notice how we load up a DbProviderFactory by reading a few configuration settings, and storing them for use later. Our app.config looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="Iridescence.Data.DataStore" providerName="System.Data.SqlClient" connectionString="..."/>
</connectionStrings>
</configuration>

The providerName attribute tells the DataStore which factory to use – in this case the SqlClient factory, which is the Ado.Net factory for Sql Server. Now, whenever the DataStore needs a connection, all it has to do is call the CreateConnection() method on the factory. Having our DataStore implemented in this way enables us to ensure there is one central place where connections are created – and more importantly, a plugable one which we can substitute the implementation of.

Implementing a Transaction Aware DbProviderFactory

Having this extensibility point in our code, we can then take advantage of it to incorporate connection reuse into the DataStore. All we need to do, is write a custom DbProviderFactory that knows when CreateConnection should create a new connection, and when it should keep returning the same instance several times to reuse an existing connection. Here’s the code we need:

public sealed class DbConnectionScope : DbProviderFactory
{
private readonly DbProviderFactory _realFactory;
private readonly Dictionary<Transaction, DbConnection> _transactionConnections = new Dictionary<Transaction, DbConnection>();
///<summary>
    /// Creates a new instance of the DbConnectionScope class
    ///</summary>
    ///<param name="factoryToScope">The factory to scope</param>
    public DbConnectionScope(DbProviderFactory factoryToScope)
{
_realFactory = factoryToScope;
}
///<summary>
    ///Returns a new instance of the provider's class that implements the <see cref="T:System.Data.Common.DbConnection"></see> class.
    ///</summary>
    ///
    ///<returns>
    ///A new instance of <see cref="T:System.Data.Common.DbConnection"></see>.
    ///</returns>
    ///<filterpriority>2</filterpriority>
    public override DbConnection CreateConnection()
{
Transaction transaction = Transaction.Current;
// we're not currently in a transaction, so we just let the real factory work normally
        if(null == transaction)
{
return _realFactory.CreateConnection();
}
else // we're in a transaction, so we want to ensure connection reuse
        {
DbConnection connection;
if (!_transactionConnections.TryGetValue(transaction, out connection))
{
// this is the first time this transaction requests a connection. 
                // Lets create it, wrapping it in a ScopedConnectionProxy which will 
                // ensure that any call to Close does not result in actually closing the connection 
                // (because we want to reuse the open connection throughout the entire transaction scope)
                connection = new ScopedConnectionProxy(_realFactory.CreateConnection());
_transactionConnections.Add(transaction, connection);
// we need to know when the transaction ends, so we can close the connection.
                transaction.TransactionCompleted += transaction_TransactionCompleted;
}
return connection;
}
}
void transaction_TransactionCompleted(object sender, TransactionEventArgs e)
{
DbConnection connection;
// transaction has ended, remove it from the cache and ensure it is disposed
        if(_transactionConnections.TryGetValue(e.Transaction, out connection))
{
_transactionConnections.Remove(e.Transaction);
connection.Dispose();
}
}
}

We’ve implemented our factory using the transparent proxy pattern, so that it wraps the actual DbProviderFactory and extends it with connection reuse functionality whenever a transaction is present. There is one problem left to tackle however, and that is keeping the connection alive – because most likely, the DataStore will do the following whenever it requests a connection:

using (IDbConnection connection = _dbProviderFactory.CreateConnection()
{
using (IDbCommand command = connection.CreateCommand())
{
// prepare and execute command
    }
}

This is of course a good thing as it ensures we don’t leave any connections lying around – they’re scarce resources, after all – but it causes a problem for our factory; the connection will be closed and disposed, so when we return the same instance the next time, it won’t be very reusable, which kinds of defeats what we were trying to accomplish… Resorting to the transparent proxy pattern again, we can solve this by wrapping the connection returned in a proxy that ensures that any call to Close and Dispose is only effective if the transaction has ended:

private class ScopedConnectionProxy : DbConnection
{
private readonly DbConnection _realConnection;
public ScopedConnectionProxy(DbConnection realConnection)
{
_realConnection = realConnection;
}
/// <summary>
    /// Closes the connection if connection reuse is disabled or we're not currently inside a transaction.
    /// </summary>
    public override void Close()
{
Transaction transaction = Transaction.Current;
if (null == transaction || transaction.TransactionInformation.Status != TransactionStatus.Active)
{
_realConnection.Close();
}
}
protected override void Dispose(bool disposing)
{
Transaction transaction = Transaction.Current;
// only dispose if transaction has ended. Otherwise, 
        //transaction end event will dispose the connection for us
        if (null == transaction || transaction.TransactionInformation.Status != TransactionStatus.Active)
{
_realConnection.Dispose();
base.Dispose(disposing);
}
}
}

And there you have it. The only change needed in our DataStore to active the connection reuse, is to wrap the DbProviderFactory in our proxy:

private DataStore()
{
_dbProviderFactory = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings.ProviderName);
_dbProviderFactory = new DbConnectionScope(_dbProviderFactory);
_connectionString = ConfigurationManager.ConnectionStrings.ConnectionString;
}

Why Transactions Escalate

To end off, I’d like to point out that this solution does not guarantee that the transaction will not escalate. What really happens when you open up a second connection, is that a second entity takes part in the transaction, and this is the reason for the requirement to escalate it. As long as there’s only one entity involved, that entity can take ownership of the transaction and coordinate it, but when more than one entities are involved a distributed transaction coordinator is needed to hold the reins. For the most part when dealing with the kind of transactions discussed here, which relate to updating stuff in the database, the connections are the only entities that will be involved. But its perfectly possible to introduce other entities – for instance you might write your own in-memory object transaction manager by implementing the IEnlistmentNotification interface and enlisting it with the transaction to ensure that a roll-back also reverts the object to keep it in synch with the database. That might be a good post for later 🙂

Get the Source Code

You can download my DbConnectionScope implementation here. I threw this together in half an hour this evening, so its not really been tested thoroughly. In particular, I’ve not really implemented the DbProviderFactory proxy fully, and only focused on the CreateConnection method. Look out for a follow-up post on this topic later, which will also include the DataStore API update that takes advantage of the proxy.

Oh yes, and in case this is the last post this year, I’d like to wish everyone a merry Christmas and a happy new year! Cheers 🙂