Transactions with TableAdapters, a lazy man's approach
If you've used TableAdapters, you'll know that their ability to deal with transactions leaves a little to be desired. Little? Did I say "little"? I mean "a lot".
Sahil Malik has a good slew of advice for people wanting to use transactions with TableAdapters. Most people will probably just wrap everything up in a TransactionScope and be done with it, which is fine if you're not running a high-traffic site. In doing this, you'll run into the annoyance of the transaction being promoted to the DTC, which is an expensive bit of "bling" to tango with.
For the people that don't like the whole promotion to DTC thing, they'll probably extend their TableAdapter's partial class and add a BeginTransaction method similar to what Sahil proposes.
For me, who's looking at a bajillion TableAdapters, with quite a few of those needing to operate within the scope of a transaction, I instead choose to play the Hacky card and just set the transaction on the TableAdapter's commands through a little bit of reflection. Some may throw your hands up in the air on this. I, however, love it, as it fits my purposes like a latex glove.
The code follows for my so-called TableAdapterHelper.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
public class TableAdapterHelper
{
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);
}
}
Here's some example usage of the helper methods above. In this example, we have some work that spans two different adapters that we need to enclose in a transaction.
SqlTransaction transaction = null;
try
{
using (FooTableAdapter fooAdapter = new FooTableAdapter())
{
transaction = TableAdapterHelper.BeginTransaction(fooAdapter);
fooAdapter.DoSomething();
}
using (BarTableAdapter barAdapter = new BarTableAdapter())
{
TableAdapterHelper.SetTransaction(barAdapter, transaction);
barAdapter.DoSomething();
}
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
transaction.Dispose();
}
So, what are the disadvantages here? We're using reflection so there's no compile-time checking nor is there any strong-typing action going on. Using reflection is obviously slower execution-wise than extending your TableAdapter's class. Also, the code-gen implementation of your TableAdapter class could theoretically change if VS2005 gets patched in the future and would therefore cause your TableAdapterHelper to break.
What are the advantages? Compile-time checking, schmompile-schmime checking. Reflection is eons faster than a promotion to DTC, and this approach is certainly faster than extending your TableAdapter classes by hand. And, the code-gen implementation of your TableAdapter class is unlikely to change.