进来对事物作一下总结,常用代码如下：
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();
}