How to sort out the fact that TransactionScope connection binding don't work as MSDN said?

Question

Here you can read:

Implicit Unbind The default. Causes the connection to detach from the transaction when it ends. After detaching, additional requests on the connection are performed in autocommit mode. The Current property is not checked when executing requests while the transaction is active. After the transaction has ended, additional requests are performed in autocommit mode.

All my connections are using Implicit Unbind, the default.

I'm opening connections after the creation of the TransactionScope instance so all opened connections are part of the same transaction. So far so good. The problem came when I tried to use one of those connections after calltransactionScope.Complete() and transactionScope.Dispose() methods, I get the following exception:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

The exception can be avoided closing and reopening the connection after the calls to the methods transactionScope.Complete() and transactionScope.Dispose(), but my issue is that I can't know in the code that uses the connections after the call transactionScope.Complete() and transactionScope.Dispose() if a Transaction Scope was created and if those connections participated in it.

Basically is not true the fact that Implicit Unbind The default. Causes the connection to detach from the transaction when it ends.: one have to close and reopen the connections after the transaction ends before submit a new command for a connection to get an actually detaching.

TransactionScope transactionScope = new TransactionScope();

SqlConnection connection1 = new SqlConnection(connectionString);
connection1.Open();

// Use connection1

SqlConnection connection2 = new SqlConnection(connectionString);
connection2.Open();

// Use connection1

transactionScope.Complete();
transactionScope.Dispose();

// The use of connection1 without close it and reopened it raise an exception

Of course that this is a simplification of my real code, the POST SCOPE uses of connection1 is not in the same class of the IN SCOPE uses of connection1 and I want to avoid have to add dependencies or global variables to solve this technical problem. Basically my connection container class is unaware of the fact that the connection it contain participated in a transaction as part of its live cycle, and I want keep in my design that in that way.

Answer 1

Implicit Unbind only does what it says when the transaction is non-distributed - that is, when only one connection uses that transaction and the server supports promotable transactions (which IIRC is SQL Server 2005 or later).

In your code example, when the second connection is opened and automatically enlists in the transaction, the transaction is promoted to a distributed transaction. From that point on, "Implicit Unbind" no longer applies, and the connection will no longer implicitly unbind from the transaction. I think you might be able to explicitly unbind using EnlistTransaction(null), sorry I'm unable to check at the moment.

This leak in the abstraction is usually not visible, because the recommended practice is to use 'using' blocks as in the below example which will cause the connection to be disposed before the transaction is completed (and therefore there is no chance to use the connection after the transaction is completed).

using (TransactionScope transactionScope = new TransactionScope())
{
    using (SqlConnection connection1 = GetConnection())
    {
        connection1.Open();

        // Use connection1
        ExecuteCommand(connection1);

        using (SqlConnection connection2 = GetConnection())
        {
            connection2.Open();
        }

        // Use connection1
        ExecuteCommand(connection1);
    }

    transactionScope.Complete();
}