C#中实现事务

C#中实现事务事例 

 SqlTransaction mytran = null;//声明事务
        try
        {
            mytran = this.Conn.BeginTransaction();//开启事务
            SqlCommand sqlxiaofei = new SqlCommand();

              string sxiaofei = "sql语句'";

            sqlxiaofei = new SqlCommand(sxiaofei, this.Conn);
            sqlxiaofei.Transaction = mytran;

            int m = sqlxiaofei.ExecuteNonQuery();
            if (m > 0)
            {
                sxiaofei = “另一个sql语句"

                sqlxiaofei = new SqlCommand(sxiaofei, this.Conn);
                sqlxiaofei.Transaction = mytran;

                int i = sqlxiaofei.ExecuteNonQuery();
                if (i > 0)
                {

               //成功信息
                      mytran.Commit();//成功提交事务
                }
                else
                {
                    //失败信息
                }
            }
            else
            {
               //失败信息
            }
        }
        catch (SqlException ex)
        {
            mytran.Rollback();//回滚事务
            HttpContext.Current.Response.Write("事务操作出错，已回滚。系统信息：" + ex.Message);
            HttpContext.Current.Response.End();
        }

 

在存储过程中编写正确的事务处理代码

 

1. 在事务语句最前面加上set xact_abort on

  
   set xact_abort on

   begin tran
      update statement 1 ...
      update statement 2 ...
      delete statement 3 ...
   commit tran
   go



当 xact_abort 选项为 on 时，sql server 在遇到错误时会终止执行并 rollback 整个事务。

2. 在每个单独的DML语句执行后，立即判断执行状态，并做相应处理。

   
begin tran
      update statement 1 ...

      if @@error <> 0 begin
         rollback tran
         goto labend
      end

      delete statement 2 ...

      if @@error <> 0 begin
         rollback tran
         goto labend
      end

   commit tran
      labend:
   go

3. 在SQL Server 2005中，可利用 try...catch 异常处理机制

   
begin tran

   begin try
      update statement 1 ...
      delete statement 2 ...
   end try
   begin catch
      if @@trancount > 0
         rollback tran
   end catch

   if @@trancount > 0
      commit tran
   go


下面是个简单的存储过程，演示事务处理过程。

  
 create procedure dbo.pr_tran_inproc
   as
   begin
      set nocount on

      begin tran
         update statement 1 ...

         if @@error <> 0 begin
            rollback tran
            return -1
         end

         delete statement 2 ...

         if @@error <> 0 begin
            rollback tran
            return -1
         end

      commit tran

      return 0
   end
   go

 

附上： SQL Server事务全攻略