强类型Dataset使用事务（改进原有方法）

以下部份转自：http://blog.****.net/nfbing/article/details/5803980

关于强类型Dataset的用法和好处，我就不再多说，网上关于这方面的资料很多 ， 感兴趣的话可以在GoOGLE搜一下。

我们直奔主题，好处是很多，但若使用事务的话就不方便了。最近通过查找国外的资料，总于找到解决的方法。经过自己测试发现很好用，所以把代码贴出来，给正处于这方面困惑的朋友解答：

首先写一个类文件，代码如下：

    public class HelperTA
    {

        public static SqlTransaction BeginTransaction(object tableAdapter)
        {
            return BeginTransaction(tableAdapter, IsolationLevel.ReadUncommitted);
        }

        public static SqlTransaction BeginTransaction(object tableAdapter, IsolationLevel isolationLevel)
        {
            // get the table adapter's type
            Type type = tableAdapter.GetType();

            // get the connection on the adapter
            SqlConnection connection = GetConnection(tableAdapter);

            // make sure connection is open to start the transaction
            if (connection.State == ConnectionState.Closed)
                connection.Open();

            // start a transaction on the connection
            SqlTransaction transaction = connection.BeginTransaction(isolationLevel);

            // set the transaction on the table adapter
            SetTransaction(tableAdapter, transaction);

            return transaction;
        }

        /// <summary>
        /// Gets the connection from the specified table adapter.
        /// </summary>
        private static SqlConnection GetConnection(object tableAdapter)
        {
            Type type = tableAdapter.GetType();
            PropertyInfo connectionProperty = type.GetProperty("Connection", BindingFlags.NonPublic | BindingFlags.Instance);
            SqlConnection connection = (SqlConnection)connectionProperty.GetValue(tableAdapter, null);
            return connection;
        }

        /// <summary>
        /// Sets the connection on the specified table adapter.
        /// </summary>
        private static void SetConnection(object tableAdapter, SqlConnection connection)
        {
            Type type = tableAdapter.GetType();
            PropertyInfo connectionProperty = type.GetProperty("Connection", BindingFlags.NonPublic | BindingFlags.Instance);
            connectionProperty.SetValue(tableAdapter, connection, null);
        }

        /// <summary>
        /// Enlists the table adapter in a transaction.
        /// </summary>
        public static void SetTransaction(object tableAdapter, SqlTransaction transaction)
        {
            // get the table adapter's type
            Type type = tableAdapter.GetType();

            // set the transaction on each command in the adapter
            PropertyInfo commandsProperty = type.GetProperty("CommandCollection", BindingFlags.NonPublic | BindingFlags.Instance);
            SqlCommand[] commands = (SqlCommand[])commandsProperty.GetValue(tableAdapter, null);
            foreach (SqlCommand command in commands)
                command.Transaction = transaction;

            // set the connection on the table adapter
            SetConnection(tableAdapter, transaction.Connection);
        }
    }
 

