DataAdapter not updating all columns of modified row via. CommandBuilder

  • Thread starter Thread starter CleverMindworks
  • Start date Start date
C

CleverMindworks

Hi everyone,

I have a C# application that is using SqlDataAdapter and
SqlCommandBuilder for performing updates to a single table in a SQL 7
database. For the most part, everything works just fine. I recently
hit a very strange issue, where it seems that not all columns are being
updated against the data source.

Scenario:
A DataSet object contains rows (1600) of data with the majority of
rows containing changes. (Determined by careful debugging and observing
the 'Current' and 'Original' values for the rows with state ==
Modified) For a changed row, for instance, there are columns
"AccountID", "Address1", "Country", "ExtraField1", etc.

Observations:
Digging into the SqlDataAdapter and looking at the UpdateCommand, I
can clearly see an update statement, in correct syntax, with 65
parameters. (As the Update command that is built contains the hints for
optimistic concurrency, so a bunch of extra 'where @pXX <> null ...'.
The key here is that all columns are listed in the statement. Using my
previous example, "Address1", "Country", "ExtraField1" are all listed
in the update clause.

Performing a SQL trace (filtering on the Text containing the PK), I
witness something rather disturbing. The statement looks like:
"UPDATE Accounts SET AccountID = @p1, ExtraField1 = @p2 WHERE ..."

It's quite subtle. This is the update statement that corresponds to
the row I was introspecting. There are a total of 11 updated columns
in the DataSet, yet, the UPDATE statement sent to the database contains
only the PK column and the last 6 columns. (Out of a total of 15).

==
So, if anyone knows how the DataAdapter builds the actual update
statement, perhaps you could lend me a hand. I'm suspecting a bug in
the DataAdapter that may be mitigated by the data, or perhaps the
schema info. The DataSet contains full schema info, including primary
key.

I'm thinking that manually writing the Update statement may solve the
issue, but I'm quite fond of letting the CommandBuilder do the work.
:0)

Any advice is greatly appreciated.

Regards,
-Sean
 
I think those statements are different by row. If a row only has 6 fields
updated, only those will be in the query. If all 10 are updated, then 10
will be in it.

Is all your data being updated or not? If it is, then dont' worry about it!
 
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
 
I have never heard of this issue before, so it may be a bug, but that is
unlikely, since in that case the commandbuilder would be completely
unusable.

Does your SELECT statement the commandbuilder is working off of contain all
the columns?

Is it the case for every row, the a particular set of columns get ignored?
What is it?

I think you need to put together a small reproduceable scenario. When you
are talking about tons of columns and tons of rows, it becomes difficult to
pinpoint the root of the problem, because there are so many factors
involved.
 
Thats a good point. I was hoping that someone would be like.. "Ah! I
know whats causing that!" :) The day's still young..

The first thing I'm going to to is replace the 'GetUpdateCommand' with
the literal UPDATE statement. (Gonna cheat and start with the statement
supplied by the CommandBuilder :)) And, remove the optimistic update
params.. simplify a bit.

Maybe something will jump out at me.

Cheers!
-Sean
 
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.
__________________________________
 
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)
 
I'm feverishly attempting to use a custom "Command Builder" of sorts.
Works similarly, except I see the code. Since the update statement
seemed kosher for the DataAdapter's UpdateCommand, it did seem like
something was modifying the statement. I'm gaining quite a bit more
insight into the command building process, and should have more
conclusive info by tomorrow afternoon.
I'll do what I can to solve this puzzle..

Regards,
-Sean
 
Is this CB being called each time you execute Update? If so, that might
account for what you're seeing. I would really encourage you to take another
course and build static Parameter-driven UPDATE commands.

--
____________________________________
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.
__________________________________
 
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.
 
Okay.. got things to work using a custom command builder. After
getting things to work, I viewed the problem from a few angles.
Ultimately, changing the select statement a bit made the CommandBuilder
and DataAdapter play nice.

For the benefit of others, this is was I found. Originally, I had set
the select statement to something similar to the following (redundant
columns removed for readability):

"select rtrim(Surname) Surname, rtrim(GivenName) GivenName,
rtrim(MiddleName) MiddleName, AccountID,
SSN, rtrim(Address1) Address1, rtrim(Address2) Address2 from Accounts
where AccountID LIKE @AccountID"

With this, viewing the UpdateCommand prior to the
DataAdapter.Update(...) method call, yields the following:

"UPDATE Accounts SET AccountID = @p1 , SSN = @p2 , State = @p3 , Title1
= @p4 , Title2 = @p5 , ExtraField1 = @p6 , ExtraField2 = @p7 ,
ExtraField3 = @p8 , ExtraField4 = @p9 , ExtraField5 = @p10 ,
ExtraField6 = @p11 , IsEmployee = @p12 , ... WHERE ( (AccountID = @p23)
AND ((@p24 = 1 AND SSN IS NULL) OR (SSN = @p25)) AND ((@p26 = 1 AND
State IS NULL) OR (State = @p27))
AND ((@p28 = 1 AND Title1 IS NULL) OR (Title1 = @p29)) AND ((@p30 = 1
AND Title2 IS NULL) OR (Title2 = @p31)) ..."

At this time, all columns seemed present in the UpdateCommand.
Thinking about this issue a bit, I did some digging into the Paramter
collection for the UpdateCommand. Things seemed legit. I'm amazed (and
curious) how the "NULL Flag" parameters are determined for each row
(ie, in this example, @p30.. @p28, etc.). I'd like to know how this is
done. :0)

