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 (9)

  1. Andy Smith says:

    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.

  2. Phil Scott says:

    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…

  3. Addy Santo says:

    Phil-

    I can get it to work with GetChanges() and separate blocks for delete/insert/update, however it gets ugly since DataRowState.Modified seems to include delete rows, etc so I get into a whole load of filtering logic.

    But anyhow, the splitting and execution blocks involve a fair amount of logic which I had hoped would be encapsulated by the dataset object. If I need to code it, test it and maintain it then it really isn’t worth the effort since I am not getting any real added value or reduction of complexity compared to the usual SqlCommand implementations.

  4. Dating says:

    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

  5. Weddings says:

    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