TableAdapter insert/update/delete order

TableAdapter insert/update/delete order

• hamedghasemi

  Bingo! Good call. I was tearing my hair out.

  Thanks.

  
  

• stanford

  
  In order to do deletes the table adapter update commands need to be called individually for deletes, inserts, and modifications.  However when I put the code in to do this I get insert errors. The deletes work fine. It's the inserts of new data that fail. They fail because the dataset tables get out of sync with the database.

   

  As I understand it, here are the list of steps occurring:
   
  1) The app gives the db a parent record with id 5 to insert
  2) The db inserts the parent record but with an id of  6 (because 5 was taken)
  3) The db gives the app the newly inserted parent record id of 6
  4) The app gives the db a child record with parent FK of  5
  5) The update fails - or data integrity is gone.
   
  My current update code is missing a critical step between steps 3 and 4.  In this example, after step 3 the dataset should update the child datatable's parent FKs to 6.   Code that simply calls tableadapter update on the entire datatable handles this automatically, but you can't do deletes and inserts with it at the same time for the reason mentioned above.

  So how do I sync the child FKs to any modified parent PKs

  Thanks

  
  

• ChrisCasey

  The DataAdapter (or TableAdapter) implicitly calls the AcceptChanges method on a DataRow if it determines that it successfully submitted the pending changes in that DataRow.

  I hope this information proves helpful.

  
  
  

• jalal khodabandeh

  Hi David,

  This is a very good solution.

  How do can add transaction control to it For example, after one table's data is updated, and its tableadapter marks those rows from added to unchanged. But then for some reason, the whole transaction rolls back. How do we reset the rows in the first dataset back to added and inform the user

  Thanks.

  Eric

  
  

• Kaustubh194874

  No problem. Hair is a precious thing.

  
  
  

• Vladislav Levashov

  The DataSet is unaware of the transaction. If you might need to "roll back" the DataSet to a known state, you'll need to save the state when you start the transaction. You can save it to another variable using Copy, or write the contents to a file using WriteXml, etc. If you roll back the transaction, you'll need to use the copy of the DataSet you stored.

  I hope this information proves helpful.

  
  
  

• Mal409

  Now, getting back to the auto-increment issue. If you're working with a SQL Server database, the TableAdapter Configuration Wizard should have auto-configured the TableAdapter to retrieve the new auto-increment value after submitting the change to the database. If you're working with similar functionality for another database (Jet or MySQL auto-increment columns, Oracle sequences, etc.), you'll need to supply your own logic to retrieve this information.

  Hi - I am having the same issue, but I am using Jet. You mentioned that I need to supply my own logic for this. I tried to Fill the master adapter after the Update, but could not because of the child records. How should I go about doing this with Jet

  Scott

  
  

