c# ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction

Question

I am having the same error i see multiple threads having, after googling for 2 days i cannot seem to fix the issue and i am failing to see why. I simplified some of the code that did not pertain to the sqltransaction portion.

    conn.Open();

using (SqlTransaction sqlTran = conn.BeginTransaction())
{
    SqlCommand command = new SqlCommand("", conn, sqlTran);
    log.Info("Connection Opened");


    foreach (var eventData in reqData)
    {
        ProcessMessage(eventData, req, conn, log, sqlTran, command);
    }

    sqlTran.Commit();
    sqlTran.Dispose();
}
conn.Close();

public static void ProcessMessage(JObject messageData, HttpRequestMessage req, SqlConnection conn, TraceWriter log, SqlTransaction sqlTran, SqlCommand command)
{
        //query building stuff
        PerformTransaction(req, query, conn, log, sqlTran, command);
}

public static void PerformTransaction(HttpRequestMessage req, string query, SqlConnection conn, TraceWriter log, SqlTransaction sqlTran, SqlCommand command)
{
     try
        {
            command.ExecuteNonQuery();
            log.Info("Query Executed Successfully");
        }
     catch
        {
            log.Info("Transaction Execution Error");
            sqlTran.Rollback();
            throw;
        }
}

Given that your error message mentions `ExecuteReader` I am inclined to think that the problem is not in this block of code as there is no call to `ExecuteReader`. Perhaps you are calling it elsewhere in the same transaction but forgot to set the sqlTran on the command object? — JayV, Jul 05 '18 at 14:41
It was this, i cannot mark a comment as an answer but our talk did help me organize my code better also. — user1552172, Jul 05 '18 at 15:26
I am glad to be of assistance. For info, your original set of statements are perfectly fine for the multiple inserts in one transaction. I have used a similar method for inserts, deletes and updates — JayV, Jul 05 '18 at 18:10

Jimenemex · Answer 1 · 2018-07-05T14:28:45.403

1

~~You forgot to add command.Transaction = sqlTran; right before you command.ExecuteNonQuery(); in your PerformTransaction().~~

public static void PerformTransaction(HttpRequestMessage req, string query, SqlConnection conn, TraceWriter log, SqlTransaction sqlTran, SqlCommand command)
{
     try
        {
            command.Transaction = sqlTran;
            command.ExecuteNonQuery();
            log.Info("Query Executed Successfully");
        }
     catch
        {
            log.Info("Transaction Execution Error");
            sqlTran.Rollback();
            throw;
        }
}

EDIT: Actually your problem is that you are trying to use the same sqlTran for multiple transactions when the first transaction that sqlTran did has not finished yet. In other words, You have started another transaction that is not commited before you called command.ExecuteNonQuery()

Try moving your sqlTran.Commit() to be inside the try block after you Command.ExecuteNonQuery() in your PerformTransaction(). That way, the current transaction get's commited before you try to execute another.

public static void PerformTransaction(HttpRequestMessage req, string query, SqlConnection conn, TraceWriter log, SqlTransaction sqlTran, SqlCommand command)
{
     try
        {
            command.ExecuteNonQuery();
            log.Info("Query Executed Successfully");
            sqlTran.Commit();
            log.Info("Transaction Commited Successfully");
        }
     catch
        {
            log.Info("Transaction Execution Error");
            sqlTran.Rollback();
            throw;
        }
}

edited Jul 05 '18 at 14:28

answered Jul 05 '18 at 14:17

Jimenemex

3,104
3
24
56

didn't he specify the transaction in the constructor of the command though? – Cato Jul 05 '18 at 14:23
@Cato Just noticed. – Jimenemex Jul 05 '18 at 14:24
is the tran and connection properly connected? It would be possible to pass just tran and get connection from tran.connection property. Are nested transactions possible? (sorry never used them) – Cato Jul 05 '18 at 14:28
my goal is to have multiple transactions and if any of them failed to roll them all back, is this not possible? – user1552172 Jul 05 '18 at 14:34
@user1552172 Yes, just create multiple transactions and call them all. Instead of using the same Transaction. If they all run successfully then you can commit them all. – Jimenemex Jul 05 '18 at 14:46
Interesting, i will give that a shot. – user1552172 Jul 05 '18 at 14:48
@user1552172 running multiple commands in a single transaction is EXACTLY the way you want to be going as per your description. Either all commit, or all fail. Perhaps clarify if you mean a Business Transaction or Database transaction. – JayV Jul 05 '18 at 14:54
database, i have maybe 10 sql statements at once (varies) that i want to successfully happen before committing. How would i do this @JayV i had the same thought that multiple could run but the error says otherwise :( – user1552172 Jul 05 '18 at 14:56
@user1552172 Use a single SqlTransaction then. See this question/answer for guidance on this one point. https://stackoverflow.com/a/17019525/9365244. And, please see my comment on your question. – JayV Jul 05 '18 at 14:57
@JayV ill get it like the link and see if it works, and i am using execute reader in one spot....thats most likely it. i did not see your comment sorry. – user1552172 Jul 05 '18 at 15:01

c# ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction

1 Answers1