DataAdapter update: multiple table joint dataset

[feng]

We are planning to use ADO.Net's SQL data adapter to do
the data updates. The problem we are facing is that if the
data adapter is based on a dataset that comes from a
single database table it's all fine, but if the dataset is
based on joining multiple tables, we don't know how we can
update it, or even is it possible at all. I remember I
came across some article that mentions that ADO.Net can
take care of this kind of updates in somewhat
transactional approach but I can't find anything that
shows me how. Can someone help us on this?

Thanks

Feng

 

[W.G. Ryan eMVP]

Create an adapter for each table you want to pull over. Fill each table
individually instead of joining them . Use a DataRelation to enforce the
integrity rules and make binding worht doing if you're using controls. Make
changes as you see fit. Call update on each table starting with the parent
and moving to each child in accord with your integrity constraints.

http://www.knowdotnet.com/articles/datarelation.html

 

[Guest]

Hi W.G.,

Thanks for your valuable input! I think that's direction I
am going to take. I just have one other question related
to this: When I call updates on those data adapters, is it
my responsibility to implement transactions or the ADO.Net
will do it for me? In other words, if one of the child
table's update failed, do I need to menuly test that and
roll back the parent table's update?

Thanks again!

Feng

 

[Alex Homer]

There are several issues involved, including the order which you update the
rows in the two tables to preserve integrity and avoid foreign key errors
across the join. This example:
http://www.daveandal.net/books/4923/updating-data/update-orders-demo.aspx
shows how it can be done for two related tables, and you can view the source
code for the page and the component it uses. The whole issue is discussed
in:
http://www.daveandal.net/books/4923/
Drop me a mail through the feedback link on that site if you want more
info...