Click here to Skip to main content
Click here to Skip to main content
Go to top

Using TableAdapters with transactions

, 18 Feb 2007
Rate this:
Please Sign up or sign in to vote.
Use TableAdapters with transactions.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Danko Greiner

Croatia Croatia
No Biography provided

Comments and Discussions

 
GeneralThank you!!! PinmemberJamesHG13-Apr-10 6:24 
GeneralThanx Pinmembermohammad16-Mar-08 7:55 
GeneralSCOPE_IDENTITY Not rolling back Pinmemberpaulvzyl31-May-07 0:16 
GeneralRe: SCOPE_IDENTITY Not rolling back PinmemberDanko Greiner31-May-07 1:32 
GeneralRe: SCOPE_IDENTITY Not rolling back Pinmemberpaulvzyl31-May-07 4:04 
Questionpartial class PinmemberBoudino23-May-07 3:39 
AnswerRe: partial class PinmemberDanko Greiner23-May-07 4:24 
GeneralRe: partial class PinmemberBoudino23-May-07 8:00 
GeneralBtw... Pinmemberfadee20-May-07 2:47 
Generaleasy way... Pinmemberfadee20-May-07 2:46 
GeneralRe: easy way... PinmemberDanko Greiner20-May-07 21:30 
GeneralRe: easy way... Pinmemberfadee21-May-07 0:02 
QuestionRe: easy way... PinmemberPreston McCormick26-Jun-07 14:55 
AnswerRe: easy way... PinmemberAlexander Myachin10-Aug-07 19:52 
GeneralI have another way... PinmemberShipcreak22-Feb-07 10:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 18 Feb 2007
Article Copyright 2007 by Danko Greiner
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid