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

Answer 1

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