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

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.

In case anyone is interested in the reflection code to accomplish this, here it goes:

    private static readonly PropertyInfo ConnectionInfo = typeof(SqlConnection).GetProperty("InnerConnection", BindingFlags.NonPublic | BindingFlags.Instance);
    private static SqlTransaction GetTransaction(IDbConnection conn) {
        var internalConn = ConnectionInfo.GetValue(conn, null);
        var currentTransactionProperty = internalConn.GetType().GetProperty("CurrentTransaction", BindingFlags.NonPublic | BindingFlags.Instance);
        var currentTransaction = currentTransactionProperty.GetValue(internalConn, null);
        var realTransactionProperty = currentTransaction.GetType().GetProperty("Parent", BindingFlags.NonPublic | BindingFlags.Instance);
        var realTransaction = realTransactionProperty.GetValue(currentTransaction, null);
        return (SqlTransaction) realTransaction;
    }

Notes:

• The types are internal and the properties private so you can't use dynamic
• internal types also prevent you from declaring the intermediate types as I did with the first ConnectionInfo. Gotta use GetType on the objects