Batch Update + Parent/Child + AutoIncrement/Identity = Yikes!

[skavan]

Hi,

I've seen about 2,345 messages on how to submit parent/child rows via
ado.net.

From what I've gathered, the process goes:

1. Make Connection, Grab Tables/DataSet & Build CommandAdapters
2. Build relations
3. Make Changes
4. Post parent row with event handler (ms Access).
5. Grab new ID in event handler with new SQL command,
6. Post new ID into the parent Row updated in #4.
7. Watch the Cascade
8. Update the Children

It's a bit of a pain - but ok...but what about the case where we have
500 parent rows and 10,000 child rows to post back to the Database?
There has to be a batch process that works.
i.e.:

1,2 & 3 as above.
4. Post all parent rows in a batch update.
5. Grab all the changed parent rows in one pull.
6. Apply the new Keys in 5 to the underlying parent records (that
cuttenly have -1 to -100 in them).
7. Watch the Cascade
8. Update the children.

I can't figure out how to do #5 and #6 without losing the relationship
to the child rows.
Can anyone help? I'm using vb.net and ms access.

My use case is a music library.
The parent table is tblArtists, with ArtistID (autoincrement, primary
key) and ArtistName (string).
The child table is tblTracks, with amongst 70 other things, an ArtistID
field that connects to its parent.
I have an inbound object that has a set of child table fields and the
ArtistName.
My code posts the ArtistName to tblArtists and the ArtistID that is
generated to tblTracks.
In a given pass, I will be creating around 500 artist rows and 10,000
track rows.

Thanks in advance.

 

[jmbledsoe]

I don't want to sound like a marketing guy, but this is the exact same
problem that I had. It was such a PITA, that I built a tool to do this

for me, and now my company is selling it (free to try, and under $100
to buy). It's called the DataSet Toolkit, and it has a class called
the MultiTableDataAdapter which does fills and updates of multiple
tables in a DataSet.

One feature is that it automatically re-fills rows during your Update
operation, after saving them to the database. That way, the identity
primary key value for each row is retrieved back into the parent table
in the DataSet. If your DataRelations cascade updates, then your child
DataRows will be updated with the key value from the database, so that
when you Update those, they will get into the database properly.

Try this out, and let me know what you think.

http://www.hydrussoftware.com


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