updating related tables in proper order

[Dmitry Duginov]

I have multiple related tables in the database which contains customer
accounts. I'm loading single account data from those tables into multiple
DataTables within the same DataSet. Any changes can be made with the data:
records can be added, deleted, edited. Because of PK/FK constraints it's
clear that the changes must be posted back to the database in specific order
to avoid RI violations. I'm using stored procs for UpdateCommand,
DeleteCommand, SelectCommand.

What are the best practices to achieve this using as much built-in ADO.NET
intelligence as possible?

 

[W.G. Ryan MVP]

I have multiple related tables in the database which contains customer
accounts. I'm loading single account data from those tables into multiple
DataTables within the same DataSet. Any changes can be made with the data:
records can be added, deleted, edited. Because of PK/FK constraints it's
clear that the changes must be posted back to the database in specific
order
to avoid RI violations. I'm using stored procs for UpdateCommand,
DeleteCommand, SelectCommand.

What are the best practices to achieve this using as much built-in ADO.NET
intelligence as possible?

It will depend on some degree to the specific situation, but in the
overwhelming number of cases, you'll fire the CRUD commands on the parent
table first, then the children. Obviously if the rows don't exist in the
parent table yet, firing inserts to the child db table will violate the
constraints. Other than the obvious contstraint issues, I don't know of any
best practices but I'll look around and see if I can find any.

As a FYI though, you can always use RowVersions in your updates to deal with
only a subset of rows. So for instance, you were using INserted Rows. You
coudl fire Update on the parent specifying only inserted rows, then you
could do the same for the child. You can do the same for any of the
rowstates to finely specify your updates. But across the board I can't
really think of any hard and fast rules outside of the scope of the
integrity constriants

 

[Dmitry Duginov]

Basically, what are you saying is that during DataAdapter.Update(DataSet)
for multiple related tables ADO.NET is unable to use PK/FK info from
existing DataRelation objects in order to call database delete/insert/update
commands in proper order?

Probably I want too much and DataAdapter.Update(DataSet) is a simple
equivalent of DataAdapter.Update(DataSet,DataTable) calls with no specific
order...

 

[Kevin Yu [MSFT]]

Hi Dmitry,

Yes, as you know, the DataAdapter will not update automatically using the
PK/FK info in the dataset. So the best way is to use the following process
to update data:

Parent insert
Child insert
Parent update
Child update
Child delete
Parent delete

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Dmitry Duginov]

Hi Dmitry,

Yes, as you know, the DataAdapter will not update automatically using the
PK/FK info in the dataset. So the best way is to use the following process
to update data:

Parent insert
Child insert
Parent update
Child update
Child delete
Parent delete

That's good for simple relationships. It gets worse if the same table is a
parent and a child for two different tables. With information about PK/FK
DataAdapter could make all the calls in correct order, saving A LOT of time
for the developer to write it manually. Too bad. Let's hope VS 2015 will
have smart DataAdapter

 

[Kevin Yu [MSFT]]

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."