locked
How to: Update only related rows in child tables RRS feed

  • Question

  • Hopefully, I have titled this correctly.  I will freely admit that I am on unknown ground.

    I have a SQL database with 8 tables.  Seven of these tables relate to one table through a single column called SetId.  SetId is a unique value and the key on the main table (call it Sets). 

    I have created a strongly typed DataSet using VS 2008 DataSet designer.  I have added the relations that allow the correct selection of child records from the other 7 tables based on the SetId column in the Sets table.

    My problem is with updates.

    Assume a table called Destinations.  There is a one to many relastionship between Sets and Destinations based on SetId.  If I update (via delete row, insert row etc) in the in memory Destinations dataset table row releated to more than one row in the Sets table and then apply an update to the Destinations table all changes are applied, not just changes that are related to the current row in the Sets table. I would like to be able to restrict updates in the child tables to rows that are releated to a given row in the Sets table.  It seems to me that there should be some sort of (almost) automated method of making that update work, but I have not found it.

    Do I need to create my on Insert, Delete, Update commands for each child table?  If so, how to I reference the current Sets row in those commands?

    I realize the above description is not very clear.  I do not know enough to explain it clearly.

    Thanks for your help.

    • Moved by jack 321 Friday, July 18, 2008 8:44 AM off topic
    Tuesday, July 15, 2008 5:55 PM

Answers

  • You do need to create Insert/Update/Delete commands for any table for which you intend to insert, update, or delete database rows.

    Once you've added those commands to the TableAdapter, you can call the TableAdapter's Update() method using a DataRow as its argument.  The TableAdapter will figure out, based on the row's RowState, if it should insert, update, or delete the row, and will call the appropriate command.  Then it'll call AcceptChanges() on the row only, leaving the other rows' RowStates unchanged.  This is what you want.

    If you've defined a DataRelation between the two tables in the DataSet Designer, code like this will update all the child rows for a parent:

    foreach (DataRow childRow in parentRow.GetChildRows(relationName)) 
       childTableAdapter.Update(childRow); 
     

    Tuesday, July 15, 2008 6:09 PM
  • Use the overload of GetChildRows() that takes a DataRowVersion argument.
    Saturday, July 19, 2008 5:04 PM

All replies

  • You do need to create Insert/Update/Delete commands for any table for which you intend to insert, update, or delete database rows.

    Once you've added those commands to the TableAdapter, you can call the TableAdapter's Update() method using a DataRow as its argument.  The TableAdapter will figure out, based on the row's RowState, if it should insert, update, or delete the row, and will call the appropriate command.  Then it'll call AcceptChanges() on the row only, leaving the other rows' RowStates unchanged.  This is what you want.

    If you've defined a DataRelation between the two tables in the DataSet Designer, code like this will update all the child rows for a parent:

    foreach (DataRow childRow in parentRow.GetChildRows(relationName)) 
       childTableAdapter.Update(childRow); 
     

    Tuesday, July 15, 2008 6:09 PM
  • Thank you for your response.

    I had code similar to this code earlier today, but it has one problem.  The problem is that if I do a delete of a row, then the GetChildRows does not return the deleted row so the loop never applies the update to that row so it is never actually deleted from the database.

    Changes to rows and inserted rows are handled just fine.  What is the trick to make deleted row work correctly?

    Thanks
    Tuesday, July 15, 2008 6:34 PM
  • Discussion in Data Platform Development


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, July 18, 2008 8:44 AM
  • Use the overload of GetChildRows() that takes a DataRowVersion argument.
    Saturday, July 19, 2008 5:04 PM
  • Thanks
    Monday, July 21, 2008 11:45 AM