c# - 在批处理结束时检测到不可提交的事务。事务被回滚

Question

我有一个存储过程，我使用事务从 C# 代码调用它。当我在 C# 中运行代码(它是一个控制台应用程序)时，我没有从 catch block 中获取结果，而是抛出了一个异常:

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

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 block ，而是收到一个异常返回消息“在批处理结束时检测到不可提交的事务。事务被回滚。”

但是如果我在 SSMS 中运行存储过程，它会按预期工作:

为什么我从 C# 代码调用它时结果不同？

Answer 1

这是因为您设置了“set xact_abort on”。根据documentation xact_abort 用于指定当 Transact-SQL 语句引发运行时错误时 SQL Server 是否自动回滚当前事务。

您还需要在 throw 中将 'state' 值设置为 -1使交易变得无法接受。