sql中的Tran和C#中的TransactionScope介绍

TRY Begin Tran transaction DECLARE @ServiceOrderId INT SET @ServiceOrderId=73342 DECLARE @ErrorCount INT SET @ErrorCount = 0 --调整积分通订单状态 支付完成 UPDATE dbo.ServiceOrderNew SET OrderStatus = 2,UpdateDate = GETDATE() WHERE ServiceOrderID = @ServiceOrderId --调整订单详细 UPDATE dbo.ServiceOrderNewDetail SET DetailStatus = 2 WHERE ServiceOrderID = @ServiceOrderId Commit Tran transaction END TRY BEGIN CATCH ROLLBACK Tran transaction END CATCH

第二种写法 用错误积累：

DECLARE @ServiceOrderId INT 
SET @ServiceOrderId=73342
 
BEGIN TRAN  A
        DECLARE @ErrorCount  INT 
        SET @ErrorCount = 0
        --调整积分通订单状态 支付完成
        UPDATE dbo.ServiceOrderNew
        SET OrderStatus = 2,UpdateDate = GETDATE()
     
        WHERE  ServiceOrderID = @ServiceOrderId
        
        SET @ErrorCount=@ErrorCount+@@error

        --调整订单详细
        UPDATE dbo.ServiceOrderNewDetail
        SET DetailStatus = 2
        WHERE ServiceOrderID = @ServiceOrderId
       
        
        SET @ErrorCount=@ErrorCount+@@error
        --付款状态
        UPDATE dbo.ScorePayApply
        SET ApplyStatus = 2
        WHERE  ScorePayApplyID = (SELECT TOP 1 ScorePayApplyID FROM ServiceOrderNewDetail WHERE ServiceOrderID = @ServiceOrderId)
        
         SET @ErrorCount=@ErrorCount+@@error
       
         SET @ErrorCount=@ErrorCount+@@error
         
  IF @ErrorCount=0 
      BEGIN   
        COMMIT TRAN  A
      END 
  ELSE   
      BEGIN   
        ROLLBACK TRAN  A
      END 

 

第二 TransactionScope用法

 using (TransactionScope tran = new TransactionScope())
        {
            try
            {
                ServiceItemDataAccess.ServicesConfirmPayment(serviceOrderId, remark, serviceId);
                //提交事务
                tran.Complete();
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
            finally
            {
                tran.Dispose();
            }
        }

TransactionScope 是一个轻量级的事物，在using中，若程序报错，则自动回滚。tran.Complete();是指程序成功，提交。 tran.Dispose(); 释放和销毁。

 

sql中的Tran和C#中的TransactionScope介绍

标签：