回滚后返回的ExecuteNonQuery价值 [英] Return value of ExecuteNonQuery after rollback
问题描述
假设我们有一个存储过程,做喜欢的事,这样的:
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? 我仍然得到受影响的行数。当文档状态<一href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx">"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结果都受到触发。的
这篇关于回滚后返回的ExecuteNonQuery价值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!