• domlev

  ok, I followed your advice and here's what happened - I've got a simple parent-child dataset. I insert a parent row and a child row. When I do the tableadapter.update(ParentTable.select("","",dataviewrowstate.added) it clears the .added flag for the ChildTable. The upshot is, the parent gets added but the child doesn't.

  Any ideas

  
  

• Praveen Chintala

  I too get foreign key constraints when performing an update of deleted records. In my case however there is only one datatable containing a tree structure (a foreign key to itself). So changing the order of the datatables is not applicable.

  Is there a way to change the order of the containing (typed) datarows

  I can put a cascade rule on the foreign key in the database, but won't that conflict with the data in the datatable (concurrency errors).

  Extra side note: Sadly we have to use Oracle and not SQL Server.

  
  

• B. Wesley

  Philip,

  First off, I'd recommend avoiding using DataTable.GetChanges for submitting the pending changes, if possible. GetChanges creates a new DataTable with copies of the modified rows from the original DataTable. Submitting updates in this fashion does not mark the modified rows in the original DataTable as unchanged at the end of the call to Update. Using this approach can also cause problems when working with auto-increment columns. GetChanges is more helpful in scenarios involving WebServices, or other components that require passing the contents of the DataSet into another process.

  A better option is to use the overloaded Select method on the DataTable, which will return an array of DataRows, without making a separate copy of the DataRows. The Select method accepts parameters for a filter (such as "WHERE Country = 'Canada'"), a sort order (such as "City DESC") and row states. You can pass empty strings for the first two parameters if you are not interested in using a filter or sort order. You can use this method as shown here to return just the deleted rows:

  DataTable.Select("", "", DataViewRowState.Deleted)

  The Update method on the DataAdapter and TableAdapter classes is overloaded to handle an array of DataRows, so your code would look like:

  //Delete from child
  tableadapter_child.Update(myChildTable.Select("", "",
                            DataViewRowState.Deleted);

  It sounds like you have the logic (top-down for inserts and updates, bottom-up for deletes) correct. I didn't see a reason for the DBConcurrencyException you described.

  Now, getting back to the auto-increment issue.  If you're working with a SQL Server database, the TableAdapter Configuration Wizard should have auto-configured the TableAdapter to retrieve the new auto-increment value after submitting the change to the database. If you're working with similar functionality for another database (Jet or MySQL auto-increment columns, Oracle sequences, etc.), you'll need to supply your own logic to retrieve this information.

  By using DataTable.Select rather than DataTable.GetChanges, the newly retrieved auto-increment values will be stored in your original DataSet rather than a seprate copy that contains only the changed rows. Once you've retrieved these values into your DataSet, the goal is to have those new values cascade down to the related child rows.

  The strongly typed DataSet automatically creates DataRelations to help you move from parent rows to child rows (or from child rows to parent rows). However, the wizards that create these DataRelations in Visual Studio .NET 2005 do not create ForeignKeyConstraints for the DataRelations. It's really the ForeignKeyConstraint that handles cascading changes to related rows.

  To create a ForeignKeyConstraint for your DataRelation, select the DataRelation in the strongly typed DataSet designer and edit the DataRelation (by double-clicking on the DataRelation, or using the Visual Studio .NET menu). On the dialog that appears, select "Both Relation and Foreign Key Constraint" and set the Update and Delete Rules to Cascade.

  Now when your TableAdapter retrieves new auto-increment values after submitting pending inserted parent rows, those new values will automatically cascade down to the related child rows.

  I'd also strongly recommend having ADO.NET generate negative placeholder values for auto-increment columns for pending inserts (-1, -2, -3, ...). Select the auto-increment column in the strongly typed DataSet designer. Then, in the Properties window, set the AutoIncrementSeed and AutoIncrementStep properties to -1. This approach ensures that the placeholder values that ADO.NET generates will not conflict with values that already exist in the database. Another benefit to this approach is that it avoids the possibility of violating constraints within the DataSet.

  I hope this information proves helpful.

  
  
  

• Chanduu

  I was struggling on this until I found the new TransactionScope

  I wrapped the various tableadapter Update calls inside a

  using (System.Transactions.TransactionScope trans = new System.Transactions.TransactionScope())

  {

  // do my tableadapter updates here

  }

  worked for me

  
  

• webmaven

  John,

  It sounds like the ForeignKeyConstraint that's associated with your DataRelation has its AcceptRejectRule property set to Cascade.

  The DataAdapter (and TableAdapter) implicitly calls AcceptChanges on a DataRow during Update if it determines that it successfully submitted the pending change stored in that DataRow. This helps make sure that multiple calls to DataAdapter.Update won't try to submit the same pending changes multiple times. As a result, the RowState of the DataRow will be set to Unchanged after submitting the pending insert.

  The ForeignKeyConstraint class' AcceptRejectRule will cascade the call to AcceptChanges (or RejectChanges) if the property is set to Cascade. It sounds like that's what's happening here. If you set the property to None (its default), you should see the desired behavior.

  I hope this information proves helpful.

  
  
  

• Sriharivvv

  Does the tableAdapter mark the rows   I thought only a call to AcceptChanges/RejectChanges changes the row's state to unchanged.
  

• AParker

  Works like a charm! Thank you David.  This is exactly what we were looking for. 

  You may want to talk to whoever's incharge of the following document as it
  had us going down the wrong road for several weeks.

  http://msdn2.microsoft.com/en-us/library/ms171933.aspx

  
  
• TableAdapter insert/update/delete order