sql事务例子【转载】

 

代码

create   proc  DemandInfoInsert
@Type   bigint ,
@DemandSort   bigint , 
@Title   varchar ( 100 ),
@detail   text ,
@EndDate   datetime ,
@AddUser   bigint ,
@IsShow   int ,
@AddDate   datetime ,
@ServeSort   bigint ,
@TransitSort   bigint ,
@GoodsSort   bigint ,
@GoodsWeight   decimal ,
@Unit   varchar ( 20 ),
@PackExplain   varchar ( 64 ),
@WillPrice   money ,
@StartPlace   varchar ( 100 ),
@FinishPlace   varchar ( 100 ),
@ConsignmentDate   datetime ,
@LinkMan   varchar ( 8 ),
@LinkAddress   varchar ( 100 ),
@PostNum   varchar ( 8 ),
@LinkPhone   varchar ( 20 ),
@Email   varchar ( 50 ),
@Fax   varchar ( 20 ),
@IsType   bigint
AS  
Begin
    begin   transaction  AddDemandInfo
     declare   @DemandInfoID   int
     insert   into  DemandInfo (Type,DemandSort,Title,detail,EndDate,AddUser,IsShow,AddDate)  values ( @Type , @DemandSort , @Title , @detail , @EndDate , @AddUser , @IsShow , @AddDate )
     set   @DemandInfoID   =   scope_identity ()
     insert   into  DemandSubInfo (DemandID,ServeSort,TransitSort,GoodsSort,GoodsWeight,Unit,PackExplain,WillPrice,StartPlace,FinishPlace,ConsignmentDate)  values ( @DemandInfoID , @ServeSort , @TransitSort , @GoodsSort , @GoodsWeight , @Unit , @PackExplain , @WillPrice , @StartPlace , @FinishPlace , @ConsignmentDate )
     insert   into  TradeLinkInfo (LinkMan,LinkAddress,PostNum,LinkPhone,Email,Fax,PyTradeID,IsType)  values ( @LinkMan , @LinkAddress , @PostNum , @LinkPhone , @Email , @Fax , @DemandInfoID , @IsType )
     if   @@Error <> 0
     begin
      Rollback   transaction  AddDemandInfo
     end
    commit   transaction  AddDemandInfo
End
GO
exec  DemandInfoInsert  1 , 2 , ' dd ' , ' ss ' , ' 2008-08-14 ' , 1 , 0 , ' 2008-09-14 ' , 2 , 3 , 1 , 122 , ' 吨 ' , ' ddd ' , 112.02 , ' 武汉 ' , ' 上海 ' , ' 2008-09-16 ' , ' zf ' , ' dfasfdaf ' , ' 12355656 ' , ' 23423424 ' , ' dj@123.com ' , ' 2123123123 ' , 1





protected void Button1_Click(object sender, EventArgs e) 
{ 
SqlConnection conn; 
SqlCommand cmd1, cmd2; 
SqlTransaction tra; 
conn  =  new SqlConnection("server = .; database = test;uid = sa;pwd = 123 "); 
conn. Open (); 
tra  =  conn.BeginTransaction(); // 开始事务 
cmd1  =  new SqlCommand(" update  Test  set  StuName = ' 佳儿 '   where  StuScore = 111 ", conn); 
cmd2  =  new SqlCommand(" update  Test  set  StuName = ' fdsafdsafdsfdsafsfsdadsadsadfsafsfsfdf '   where  StuScore = 222 ", conn); 
try 
{ 
cmd1.ExecuteNonQuery(); // 此句可以成功执行 
cmd2.ExecuteNonQuery(); // 此句故意让其执行失败 
tra. Commit (); // 无异常，提交事务(两句执行都有效) 
Response.Write(" < script > alert( ' 执行成功 ' ) </ script > "); 
} 
catch 
{ 
tra. Rollback (); // 出现异常，回滚事务(两句执行都无效) 
Response.Write(" < script > alert( ' 执行失败 ' ) </ script > "); 
} 
finally 
{ 
conn. Close (); 
} 
}

 

 

转载于:https://www.cnblogs.com/creazy_windy/archive/2010/03/25/1694734.html