Can I get a reference to a pending transaction from a SqlConnection object?

Question

Hey,

Suppose someone (other than me) writes the following code and compiles it into an assembly:

  using (SqlConnection conn = new SqlConnection(connString)) 
  {
   conn.Open();
   using (var transaction = conn.BeginTransaction())
   {

    /* Update something in the database */
    /* Then call any registered OnUpdate handlers */
    InvokeOnUpdate(conn);

    transaction.Commit();
   }
  }

The call to InvokeOnUpdate(IDbConnection conn) calls out to an event handler that I can implement and register. Thus, in this handler I will have a reference to the IDbConnection object, but I won't have a reference to the pending transaction. Is there any way in which I can get a hold of the transaction? In my OnUpdate handler I want to execute something similar to the following:

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

 cmd.ExecuteNonQuery();
}

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".

Can I in any way enlist my SqlCommand cmd with the pending transaction? Can I retrieve a reference to the pending transaction from the IDbConnection object (I'd be happy to use reflection if necessary)?

Answer 1

The command object can only be assigned a transaction object using one of its constructors. You can go for the .NET 2.0 approach and use a TransactionScope object which is defined in the System.Transactions namespace (has a dedicated assembly).

   using System.Transactions;

    class Foo
    {   
        void Bar()
        {
            using (TransactionScope scope = new TransactionScope())
            {
                // Data access
                // ...
                scope.Complete()
            }
        }
    }

The System.Transactions approach uses in conjunction with SQL Server 2005 a lightweight transaction coordinator (LTM). Be careful not to use multiple connection objects in your transaction scope or the transaction will get promoted as it is seen as distributed. This more resource-intensive version of the transaction will then be handled by DTC.

Answer 2

Wow I didn't belive this at first. I am surprised that CreateCommand() doesn't give the command it's transaction when using local SQL Server transactions, and that the transaction is not exposed on the SqlConnection object. Actually when reflecting on SqlConnection the current transaction is not even stored in that object. In the edit bellow, I gave you some hints to track down the object via some of their internal classes.

I know you can't modify the method but could you use a TransactionScope around the method bar? So if you have:

public static void CallingFooBar()
{
   using (var ts=new TransactionScope())
   {
      var foo=new Foo();
      foo.Bar();
      ts.Complete();
   }
}

This will work, I tested using simillar code to yours and once I add the wrapper all works fine if you can do this of course. As pointed out watch out if more then one connection is opened up within the TransactionScope you'll be escalated to a Distributed Transaction which unless your system is configured for them you will get an error.

Enlisting with the DTC is also several times slower then a local transaction.

Edit

if you really want to try and use reflection, SqlConnection has a SqlInternalConnection this in turn has a Property of AvailableInternalTransaction which returns an SqlInternalTransaction, this has a property of Parent which returns the SqlTransaction you'd need.