update order

  • Thread starter Thread starter Jon the Blind
  • Start date Start date
J

Jon the Blind

I feel like I must be missing something here. I'm using SQL Server 2000 and
ADO.NET. I have 2 tables, a master and a detail table with the usual
relationship. Cascade deletions are disabled by design (though I realize
enabling them would solve this issue).

Now, in a DataSet, say I create some records in the master and detail tables
and delete some in both tables as well.

If I update the master table first I violate the foreign key relationship
because it orphans rows in the detail table. If I update the detail table
first, I'm violating the same relationship because the parent rows haven't
been created in the DB yet.

Is there a way in ADO.NET to submit these updates all at once before the
database checks the constraints, something equivalent to the DataSet's
EnableConstraints functionality? (In other words, can I update each table
only once, or do I have to submit the master inserts, then update the detail
table, then submit the master table deletions?)

Any help is appreciated.

Regards,

Jon
 
Nope, you have to (add/change the parents and delete existing children)
first and then (add new children and delete any parents) next.
You can't delete parents with existing children or add children that don't
have existing parents. David Scheppa's book details this pretty well.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Jon,

Bill is correct about the order of updates. Submit pending deletions
"bottom up" and pending insertions "top down". The DataTable's Select
method has an overload that lets you select rows of a particular RowState.
You can use that call in conjunction with DataAdapter.Update. Your code
would look something like...

//Submit the child deletions
ChildAdapter.Update(ChildTable.Select("", "", DataViewRowState.Deleted))

//You can now submit all pending parent rows
ParentAdapter.Update(ParentTable)

//Submit the child inserts and updates
// Note: Since you've already submitted the deletions,
// you can submit all remaining pending changes
ChildAdapter.Update(ChildTable)

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.

P.S. I finally get a plug from Bill and he misspells my name. ;-)
 
Back
Top