DataSet update questions

Just like everyone else I usually use datasets for database reads only. When updating, I simply write the changes directly to the database using SqlCommands.  In my latest app I went in search of trouble and decided use .Update(DataSet) for writes.  There were several implementation details which were semi-ugly, but the main thing that currently has me stumped is related to referential integrity:

If I have 2 tables, lets say Orders and OrderRows and the following code:

OrdersDA.Update(MyDataSet("Orders"))
OrderRowsDA.Update(MyDataSet("OrderRows"))

The code will work for inserts but not for deletes, since deletes must be done in the reverse order:

OrderRowsDA.Update(MyDataSet("OrderRows"))
OrdersDA.Update(MyDataSet("Orders"))

So basically I can't just modify the dataset in the biz layer and send it to a generic "update" method in the data layer which persists the changes to the database - I actually need to know much than I want in inside the data later to in order to make this work. 

Is there something simple which I am missing (code samples anyone?) or is really as limited as it seems? 

 

P.S. I guess my mom knew what she was talking about when she said that Metallica is torture - US military interogating Iraqi POWs with Sesame Street & Metallica  :) 
<edited to remove any political overtones>

Comments

• Anonymous
  May 20, 2003
  You pretty much need to write a helper DataSetUpdater class.
  
  What you need to do is set up the relationships correctly in the dataset, and then get a correctly sorted list of the tables by their relationships such that the parent tables are before the child tables.
  
  From there, you can run forwards for updates and inserts, and then backwards for deletes.
  
  use the GetChanges overloads that take a RowState enum to get only the records you want to deal with.
• Anonymous
  May 20, 2003
  Take a look at the help in MSDN on using the dataadapter for updates (I can't remember the name of the article, and I don't have msdn installed here so I'm useless for that). I do know that it is linked to from the help from the Update method.
  
  Basically, what you do is use the select method to pull out the rows that have been updated or deleted or inserted since the last edit, and pass those to the dataadapter in distinct chunks in the proper order instead of the whole table at once.
  
  
  Good luck, and I'll try to get the article title for ya...
• Anonymous
  May 20, 2003
  The comment has been removed
• Anonymous
  May 30, 2008
  Just like everyone else I usually use datasets for database reads only. When updating, I simply write the changes directly to the database using SqlCommands. In my latest app I went in search of trouble and decided use .Update(DataSet) for writes. Ther
• Anonymous
  June 05, 2008
  Just like everyone else I usually use datasets for database reads only. When updating, I simply write the changes directly to the database using SqlCommands. In my latest app I went in search of trouble and decided use .Update(DataSet) for writes. Ther
• Anonymous
  May 31, 2009
  PingBack from http://outdoorceilingfansite.info/story.php?id=19848
• Anonymous
  June 02, 2009
  PingBack from http://patiochairsite.info/story.php?id=29216
• Anonymous
  June 11, 2009
  PingBack from http://castironbakeware.info/story.php?title=santomania-dataset-update-questions
• Anonymous
  June 16, 2009
  PingBack from http://topalternativedating.info/story.php?id=4075