However, tracing the calls to the DB with Sql Trace, I witnessed this:

"UPDATE Accounts SET AccountID = @p1 , ExtraField1 = @p2 , ExtraField2
= @p3 , ExtraField3 = @p4 , ExtraField4 = @p5 , ExtraField5 = @p6
WHERE ( (AccountID = @p7) AND ((@p8 = 1 AND SSN IS NULL) OR (SSN =
@p9)) AND ((@p10 = 1 AND State IS NULL) OR (State = @p11))
AND ((@p12 = 1 AND Title1 IS NULL) OR (Title1 = @p13)) AND ((@p14 = 1
AND Title2 IS NULL) OR (Title2 = @p15))
AND ((@p16 = 1 AND ExtraField1 IS NULL) OR (ExtraField1 = @p17)) AND
((@p18 = 1 AND ExtraField2 IS NULL) OR ..."

The problem, is that the last column being update is "ExtraField5".
Bizarrrre.
So, after changing my SelectCommand to be:

"select * from Accounts where AccountID LIKE @AccountID"

I performed the same trace / test. The update statement seems almost
identical (with the addition of a couple columns I wasn't using yet),
however, the trace reveals that the update did in fact behave and work
correctly! All columns were accounted for!

Moral of this story: Don't trust the CommandBuilder and DataAdapter.
They have low IQ's.

Best regards! Thanks for the tips!
-Sean
 
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)
 
I don't know what you're seeing but given the scenario you gave, this is the profiler dump:

exec sp_executesql N'UPDATE [TestUpdate] SET [Col1] = @Col1, [Col2] = @Col2 WHERE (([id] = @Original_id) AND ((@IsNull_Col1 = 1 AND [Col1] IS NULL) OR ([Col1] =
@Original_Col1)) AND ((@IsNull_Col2 = 1 AND [Col2] IS NULL) OR ([Col2] = @Original_Col2)));

SELECT id, Col1, Col2 FROM TestUpdate WHERE (id = @id)',N'@Col1 nchar(5),@Col2 nchar(10),@Original_id int,@IsNull_Col1 int,@Original_Col1 nchar(10),@IsNull_Col2
int,@Original_Col2 nchar(10),@id int',@Col1=N'R1 C1',@Col2=N'Row1 Col2 ',@Original_id=1,@IsNull_Col1=0,@Original_Col1=N'Row1 col1 ',@IsNull_Col2=0,@Original_Col2=N'Row1 Col2
',@id=1
go
exec sp_executesql N'UPDATE [TestUpdate] SET [Col1] = @Col1, [Col2] = @Col2 WHERE (([id] = @Original_id) AND ((@IsNull_Col1 = 1 AND [Col1] IS NULL) OR ([Col1] =
@Original_Col1)) AND ((@IsNull_Col2 = 1 AND [Col2] IS NULL) OR ([Col2] = @Original_Col2)));

SELECT id, Col1, Col2 FROM TestUpdate WHERE (id = @id)',N'@Col1 nchar(10),@Col2 nchar(5),@Original_id int,@IsNull_Col1 int,@Original_Col1 nchar(10),@IsNull_Col2
int,@Original_Col2 nchar(10),@id int',@Col1=N'Row2 c1New',@Col2=N'R2 C2',@Original_id=2,@IsNull_Col1=0,@Original_Col1=N'Row2 c1New',@IsNull_Col2=0,@Original_Col2=N'R2 C2 New
',@id=2
go

Note that both Col1 and Col2 are updated each and every time--whether or not they were changed in the bound UI control. This UPDATE statement is generated by the CommandBuilder (using the DACW).

--
____________________________________
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.
__________________________________
 
And here is the profiler output. Notice that only one column is being
updated in every row, and it alternates:

exec sp_executesql N'UPDATE MyTable SET Column1 = @p1 WHERE ( (MyKeyColumn =
@p2) AND ((@p3 = 1 AND Column1 IS NULL) OR (Column1 = @p4)) AND ((@p5 = 1
AND Column2 IS NULL) OR (Column2 = @p6)) )', N'@p1 varchar(3),@p2
varchar(5),@p3 int,@p4 varchar(2),@p5 int,@p6 varchar(1)', @p1 = ' a', @p2
= 'test1', @p3 = 0, @p4 = ' a', @p5 = 0, @p6 = 'b'

exec sp_executesql N'UPDATE MyTable SET Column2 = @p1 WHERE ( (MyKeyColumn =
@p2) AND ((@p3 = 1 AND Column1 IS NULL) OR (Column1 = @p4)) AND ((@p5 = 1
AND Column2 IS NULL) OR (Column2 = @p6)) )', N'@p1 varchar(3),@p2
varchar(5),@p3 int,@p4 varchar(1),@p5 int,@p6 varchar(2)', @p1 = ' b', @p2
= 'test2', @p3 = 0, @p4 = 'a', @p5 = 0, @p6 = ' b'

exec sp_executesql N'UPDATE MyTable SET Column1 = @p1 WHERE ( (MyKeyColumn =
@p2) AND ((@p3 = 1 AND Column1 IS NULL) OR (Column1 = @p4)) AND ((@p5 = 1
AND Column2 IS NULL) OR (Column2 = @p6)) )', N'@p1 varchar(3),@p2
varchar(5),@p3 int,@p4 varchar(2),@p5 int,@p6 varchar(1)', @p1 = ' a', @p2
= 'test3', @p3 = 0, @p4 = ' a', @p5 = 0, @p6 = 'b'

exec sp_executesql N'UPDATE MyTable SET Column2 = @p1 WHERE ( (MyKeyColumn =
@p2) AND ((@p3 = 1 AND Column1 IS NULL) OR (Column1 = @p4)) AND ((@p5 = 1
AND Column2 IS NULL) OR (Column2 = @p6)) )', N'@p1 varchar(3),@p2
varchar(5),@p3 int,@p4 varchar(1),@p5 int,@p6 varchar(2)', @p1 = ' b', @p2
= 'test4', @p3 = 0, @p4 = 'a', @p5 = 0, @p6 = ' b'

exec sp_executesql N'UPDATE MyTable SET Column1 = @p1 WHERE ( (MyKeyColumn =
@p2) AND ((@p3 = 1 AND Column1 IS NULL) OR (Column1 = @p4)) AND ((@p5 = 1
AND Column2 IS NULL) OR (Column2 = @p6)) )', N'@p1 varchar(3),@p2
varchar(5),@p3 int,@p4 varchar(2),@p5 int,@p6 varchar(1)', @p1 = ' a', @p2
= 'test5', @p3 = 0, @p4 = ' a', @p5 = 0, @p6 = 'b'
 
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
 
Update commands are not changed by data adapter.

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



I don't know what you're seeing but given the scenario you gave, this is the
profiler dump:

exec sp_executesql N'UPDATE [TestUpdate] SET [Col1] = @Col1, [Col2] = @Col2
WHERE (([id] = @Original_id) AND ((@IsNull_Col1 = 1 AND [Col1] IS NULL) OR
([Col1] =
@Original_Col1)) AND ((@IsNull_Col2 = 1 AND [Col2] IS NULL) OR ([Col2] =
@Original_Col2)));

SELECT id, Col1, Col2 FROM TestUpdate WHERE (id = @id)',N'@Col1
nchar(5),@Col2 nchar(10),@Original_id int,@IsNull_Col1 int,@Original_Col1
nchar(10),@IsNull_Col2
int,@Original_Col2 nchar(10),@id int',@Col1=N'R1 C1',@Col2=N'Row1 Col2
',@Original_id=1,@IsNull_Col1=0,@Original_Col1=N'Row1 col1
',@IsNull_Col2=0,@Original_Col2=N'Row1 Col2
',@id=1
go
exec sp_executesql N'UPDATE [TestUpdate] SET [Col1] = @Col1, [Col2] = @Col2
WHERE (([id] = @Original_id) AND ((@IsNull_Col1 = 1 AND [Col1] IS NULL) OR
([Col1] =
@Original_Col1)) AND ((@IsNull_Col2 = 1 AND [Col2] IS NULL) OR ([Col2] =
@Original_Col2)));

SELECT id, Col1, Col2 FROM TestUpdate WHERE (id = @id)',N'@Col1
nchar(10),@Col2 nchar(5),@Original_id int,@IsNull_Col1 int,@Original_Col1
nchar(10),@IsNull_Col2
int,@Original_Col2 nchar(10),@id int',@Col1=N'Row2 c1New',@Col2=N'R2
C2',@Original_id=2,@IsNull_Col1=0,@Original_Col1=N'Row2
c1New',@IsNull_Col2=0,@Original_Col2=N'R2 C2 New
',@id=2
go

Note that both Col1 and Col2 are updated each and every time--whether or not
they were changed in the bound UI control. This UPDATE statement is
generated by the CommandBuilder (using the DACW).

--
____________________________________
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.
__________________________________
 
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.

Sahil Malik said:
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
----------------------------------------------------------------------------
---------------



Marina said:
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)
Click to expand...
Click to expand...
 
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
----------------------------------------------------------------------------
---------------



Marina said:
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.

Sahil Malik said:
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
--------------------------------------------------------------------------
Click to expand...
--
---------------



but
it
Click to expand...
------------------------------------------------------------------------- -
-- it.
See execute
the method,
not apart
the only
the
Click to expand...
Click to expand...
 
Back
Top