Introduction
How can we update two TableAdapters in one transaction? I really was surprised to have so much trouble with this issue, which I assumed would be a basic functionality.
I will use the Northwind database as an example. I want to add a new record to the Order and Order Details tables. I want to do this using a transaction. I do not want to add orders without order details. And, I want to use TableAdapters.
Problem
Well, the problem is that TableAdapters do not expose a Connection
property or Transaction
property, so how am I going to do this?! Beats me! Luckily, Reflection, Google, and Ryan Whitaker did save my day(s).
Solution
- 0. Initialize connection, begin transaction.
- 1. Use Reflection to access the
SqlDataAdapter
of the TableAdapter(s) on which you want to use the transaction. - 2. Set this adapter's Insert/Update/Delete
Connection
property to the connection which did create the transaction. - 3. Set this adapter's Insert/Update/Delete
Transaction
property to the appropriate transaction. - 4. Do this for each TableAdapter you need to update in the transaction.
- 5. Commit the transaction.
In other words:
0.
conn = new SqlConnection(Properties.Settings.Default.NorthwindConnectionString);
conn.Open();
trans = conn.BeginTransaction();
1.
public SqlDataAdapter GetAdapter(object tableAdapter)
{
Type tableAdapterType = tableAdapter.GetType();
SqlDataAdapter adapter = (SqlDataAdapter)tableAdapterType.GetProperty("Adapter",
BindingFlags.Instance | BindingFlags.NonPublic).GetValue(tableAdapter, null);
return adapter;
}
2.
adapter.InsertCommand.Connection = trans.Connection;
adapter.UpdateCommand.Connection = trans.Connection;
adapter.DeleteCommand.Connection = trans.Connection;
3.
adapter.InsertCommand.Transaction = trans;
adapter.UpdateCommand.Transaction = trans;
adapter.DeleteCommand.Transaction = trans;
4.
-
5.
trans.commit();
Download the attached project for a working example.
Make sure you change this line of code to your appropriate database connection:
Properties.Settings.Default.NorthwindConnectionString =
@"Data Source=kenny2;Initial Catalog=Northwind;Integrated Security=True";