I recently had to use System.Transactions to perform multiple database updates in a single atomic operation. At first I was going to use the native SQL Server transaction API, but I like the auto enlistment feature of System.Transactions – hey, one less line of code to write! The thing is, if you’re not careful you could end up dragging in the Distributed Transaction Coordinator (DTC), even though you’re only using a single database. How? Just open a database connection each time you perform an update. But wait a minute. Isn’t that what you’re supposed to do? The way to play nice with the connection pool is to open the connection just before you need it, then close it as soon as you’re done (probably by incorporating the SqlConnection into a C# “using” block). In that case, you’re not really closing the connection, just returning it to the pool, where it can be reused.

The problem with using this approach is that you need to hold a single connection open across all updates that you want to include in the transaction. That’s true whether you’re using the native SQL transaction API or the System.Transactions API. So you’re code might look something like this:

using(TransactionScope scope = new TransactionScope())
{
    using(SqlConnection cn = new SqlConnection(“connectionString;”))
    {
        cn.Open();
        ProcessUpdates1(cn);
        ProcessUpdates2(cn);
        ProcessUpdates3(cn);
    }
}

If this just leaves a bad taste in your mouth, you might create a “ConnectionScope” class that creates and opens a connection the first time, then subsequently returns the existing open connection. Alazel Acheson provides this sort of solution. Here’s my version – notice the [ThreadStatic] attribute, which adds thread affinity making thread synchronization unnecessary.

public
class
SqlConnectionScope : IDisposable
{
    [ThreadStatic]
    static
SqlConnection _connection = null;

    public SqlConnectionScope(string connString)
    {
        _connection = new
SqlConnection(connString);
        _connection.Open();
    }

    public
static
SqlConnection Current
    {
        get
        {
            return _connection;
        }
    }

    public
void Dispose()
    {
        _connection.Close();
    }
}

The code to use SqlConnectionScope would then look like this:

using (TransactionScope txScope = new
TransactionScope())
using (SqlConnectionScope cnScope = new
SqlConnectionScope(cnStr))
{
    ProcessUpdates1();
    ProcessUpdates2();
    ProcessUpdates3();
}

But inside of each method, instead of creating a new connection, you just get the current connection, like so:

SqlCommand cmd = new
SqlCommand(sql, SqlConnectionScope.Current);

You can download my sample code here.

The other thing that bugs me about System.Transactions is that resource managers like MSMQ do not support promotable transactions, so the DTC always gets dragged in, whether you like it or not. That means you’re back to using the native API if you don’t want to take the performance hit you get when the DTC gets involved. But don’t despair. Florin Lazar wrote some nifty code that lets you use System.Transactions with MSMQ and avoid a distributed transaction. Not only that, it also lets you use System.Transactions for local transactions with databases other than SQL Server 2005 (like SQL Server 2000 and Oracle). The secret sauce is a base class that implements IPromotableSinglePhaseNotification. Your code would looks like this:

using (TransactionScope scope = new
TransactionScope())
{
    using (MessageQueue queue = new
MessageQueue(“BevQueue”))
    {
        MessageQueueTransaction trans = new
MessageQueueTransaction();
        MessageQueueTransactionAdapter mqAdapter =
            new
MessageQueueTransactionAdapter(trans);
        // Elided for clarity …
        queue.Send(message, bev.ToString(), trans);
    }
}

I fixed one a bug in Florin’s code (he failed to call transaction.Begin in Connect), then incorporated his TransactionAdapter.cs in my project. Download the code here.

Go now, and transact in peace.