Almost everyone who uses the new TableAdapter functionality in Visual Studio 2005 (note that I did not write .NET 2.0 as you will find no such thing in the framework) will eventually have the need for transaction support. The basic problem is that TableAdapters do not expose the underlying Command objects, as a result you cannot set their Transaction properties in order to enlist them in a transaction.
Fortunately we can derive our TableAdapters from a custom base class which will allow us to extend the out of the box implementation and provide the functionality we need. The first thing we need to do is define the interface our base class will implement.
public interface IExtendTableAdapter
{
/// <summary>
/// Returns the DataAdapter for the TableAdapter
/// </summary>
AdapterDecorator DataAdapter { get; }
/// <summary>
/// Gets or sets the DbConnection for the TableAdapter.
/// </summary>
System.Data.IDbConnection DbConnection { get; set;}
}
The purpose of the DataAdapter property is to expose and extend the underlying DataAdapter to include transaction support. The DbConnection property allows us to set the Connection object used by our transaction.
Next we define the AdapterDecorator class which exposes the underlying DataAdapter and provides a method to enlist the TableAdapter in a transaction.
public class AdapterDecorator
{
#region Properties
private System.Data.Common.DbDataAdapter _adapter;
/// <summary>
/// Gets or sets the DataAdapter for the TableAdapter.
/// </summary>
public System.Data.Common.DbDataAdapter Adapter
{
get { return _adapter; }
set { _adapter = value; }
}
private System.Data.Common.DbCommand[] _commands;
/// <summary>
/// Gets or sets the non-DataAdapter Command objects.
/// </summary>
public System.Data.Common.DbCommand[] Commands
{
get { return _commands; }
set { _commands = value; }
}
#endregion Properties
#region Constructors
public AdapterDecorator(System.Data.Common.DbDataAdapter adapter)
{
_adapter = adapter;
}
public AdapterDecorator(System.Data.Common.DbDataAdapter adapter, System.Data.Common.DbCommand[] commands)
{
_adapter = adapter;
_commands = commands;
}
#endregion Constructors
#region Public Methods
public void EnlistTransaction(System.Data.Common.DbTransaction transaction)
{
//set Adapter Command object transactions
if (_adapter != null)
{
if (_adapter.InsertCommand != null)
{
_adapter.InsertCommand.Connection = transaction.Connection;
_adapter.InsertCommand.Transaction = transaction;
}
if (_adapter.UpdateCommand != null)
{
_adapter.UpdateCommand.Connection = transaction.Connection;
_adapter.UpdateCommand.Transaction = transaction;
}
if (_adapter.DeleteCommand != null)
{
_adapter.DeleteCommand.Connection = transaction.Connection;
_adapter.DeleteCommand.Transaction = transaction;
}
}
//set non-Adapter Command object transactions
if (_commands != null)
{
for (int i = 0; i < this._commands.Length; i++)
{
_commands[i].Connection = transaction.Connection;
_commands[i].Transaction = transaction;
}
}
}
#endregion Public Methods
}
Now we define the TableAdapterBase class which will be the base class from which our TableAdapters will be derived.
public abstract class TableAdapterBase : System.ComponentModel.Component, IExtendTableAdapter
{
#region Private Fields
AdapterDecorator adapterDecorator; //The extended DataAdapter
IDbConnection connection; //The database connection
#endregion Private Fields
#region IExtendTableAdapter Members
public AdapterDecorator DataAdapter
{
get
{
//========================================================================================
// Use Reflection to get the private Adapter and CommandCollection properties defined by
// the Designer generated code.
//========================================================================================
if (this.adapterDecorator == null)
{
System.Data.Common.DbDataAdapter adapter = null;
System.Data.Common.DbCommand[] commands = null;
Type t = this.GetType();
//If parent type is not TableAdapterBase then we need to reflect the properties
//from the parent type. Otherwise the "Adapter" property will not be found since
//it is private.
Type parentT = t.BaseType;
if (!(parentT.FullName == "TransactionalTableAdapters.TableAdapterBase"))
t = parentT;
//Get the Adapter property defined by the TableAdapter's designer generated code
BindingFlags bf = BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance;
PropertyInfo pi = t.GetProperty("Adapter", bf);
if (pi != null)
adapter = pi.GetValue(this, null) as System.Data.Common.DbDataAdapter;
//Get the CommandCollection property defined by the TableAdapter's designer generated code
pi = t.GetProperty("CommandCollection", bf);
if (pi != null)
commands = pi.GetValue(this, null) as System.Data.Common.DbCommand[];
//Create the AdapterDecorator object
this.adapterDecorator = new AdapterDecorator(adapter, commands);
}
return this.adapterDecorator;
}
}
public IDbConnection DbConnection
{
get
{
//=================================================================
// Use Reflection to get the Connection property defined by the
// Designer generated code.
//=================================================================
if (this.connection == null)
{
Type t = this.GetType();
BindingFlags bf = BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance;
//Get the Connection property.
PropertyInfo pi = t.GetProperty("Connection", bf);
if (pi != null)
connection = pi.GetValue(this, null) as IDbConnection;
}
return this.connection;
}
set
{
this.connection = value;
}
}
#endregion
}
Finally, we define a TransactionHelper class to make it easy to enlist our TableAdapters in a transaction.
public sealed class TransactionHelper : IDisposable
{
#region Private Fields
DbConnection _connection; //The database connection
DbTransaction _tran = null; //The database transaction
#endregion Private Fields
#region Public Methods
/// <summary>
/// Enlists the specified table adapter in the current transaction.
/// </summary>
/// <param name="tableAdapter">The table adapter to enlist. The table adapter must implement <see cref="IExtendTableAdapter"/>.</param>
/// <remarks>
/// </remarks>
public void EnlistParticipant(System.ComponentModel.Component tableAdapter)
{
//Make sure the TableAdapter implements IExtendTableAdapter which is required for transaction support
if (!(tableAdapter is IExtendTableAdapter))
throw new Exception("Type " + tableAdapter.GetType().Name + " is invalid because it does not implement IExtendTableAdapter.");
IExtendTableAdapter ta = tableAdapter as IExtendTableAdapter;
//We need to share the same connection across TableAdapters for them to participate in the same
//transaction so get connection from the first TableAdapter we encounter and use it for all
//others.
if (_connection == null)
{
_connection = ta.DbConnection as DbConnection; //get the connection
_connection.Open(); //open the connection
_tran = _connection.BeginTransaction(); //create a transaction
}
else //we already have our connection so change the connection of the TableAdapter to use it
{
ta.DbConnection = _connection;
}
//Enslist the TableAdapter in the transaction
ta.DataAdapter.EnlistTransaction(_tran);
}
/// <summary>
/// Completes the operations and commits the transaction.
/// </summary>
public void Complete()
{
//Commit and dispose the transaction
if (_tran != null)
{
_tran.Commit();
_tran.Dispose();
_tran = null;
}
}
#endregion Public Methods
#region IDisposable Members
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
/// </summary>
public void Dispose()
{
//Rollback and dispose transaction if still present
if (_tran != null)
{
_tran.Rollback();
_tran.Dispose();
}
//close the connection
if ((_connection != null) && (_connection.State != ConnectionState.Closed))
_connection.Close();
}
#endregion
}
This class is used very much like the TransactionScope class with the exception that we must explicitly enlist our TableAdapters in the transaction by calling the EnlistParticipant() method. For example:
using (TransactionHelper th = new TransactionHelper())
{
//enlist TableAdapters in our transaction
NorthwindDataSetTableAdapters.OrdersTableAdapter ordersTA =
new TransactionalTableAdapters.NorthwindDataSetTableAdapters.OrdersTableAdapter();
th.EnlistParticipant(ordersTA);
NorthwindDataSetTableAdapters.Order_DetailsTableAdapter detailsTA =
new TransactionalTableAdapters.NorthwindDataSetTableAdapters.Order_DetailsTableAdapter();
th.EnlistParticipant(detailsTA);
//Update the Orders table
ordersTA.Update(ordersRow);
//Get the order id
int orderID = ordersRow.OrderID;
//Insert Order_Details row
detailsTA.Insert(orderID, 1, Convert.ToDecimal(3.39), 10, 0f);
//Commit the transaction
th.Complete();
}
Complete source code for this post can be found in the attachment below.
TransactionalTableAdapters.zip (29.29 kb)