In last week's post about transactions, I provided an initial implementation that is transaction aware, but couldn't close the SqlConnection that was in use.  I'm going to outline here what the final solution looks like.

First, most of the functionality was refactored in a few reusable functions, leaving the data access code looking like this instead:

public static void ExecuteNonQuery(string connectionStringName, CommandType commandType, string commandText, params SqlParameter[] parameters)
{
    if (Transaction.Current == null)
    {
        string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = CreateCommand(connection, commandType, commandText, parameters))
            {
                command.ExecuteNonQuery();
            }
        }
    }
    else
    {
        SqlConnection connection = GetTransactionSqlConnection(connectionStringName);
        using (SqlCommand command = CreateCommand(connection, commandType, commandText, parameters))
        {
            command.ExecuteNonQuery();
        }
    }
}

The new function, GetTransactionSqlConnection, is responsible for checking to see if a shared connection should be used.

private static SqlConnection GetTransactionSqlConnection(string connectionStringName)
{
    LocalDataStoreSlot connectionDictionarySlot = Thread.GetNamedDataSlot("ConnectionDictionary");
    Dictionary<string, SqlConnection> connectionDictionary = (Dictionary<string, SqlConnection>) Thread.GetData(connectionDictionarySlot); 

    if (connectionDictionary == null)
    {
        connectionDictionary = new Dictionary<string, SqlConnection>();
        Thread.SetData(connectionDictionarySlot, connectionDictionary);
    }
    SqlConnection connection = null;

    if (connectionDictionary.ContainsKey(connectionStringName))
    {
        connection = connectionDictionary[connectionStringName];
    }
    else
    {
        string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        connection = new SqlConnection(connectionString);
        connectionDictionary.Add(connectionStringName, connection); 
        Transaction.Current.TransactionCompleted += new TransactionCompletedEventHandler(Current_TransactionCompleted);
    }

    return connection;
}

Instead of trying to store a single connection, a Dictionary is used instead so that multi-connection transactions can be supported.  Also, notice that the TransactionCompleted event is wired up; that's how we'll close the close the connection.

private static void Current_TransactionCompleted(object sender, TransactionEventArgs e)
{
    LocalDataStoreSlot connectionDictionarySlot = Thread.GetNamedDataSlot("ConnectionDictionary");
    Dictionary<string, SqlConnection> connectionDictionary = (Dictionary<string, SqlConnection>) Thread.GetData(connectionDictionarySlot);
    if (connectionDictionary != null)
    {
        foreach (SqlConnection connection in connectionDictionary.Values)
        {
            if (connection != null && connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }
        }

        connectionDictionary.Clear();
    }

    Thread.FreeNamedDataSlot("ConnectionDictionary");
}

You can find the full implementation in my SharpCore project on SourceForge if you're interested.