C# ExecuteNonQuery 要求命令具有事务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8830637/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ExecuteNonQuery requires the command to have a transaction
提问by pothios
I am receiving this error message when i try to execute the following code.
当我尝试执行以下代码时收到此错误消息。
ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction
Can anyone advice where the problem is? I guess the root of the problem is the part where i try to execute a stored procedure.
任何人都可以建议问题出在哪里？我想问题的根源在于我尝试执行存储过程的部分。
The stored procedure is creates its own transaction when execute
存储过程在执行时创建自己的事务
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand command = conn.CreateCommand();
SqlTransaction transaction;
// Start a local transaction.
transaction = conn.BeginTransaction("createOrder");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
command.Connection = conn;
command.Transaction = transaction;
try
{
command.CommandText = "INSERT INTO rand_resupply_order (study_id, centre_id, date_created, created_by) " +
"VALUES (@study_id, @centre_id, @date_created, @created_by) SET @order_id = SCOPE_IDENTITY()";
command.Parameters.Add("@study_id", SqlDbType.Int).Value = study_id;
command.Parameters.Add("@centre_id", SqlDbType.Int).Value = centre_id;
command.Parameters.Add("@date_created", SqlDbType.DateTime).Value = DateTime.Now;
command.Parameters.Add("@created_by", SqlDbType.VarChar).Value = username;
SqlParameter order_id = new SqlParameter("@order_id", SqlDbType.Int);
//study_name.Value =
order_id.Direction = ParameterDirection.Output;
command.Parameters.Add(order_id);
command.ExecuteNonQuery();
command.Parameters.Clear();
//loop resupply list
for (int i = 0; i < resupplyList.Count(); i++)
{
try
{
SqlCommand cmd = new SqlCommand("CreateOrder", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@study_id", SqlDbType.Int).Value = study_id;
cmd.Parameters.Add("@centre_id", SqlDbType.Int).Value = centre_id;
cmd.Parameters.Add("@created_by", SqlDbType.VarChar).Value = username;
cmd.Parameters.Add("@quantity", SqlDbType.VarChar).Value = resupplyList[i].Quantity;
cmd.Parameters.Add("@centre_id", SqlDbType.Int).Value = centre_id;
cmd.Parameters.Add("@depot_id", SqlDbType.VarChar).Value = depot_id;
cmd.Parameters.Add("@treatment_code", SqlDbType.Int).Value = centre_id;
cmd.Parameters.Add("@order_id", SqlDbType.Int).Value = (int)order_id.Value;
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
transaction.Rollback();
ExceptionUtility.LogException(ex, "error");
throw ex;
}
catch (Exception ex)
{
transaction.Rollback();
ExceptionUtility.LogException(ex, "error");
throw ex;
}
finally
{
conn.Close();
conn.Dispose();
}
}
return (int)order_id.Value;
}
catch (Exception ex)
{
transaction.Rollback();
ExceptionUtility.LogException(ex, "error");
throw ex;
}
finally
{
// Attempt to commit the transaction.
transaction.Commit();
conn.Close();
conn.Dispose();
command.Dispose();
}
回答by Arian
using Connection String transaction not popular so far.you can delete every things that related to SqlTransactionand then wrap your code with TransactionScope
使用目前不流行的连接字符串事务。您可以删除与此相关的所有内容，SqlTransaction然后使用TransactionScope包装您的代码
回答by Afshin
when using transaction, you should use it everywhere.
使用事务时，应该到处使用它。
cmd.Transaction = transaction;