然后在你使用TableAdapter 的类文件中使用上面的类：

        public int InsertTest(Book book)
        {
            SqlTransaction transaction = null;

            try
            {
                int? myresult = 0;
                using (SKUTableAdapter barAdapter = new SKUTableAdapter())
                {
                    transaction = HelperTA .BeginTransaction(barAdapter);
                    barAdapter.SP_InsertSku("dfsdf", "contentname", 2, "", ref myresult);
                }

                using (UserEvaluateTableAdapter EvaluateAdapter = new UserEvaluateTableAdapter())
                {
                    HelperTA .SetTransaction(EvaluateAdapter, transaction);
                    EvaluateAdapter.InsertQuery(Guid.NewGuid().ToString(), "username", "szevaluate", 2);
                }

                int? result = 0;
                using (tb_BookTableAdapter bookadapter = new tb_BookTableAdapter())
                {
                    HelperTA .SetTransaction(bookadapter, transaction);
                    bookadapter.SP_CheckDuplicateEbook("title", "code", 2, System.DateTime.Now, ref result);
                }

                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
            finally
            {
                transaction.Dispose();
            }

            return 1;
        }

以上内容转自nfbing的专栏：http://blog.****.net/nfbing/article/details/5803980

使用后发现不能针对TableAdapter自动生成的Insert/Update/Delete进行事务处理，可以对添加的查询（SQL或者存储过程）进行事务处理。所以如果想使用xxTableAdapter.Update(xxTable)一类的更新就有问题了。方法是自己写代码为TableAdapter加带事务的方法，但没有这个通用性好，需要每个去编写。

经过实验，将原来的代码部份更改，并更新相应的TableAdapter类即可实现较好的效果：

更新后类代码：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;

/// <summary>
/// 为强类型的DataSet中的TableAdapter提供事务支持
/// </summary>
public class HelperTA
{
    /// <summary>
    /// 开始事务，第一个tableAdapter中使用，接受脏读
    /// </summary>
    /// <param name="tableAdapter"></param>
    /// <returns>返回一个SqlTransaction</returns>
    public static SqlTransaction BeginTransaction(object tableAdapter)
    {
        return BeginTransaction(tableAdapter, IsolationLevel.ReadUncommitted);
    }

    /// <summary>
    /// 开始事务，第一个tableAdapter中使用，可设置接受脏读及其它参数
    /// </summary>
    /// <param name="tableAdapter"></param>
    /// <param name="isolationLevel">事务锁定行为</param>
    /// <returns>返回一个SqlTransaction</returns>
    public static SqlTransaction BeginTransaction(object tableAdapter, IsolationLevel isolationLevel)
    {
        // get the table adapter's type
        Type type = tableAdapter.GetType();

        // get the connection on the adapter
        SqlConnection connection = GetConnection(tableAdapter);

        // make sure connection is open to start the transaction
        if (connection.State == ConnectionState.Closed)
            connection.Open();

        // start a transaction on the connection
        SqlTransaction transaction = connection.BeginTransaction(isolationLevel);

        // set the transaction on the table adapter
        SetTransaction(tableAdapter, transaction);

        return transaction;
    }

    /// <summary>
    /// Gets the connection from the specified table adapter.
    /// </summary>
    private static SqlConnection GetConnection(object tableAdapter)
    {
        Type type = tableAdapter.GetType();
        PropertyInfo connectionProperty = type.GetProperty("Connection", BindingFlags.NonPublic | BindingFlags.Instance);
        SqlConnection connection = (SqlConnection)connectionProperty.GetValue(tableAdapter, null);
        return connection;
    }

    /// <summary>
    /// 设置 table adapter 的连接属性（自动调用）
    /// </summary>
    private static void SetConnection(object tableAdapter, SqlConnection connection)
    {
        Type type = tableAdapter.GetType();
        PropertyInfo connectionProperty = type.GetProperty("Connection", BindingFlags.NonPublic | BindingFlags.Instance);
        connectionProperty.SetValue(tableAdapter, connection, null);
    }

    /// <summary>
    /// 将新的tableAdapter加入到事务中
    /// </summary>
    /// <param name="tableAdapter"></param>
    /// <param name="transaction"></param>
    public static void SetTransaction(object tableAdapter, SqlTransaction transaction)
    {
        // get the table adapter's type
        Type type = tableAdapter.GetType();

        // set the transaction on each command in the adapter
        PropertyInfo commandsProperty = type.GetProperty("CommandCollection", BindingFlags.NonPublic | BindingFlags.Instance);
        SqlCommand[] commands = (SqlCommand[])commandsProperty.GetValue(tableAdapter, null);
        foreach (SqlCommand command in commands)
            command.Transaction = transaction;

        //设置自动生成的增、删、改命令事务
        PropertyInfo AdapterProperty = type.GetProperty("Adapter", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Static);
        SqlDataAdapter adapter = (SqlDataAdapter)AdapterProperty.GetValue(tableAdapter, null);
        if (adapter.InsertCommand != null) adapter.InsertCommand.Transaction = transaction;
        if (adapter.UpdateCommand != null) adapter.UpdateCommand.Transaction = transaction;
        if (adapter.DeleteCommand != null) adapter.DeleteCommand.Transaction = transaction;

        // set the connection on the table adapter
        SetConnection(tableAdapter, transaction.Connection);
    }
}