asp.net使用事务

1、SqlServer存储过程 的事务处理
方法一：
--测试的表  
create   table   tb(  
id   int     not   null     constraint   PK_sys_zj_fielddict   primary   key  
,aa   int)  

--事务处理  
begin   tran  
insert   into   tb   values(1,1)  
if   @@error<>0   goto   lb_rollback  
insert   into   tb   values(1,1)  
if   @@error<>0   goto   lb_rollback  
insert   into   tb   values(2,1)  
if   @@error<>0   goto   lb_rollback  
insert   into   tb   values(2,1)  
if   @@error<>0   goto   lb_rollback  
insert   into   tb   values(3,1)  
if   @@error<>0   goto   lb_rollback  
lb_commit:  
commit   tran  
goto   lb_ok  
lb_rollback:  
rollback   tran  

--显示结果  
lb_ok:  
select   *   from   tb  
drop   table   tb

方法二：
-- 创建测试表  
create   table   tb(  
id   int     not   null     constraint   PK_sys_zj_fielddict   primary   key  
,aa   int)  

--设置选项  
SET   XACT_ABORT   on  

--事务处理  
begin   tran  
insert   into   tb   values(1,1)  
insert   into   tb   values(1,1)  
insert   into   tb   values(2,1)  
commit   tran  

--显示结果  
/*--------注意  
如果这样写的话,后面的语句不会被执行,如果要执行后面的语句,要在这句后面加上GO,仅查询分析分析器支持,所以如果是在存储过程中,要保证 commit   tran后面没有其他语句,否则出错时,其他语句不会被执行  
-----------*/  
select   *   from   tb  
drop   table   tb  


2、.Net中使用事务处理
SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;");
myConnection.Open();

SqlTransaction myTrans = myConnection.BeginTransaction(); //使用New新生成一个事务
SqlCommand myCommand = new SqlCommand();
myCommand.Transaction = myTrans;

myCommand.Connection=myConnection;

try
{
myCommand.CommandText = "Update Address set location='23 rain street' where userid='0001'";
myCommand.ExecuteNonQuery();

myCommand.CommandText = "Update table2 set dd='23 rain street' where userid='0001'";
myCommand.ExecuteNonQuery();

myTrans.Commit();
Console.WriteLine("Record is udated.");
}
catch(Exception e)
{
myTrans.Rollback();
Console.WriteLine(e.ToString());
Console.WriteLine("Sorry, Record can not be updated.");
}
finally
{
myConnection.Close();
}

 

3.含有DataTable

      /// <summary>
/// 删除网上商城商家
/// </summary>
/// <param name="shopID"></param>
/// <returns></returns>
public int del_netShop(string shopID)
{
int res = 0;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
SqlTransaction trans = conn.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = trans;
try
{
cmd.CommandText = "update shop_newsList set psid=NULL where shopID=" + shopID;
cmd.ExecuteNonQuery();

DataTable dt = new DataTable();
cmd.CommandText = "select id,shopID from shop_proBsort1 where shopID=" + shopID;
dr = cmd.ExecuteReader();
dt.Load(dr);//使用fill方法会报事务未初始化
for (int i = 0; i < dt.Rows.Count; i++)
{
cmd.CommandText = "delete from shop_proBsort2 where aid=" + dt.Rows[i]["id"].ToString();
cmd.ExecuteNonQuery();
}
cmd.CommandText = "delete from shop_proBsort1 where shopID=" + shopID;
cmd.ExecuteNonQuery();
trans.Commit();
res = 1;
}
catch
{
trans.Rollback();//回滚

}
finally
{
conn.Close();
trans.Dispose();
conn.Dispose();
}
return res;
}

说明：在SqlServer中，每条Sql语句都作为一个事务 来执行，所以无论在存储过程，还是在.net代码中使用，执行单条Sql语句没有必要使用事务处理