Update more than 1 table in a DataSet using SqlDataAdapter.Update

[LP]

Hello,

In the past I used SqlCommandBuilder and SqlDataAdapter .Update method to
apply changes in a DataTable back to its table source in SQL Server. It
worked fine when DataSet had only 1 table, this time I have 2 tables in a
dataset and I want to update them all at once. However it only updates the
first one and fails when attempts to update 2nd table.. Has anyone done
anything like that? It looks like SqlCommandBuilder generates insert command
only for the first table.

Here's the most relevant code, if it helps:

//command that gets 2 tables structure
SqlCommand commandClosedEnd = new SqlCommand("select f1, f2 from table1
where 1=2\r\nselect f1, f2 from table2 where 1=2", connClosedEnd);

commandClosedEnd.CommandType = CommandType.Text;
DataSet dsClosedEnd = new DataSet();
SqlDataAdapter daClosedEnd = new SqlDataAdapter();
daClosedEnd.SelectCommand = commandClosedEnd;//select command

daClosedEnd.Fill(dsClosedEnd); //gets 2 tables

/*
Code inserts new rows to both tables
*/

//Generate insert command
SqlCommandBuilder cmndBuild = new SqlCommandBuilder(daClosedEnd);
daClosedEnd.InsertCommand = cmndBuild.GetInsertCommand(); //should
genereate for 2 tables, but it doesn't
//update all 2 tables -- updates the first 1 and breaks on the second
foreach (DataTable tblUpdate in dsClosedEnd.Tables)
{
daClosedEnd.Update(dsClosedEnd);
}

Please help!!!!

 

[Guest]

Hi,

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/commandbuilder.asp

Ken

 

[LP]

Hi,
Thanks for the article it doesn't help me though. It reinforces my concern
about CommandBuilder limitations:

a.. The SelectCommand cannot refer to SQL queries, stored procedures, or
views that contain JOIN operators. This means your SELECT statement must
refer to a single table.

So, I think the answer is I can only update one table at a time or write my
own custom update command logic.

 

[Sahil Malik]

The CommandBuilder object is a quick and dirty way to get something done.
Even for the simplistic case you speak of, the SQL query it generates is
awfully ugly and inefficient. You are better off implementing your own
queries logic IMO.


--

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/

..