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

Question

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)?

question from:https://stackoverflow.com/questions/417024/can-i-get-a-reference-to-a-pending-transaction-from-a-sqlconnection-object

Answer 1

Wow I didn't believe 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 similar 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.