CommandBuilder generates incorrect SQL

[enders]

Hi,

I have the following SQL :
Select AutoID, TS, Object, ActionType, ID, Property, OldValue,
NewValue From Changes.

When I use the commandbuilder (both OLEDB and SQL) I get the following
result back (for delete) :
(SQL)
"DELETE FROM Changes WHERE ( (AutoID = @p1) AND (TS = @p2) AND ((@p3
= 1 AND Object IS NULL) OR (Object = @p4)) AND ((@p5 = 1 AND
ActionType IS NULL) OR (ActionType = @p6)) AND ((@p7 = 1 AND ID IS
NULL) OR (ID = @p8)) AND ((@p9 = 1 AND Property IS NULL) OR (Property
= @p10)) AND ((@p11 = 1 AND OldValue IS NULL) OR (OldValue = @p12))
AND ((@p13 = 1 AND NewValue IS NULL) OR (NewValue = @p14)) )"

(OLEDB)
"DELETE FROM Changes WHERE ( (AutoID = ?) AND (TS = ?) AND ((? = 1
AND Object IS NULL) OR (Object = ?)) AND ((? = 1 AND ActionType IS
NULL) OR (ActionType = ?)) AND ((? = 1 AND ID IS NULL) OR (ID = ?))
AND ((? = 1 AND Property IS NULL) OR (Property = ?)) AND ((? = 1 AND
OldValue IS NULL) OR (OldValue = ?)) AND ((? = 1 AND NewValue IS NULL)
OR (NewValue = ?)) )"

The SQL I was expecting was :
(replace all ? = 1 by ? is NULL)
"DELETE FROM Changes WHERE ( (AutoID = ?) AND (TS = ?) AND ((? is
NULL AND Object IS NULL) OR (Object = ?)) AND ((? is NULL AND
ActionType IS NULL) OR (ActionType = ?)) AND ((? is NULL AND ID IS
NULL) OR (ID = ?)) AND ((? is NULL AND Property IS NULL) OR (Property
= ?)) AND ((? is NULL AND OldValue IS NULL) OR (OldValue = ?)) AND ((?
is NULL AND NewValue IS NULL) OR (NewValue = ?)) )"

Since ? = 1 will always fail during an update (delete or update) I get
an concurrency exception back. I will try to replace th ? = 1 by ? is
NULL and
see if that works. I am open for any other suggestions.

I am using VS 2003.NET with all .NET patches.

Table script

CREATE TABLE [Changes] (
[AutoID] [bigint] IDENTITY (1, 1) NOT NULL ,
[TS] [timestamp] NOT NULL ,
[Object] [nvarchar] (50) NOT NULL ,
[ActionType] [nvarchar] (50) NOT NULL ,
[ID] [nvarchar] (50) NOT NULL ,
[Property] [nvarchar] (50) NULL ,
[OldValue] [nvarchar] (50) NULL ,
[NewValue] [nvarchar] (50) NULL ,
CONSTRAINT [PK_Changes] PRIMARY KEY CLUSTERED
(
[AutoID],
[TS]
) ON [PRIMARY]
) ON [PRIMARY]
GO

 

[Sahil Malik]

Why don't you just go ahead and write your own SQL instead of
CommandBuilder's?

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

 

[enders]

If you have a lot of tables and the tables are still under development,
you want to have dynamically generated queries.

For some reason I got it fixed.

 

[Sahil Malik]

CommandBuilder probably works for the simplest of the simple cases, and even
then it generates ugly queries. Your best bet is to settle the database
design first, and then start from then on.

I understand in certain situations that might not be do-able, but database
upwards is the right design paradigm IMO for this very reason.

Sorry couldn't be of anymore help.

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

 

[David Sceppa]

Enders,

The CommandBuilder is using a little bit of magic for null check
parameters to improve query performance on the back end. Here's a snippet
from a sample CommandBuilder generated UPDATE/DELETE query

((@p9 = 1 AND MyCol IS NULL) OR (MyCol = @p10))

The value in parameter p10 is the original value in the DataRow's
column. The value in parameter p9 is treated slightly differently. If the
value in the DataRow's column is DBNull.Value, the CommandBuilder sets the
parameter value to 1. Otherwise, it's set to 0.

The CommandBuilder should handle the conversion automatically so the
concurrency check passes/fails when it should.

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.

 

[Thomas Weingartner]

We use CommandBuilder in our project with about 180 tables and had no
problem for the last 2 years (except performance).

I tried your table and get the following sql statement with
SqlCommandBuilder:

exec sp_executesql N'DELETE FROM [Changes] WHERE ( ([AutoID] = @p1) AND
([TS] = @p2) AND (([Object] IS NULL AND @p3 IS NULL) OR ([Object] =
@p4)) AND (([ActionType] IS NULL AND @p5 IS NULL) OR ([ActionType] =
@p6)) AND (([ID] IS NULL AND @p7 IS NULL) OR ([ID] = @p8)) AND
(([Property] IS NULL AND @p9 IS NULL) OR ([Property] = @p10)) AND
(([OldValue] IS NULL AND @p11 IS NULL) OR ([OldValue] = @p12)) AND
(([NewValue] IS NULL AND @p13 IS NULL) OR ([NewValue] = @p14)) )', N'@p1
bigint,@p2 timestamp,@p3 nvarchar(4000),@p4 nvarchar(4000),@p5
nvarchar(4000),@p6 nvarchar(4000),@p7 nvarchar(4000),@p8
nvarchar(4000),@p9 nvarchar(4000),@p10 nvarchar(4000),@p11
nvarchar(4000),@p12 nvarchar(4000),@p13 nvarchar(4000),@p14
nvarchar(4000)', @p1 = 1, @p2 = 0x00000000000000B7, @p3 = N'1', @p4 =
N'1', @p5 = N'1', @p6 = N'1', @p7 = N'1', @p8 = N'1', @p9 = N'11', @p10
= N'11', @p11 = N'111', @p12 = N'111', @p13 = N'1111', @p14 = N'1111'


Good Luck
Thomas