|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DataAdapter and foreignkey constraintsUsing 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 Show quote 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 Show quote "W.G. Ryan eMVP" <WilliamR***@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... Glad it worked. By using GetChanges, you reduce the number of rows you're > 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! 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. Show quote > > Sincerely > > Joerg Fischer > > > "W.G. Ryan eMVP" <WilliamR***@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 >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics