Click here to Skip to main content
14,599,199 members

Using TableAdapters with transactions

Rate this:
2.30 (6 votes)
Please Sign up or sign in to vote.
2.30 (6 votes)
18 Feb 2007CPOL
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!!! 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 
Yes, but as i said in previous post:
"The thing is that I do not want to change code generated by VS.
Imagine doing it to all tables in your project and re-doing it when a database design changes"

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
Neil Barnwell22-Feb-07 10:46
MemberNeil Barnwell22-Feb-07 10:46 

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

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

Article
Posted 18 Feb 2007

Stats

92.9K views
954 downloads
22 bookmarked