【转】数据库事物的操作 - 冬雨在路上

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

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

2
create table bank1 

3
( 

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

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

6
CurrentName nvarchar(10) 

7
)

下面就是事物的操作：

01
declare @sum int

02
set @sum =0 

03
begin 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

08
if(@sum >0) 

09
 begin

10
     rollback tran 

11
     print 'Error'

12
 end

13
else

14
 begin

15
  commit tran 

16
  print 'OD'

17
end

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

01
Create  PROC Proc_Tran 

02
  @money int, 

03
@fromName nvarchar(10), 

04
@toName nvarchar(10), 

05
@msg nvarchar(10) output 

06
as

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

26
declare @a nvarchar(10) 

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

28
print @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

posted on 2011-03-14 11:03 冬雨在路上阅读(320) 评论(0) 收藏举报

刷新页面返回顶部