Click here to Skip to main content
11,717,126 members (79,433 online)
Click here to Skip to main content

Using TableAdapters with transactions

, 18 Feb 2007 CPOL 65.5K 732 22
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

You may also be interested in...

Comments and Discussions

 
GeneralThank you!!! Pin
JamesHG13-Apr-10 6:24
memberJamesHG13-Apr-10 6:24 
GeneralThanx Pin
mohammad16-Mar-08 7:55
membermohammad16-Mar-08 7:55 
GeneralSCOPE_IDENTITY Not rolling back Pin
paulvzyl31-May-07 0:16
memberpaulvzyl31-May-07 0:16 
GeneralRe: SCOPE_IDENTITY Not rolling back Pin
Danko Greiner31-May-07 1:32
memberDanko Greiner31-May-07 1:32 
GeneralRe: SCOPE_IDENTITY Not rolling back Pin
paulvzyl31-May-07 4:04
memberpaulvzyl31-May-07 4:04 
Questionpartial class Pin
Boudino23-May-07 3:39
memberBoudino23-May-07 3:39 
AnswerRe: partial class Pin
Danko Greiner23-May-07 4:24
memberDanko Greiner23-May-07 4:24 
GeneralRe: partial class Pin
Boudino23-May-07 8:00
memberBoudino23-May-07 8:00 
GeneralBtw... Pin
fadee20-May-07 2:47
memberfadee20-May-07 2:47 
Generaleasy way... Pin
fadee20-May-07 2:46
memberfadee20-May-07 2:46 
GeneralRe: easy way... Pin
Danko Greiner20-May-07 21:30
memberDanko Greiner20-May-07 21:30 
GeneralRe: easy way... Pin
fadee21-May-07 0:02
memberfadee21-May-07 0:02 
QuestionRe: easy way... Pin
Preston McCormick26-Jun-07 14:55
memberPreston McCormick26-Jun-07 14:55 
AnswerRe: easy way... Pin
Alexander Myachin10-Aug-07 19:52
memberAlexander Myachin10-Aug-07 19:52 
GeneralI have another way... Pin
Shipcreak22-Feb-07 10:46
memberShipcreak22-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 | Terms of Use | Mobile
Web02 | 2.8.150901.1 | Last Updated 18 Feb 2007
Article Copyright 2007 by Danko Greiner
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid