commandBuilder Update SQL generation for > 1 table?

[Mark]

In ADO, I could issue a query (using an MSS provider) which returned a
result set from multiple tables, and update the data in a grid and
post it. (I'm actually not quite sure how that works but it does).

In ADO.NET I can't do this, I have to roll my own UPDATE, which isn't
possible for ad-hoc queries.

Does anyone know why this changed and if there is any technique to
allow updates of ad-hoc queries in a data grid involving multiple
tables?

Thanks

 

[William Ryan eMVP]

In ADO, I could issue a query (using an MSS provider) which returned a
result set from multiple tables, and update the data in a grid and
post it. (I'm actually not quite sure how that works but it does).

In ADO.NET I can't do this, I have to roll my own UPDATE, which isn't
possible for ad-hoc queries.

Does anyone know why this changed and if there is any technique to
allow updates of ad-hoc queries in a data grid involving multiple
tables?

It changed b/c the ADO.NET paradigm entails decoupling the data from the
database. In old school ado, you'd have a bitch of a time grabbing a
recordset from SQL Server and having it elegantly created in excel. Not so
in ADO.NET. You'd have a bear of a time grabbing data from all sorts of
places.

Thanks

You don't need to roll your own logic although it's advisable to . You have
TRemendous power in client side queries with DataTables and DataViews and
while it isn't as rich as ANSI SQL, it's not far from it. So the trick is
to pull your data over into different tables that mirror your database, link
them with DataRelations where applicable, modify your data, and send
updates against each individual table. If you don't care about performance
or concurrency, the commandbuilder will do it for you. Check out
www.betav.com ->Articles ->MSDN, Weaning develoeprs from the
CommandBuilder...it's a great discussion on this topic.

HTH,

Bill