.Net和SqlServer的事务处理实例

1，sqlserver存储过程的事务处理

一种比较通用的出错处理的模式大概如下：

create procdure prinsertproducts

(

 @intproductid int,

 @chvproductname varchar(30),

 @intproductcount int

)

as

declare @interrorcode int

select @interrorcode=@@error

begin transaction

 if @interrorcode=0

   begin

     -insert products

     insert products(productid,productname,productcount) 

     values(@intproductid,@chvproductname,@intproductcount)

     select @interrorcode=@@error --每执行完一条t-sql语句马上进行检测，并把错误号保存到局部变量中

   end

 if @interrorcode=0

   begin 

     -update products

     update products set productname='microcomputer' where productid=5

     select @interrorcode=@@error

   end

if @interrorcode=0

   commit transaction

else

   rollback transaction

 

 return @interrorcode --最好返回错误代号给调用的存储过程或应用程序

 

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;

 

try 

{ 

mycommand.commandtext = "update address set location='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(); 

}