Update 2 Database Tables in a single transaction

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.
Who is Participating?
TheAvengerConnect With a Mentor Commented:
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).
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
vikkyvAuthor Commented:
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.
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)
vikkyvAuthor Commented:
Thanks again..
Do you mean to commit the insertion of each row?  Will this not become a performance issue?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.