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

Levity

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.

Saintmihiel

Answer 2

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

Wolfgang

Checking currentTransaction before proceeding might be a good idea. if(currentTransaction == null ) return null; – Transhumance

-1 Using this code I ended up in situation like this: insert, insert, silent random rollback without any exception in my code, more inserts outside of transaction – Chandless

I've tried the code above and it will only work if you have an actuall SqlConnection, it will not work at the interface level (IDbConnection) and will not work for OleDbConnection, OracleConnection, or any other implementations. You guys think we can make this work at the interface level (IDbConnection)? It would also be helpfull if not necessary to get the current active IDbCommand as well. – Overhang

Answer 3

For anybody who is interested in the C# version of the decorator class that Denis made in VB.NET, here it is:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace DataAccessLayer
{
    /// <summary>
    /// Decorator for the connection class, exposing additional info like it's transaction.
    /// </summary>
    public class ConnectionWithExtraInfo : IDbConnection
    {
        private IDbConnection connection = null;
        private IDbTransaction transaction = null;

        public IDbConnection Connection
        {
            get { return connection; }
        }

        public IDbTransaction Transaction
        {
            get { return transaction; }
        }

        public ConnectionWithExtraInfo(IDbConnection connection)
        {
            this.connection = connection;
        }

        #region IDbConnection Members

        public IDbTransaction BeginTransaction(IsolationLevel il)
        {
            transaction = connection.BeginTransaction(il);
            return transaction;
        }

        public IDbTransaction BeginTransaction()
        {
            transaction = connection.BeginTransaction();
            return transaction;
        }

        public void ChangeDatabase(string databaseName)
        {
            connection.ChangeDatabase(databaseName);
        }

        public void Close()
        {
            connection.Close();
        }

        public string ConnectionString
        {
            get 
            {
                return connection.ConnectionString; 
            }
            set 
            {
                connection.ConnectionString = value;
            }
        }

        public int ConnectionTimeout
        {
            get { return connection.ConnectionTimeout; }
        }

        public IDbCommand CreateCommand()
        {
            return connection.CreateCommand();
        }

        public string Database
        {
            get { return connection.Database; }
        }

        public void Open()
        {
            connection.Open();
        }

        public ConnectionState State
        {
            get { return connection.State; }
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            connection.Dispose();
        }

        #endregion
    }
}

Shenitashenk

I wouldn't use this. You could instantiate one of these after you already have an active transaction on the connection, and in that case this wouldn't work. – Sangfroid

You're right, this works only if you pass a connection that doesn't have an active transaction... for your case a better solution is to create the new connection in the ConnectionWithExtraInfo constructor, instead of receiving an already created connection as a parameter. The best thing would be to have direct access to the transaction from the connection object, and this decorator class wouldn't be needed at all... – Shenitashenk

Your decorator is missing a finalizer. In the event that Dispose isn't called, the GC will call it with a finalizer. Your class doesn't have that, but the SqlConnection object does. – Dyspeptic

Answer 4

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.

Unbelieving

Answer 5

I am a big proponent of simple so how about writing a wrapper over IDBConnection (DELEGATE PATTERN) that exposes Transaction. (Sorry for VB.NET code, I am writing this in VB.NET right now) Something like this:

  Public class MyConnection
      Implements IDbConnection

      Private itsConnection as IDbConnection
      Private itsTransaction as IDbTransaction

      Public Sub New(ByVal conn as IDbConnection)
         itsConnection = conn
      End Sub

      //...  'All the implementations would look like
      Public Sub Dispose() Implements IDbConnection.Dispose
         itsConnection.Dispose()
      End Sub
      //...

      //     'Except BeginTransaction which would look like
       Public Overridable Function BeginTransaction() As IDbTransaction Implements IDbConnection.BeginTransaction
         itsTransaction = itsConnection.BeginTransaction()
         Return itsTransaction
       End Function  


      // 'Now you can create a property and use it everywhere without any hacks
       Public ReadOnly Property Transaction
          Get
              return itsTransaction
          End Get
       End Property

    End Class

So you would instantiate this as:

Dim myConn as new MyConnection(new SqlConnection(...))

and then you can get the transaction any time by using:

 myConn.Transaction

Alsup

I may be mistaken, but it seems to me that your suggestion requires that I can have the third-party library that I am using instantiate a MyConnection instead of a plain SqlConnection. Unfortunately, I don't have the source code and it doesn't support dependency injection in any shape or form. – Levity

Not sure what you mean. In your case your "MyConnection" class would take SqlConnection, OleConnection or what have you as an argument. No injection is necessary. Have a look at how the "Delegation Design Pattern" works as in here: en.wikipedia.org/wiki/Delegation_pattern – Alsup

I see what you mean. Your case is difficult because you are letting a 3rd create your connection, in my case I am creating many connections but they are global in my app so I am constantly losing track of who is opening what transactions and I'd like to keep transactions with my connection. In your case I guess after your third-party created connection is passed into MyConnection then the transactions will be tracked. You have to figure out if the 3rd party created a transaction before you got a hold of conn so you would have little choice but to use reflection to find the initial transaction. – Alsup

As long as you can get a hold of the connection early enough and put the wrapper around it (if property is not readonly), you should be golden. It's kind of amazing that the third-party doesn't give you any access to the connection at creation or an event when the connection is created or when a transaction is started/ended. Maybe you can create the connection and pass it to the 3rd party app to substitute for its own? Maybe you can use reflection to set the 3rd party's connection property or internal variable to MyConnection? – Alsup

Just had another thought, assuming your third-party app has a sql driver-to-use in some config file maybe you can point it to use MyConnection (add a constructor with no arguments to instantiate a SQLConnection inside MyConnection) and this should be loadable by the third-party app - thereby allowing you to inject MyConnection instead of SQLConnection – Alsup

Answer 6

In case where anyone faced this problem on .Net 4.5 you can use Transaction.Current in System.Transactions.

Mauri

That retrieves the current TransactionScope, not the SqlTransaction. Similar, but different technology. – Imposing

Where do you see in the question that he needs SqlTransaction and not TransactionScope ? – Mauri

The first 5 lines of his 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())" – Imposing