.net 当分配给命令的连接处于挂起的本地事务中时,ExecuteNonQuery要求命令具有事务,在dbcall中

dwbf0jvd  于 2023-10-21  发布在  .NET
关注(0)|答案(1)|浏览(111)

这是我的代码我已经为命令和SqlDataAdapter添加了事务。但是我仍然得到这个错误“ExecuteNonQuery要求命令在分配给命令的连接处于挂起的本地事务中时具有事务。"我尝试用多种方法将事务添加到SqlCommand,但仍然得到相同的错误。

using (SqlConnection connection = Database.GetConnection(connString))
{
    SqlTransaction transaction = connection.BeginTransaction();

    try
    {
        var batchCommand = new SqlCommand("spname", connection, transaction);
        batchCommand.Transaction = transaction;

        batchCommand.Parameters.Add("@param1", SqlDbType.Int, 4, dt.Columns[0].ColumnName);

        batchCommand.CommandType = CommandType.StoredProcedure;
        batchCommand.UpdatedRowSource = UpdateRowSource.None;

        SqlDataAdapter adpt = new SqlDataAdapter(batchCommand.CommandText, connection);
        adpt.UpdateCommand = batchCommand;
        adpt.InsertCommand = batchCommand;
        adpt.InsertCommand.Transaction = transaction;
        adpt.UpdateCommand.Transaction = transaction;

        adpt.UpdateBatchSize = 100;

        int recordsImpacted = adpt.Update(dt);

        transaction.Commit();
    }
    catch (Exception)
    {
        transaction.Rollback();
        throw;
    }
}
goqiplq2

goqiplq21#

你可以尝试同样的例子:

using (SqlConnection connection = new SqlConnection(connString))
    {
        connection.Open();
        using (SqlTransaction transaction = connection.BeginTransaction())
        {
            using (SqlCommand batchCommand = new SqlCommand("spname", connection, transaction))
            {
                try
                {
                     batchCommand.Parameters.Add("@param1", SqlDbType.Int, 4, dt.Columns[0].ColumnName);

                     batchCommand.CommandType = CommandType.StoredProcedure;
                     batchCommand.UpdatedRowSource = UpdateRowSource.None;

                     SqlDataAdapter adpt = new SqlDataAdapter(batchCommand.CommandText, connection);
                     adpt.UpdateCommand = batchCommand;
                     adpt.InsertCommand = batchCommand;
                     adpt.InsertCommand.Transaction = transaction;
                     adpt.UpdateCommand.Transaction = transaction;

                     adpt.UpdateBatchSize = 100;

                    int recordsImpacted = adpt.Update(dt);
                    batchCommand.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                }
            }
        }
    }

相关问题