Complex update Command objects

  • Thread starter Thread starter Mervin Williams
  • Start date Start date
M

Mervin Williams

If my SelectCommand points to a stored procedure containing a select
statement which joins two tables, my DataTable will consist of the results
of the query. Here is my statement:
SELECT * FROM Contact INNER JOIN Company_Contact ON Contact.contact_id =
Company_Contact.contact_id WHERE (Company_Contact.company_id = @companyid)

Well, since that is the case, how do I write my UpdateCommand,
InsertCommand, and DeleteCommand objects to update the two different tables
contained in the join query?

Thanks in advance,

Mervin Williams
 
hi marvin.
i suggest that you post you question at:
microsoft.public.sqlserver.programming
you will probably get a better answer there.
 
Mervin,

The short answer is that DataAdapters are designed to submit updates
against a single table.

Submitting updates made to a resultset based on a join is a challenge,
and how you handle a particular scenario (an insert, for example) may
depend on your application.

To handle this challenge as is, you'll need to write a lot of code.
For example, when you find a DataRow whose RowState is Modified, you'll
need to interrogate the row to see if the change requires executing an
UPDATE query against just one table, or both. If you find a DataRow that's
marked as Inserted or Deleted, you'll need to determine whether that action
should be applied against just the "child" table, or both tables. Once you
determine which table(s) you need to update for a particular DataRow,
you'll need to execute the appropriate query and determine if the update
attempt succeeded. This process gets a little more complicated if the
changes to a DataRow involve submitting updates against multiple tables,
and the first update attempt succeeds but the second fails.

A simpler approach might be to store the data from each table in a
separate DataTable in a DataSet and relate the data using a DataRelation.
You could then use a separate DataAdapter to submit pending changes in each
DataTable with a lot less code.

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.
© 2004 Microsoft Corporation. All rights reserved.
 
Back
Top