.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(); 

}