Archive for January, 2009

How to get a pending transaction from a SqlConnection


This blogpost originated from a question I asked on stackoverflow.

I’ve been building an application on top of a CMS lately. This CMS has a number of extension points. In particular, when certain business objects are updated, I can have the system call a custom handler for this event. In my custom handler I want to perform and persist my own data access operations. If I just new up a SqlConnection and use this for my data access, I run into two problems:

  • The changes that I make will not be enclosed in the transaction that the CMS is using for its updates. Thus, my updates might be persisted while the CMS’ changes are rolled back or vice versa, resulting in my business objects’ state being corrupted.
  • If I am updating database rows related to the business objects updated by the CMS, it is very likely that my updates will block on a lock owned by the CMS’ transaction. This will result in a deadlock, since the CMS won’t commit its transaction until my custom handler has finished.

Okay, so surely the guys building the CMS must have provided some contextual information for the custom handler? Well, sort of. Implementing the custom handler is a matter of implementing and registering a method with signature

void OnAfterUpdate(IDbConnection conn)

Hence, in OnAfterUpdate, the obvious thing to do would be something like

private void OnUpdateHandler(IDbConnection conn)
var cmd = conn.CreateCommand();
.CommandText = someSQLString;
.CommandType = CommandType.Text;

However, the call to cmd.ExecuteNonQuery() throws an InvalidOperationException complaining that

ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized

So we’ll need to provide a reference to the CMS’ pending transaction through the IDbCommand.Transaction property on cmd. You might suspect that this would be as straightforward as

cmd.Transaction = conn.CurrentTransaction;

While I think this would certainly be a reasonable expectation, things aren’t so simple. The IDbConnection object doesn’t have a public accessor providing access to the current transaction.
Of course, using reflection, you can get a hold of the transaction, and here is how:

The SqlConnection has a private member InnerConnection of type System.Data.SqlClient.SqlInternalConnectionTds, which is an internal type of System.Data.dll. The SqlInternalConnectionTds in turn has a member called AvailableInternalTransaction of type System.Data.SqlClient.SqlInternalTransaction. Finally SqlInternalTransaction has a Parent property of type System.Data.SqlClient.SqlTransaction, which is the transaction we’re looking for. Thus, the transaction may be retrieved as follows:

private SqlTransaction FetchTransaction(SqlConnection conn)
Type sqlConnectionType = conn.GetType();
object innerConnection =
BindingFlags.NonPublic |
          BindingFlags.Instance).GetValue(conn, null);
object sqlInternalTransaction =
BindingFlags.NonPublic |
BindingFlags.Instance).GetValue(innerConnection, null);
SqlTransaction transaction =
BindingFlags.NonPublic |
BindingFlags.Instance).GetValue(sqlInternalTransaction, null);
return transaction;

I have no idea why it must be this hard to get a hold of the transaction, but if you happen to understand the inner workings of SQL connections and transactions and can provide an explanation, please leave a comment.