DataAdapter and foreignkey constraints

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Using dataadapters, I got the follwing question:

Given a DataSet with two tables (lets call them HEAD and ROW). As you can
imaging, there is a foreign key constraint indicating that every ROW entry
must have a corresponding HEAD entry.

So far so good. Now, I fill a dataset with these two tables. During the
modification, I delete HEAD entries (including their corresponding ROW
entries), and I create new HEAD entries (with subsequent ROW entries).

The problem arises when trying to update the database:

With this code:

ds.Update(dataAdapterHEAD)
ds.Update(dataAdapterROW)
....fails because during the update, I cannot delete HEAD table entries as
long as there are still corresponding ROW entries.

ds.Update(dataAdapterROW)
ds.Update(dataAdapterHEAD)
....fails because I cannot create ROW table entries as long as there is no
corresponding HEAD entry.


Which way to most easily overcome this? Is there a way to limit the Update
command to only execute either INSERT; DELETE or UPDATE?

Thanks for the enlightning!

Sincerely

Joerg Fischer
 
Joerg:

There are a few ways to handle this, but the most straightforward IMHO is to
do a RowState based update. For instance, you can specify which rows to
update based on Rowstate allows you to specify Updated rows first, deleted
next, inserted last or whatever combination you need.

http://www.knowdotnet.com/articles/rowstateupdate.html

That link shows you how to do it. I think that should fix this problem but
if not, please let me know.
 
Hi,

Thanks for the input! I already new the concept or RowState, but what was
missing from my mind was the GetChanges method which allows me to filter
what modifications I want!

Perfect, thanks a lot!

Sincerely

Joerg Fischer
 
Hi,

Thanks for the input! I already new the concept or RowState, but what was
missing from my mind was the GetChanges method which allows me to filter
what modifications I want!

Perfect, thanks a lot!
Click to expand...
Glad it worked. By using GetChanges, you reduce the number of rows you're
working with and in remoting scenarios, it's potentially a big deal. You
can still just use Updated/Inserted/Deleted but using GetChanges can greatly
enhance performance in many cases and it's definitely worth doing.
 
Back
Top