Does the order of the update matter?

[TheNortonZ]

I have a dataset with several tables in it. Some of the tables are related,
such as like where I would have an 'Order' parent table and an 'Order
Details' child table. The 'Order Details' also has a child table to it named
'Order Years' table. 'Order Details' has a foreign key of the primary key in
the Order table. The primary key in the Orders table is a guid.

'Order Years' has a foreign key to the 'Order Details' table. The primary
key of the 'Order Details' table is a guid.

I have made sure in my code, when I delete an order, I delete all associated
order details and then I have an inner loop that deletes all associated
order years data. Then I send the dataset to be saved to the database.

I do not have any data relationships setup in the dataset for these tables.

When I got to add/update/delete data in these sets, I create a data adapter,
set the select, update, insert etc commands to stored procedure calls and
then call .Update on that individual table.

In the code, I call update on the parent table, then on the child table that
has the foriegn key.

It seems that no matter which order I set these updates in, when I call the
child 'years' Update, I get a concurrency violation for the delete command.

Does it matter what order these updates are called in?

Thanks.

Norton

 

[Jeff Dillon]

You always have to delete all lowest children first.

Jeff

 

[jji]

Hi, Norton

I think you don't need to setup data relationship in the dataset , if you
don't want to. But you will access child rows and parent row based on
relationship object, if you set up a relationship between the tables in
dataset, which you can programmatically create it or setup in the dataset
schema.

As fas as I know, the good squence of the update dataset could keep data
integrity and referential integrity on database

You could follow the order below.

process grandchild row that are deleted , i.e. call
DataTable.GetChanges(RowState.Deleted) and call Adapter to update the
changes.
process child row that are deleted
process parent row that are deleted

why? This will keep your identity field in data table has correct number.

process parent rows that are updated i.e. call
DataTable.GetChanges(RowState.Modified) and call Adapter to update the
changes
process parent rows that are inserted i.e. call
DataTable.GetChanges(RowState.Added) and call Adapter to update the changes

why? This make trigger invoked and keep referential key ready for child
rows

process child rows that are updated
process child rows that are inserted
process grandchild rows that are updated
process grandchild rows that are inserted.


HTH,

Jason

 

[TheNortonZ]

Ok, but if my .Update statements start off with the lowest children first,
what happens when an insert is to be performed? I can't do an insert in a
child until the parent is completed, right?

Norton.

 

[TheNortonZ]

Well, I moved around a bunch of code to follow this sequence and about all I
can say is, things really went haywire.

Of course, I used DataSet.GetChanges, not DataTable.GetChanges like this:
adapter.Update(dataset,"tablename")

Not sure if this would matter.
Norton

 

[Jeff Dillon]

Sorry, I thought you asked about delete.

Yes, inserts are just the opposite. You need the primary key for the parents
before inserting children (obviously)

Jeff

 

[jji]

I think it's better idea that you update database against datatable ,
instead of dataset, and the best is passing the DataRow array to Update
method of DataAdapter
to process the modified rows.

Why? Because, if you get changes basing on DataSet , you might get modified
parent row by filtering child deleted row ( if there are some relationships
inside)
When you update this kind of DataSet, DataAdpter will update each row ,
based on the order of the indexes. not on RowStat ( don't forget you might
have modified row in parent table)

BTW, you should write your Sql statment for DataAdapter carefully, if you
have any parameters as input,

i.e. used original value of the row to make comparison , when you create
update command text.

HTH,

Jason