asked on

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.

TheAvenger

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

vikkyv

ASKER

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.

TheAvenger

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)

vikkyv

ASKER

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

ASKER CERTIFIED SOLUTION

TheAvenger

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial