使用Transaction访问数据库（C#，TransactionScope，.NET 2.0）

来源于：http://www.cppblog.com/ganmuren/archive/2011/03/07/141263.html

 

针对SQL2005和.NET 2.0的事物机制有了新的突破
传统数据库事物访问机制，代码如下：

 1   public   void  TransactionTest()
 2           {
 3                string  connectionString  =   "" ;
 4               IDbConnection connection  =   new  SqlConnection(connectionString);
 5               connection.Open();
 6               IDbCommand command  =   new  SqlCommand();
 7               command.Connection  =  connection;
 8               IDbTransaction transaction;
 9               transaction  =  connection.BeginTransaction();  // Enlisting database
10               command.Transaction  =  transaction;
11                try
12               {
13                    /**//*  Interact with database here, then commit the transaction
14                    */
15                   transaction.Commit();
16               }
17                catch
18               {
19                   transaction.Rollback();  // Abort transaction
20               }
21                finally
22               {
23                   connection.Close();
24               }
25           }
26  

或者这种，其实都差不多

 1   using System.Data.SqlClient
   
 2                             DataTable   dt    =     new    DataTable();   
 3                             SqlConnection   cnn    =     new    SqlConnection( " 连接字符串 " );  
 4                             SqlCommand   cm    =     new   SqlCommand();  
 5                             cm.Connection    =    cnn;  
 6                             cnn.Open();  
                           //一定要BeginTransaction()
 7                            SqlTransaction   trans    =    cnn.BeginTransaction(); 

 8                              try   
 9                             {  
10                                      foreach (DataRow   dr    in    dt.Rows)  
11                                     {  
12                                             cm.CommandText    =     " update   [表]   set   [数量]   =   @amount   where   productID   =   @productID " ;  
13                                             cm.Parameters.Add( " @amount " ,SqlDbType.Int);  
14                                             cm.Parameters[ " @amount " ].Value    =    Convert.ToInt32(dr[ " amount " ]);  
15                                             cm.Parameters.Add( " @productID " ,SqlDbType.VarChar);  
16                                             cm.Parameters[ " @productID " ].Value    =    dr[ " productID " ].ToString();  
17                                             cm.ExecuteNonQuery();  
18                                     }  
19                                     trans.Commit();  
20                             }  
21                              catch   
22                             {  
23                                     trans.Rollback();  
24                             }  
25                              finally   
26                             {  
27                                     cnn.Close();  
28                                     trans.Dispose();  
29                                     cnn.Dispose();  
30                             }  
31                     }

至少需要try catch，

如今使用TransactionScope

1   using (TransactionScope scope  =   new  TransactionScope())
2   {
3   /**//*  Perform transactional work here  */
4   // No errors - commit transaction
5   scope.Complete();
6   }
7  

一句using中的new，然后scape.complete()就解决了。
TransactionScope是Transaction的精简版，也能很好的完成工作。

另外对嵌套事务和事务的隔离级别也提供了支持

 1   using (TransactionScope scope1  =   new  TransactionScope())
 2   // Default is Required
 3   {
 4   using (TransactionScope scope2  =   new
 5   TransactionScope(TransactionScopeOption.Required))
 6   {}
 7   using (TransactionScope scope3  =   new
 8   TransactionScope(TransactionScopeOption.RequiresNew))
 9   {}
10   using (TransactionScope scope4  =   new
11   TransactionScope(TransactionScopeOption.Suppress))
12   {}
13  
14   }
15  

下面是对嵌套事务的说明：

嵌套事务的具体请看：http://perhaps.cnblogs.com/archive/2005/08/17/216863.html

需要注意的是：使用transaction时，尽量在其中做必需要的操作，其它和数据库无关的操作就放在transaction外面。毕竟SQL事务处理时，是一种独占的状态。尽快使用完毕释放资源非常重要。