insert parent-child rows with transaction

[adeveloper]

hi,
i have a typed dataset containing two data tables and a data relation
which links the two tables in a parent-child manner.

i use a sqltransaction to control the insertion of records into the
database.

here is the code for the insertion.

foreach (SeminarData.ActivityRow activity in dsSeminar.Activity) {
int newActiviyId = InsertActivity(activity);

foreach (SeminarData.ActivitySpeakerRow speakerRow in
activity.GetChildRows("relActivity_ActivitySpeaker")) {
speakerRow.ActivityId = newActiviyId;
InsertActivitySpeaker(speakerRow);
}
}

the two Insertxxx() methods each takes a datarow and sets up a
datacommand and executes the command.

after i have inserted a parent row, i take the returned
auto-incremented ID of the parent row, assign it to the child row, and
try to insert the child row.

however, because this is done in a transaction, the new ID of the
parent row is probably not yet inserted into the database, so i keep
getting this error:
"ForeignKeyConstraint relActivity_ActivitySpeaker requires the child
key values (691) to exist in the parent table."

691, is this case, is the new ID of the parent row which is supposed to
have been inserted.

It seems that I may have two options,
1. commit the transaction before inserting the child row. but that
defeats the whole purpose of using transaction as the first place.
2. Somehow update the in-momery parent rows with the new IDs. however,
when i try to assign the new IDs to each parent row, i get error
message saying that the ID column is read-only, cannot be updated,
..understandable.

So, how can i insert parent-child rows within a transaction?? I don't
want to go for a dataadapter, it would be too much change to the
dataaccess code. please enlighten me.

 

[jmbledsoe]

If you want to use a tool for your operation, there's one called the
DataSet Toolkit. It deals specifically with parent-child inserts like
you're doing. Check it out here:

http://www.hydrussoftware.com

Let me know if that solves your problem.

John B.
http://johnsbraindump.blogspot.com