强类型Dataset使用事务（改进原有方法）
以下部份转自：http://blog.csdn.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.csdn.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);
}
}