c# - c# ExecuteReader 要求命令有一个事务，当分配给命令的连接处于挂起的本地事务中时

Question

我遇到了同样的错误，我看到多个线程有，在谷歌搜索 2 天后，我似乎无法解决这个问题，我不明白为什么。我简化了一些与 sqltransaction 部分无关的代码。

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

标签： c#sql-server

Answer 1

您忘记command.Transaction = sqlTran;在command.ExecuteNonQuery();您的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;
        }
}

编辑：sqlTran实际上，您的问题是，当第一个交易sqlTran尚未完成时，您正尝试对多个交易使用相同的交易。换句话说，您已经启动了另一个在您调用之前未提交的事务command.ExecuteNonQuery()

尝试sqlTran.Commit()在try您Command.ExecuteNonQuery()的PerformTransaction(). 这样，在您尝试执行另一个事务之前，当前事务就会被提交。

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