在DataAdapter中开启事务

穆巴 2011-05-12

展开全文

在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使用范例如下：