【转】数据库事物的操作

最新推荐文章于 2025-09-11 20:53:34 发布

转载最新推荐文章于 2025-09-11 20:53:34 发布 · 58 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/JuneZhang/archive/2011/03/14/1983343.html

文章标签：

#数据库

本文介绍如何使用SQL进行事务处理，包括直接操作及通过存储过程实现转账功能的方法，并展示了ADO.NET中事务的具体应用。

进来对事物作一下总结,常用代码如下：

 
      1--创建一个银行账户表 

      2create table bank1 

      3( 

      4id int identity(1,1) not null primary key, 

      5CurrentMoney int not null check(CurrentMoney >1), 

      6CurrentName nvarchar(10) 

      7)

下面就是事物的操作：

 
       01declare @sum int

       02set @sum =0 

       03begin tran 

       04  update bank1 set CurrentMoney = CurrentMoney -200 where CurrentName = 'zs'

       05  set @sum = @@error +@sum

       06  update bank1 set CurrentMoney = CurrentMoney+200 where CurrentName ='ls'

       07  set @sum =@@error +@sum

       08if(@sum >0) 

       09 begin

       10     rollback tran 

       11     print 'Error'

       12 end

       13else

       14 begin

       15  commit tran 

       16  print 'OD'

       17end

下面是利用存储过程操作事物

 
       01Create  PROC Proc_Tran 

       02  @money int, 

       03@fromName nvarchar(10), 

       04@toName nvarchar(10), 

       05@msg nvarchar(10) output 

       06as

       07 declare @errsum int

       08 set @errsum =0 

       09    begin tran 

       10          update bank1 set CurrentMoney =CurrentMoney -@money where CurrentName =@fromName 

       11          set @errsum=@errsum+@@error 

       12          update bank1 set CurrentMoney =CurrentMoney +@money where CurrentName = @toName 

       13          set @errsum =@errsum +@@error 

       14     if(@errsum >0) 

       15          begin

       16             rollback tran 

       17             print 'Error'

       18            set @msg ='操作失败'

       19           end

       20     else 

       21   begin

       22   commit tran 

       23   print 'OK'

       24   set @msg ='操作成功'

       25   end

       26declare @a nvarchar(10) 

       27exec Proc_Tran 10,'ls','zs' ,@msg =@a output

       28print @a

ADO.NET的方面操作

代码

SqlConnection con = new SqlConnection( " Data Source=.;Initial Catalog=Northwind;Integrated Security=True " );
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand( " Update bank1 set CurrentMoney = CurrentMoney - 200 where CurrentName =' " + this .txtFromName.Text.Trim() + " ' " ,con);
SqlCommand cmd1 = new SqlCommand( " update bank1 set CurrentMoney = CurrentMoney + 200 where CurrentName=' " + this .txtToName.Trim() + " ' " ,con);
SqlTransaction tran = con.BeginTransaction(); // 调用SqlConnection对象的BeginTransaction方法来实例化SqlTransaction对象
try
{
cmd.ExcuteNonQuery();
cmd1.ExcuteNonQuery();
tran.commit();
}
catch (SqlException ex)
{
tran.RollBack();

}

转自 http://www.cnblogs.com/hfliyi/archive/2010/12/11/hftarena.html

转载于:https://www.cnblogs.com/JuneZhang/archive/2011/03/14/1983343.html