C# 事务

在执行sql语句或存储过程时，为了避免执行出错，对数据库进行错误修改。为sql语句加上事务，出错时回滚。

两种方法：

一：在C# 代码中加事务例如

public static int UpdatePassWord(UserModel userModel)
       {
           int num = 0;
           OleDbConnection Conn = DBHelper.Connection;
           OleDbCommand Comm = new OleDbCommand("", Conn);
           OleDbTransaction trans = Conn.BeginTransaction();
           Comm.Transaction = trans;
           try
           {
               string sql = "UPDATE Users1 SET [PassWord]=@PassWord where LoginName = @LoginName";

               OleDbParameter[] paras ={
                                       
                                     new OleDbParameter("@password",Translate.Encrypt(userModel.PassWord)),
                                     new OleDbParameter("@loginname",Translate.Encrypt(userModel.LoginName)),
                                    };
               //int num = DBHelper.ExecuteCommand(sql, pares);
               //return num;
               Comm.CommandText = sql;
               Comm.Parameters.AddRange(paras);
               num = Comm.ExecuteNonQuery();
               if (num == 0)
                   throw new Exception("保存出错，稍后重试");
               trans.Commit();
           }
           catch (Exception ex)
           {
               trans.Rollback();
               num = 0;
           }
           finally
           {
               Conn.Close();
           }
           return num;
       }

 

二：在存储过程中加事物，例如

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[PosKind_Add]
(
@kind varchar(100),
@number varchar(50),
@ShowOrder int
)
 AS
declare @CurrentError int
begin transaction

insert into PosKind(kind,number,ShowOrder)Values(@kind,@number,@ShowOrder)


select @CurrentError = @@Error
if @CurrentError != 0
begin
rollback transaction
goto ErrorHead
end
commit transaction

ErrorHead:

return