Update 2 Database Tables in a single transaction

Posted on 2005-04-11
Last Modified: 2010-04-16
I need to update Two Tables in SQL 2000, row at a time, using two different datasets.  The following is my requirement:
1.  Insert a row in Table1 using a Datset1 - Already Dataset got new records added.
2.  Get the identity Column from the table1.
3.  Insert row in Table2 using DataSet2 (already with new records).  Primary Key of Table1 is Foreign Key in Table2.
4.  Above insert process should be in the same transaction ( i mean same commitment cycle).  If the insert failed in Table2, rollback should occur in both tables.

Also I defined the relationship between dataset1 and dataset2.  Please suggest me the solution.
Question by:vikkyv
    LVL 20

    Expert Comment

    You need to follow these steps (I assume you use adapters to update the data):

    1. Open the connection that the adapters use
    2. Start a transaction by calling connection.BeginTransaction()
    3. Assign the transaction to the update/delete/insert commands of the adapters
    4. Assign new values to the primary/foreign key fields directly in the dataset. Otherwise you need to execute an intermediate operation after every update to get the new ID
    5. Execute the update in the first dataset. On error rollback the transaction using the transaction.Rollback() method
    6. Execute the update in the second dataset. On error rollback the transaction
    7. Execute transaction.Commit()
    8. Close the connection

    Author Comment

    Thanks for your quick response.
    But I do not know the value of Primary Key Value of Table1 until I insert the row in that Table.
    LVL 20

    Expert Comment

    You can change your database to insert the primary key values, not to generate them. The other option is to get the value of the @@IDENTITY variable from SQL Server after every insert, for which you need to handle the RowUpdated event of the dataadapter and check which was the original value of the row just updated (the row is given in the event arguments and you can easily check it) and you need to execute a separate command in the same transaction to get the value of @@IDENTITY (something like: SELECT @@IDENTITY)

    Author Comment

    Thanks again..
    Do you mean to commit the insertion of each row?  Will this not become a performance issue?
    LVL 20

    Accepted Solution

    No, not to commit the insertion. When you call the Update method of the adapter, it actually calls insert/update/delete for every changed row in the table. After every insert/update/delete it will fire the RowUpdated event. So you can start another command here, wait for it to return your new ID, change it in both tables and then let the adapter go on with the next row. You only need to be careful to execute the command for getting the ID in the same transaction (so assign the command.Transaction property to the previously created transaction).

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
    Article by: Najam
    Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now