Single SqlConnection with Transaction, multiple commands situation

Question

I'm trying to use only a single connection and run two commands together, one using transaction and one without.

The one without is a trace/logging function as this solution is deployed in another location. So that when the process fails partly I can at least follow the logs.

I'll add my test code here:

SqlConnection connection = GetConnection();
SqlTransaction transaction = null;

try
{
    connection.Open();
    transaction = connection.BeginTransaction();

    SqlCommand logCommand = new SqlCommand("Log before main command", connection);
    logCommand.ExecuteNonQuery();

    string sql = "SELECT 1";
    SqlCommand command = new SqlCommand(sql, connection, transaction);
    int rows = command.ExecuteNonQuery();

    logCommand = new SqlCommand("Log after main command", connection);
    logCommand.ExecuteNonQuery();

    // Other similar code

    transaction.Commit();
    command.Dispose();
}
catch { /* Rollback etc */ }
finally { /* etc */ }

I'm getting an error:

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.

Is there any way to achieve what I'm trying to do without another transaction-less connection?

Or if there's a better suggestion to optimize my code with a single connection in a different way I'm open to learning about it.

Answer 1

The error is happening here:

SqlConnection connection = GetConnection();
SqlTransaction transaction = null;

try
{
    connection.Open();
    transaction = connection.BeginTransaction();

    SqlCommand logCommand = new SqlCommand("Log before main command", connection); // <--- did not give the transaction to the command
    logCommand.ExecuteNonQuery(); // <--- Exception: ExecuteNonQuery requires the command to have a transaction ...

    string sql = "SELECT 1";
    SqlCommand command = new SqlCommand(sql, connection, transaction);
    int rows = command.ExecuteNonQuery();

    logCommand = new SqlCommand("Log after main command", connection);
    logCommand.ExecuteNonQuery(); // <--- Same error also would have happened here

    // Other similar code

    transaction.Commit();
    command.Dispose();
}
catch { /* Rollback etc */ }
finally { /* etc */ }

The reason that this is happening is that when a connection is enlisted in a transaction, all commands on that connection are in that transaction. In other words, you cannot have a command "opt out" of being in a transaction, because transactions apply to the entire connection.

Unfortunately the SqlClient API is a little misleading because after calling connection.BeginTransaction() you still have to give the SqlTransaction to the SqlCommand. If you don't explicitly give the transaction to the command, then when you execute the command, SqlClient will chide you for it ("Don't forget to tell me about the transaction that I already know that I'm in!") which is the exception that you're seeing.

This clunkiness is one of the reasons that some people prefer to use TransactionScope, although personally I don't like TransactionScope for non-distributed transaction due to its 'implicit magic' API and its bad interaction with async.

If you do not want the 'log' commands to be in the same transaction as the main command, you must either use another connection for them, or just only have the transaction around for the duration of that main command:

try
{
    connection.Open();

    // No transaction
    SqlCommand logCommand = new SqlCommand("select 'Log before main command'", connection);
    logCommand.ExecuteNonQuery();

    // Now create the transaction
    transaction = connection.BeginTransaction();
    string sql = "SELECT 1";
    SqlCommand command = new SqlCommand(sql, connection, transaction);
    int rows = command.ExecuteNonQuery();
    transaction.Commit();

    // Transaction is completed, now there is no transaction again
    logCommand = new SqlCommand("select 'Log after main command'", connection);
    logCommand.ExecuteNonQuery();

    // Other similar code

    command.Dispose();
}
//catch { /* Rollback etc */ }
finally
{
    if (transaction != null)
    {
        transaction.Dispose();
    }
}

If you do want them to be part of the transaction, you have to explicitly hand the transaction to them:

SqlConnection connection = GetConnection();
SqlTransaction transaction = null;

try
{
    connection.Open();
    transaction = connection.BeginTransaction();

    SqlCommand logCommand = new SqlCommand("select 'Log before main command'", connection, /* here */ transaction);
    logCommand.ExecuteNonQuery();

    string sql = "SELECT 1";
    SqlCommand command = new SqlCommand(sql, connection, transaction);
    int rows = command.ExecuteNonQuery();

    logCommand = new SqlCommand("select 'Log after main command'", connection, /* and here */ transaction);
    logCommand.ExecuteNonQuery();

    // Other similar code

    transaction.Commit();
    command.Dispose();
}
//catch { /* Rollback etc */ }
finally
{
    if (transaction != null)
    {
        transaction.Dispose();
    }
}