SqlConnection.CreateCommand() - Auto-Enlist in Pending Local Transaction

[bgribaudo]

When CreateCommand() is called on a SqlConnection that is in a transaction, the SqlCommand returned by that method is not associated with the connection's transaction. Instead, the command must be explicitly informed of the transaction or an exception similar to the following will be thrown when the command is executed:

MethodInvocationException: Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader 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."

Cost of the Manual Wire-Up

• The need to manually associate the command with the connection's transaction adds a small amount of code clutter.

    using (var connection = new SqlConnection(connectionString)) {
        connection.Open();
        var transaction = connection.BeginTransaction();
  
        using (var command = connection.CreateCommand()) {
            command.Transaction = transaction; // code clutter
    }
  

• More significantly, the need to explicitly set up this association when a transaction is used means that modifying a high-level component to use transactions requires code changes to lower-level components sharing the same SqlConnection, even when these lower-level components are transaction-agnostic.

    class Service
    {
        public void Process()
        {
            /*
              Modifying Process() to use transactions isn't as simple as uncommenting the below 
              because component also needs to be modified to wire up its SqlCommand to use the 
              transaction even though component may not care whether it is in a transaction. 
           */
            
            // using (var transaction = connection.BeginTransaction())
            // {
                component.Process(connection);
  
            //    transaction.Commit();
            // }
        }
    }
  

• All methods executing SqlCommands on the connection when the transaction is in use must have access to the transaction object. SqlConnection does not provide a public property exposing its transaction, so the object either must be passed around or accessed via reflection.

    using (var command = connection.CreateCommand()) {
        command.Transaction = connection.Transaction; // not possible because connection does not publically expose the transaction
    }
  

Proposal

When CreateCommand() is called on a SqlConnection in a transaction, the SqlCommand it returns should be associated with the connection's transaction, eliminating the need for the explicit wire-up.

[Added: 2019-06-03]:
Alternately (perhaps better yet), change command execution so that it automatically uses the transaction associated with the connection when it's executed. With this approach, any command (whether or not created by CreateCommand()) will execute against the proper transaction. Existing logic requires that the connection's transaction is used; the change proposed here is for the command to automatically use this transaction vs. the current behavior of the command throwing an exception when the transaction hasn't been explicitly referenced.

[divega]

As recently announced in the .NET Blog, focus on new SqlClient features an improvements is moving to the new Microsoft.Data.SqlClient package. For this reason, we are moving this issue to the new repo at https://github.com/dotnet/SqlClient. We will still use https://github.com/dotnet/corefx to track issues on other providers like System.Data.Odbc and System.Data.OleDB, and general ADO.NET and .NET data access issues.

[bgribaudo]

After a cursory check, it seems like the core essence of implementing the alternate proposal would be to change a couple lines in SqlCommand from checking whether the command's transaction is set when the connection's transaction is set to setting the command's transaction to match the connection's transaction, if the former is unset and the latter is set.

Change:

if (_activeConnection.HasLocalTransactionFromAPI && (null == _transaction))
  throw ADP.TransactionRequired(method); 

To something like:

if (_activeConnection.HasLocalTransactionFromAPI && (null == _transaction))
  _transaction = // appropriate method to get connection's transaction … maybe something based off of SqlInternalConnectionTds.SqlInternalTransaction

[Wraith2]

The change is quite simple as you point out but it changes default behaviour which means it isn't backwards compatible. Given the wide usage of this library you can be certain that someone somewhere has (incorrectly) used the behaviour that would change as a certainty and would be broken by changing it.

That said, the move from System.Data.SqlClient to Microsoft.Data.SqlClient isn't drop-in compatible so it does provide an idea opportunity for such a change.

[CodeAngry]

[Added: 2019-06-03]:
Alternately (perhaps better yet), change command execution so that it automatically uses the transaction associated with the connection when it's executed. With this approach, any command (whether or not created by CreateCommand()) will execute against the proper transaction. Existing logic requires that the connection's transaction is used; the change proposed here is for the command to automatically use this transaction vs. the current behavior of the command throwing an exception when the transaction hasn't been explicitly referenced.

This would be very useful just having SqlCommand use the SqlConnection's Transaction without any need for the developer to specify. Especially since you can't execute a transaction'less Command on a transaction'ed Connection. This should all be wired internally.

[cheenamalhotra]

cc @saurabh500

[CodeAngry]

This such basic and useful thing (and most likely very easy to implement for anyone slightly involved in developing the SqlClient) is pending since 2016. And now was again moved to a new milestone. Quite amazing!

[Wraith2]

I see this sort of comment far too often. If it's so easy why don't you contribute a fix for it instead of complaining?

[ErikEJ]

@CodeAngry you should be grateful that it is planned for the next milestone, it could have been Future or "won't fix'

[CodeAngry]

@ericstj The problem is not whether it is or is not planned or if I should be grateful. I coded against the stream on this for years now always having to drag the Transaction around when create commands. The problem is the milestone can keep on moving. It just moved again. It can be 3 more years before anything happens on such small but very useful feature. It's always the Microsoft way. It's all perfect but there's always some quality of life thing missing that baffles anyone that has used MS APIs or Software long enough and also makes you write a lot of pointless extra code.

@Wraith2 If you see this sort of comment far too often... it should make you think Nobody likes to waste time writing comments on Github. I'll take a look at the code during this weekend. But as I said, if I was on that team, already knowing the code, this should be a very quick and logical fix (I would not call it improvement).

I'll also check back in a few years to see how this goes.