在SQL Server 2014年左右的存储过程使用的TransactionScope与交易 [英] Using TransactionScope around a stored procedure with transaction in SQL Server 2014

问题描述

我使用C＃和ADO.Net在一个ASP.Net应用一个的TransactionScope 来运行事务。 。本次交易是应该保存在多个表的一些数据，然后发送电子邮件到订户

问：这是一个有效的使用的TransactionScope ，当它包含到在SQL Server 2014年自己的事务存储过程的调用，或者我应该删除SQL事务语句即的begin tran ，提交TRAN 和 ROLLBACK TRAN从存储过程语句中这个<$被称为C $ C>的TransactionScope ？

对于这种情况，也是存储过程的T-SQL代码的C＃代码均如下所述。

使用的TransactionScope C＃代码：

 试
 {
使用（TransactionScope的范围=新的TransactionScope（））
 {
使用（SqlConnection的连接1 =新的SqlConnection（connectString1））
 {
 //打开连接，自动登记它在
 //的TransactionScope作为一个轻量级的交易。 
 connection1.Open（）; 
 
 // SaveEmailData是里面出现
的SqlCommand命令1 =新的SqlCommand（SaveEmailData，连接1）事务的存储过程; 
 command1.CommandType = CommandType.StoredProcedure; 
 command1.ExecuteNonQuery（）; 
 
} 
 
 //使用辅助方法，
 EmailHelper.SendCustomerEmails（customerIds）发送电子邮件; 
 
 //完整的方法提交事务。如果一个异常被抛出，
 //完全不叫和事务回滚。 
 scope.Complete（）; 
 
} 
} 
赶上（异常前）
 {
 Logger.Log（除息）; 
} 
  

存储过程的T-SQL SaveEmailData ：

  SET NOCOUNT ON 
 
 BEGIN TRY 
 DECLARE @emailToUserId BIGINT 
 
 BEGIN TRAN 
  -  // update语句。详细声明省略
 UPDATE TABLE1 ... 
 
 --update声明。详细声明省略
 UPDATE TABLE2 ... 
 
如果@@ TRANCOUNT> 0 
 BEGIN 
 COMMIT TRAN 
端
端TRY 
 
 BEGIN CATCH 
 
如果@@ TRANCOUNT> 0 
 BEGIN 
 ROLLBACK TRAN 
端
 
 EXEC Error_RaiseToADONET 
 
端CATCH 
  

解决方案

是的TransactionScope 包装一个TS​​QL <时，仍可以正常工作code> BEGIN / COMMIT事务或ADO SqlConnection.BeginTransaction 。当包装单个连接，其行为类似于嵌套交易的Sql ：

• @@ TRANCOUNT 将在每个递增 BEGIN TRAN




• COMMIT TRAN 只会递减 @@ TRANCOUNT 。如果 @@ TRANCOUNT 降为零的交易才会被提交。

然而

• ROLLBACK TRAN 将退出整个事务（即<一HREF =https://msdn.microsoft.com/en-us/library/ms187967.aspx?f=255&MSPPError=-2147217396相对=nofollow> @@ TRANCOUNT零），除非您正在使用保存点（即节约交易XX ... ROLLBACK TRANSACTION XX 。

此外，在使用存储过程时，你会如果连接的 @@ TRANCOUNT 退出从它有当值的存储过程时不同，收到错误进入一个存储过程。

因此，通常更容易离开事务语义为的TransactionScope 并移除任何手动 BEGIN TRAN / COMMIT塞满你的TSQL TRAN 逻辑。

I am using C# and ADO.Net with a TransactionScope to run a transaction in an ASP.Net app. This transaction is supposed to save some data across multiple tables and then send an email to subscribers.

Question: is it a valid use of TransactionScope, when it includes a call to a stored procedure that has its own transaction in SQL Server 2014, or should I remove the SQL transaction statements i.e. begin tran, commit tran and rollback tran statements from the stored procedure being called within this TransactionScope?

The C# code for this scenario and also the T-SQL code of stored procedure are both mentioned below.

C# code using TransactionScope:

  try 
    {
        using (TransactionScope scope = new TransactionScope())
        {
            using (SqlConnection connection1 = new SqlConnection(connectString1))
            {
                // Opening the connection automatically enlists it in the  
                // TransactionScope as a lightweight transaction.
                connection1.Open();

                // SaveEmailData is a stored procedure that has a transaction within it
                SqlCommand command1 = new SqlCommand("SaveEmailData", connection1);
                command1.CommandType = CommandType.StoredProcedure;
                command1.ExecuteNonQuery();

            }

            //Send Email using the helper method
            EmailHelper.SendCustomerEmails(customerIds);

            // The Complete method commits the transaction. If an exception has been thrown, 
            // Complete is not  called and the transaction is rolled back.
            scope.Complete();

        }
    }
    catch( Exception ex)
    {
       Logger.Log(ex);
    }

T-SQL of stored procedure SaveEmailData:

SET NOCOUNT ON

    BEGIN TRY
        DECLARE @emailToUserId BIGINT

        BEGIN TRAN
        -- //update statement. detail statement omitted
        UPDATE TABLE1...

         --update statement. detail statement omitted
        UPDATE TABLE2...

        IF @@trancount > 0
        BEGIN
            COMMIT TRAN
        END
    END TRY

    BEGIN CATCH

        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN
        END

        EXEC Error_RaiseToADONET

    END CATCH

解决方案

Yes, TransactionScope can still work when wrapping a TSQL BEGIN / COMMIT TRANSACTION or an ADO SqlConnection.BeginTransaction. When wrapping a single connection, the behaviour is similar to nesting transactions in Sql:

• @@TranCount will be incremented on each BEGIN TRAN

• COMMIT TRAN will simply decrement @@TRANCOUNT. The transaction will only be committed if @@TRANCOUNT hits zero.

however

• ROLLBACK TRAN will abort the whole transaction (i.e. @@TRANCOUNT to zero), unless you are using Save Points (i.e. SAVE TRANSACTION xx ... ROLLBACK TRANSACTION xx.

Also, when using stored procedures, you will receive an error if the connection's @@TRANCOUNT differs when exiting the SPROC from the value it had when entering a SPROC.

As a result, it is typically much easier to leave transaction semantics to TransactionScope and remove any manual BEGIN TRAN / COMMIT TRAN logic from cluttering up your TSQL.

这篇关于在SQL Server 2014年左右的存储过程使用的TransactionScope与交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！