Using TableAdapters with transactions

• Download source code - 126 KB

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";