Click here to Skip to main content
14,599,268 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 
Danko Greiner wrote:
What database are you connectiong to?
In my experience Identity column (new, inserted value) is retreived by default if you are using SQL Server.
Same can not be said for Access 2000. This is just my observation, i did not find documentation on this behaviour.

It looks odd that all but one row is rolled back, maybe you can check if transaction is set for that InsertCommand.

By the way, does it work ok if transaction is commited?


I am connecting to SQL Server 2000. By default the tableAdapter's insert statement is set to NonQuery. With the extra insert statement I set it to Scalar. It works fine when commited.

I don't know if maybe the table adapter is creating a transaction of its own to execute my two sql statements and that, that is what is causing the transaction not to roll back completely.

I have removed the second statement and now use the default insert statement to insert the data. After the insert I call SELECT IDENT_CURRENT('tableName') to retrieve the last identity inserted. This is working fine and everything is now rolling back as it should.

I might still get some problems if the table is accessed simultaniously but I think its less likely to be a problem and would rather have the transaction working correctly.
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
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