DataAdapter not updating all columns of modified row via. CommandBuilder

[Marina]

Right, and it makes sense that the command builder would be smart enough to
avoid trying to update columns if there were no changes in them. It means
less work for the database server to do, less network traffic.

Otherwise we probably wouldn't need a CommandBuilder object at all. Just
some sort of GenerateCommands method on the adapter, to generate static
update/insert/delete commands on itself.

Holy moly .. you're right .. I see it now .. it's a minor difference, but
WOW ..

But that is craziness .. Hmm .. !!!

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------

No, those are *not* identical.

In the first row, you are updating Column1. In the second row, you are
update Column2.

If you were to update both columns, you would see the SET updating both
Column1 and Column2.

So clearly, each row's update statement changes depending on what columns
were updated in it.

Well the code sample is easy to reproduce .. yeah .. but I wasn't able to
repro your claim that UpdateCommand actually changes as you go ..

So I wrote up the sample as you say below, and here are the update
commands
I got for two rows.

UPDATE [MyTest] SET [Column1] = @p1 WHERE (([MyTestID] = @p2) AND ((@p3 =
1
AND [Column1] IS NULL) OR ([Column1] = @p4)) AND ((@p5 = 1 AND
[Column2]
IS
NULL) OR ([Column2] = @p6)))
UPDATE [MyTest] SET [Column2] = @p1 WHERE (([MyTestID] = @p2) AND ((@p3 =
1
AND [Column1] IS NULL) OR ([Column1] = @p4)) AND ((@p5 = 1 AND
[Column2]
IS
NULL) OR ([Column2] = @p6)))

As you can see, they are identical. DataAdapter, therefore DOES NOT change
UpdateCommands.

Here is one more thing to consider. A DataAdapter is completely blind
to
the
fact that the commands came out of CommandBuilder. So if it does indeed
change commands on the fly, it may even change the commands that I
specified
(not command builder) - which would be completely catastrophic.

Anyway, DataAdapter, does not change UpdateCommand.CommandText.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
--------------------------------------------------------------------------

--

---------------



No, I am just creating a command builder. I didn't think a sample was
necessary, because reproducing it is so trivial.

Here is code that updates 5 rows. Each row has only 1 column updated, but
it
alternates:

Dim conn As New SqlConnection("Data Source=(local);Initial Catalog=
MyDatabase;user=sa;Password=mypassword")
Dim adap As New SqlDataAdapter("Select top 5 MyKeyColumn,
Column1,
Column2 FROM MyTable", conn)
Dim cb As New SqlCommandBuilder(adap)
Dim dt As New DataTable

adap.Fill(dt)

For i As Integer = 0 To 4
dt.Rows(i)((i Mod 2) + 1) = " " + dt.Rows(i)((i Mod 2) +
1).ToString
Next

adap.Update(dt)

Okay wait a minute ..

In the suggested sample you specify below, how exactly is the
UpdateCommand
being filled in? Are you creating a command builder, then getting
UpdateCommand, and then put that on a DataAdapter?

Y'know a working code sample that repro's what you say below would
be
*really* helpful

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

------------------------------------------------------------------------- -
--
---------------



This is trivial to reproduce. Fille a DataTable with 2 rows from a
table
with say 3 columns. The first column is the primary key, the other
columns
can be updated.

Then update the second column in row 1, and the third column in row 2.

Run your program.

You will see that the update statement that runs to update row 1 is
different that that which runs to update row2.
The first update statement includes updating only column 2 (because
column
3
has not changed), and vice versa for row 2. If the statement was
always
the
same, then both columns would always be updated.

Can you provide a repro case for this?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________


I've actually used the profiler to trace the statements as they are
being
executed by the dataadapter. They looked like they were
changing
depending on what particular columns each row needed updating.

I didn't say the actual UpdateCommand object was being changed
on
the
adapter (since there isn't even one defined because the
commandbuilder
is
being used)

message
Ah, ADO.NET does NOT change the UpdateCommand or any of the other
action
commands on the fly. They are frozen as set by the CommandBuilder.
ADO
does insert values into the Parameters on the fly, but that's it.
See
my
article on the CommandBuilder for more insight
(http://www.betav.com/msdn_magazine.htm). Many of us are not
particularly happy with the CommandBuilder in V1.0 and 1.1.
It's
somewhat better in 2.0 but I think it should be
avoided--especially
for
complex UPDATE/INSERT operations.

Since you're using SQL Server, you should be using SPs to execute
the
changes and building a tuned UPDATE statement for it to execute
(using
TimeStamp concurrency).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and
confers
no
rights.
__________________________________

Thats actually what the problem is. For the row in question (row
with
index of 2, out of 1610), 11 columns have changed values. Most
notably,
the "Country" column has changed from '' to 'USA'. This is
evident
when I introspect the DataRow with the current location set to
the
"Update(...)" call. When I step forward into the Update method,
not
all 11 columns appear in the SQL UPDATE statement. Ripping apart
the
SqlDataAdapter and looking at the UpdateCommand at this time,
I
see
the
UpdateCommand contains all columns.

If the DataAdapter does modify the SQL statement to include only
the
changed columns, then I fear that something is wrong with the
algorithm, or there is a bug. The UpdateCommand definitely lists
all
columns, and, more columns are changed than what is being sent
across
the wire.

I'm bewildered.

Thanks for the help!
-Sean

 

[Sahil Malik [MVP]]

Well .. but ..
a) It isn't that much DB traffic and
b) Query structure keeps changing.
and
c) How did the DataAdapter know that this command came out of command
builder?

- SM

Right, and it makes sense that the command builder would be smart enough to
avoid trying to update columns if there were no changes in them. It means
less work for the database server to do, less network traffic.

Otherwise we probably wouldn't need a CommandBuilder object at all. Just
some sort of GenerateCommands method on the adapter, to generate static
update/insert/delete commands on itself.

Holy moly .. you're right .. I see it now .. it's a minor difference, but
WOW ..

But that is craziness .. Hmm .. !!!

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
--------------------------------------------------------------------------

--

---------------

No, those are *not* identical.

In the first row, you are updating Column1. In the second row, you are
update Column2.

If you were to update both columns, you would see the SET updating both
Column1 and Column2.

So clearly, each row's update statement changes depending on what columns
were updated in it.

Well the code sample is easy to reproduce .. yeah .. but I wasn't

able
to

repro your claim that UpdateCommand actually changes as you go ..

So I wrote up the sample as you say below, and here are the update
commands
I got for two rows.

UPDATE [MyTest] SET [Column1] = @p1 WHERE (([MyTestID] = @p2) AND

((@p3
=

1
AND [Column1] IS NULL) OR ([Column1] = @p4)) AND ((@p5 = 1 AND
[Column2]
IS
NULL) OR ([Column2] = @p6)))
UPDATE [MyTest] SET [Column2] = @p1 WHERE (([MyTestID] = @p2) AND

((@p3
=

1
AND [Column1] IS NULL) OR ([Column1] = @p4)) AND ((@p5 = 1 AND
[Column2]
IS
NULL) OR ([Column2] = @p6)))

As you can see, they are identical. DataAdapter, therefore DOES NOT change
UpdateCommands.

Here is one more thing to consider. A DataAdapter is completely blind
to
the
fact that the commands came out of CommandBuilder. So if it does indeed
change commands on the fly, it may even change the commands that I
specified
(not command builder) - which would be completely catastrophic.

Anyway, DataAdapter, does not change UpdateCommand.CommandText.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

------------------------------------------------------------------------- -
-- updated,
but -
- row
2. confers
no they
are

 

[Marina]

Ok yes, not much more, but it's still more efficient to not send extra stuff
over the wire. And it is more efficient for the engine to not have to update
columns to a value they already have.

The adapter has to know about the commandbuilder (or else it wouldn't have a
way to get the commands at all), so presumably it calls something in the
commandbuilder to get the right command. In doing so, it must pass the row
it is about to update, and the commandbuilder examines the row, and returns
the appropriate statement.

I haven't looked at the decompiled version of that section of code or the
IL, but I'm guessing that's what it does.

Well .. but ..
a) It isn't that much DB traffic and
b) Query structure keeps changing.
and
c) How did the DataAdapter know that this command came out of command
builder?

- SM

Right, and it makes sense that the command builder would be smart enough to
avoid trying to update columns if there were no changes in them. It means
less work for the database server to do, less network traffic.

Otherwise we probably wouldn't need a CommandBuilder object at all. Just
some sort of GenerateCommands method on the adapter, to generate static
update/insert/delete commands on itself.

Holy moly .. you're right .. I see it now .. it's a minor difference, but
WOW ..

But that is craziness .. Hmm .. !!!

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
--------------------------------------------------------------------------

--

---------------



No, those are *not* identical.

In the first row, you are updating Column1. In the second row, you are
update Column2.

If you were to update both columns, you would see the SET updating
both
Column1 and Column2.

So clearly, each row's update statement changes depending on what columns
were updated in it.

Well the code sample is easy to reproduce .. yeah .. but I wasn't able
to
repro your claim that UpdateCommand actually changes as you go ..

So I wrote up the sample as you say below, and here are the update
commands
I got for two rows.

UPDATE [MyTest] SET [Column1] = @p1 WHERE (([MyTestID] = @p2) AND ((@p3
=
1
AND [Column1] IS NULL) OR ([Column1] = @p4)) AND ((@p5 = 1 AND
[Column2]
IS
NULL) OR ([Column2] = @p6)))
UPDATE [MyTest] SET [Column2] = @p1 WHERE (([MyTestID] = @p2) AND ((@p3
=
1
AND [Column1] IS NULL) OR ([Column1] = @p4)) AND ((@p5 = 1 AND
[Column2]
IS
NULL) OR ([Column2] = @p6)))

As you can see, they are identical. DataAdapter, therefore DOES NOT
change
UpdateCommands.

Here is one more thing to consider. A DataAdapter is completely
blind
to
the
fact that the commands came out of CommandBuilder. So if it does indeed
change commands on the fly, it may even change the commands that I
specified
(not command builder) - which would be completely catastrophic.

Anyway, DataAdapter, does not change UpdateCommand.CommandText.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

------------------------------------------------------------------------- -
--
---------------



No, I am just creating a command builder. I didn't think a sample was
necessary, because reproducing it is so trivial.

Here is code that updates 5 rows. Each row has only 1 column updated,
but
it
alternates:

Dim conn As New SqlConnection("Data Source=(local);Initial Catalog=
MyDatabase;user=sa;Password=mypassword")
Dim adap As New SqlDataAdapter("Select top 5 MyKeyColumn,
Column1,
Column2 FROM MyTable", conn)
Dim cb As New SqlCommandBuilder(adap)
Dim dt As New DataTable

adap.Fill(dt)

For i As Integer = 0 To 4
dt.Rows(i)((i Mod 2) + 1) = " " + dt.Rows(i)((i Mod 2)
+
1).ToString
Next

adap.Update(dt)

Okay wait a minute ..

In the suggested sample you specify below, how exactly is the
UpdateCommand
being filled in? Are you creating a command builder, then getting
UpdateCommand, and then put that on a DataAdapter?

Y'know a working code sample that repro's what you say below
would
be
*really* helpful

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

------------------------------------------------------------------------ -
-
--
---------------



This is trivial to reproduce. Fille a DataTable with 2 rows from a
table
with say 3 columns. The first column is the primary key, the other
columns
can be updated.

Then update the second column in row 1, and the third column in row
2.

Run your program.

You will see that the update statement that runs to update row 1 is
different that that which runs to update row2.
The first update statement includes updating only column 2 (because
column
3
has not changed), and vice versa for row 2. If the statement
was
always
the
same, then both columns would always be updated.

message
Can you provide a repro case for this?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers
no
rights.
__________________________________


I've actually used the profiler to trace the statements as they
are
being
executed by the dataadapter. They looked like they were
changing
depending on what particular columns each row needed
updating.

I didn't say the actual UpdateCommand object was being
changed
on
the
adapter (since there isn't even one defined because the
commandbuilder
is
being used)

in
message
Ah, ADO.NET does NOT change the UpdateCommand or any of the
other
action
commands on the fly. They are frozen as set by the
CommandBuilder.
ADO
does insert values into the Parameters on the fly, but
that's
it.
See
my
article on the CommandBuilder for more insight
(http://www.betav.com/msdn_magazine.htm). Many of us are not
particularly happy with the CommandBuilder in V1.0 and 1.1.
It's
somewhat better in 2.0 but I think it should be
avoided--especially
for
complex UPDATE/INSERT operations.

Since you're using SQL Server, you should be using SPs to
execute
the
changes and building a tuned UPDATE statement for it to execute
(using
TimeStamp concurrency).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and
confers
no
rights.
__________________________________

Thats actually what the problem is. For the row in
question
(row
with
index of 2, out of 1610), 11 columns have changed values. Most
notably,
the "Country" column has changed from '' to 'USA'. This is
evident
when I introspect the DataRow with the current location set to
the
"Update(...)" call. When I step forward into the Update
method,
not
all 11 columns appear in the SQL UPDATE statement. Ripping
apart
the
SqlDataAdapter and looking at the UpdateCommand at this time,
I
see
the
UpdateCommand contains all columns.

If the DataAdapter does modify the SQL statement to include
only
the
changed columns, then I fear that something is wrong with the
algorithm, or there is a bug. The UpdateCommand definitely
lists
all
columns, and, more columns are changed than what is being sent
across
the wire.

I'm bewildered.

Thanks for the help!
-Sean

 

[David Sceppa]

Sean,

If I remember correctly, the parameters used for server-side null
checking:

(@p42 = 1 AND MyCol IS NULL)

are set to 1 if the original value for that column in the DataRow is null.
Otherwise the parameter's value is set to 0. This process reduces the
amount of data passed to the back-end per update attempt for the positional
parameter technologies - OLE DB and ODBC. Again, if I remember correctly,
the CommandBuilder started using this approach in ADO.NET 1.1 rather than:

(@p42 IS NULL AND MyCol IS NULL)

There's a new property on the parameter classes in ADO.NET 2.0 called
SourceColumnNullMapping to help you achieve this behavior programmatically.
If you bind the parameter to a DataTable column with set this property to
True, the parameter will pass 1 to the database if the value in the DataRow
is null and 0 otherwise.

Relying on your own updating logic whenever possible, rather than
using CommandBuilders at run-time, is a good idea. However, I would like
to reproduce the behavior you described where the CommandBuilder is not
updating all modified columns. If I had to guess, I'd say that the
CommandBuilder will not try to update the calculated columns, such as
"rtrim(Surname) Surname".

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.
© 2005 Microsoft Corporation. All rights reserved.

 

[CleverMindworks]

David,

Thank you for the tip regarding the SourceColumnNullMapping! That was
EXACTLY what I was looking for! I understand the efficiency gain when
comparing "1=1" or "0=1" vs. a value column vs. null, but didn't know
the magic behind the curtain. :0)

I truly think the rtrim(...) was the culprit. Honestly, I'm quite busy
lately and haven't had too much time to play .. (Been exploring Team
System). I'll do what I can to help, for the benefit of others!

Cheers!
-Sean

 

[CleverMindworks]

So, I thought the SqlCommandBuilder set the update command once prior
to the SqlDataAdapter looping through the rows looking for changes.
It's as if the SqlDataAdapter is the culrpit here. Perhaps any derived
columns just break the update statement generation algorithm in
DataADapter. (Which breaks the assumption that it uses the same update
command for each row, despite changed columns)

Bizarre, but at least it's not a secret. I wonder if this is a bug or
there is a better explanation.

Cheers!
-sean

 

[Sahil Malik [MVP]]

Sean,

Turns out this is intentional. We had raised it to the dataworks team, and
apparently the command builder works with the dataadapter in changing the
command.

I must say it was quite a surprise to me , but glad that an explanation
exists.

SM