回档后ExecuteNonQuery返回值
回档后ExecuteNonQuery返回值
提问于 2013-02-25 23:36:26
假设我们有一个类似以下内容的存储过程：
BEGIN TRANSACTION
UPDATE sometable SET aField = 0 WHERE anotherField = 1;
UPDATE sometable SET aField = 1 WHERE anotherField = 2;
ROLLBACK TRANSACTION;从C#我们得到了类似这样的东西：
using (var connection = new SqlConnection("connection string"))
{
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "my_procedure";
var res = cmd.ExecuteNonQuery();
}为什么我得不到res == -1？我仍在获取受影响的行数。当文档声明为"If a rollback occurs, the return value is also -1"时
我错过了什么？
回答 1
Stack Overflow用户
发布于 2013-02-26 22:54:59
似乎ExecuteNonQuery的返回值不受回滚的影响，即使文档中清楚地说明了这一点。以下是一些可能的解决方法。
1)使用ExecuteScalar
SP：
DECLARE @RowCount INT
DECLARE @Error INT
BEGIN TRAN
UPDATE Table1 SET Value1 = NULL
SELECT @RowCount = @@ROWCOUNT, @Error = @@ERROR
IF @Error <> 0 BEGIN
ROLLBACK TRAN
SELECT -1
END ELSE BEGIN
COMMIT TRAN
SELECT @RowCount
ENDC#
using (SqlConnection dbConnection = new SqlConnection("Data Source=.;Initial Catalog=Database1;Integrated Security=True;MultipleActiveResultSets=True"))
{
dbConnection.Open();
using (SqlCommand command = dbConnection.CreateCommand())
{
command.CommandText = "QuickTest";
command.CommandType = CommandType.StoredProcedure;
rowsAffected = command.ExecuteScalar();
}
}2)使用返回/输出参数
SP: DECLARE @RowCount INT声明@Error INT
BEGIN TRAN
UPDATE Table1 SET Value1 = NULL
SELECT @RowCount = @@ROWCOUNT, @Error = @@ERROR
IF @Error <> 0 BEGIN
ROLLBACK TRAN
RETURN -1
END ELSE BEGIN
COMMIT TRAN
RETURN @RowCount
ENDC#
using (SqlConnection dbConnection = new SqlConnection("Data Source=.;Initial Catalog=Database1;Integrated Security=True;MultipleActiveResultSets=True"))
{
dbConnection.Open();
using (SqlCommand command = dbConnection.CreateCommand())
{
command.Parameters.Add(new SqlParameter() {Direction = ParameterDirection.ReturnValue });
command.CommandText = "QuickTest";
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
rowsAffected = command.Parameters[0].Value;
}
}3)将回滚/提交逻辑移动到代码中
这将使您能够确定是否发生了回滚，并在必要时输出值-1。事务语句需要从存储过程中删除。
SP：
UPDATE Table1 SET Value1 = NULLC#：
using (SqlConnection dbConnection = new SqlConnection("Data Source=.;Initial Catalog=Database1;Integrated Security=True;MultipleActiveResultSets=True"))
{
dbConnection.Open();
using (SqlTransaction tran = dbConnection.BeginTransaction())
{
using (SqlCommand command = dbConnection.CreateCommand())
{
command.Transaction = tran;
try
{
command.Parameters.Add(new SqlParameter() {Direction = ParameterDirection.ReturnValue });
command.CommandText = "QuickTest";
command.CommandType = CommandType.StoredProcedure;
rowsAffected = command.ExecuteNonQuery();
}
catch (Exception)
{
rowsAffected = -1;
throw;
}
tran.Commit();
}
}
}如前所述，@@ROWCOUNT值和ExecuteNonQuery结果都受到触发器的影响。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/15070579
复制相关文章
相似问题