Click here to Skip to main content
15,867,967 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.1K   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 
I can it imagine. It can be done for example in this way: you can have multiple parts of partial class. Some parts can be generated automaticaly, and those can be the parts I have ment. Everything you need is a list of all table adapters, and you can create your own task for MS Build, which will produce those partials before common compile process starts. Thats the reason, why partial classes were added. It is clear? It is just an unproved idea, but I think it can work.

Maybe someone could try it (maybe me? Smile | :) )
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.