在DataAdapter中开启事务

在ADO.NET 2.0 中引入了strong typed table adapter,强化了strong typed dataset的设计视图， 使用非常方便，但是在实际运用当中，还是遇到了一些麻烦，比如怎么在多个table adapter 之间开启事务，由于table adapter 自身没有提供事务功能，而且它的connection 对象默认是private ，所以要实现事务稍微有些麻烦，目前的解决方法大概如下几种 最简单的一个方法就是使用 transaction scope, 如下：

using  (TransactionScope ts  =   new  TransactionScope())
            {
                 // do something here
                ts.Complete();
            }

但该方法有个缺点，需要开启135端口，还要配置 MS DTC ，在某些不能随便开启端口的环境下就不能使用该方法。
  方法二：将connection的modifier属性改为public,然后操作connection,如下：

SqlConnection connection  =  table1TableAdapter.Connection; 
table2TableAdapter.Connection  =  connection;
//  Start a local Transaction 
SqlTransaction transaction  =  connection.BeginTransaction(); 
table1TableAdapter.MyAdapter.InsertCommand.Transaction  =  transaction; 
table2TableAdapter.MyAdapter.InsertCommand.Transaction  =  transaction; 
try  
{ 
//  Update Database 
table1TableAdapter.Update(dataSet1.Table1); 
table2TableAdapter.Update(dataSet1.Table2); 
//  Commit Changes to database 
transaction.Commit(); 
}
// more code here

   方法三：利用System.Reflection命名空间下的PropertyInfo类的GetProperty方法取得table adapter的私有connection属性，附加上transaction对象后，再通过PropertyInfo的SetValue方法将改造后的connection属性设置回table adapter实例:

using  System;
using  System.Collections.Generic;
using  System.Text;
using  System.Data;
using  System.Reflection;
using  System.Data.Common;
namespace  CDSafe.DBUtilities
{
     ///   <summary>
     ///  a helper class when u are using a dataset's data adapter.
     ///  it use the reflection to add the transaction into the data adpater's connection
     ///   </summary>
    public   sealed    class  DataAdapterHelper
    {
        ///   <summary>
        ///  begin the transaction
        ///   </summary>
        ///   <param name="tableAdapter"> the first data adapter in the transaction </param>
        ///   <param name="isolationLevel"> the isolation level of the transaction </param>
        ///   <returns>  a transaction object, use to add another data adapter into the same transaction </returns>
        public   static  DbTransaction BeginTransaction( object  tableAdapter,IsolationLevel isolationLevel)
       {
           Type adapterType  =  tableAdapter.GetType();
           DbConnection connection  =  GetAdpaterConnection(tableAdapter);
            if  (connection.State  ==  ConnectionState.Closed)
           {
               connection.Open();
           }
           DbTransaction transaction  =  connection.BeginTransaction(isolationLevel);
           SetTransaction(tableAdapter, transaction);
            return  transaction;
       }

        ///   <summary>
        ///   begin the transaction
        ///   </summary>
        ///   <param name="tableAdapter"> the first data adapter in the transaction </param>
        ///   <returns> a transaction object, use to add another data adapter into the same transaction </returns>
        public   static  DbTransaction BeginTransaction( object  tableAdapter)
       {
            return  BeginTransaction(tableAdapter, IsolationLevel.ReadCommitted);
       }

        ///   <summary>
        ///  use the reflection to get the table adapter's connection object
        ///   </summary>
        ///   <param name="tableAdapter"></param>
        ///   <returns> the connection object </returns>
        private   static  DbConnection GetAdpaterConnection( object  tableAdapter)
       {
           Type adapterType  =  tableAdapter.GetType();
           PropertyInfo connectionProperty  =  adapterType.GetProperty( " Connection " ,
               BindingFlags.NonPublic  |  BindingFlags.Instance);
           DbConnection connection  =  (DbConnection)connectionProperty.GetValue(tableAdapter,  null );
            return  connection;
       }

        ///   <summary>
        ///  attach the connection which contains a transaction on the data adapter
        ///   </summary>
        ///   <param name="tableAdapter"></param>
        ///   <param name="connection"></param>
        private   static   void  SetConnection( object  tableAdapter, DbConnection connection)
       {
           Type type  =  tableAdapter.GetType();
           PropertyInfo connectionProperty  =  type.GetProperty( " Connection " , BindingFlags.NonPublic  |  BindingFlags.Instance);
           connectionProperty.SetValue(tableAdapter, connection,  null );
       }

        ///   <summary>
        ///  set transaction on the other data adapter
        ///   </summary>
        ///   <param name="tableAdapter"></param>
        ///   <param name="transaction"></param>
        public   static   void  SetTransaction( object  tableAdapter, DbTransaction transaction)
       {
           Type adapterType  =  tableAdapter.GetType();
           PropertyInfo commandsProperty  =  adapterType.GetProperty( " CommandCollection " , 
               BindingFlags.NonPublic  |  BindingFlags.Instance);
           DbCommand[] commands  =  (DbCommand[])commandsProperty.GetValue(tableAdapter,  null );
            foreach  (DbCommand command  in  commands)
           {
               command.Transaction  =  transaction;
           }
           PropertyInfo adpterProperty  =  adapterType.GetProperty( " Adapter " ,
              BindingFlags.NonPublic  |  BindingFlags.Instance);
           DbDataAdapter dataAdapter  =  (DbDataAdapter)adpterProperty.GetValue(tableAdapter,  null );
            if  (dataAdapter.InsertCommand  !=   null )
           {
               dataAdapter.InsertCommand.Transaction  =  transaction;
           }
            if  (dataAdapter.DeleteCommand  !=   null )
           {
               dataAdapter.DeleteCommand.Transaction  =  transaction;
           }
            if  (dataAdapter.UpdateCommand  !=   null )
           {
               dataAdapter.UpdateCommand.Transaction  =  transaction;
           }
            if  (dataAdapter.SelectCommand  !=   null )
           {
               dataAdapter.SelectCommand.Transaction  =  transaction;
           }
           SetConnection(tableAdapter, transaction.Connection);
       }
    }
}

DataAdater的command分两种，一种是Adapter成员变量的insert,update,delete，还有一种是使用向导创建的command，这类command被放到了CommandCollection集合内。所以设置transaction时应考虑这两种类型的command。
该helper class使用范例如下：

            TestTableAdapter adapter1  =   new  testTableAdapter();
            TableAdapter2 adapter2  =   new  TableAdapter2();
            DbTransaction trans  =  DataAdapterHelper.BeginTransaction(adapter1);
            DataAdapterHelper.SetTransaction(adapter2, trans);
            adapter1.Insert( " 1 " ,  " 2 " );
            adapter2.Insert( " 3 " ,  " 4 " ,  null );
            trans.Commit();