为SqlCommand和SqlDataAdapter加事务仍遇ExecuteNonQuery事务报错

你的错误根源有两个：

1. SqlDataAdapter自动生成的SelectCommand未绑定事务：当你用new SqlDataAdapter(batchCommand.CommandText, connection)初始化适配器时，它会自动创建一个SelectCommand，这个命令没有关联你开启的事务，但当前连接已经处于事务中，执行这个命令时就会触发报错。
2. 复用同一个SqlCommand作为Update和Insert命令：插入和更新的业务逻辑通常不同，复用同一个命令会导致逻辑混乱，甚至参数不匹配的问题。

修正后的代码 #

using (SqlConnection connection = Database.GetConnection(connString))
{
    connection.Open(); // 必须先打开连接再开启事务
    SqlTransaction transaction = connection.BeginTransaction();

    try
    {
        // 单独创建更新命令
        var updateCommand = new SqlCommand("sp_update_name", connection, transaction);
        updateCommand.CommandType = CommandType.StoredProcedure;
        updateCommand.UpdatedRowSource = UpdateRowSource.None;
        updateCommand.Parameters.Add("@param1", SqlDbType.Int, 4, dt.Columns[0].ColumnName);
        // 按需添加更新所需的其他参数

        // 单独创建插入命令
        var insertCommand = new SqlCommand("sp_insert_name", connection, transaction);
        insertCommand.CommandType = CommandType.StoredProcedure;
        insertCommand.UpdatedRowSource = UpdateRowSource.None;
        insertCommand.Parameters.Add("@param1", SqlDbType.Int, 4, dt.Columns[0].ColumnName);
        // 按需添加插入所需的其他参数

        // 用无参构造初始化DataAdapter，避免自动生成未绑定事务的SelectCommand
        SqlDataAdapter adpt = new SqlDataAdapter();
        adpt.UpdateCommand = updateCommand;
        adpt.InsertCommand = insertCommand;

        adpt.UpdateBatchSize = 100;

        int recordsImpacted = adpt.Update(dt);

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

关键修改点 #

• 显式打开连接后再开启事务：避免事务开启时的隐式连接状态异常。
• 为插入、更新操作分别创建独立SqlCommand：确保各自的存储过程和参数匹配业务逻辑。
• 使用无参SqlDataAdapter构造函数：避免自动生成未绑定事务的SelectCommand，若需SelectCommand，需手动创建并绑定事务。
• 创建命令时直接传入transaction参数：一步到位确保命令与事务关联，无需额外设置属性。

内容的提问来源于stack exchange，提问作者Kavi