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.
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).
- 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:
conn = new SqlConnection(Properties.Settings.Default.NorthwindConnectionString);
trans = conn.BeginTransaction();
public SqlDataAdapter GetAdapter(object tableAdapter)
Type tableAdapterType = tableAdapter.GetType();
SqlDataAdapter adapter = (SqlDataAdapter)tableAdapterType.GetProperty("Adapter",
BindingFlags.Instance | BindingFlags.NonPublic).GetValue(tableAdapter, null);
adapter.InsertCommand.Connection = trans.Connection;
adapter.UpdateCommand.Connection = trans.Connection;
adapter.DeleteCommand.Connection = trans.Connection;
adapter.InsertCommand.Transaction = trans;
adapter.UpdateCommand.Transaction = trans;
adapter.DeleteCommand.Transaction = trans;
Download the attached project for a working example.
Make sure you change this line of code to your appropriate database connection:
@"Data Source=kenny2;Initial Catalog=Northwind;Integrated Security=True";