回滚后返回的ExecuteNonQuery价值

本文介绍了回滚后返回的ExecuteNonQuery价值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

假设我们有一个存储过程，做喜欢的事，这样的：

Assuming that we have a stored procedure that does like something this:

BEGIN TRANSACTION UPDATE sometable SET aField = 0 WHERE anotherField = 1; UPDATE sometable SET aField = 1 WHERE anotherField = 2; ROLLBACK TRANSACTION;

和C＃中，我们有这样的事情：

And from C# we have something like this:

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

为什么我没有收到获得资源== -1？ 我仍然得到受影响的行数。当文档状态"If发生回滚，返回值也为-1

Why I'm not getting getting res == -1? I'm still getting the number of affected rows. When the documentation states "If a rollback occurs, the return value is also -1"

什么，我在这里失踪？

推荐答案

看来返回值的ExecuteNonQuery 不受即使文件明确指出，回滚IS一样。下面是一些可能的解决方法。

It appears that the return value of ExecuteNonQuery is unaffected by a rollback even though the documentation clearly states that is does. Here are some possible workarounds.

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 END

C＃的

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     DECLARE @Error INT

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 RETURN -1 END ELSE BEGIN COMMIT TRAN RETURN @RowCount END

C＃的

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）将回滚/提交逻辑到code

这会给你的能力，以确定是否发生回退和输出必要的-1时的值。本次交易的语句就需要从存储过程中删除。

This would give you the ability to determine if a rollback occurred and output a value of -1 when necessary. The transaction statement would need to removed from the sproc.

SP：的

UPDATE Table1 SET Value1 = NULL

C＃：的

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

正如previously的@@ ROWCOUNT值，为ExecuteNonQuery结果都受到触发。的