DataAdapter and foreignkey constraints

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.
<Joerg Fischer> wrote in message
news:%23gUr6TBMGHA.1760@TK2MSFTNGP10.phx.gbl...
> 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
>
>

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


"W.G. Ryan eMVP" <WilliamRyan@gmail.com> schrieb im Newsbeitrag
news:Ovk4InBMGHA.1124@TK2MSFTNGP15.phx.gbl...
> 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.
> <Joerg Fischer> wrote in message
> news:%23gUr6TBMGHA.1760@TK2MSFTNGP10.phx.gbl...
>> 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 Fischer> wrote in message
news:%236fm8lCMGHA.2012@TK2MSFTNGP14.phx.gbl...
> 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!
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.
>
> Sincerely
>
> Joerg Fischer
>
>
> "W.G. Ryan eMVP" <WilliamRyan@gmail.com> schrieb im Newsbeitrag
> news:Ovk4InBMGHA.1124@TK2MSFTNGP15.phx.gbl...
>> 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.
>> <Joerg Fischer> wrote in message
>> news:%23gUr6TBMGHA.1760@TK2MSFTNGP10.phx.gbl...
>>> 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
>>>
>>>
>>
>>
>
>