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.

I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Sep 16 '14 at 04:43
Why do you need to run both queries using the same connection? Have you tried committing the transaction right after the last command that uses it? — JeremyWeir, Sep 16 '14 at 04:44
@JeremyWeir: The deployment enviroment is giving timeouts when I use multiple connections and I have no control over that environment. I would rather have the whole function fail as a result and get user to retry. And this is just an example, I have a string of 5 - 6 commands running in the transaction. I would like to log every step individually for logging/tracing purposes. — Uknight, Sep 16 '14 at 04:47
I don't see the difference between using 3 connections in the logical flow. But if you really need the same connection, try committing the transaction before running another query not using the transaction. Also, people usually assume incorrectly that opening/closing connections frequently hurts performance. .NET uses connection pooling, so reusing connections will more often hurt performance. The rule is to return connections to the pool asap. More about pooling... http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx — JeremyWeir, Sep 16 '14 at 04:54
@JeremyWeir: I don't see the difference either, I get your point. However, the provided environment (including the database) is not under my control. Multiple connections get time-d out occasionally and my best control is to use a single connection. My commands are dependent on the data the previous command inserts and that's why I'm using the transaction. Isn't that the purpose? That's why I'm only committing right at the end of the try. — Uknight, Sep 16 '14 at 04:55

Jared Moore · Answer 1 · 2015-03-03T04:47:38.833

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();
    }
}

Hi Jared, thank you for contributing an answer, though it did add anything new to what I have already discussed with the other users. — Uknight, Mar 03 '15 at 09:53
Ok. Is there anything else around this scenario that I can clarify? :) — Jared Moore, Mar 03 '15 at 18:09
Since there's no way to share a connection without removing/including the transaction, I guess it's a lost cause to attempt that. Thanks anyway. — Uknight, Mar 04 '15 at 03:51
Why call `Dispose()` when you can use a `using` block instead? — Deantwo, Mar 07 '18 at 12:33

Single SqlConnection with Transaction, multiple commands situation

1 Answers1