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

.net, ado.net, c#, sql-server, transactions

Solution

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.

Problem

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

Original source