Click here to Skip to main content
15,880,364 members
Articles / Database Development / SQL Server

Using TableAdapters with transactions

Rate me:
Please Sign up or sign in to vote.
2.30/5 (6 votes)
18 Feb 2007CPOL1 min read 103.3K   1K   23   16
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:

C#
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:

C#
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)


Written By
Croatia Croatia
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralThank you!!! Pin
JamesHG13-Apr-10 6:24
JamesHG13-Apr-10 6:24 
GeneralThanx Pin
mSafdel16-Mar-08 7:55
mSafdel16-Mar-08 7:55 
GeneralSCOPE_IDENTITY Not rolling back Pin
paulvzyl31-May-07 0:16
paulvzyl31-May-07 0:16 
GeneralRe: SCOPE_IDENTITY Not rolling back Pin
Danko Greiner31-May-07 1:32
Danko Greiner31-May-07 1:32 
GeneralRe: SCOPE_IDENTITY Not rolling back Pin
paulvzyl31-May-07 4:04
paulvzyl31-May-07 4:04 
Questionpartial class Pin
Boudino23-May-07 3:39
Boudino23-May-07 3:39 
AnswerRe: partial class Pin
Danko Greiner23-May-07 4:24
Danko Greiner23-May-07 4:24 
GeneralRe: partial class Pin
Boudino23-May-07 8:00
Boudino23-May-07 8:00 
GeneralRe: partial class Pin
kuklei7-Jan-21 4:32
kuklei7-Jan-21 4:32 
GeneralBtw... Pin
fadee20-May-07 2:47
fadee20-May-07 2:47 
Generaleasy way... Pin
fadee20-May-07 2:46
fadee20-May-07 2:46 
GeneralRe: easy way... Pin
Danko Greiner20-May-07 21:30
Danko Greiner20-May-07 21:30 
GeneralRe: easy way... Pin
fadee21-May-07 0:02
fadee21-May-07 0:02 
QuestionRe: easy way... Pin
Preston McCormick26-Jun-07 14:55
Preston McCormick26-Jun-07 14:55 
AnswerRe: easy way... Pin
Alexander Myachin10-Aug-07 19:52
Alexander Myachin10-Aug-07 19:52 
GeneralI have another way... Pin
Neil Barnwell22-Feb-07 10:46
Neil 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.