我有一个存储过程,用事务从c#代码中调用。当我在C#中运行代码(它是控制台应用程序)时,在catch块中没有得到结果,而是抛出了异常:
批处理结束时检测到不可提交的事务。事务被回滚。
C#代码:
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace app1
{
class Program
{
static void Main(string[] args)
{
string res = "";
string resDesc = "";
res = WriteToDB(1,out resDesc);
Console.WriteLine(res);
Console.WriteLine(resDesc);
Console.Read();
}
public static string WriteToDB(int val, out string resultDesc)
{
resultDesc = "";
string result = "";
string connectionString = ConfigurationManager.ConnectionStrings["SqlAppConnection"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction("transcation1");
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = transaction.Connection;
cmd.Transaction = transaction;
cmd.CommandText = "usp_Test_Proc";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@check", val);
SqlParameter res = cmd.Parameters.Add("@result", SqlDbType.Int);
res.Direction = ParameterDirection.Output;
SqlParameter resDesc = cmd.Parameters.Add("@resultDesc", SqlDbType.VarChar, 100);
resDesc.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
result = res.Value.ToString().Trim();
resultDesc = resDesc.Value.ToString();
transaction.Commit();
}
}
catch (Exception ex)
{
result = "Exception";
resultDesc = ex.Message;
transaction.Rollback();
}
}
return result;
}
}
}
存储过程:
ALTER PROCEDURE [dbo].[usp_Test_Proc] (
@check int,
@result INT output,
@resultDesc VARCHAR(100)
output)
AS
BEGIN
SET nocount ON;
SET xact_abort ON;
IF @check != 0
BEGIN
BEGIN try
SET @result = 0;
SET @resultDesc = 'aa';
--RAISERROR('Error from raiserror',1,1)
THROW 99001, 'Error from throw', 1;
END try
BEGIN catch
SET @result = 1;
SET @resultDesc = concat('catch block',ERROR_MESSAGE());
END catch;
END
ELSE
BEGIN
SET @result = 0;
SET @resultDesc = 'done';
end
END;
GO
当存储过程出现错误时,我没有进入catch块,而是收到了一个异常,上面写着“在批处理结束时检测到不可提交的事务。该事务已被回滚。” 但是如果我在SSMS中运行存储过程,它就像预期的那样工作:
![enter image description here](https://istack.dev59.com/08TeK.webp)
Catch
块中,如果存储过程超时并且从未到达catch块,则还应该有一个IF @@TRANCOUNT>0 ROLLBACK
(需要)。 - Razvan Socol