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!!!!

[Ken Tucker [MVP]]

Hi,

http://msdn.microsoft.com/library/de...andbuilder.asp

Ken
-----------------------------

"LP" wrote:

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!!!!

[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.
"Ken Tucker [MVP]" <Ke**********@discussions.microsoft.com> wrote in message
news:6A**********************************@microsof t.com...

Hi,

http://msdn.microsoft.com/library/de...andbuilder.asp
Ken
-----------------------------

"LP" wrote:

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!!!!

[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/

..

"LP" <[e-mail]> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

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.
"Ken Tucker [MVP]" <Ke**********@discussions.microsoft.com> wrote in
message
news:6A**********************************@microsof t.com...

Hi,

http://msdn.microsoft.com/library/de...andbuilder.asp

Ken
-----------------------------

"LP" wrote:

> 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!!!!
>
>
>