Parameterized Query, UPDATE statement, and Nulls

[xlar54]

I am writing a parametrized query, where Im building an UPDATE
statement through concatenation, and then adding the parameters such
as:

UPDATE myTable SET [columna] = @newcolumna, [columnb] = @newcolumnb
WHERE [columna] = @origcolumna
AND [columnb] = @origcolumnb

This works fine, unless one of the original database values are a
NULL. Im creating the parameters as:

SqlParameter sqlOrgParam = new SqlParameter();
sqlOrgParam.SqlDbType = this.GetColumnType(tableName,
rowUpdate.ColumnName); // my own method for type
sqlOrgParam.ParameterName = "@org_" + rowUpdate.ColumnName;
if (rowUpdate.OriginalValue == "") // By now, an empty
string would represent a null
sqlOrgParam.Value = DBNull.Value; //This line executes as it
should
else
sqlOrgParam.Value = rowUpdate.OriginalValue;
sqlCmd.Parameters.Add(sqlOrgParam);

The query is called without failure, its just that it doesn't find the
null in the original record, and as such, doesn't perform any
updates. If I remove the null in the table, everything works fine.
It seems like parameter substitution doesnt like DBNull.Value for some
reason. Any thoughts?

 

[.\\\\axxx]

This is an SQL issue not a .Net issue.

A column with a null value does NOT = a parameter with a null value -
it 'Is Null'.

So - your SQL needs to be something like

WHERE (columna is null and @origValue is null)
OR )columna = @origValue
...

 

[xlar54]

This is an SQL issue not a .Net issue.

A column with a null value does NOT = a parameter with a null value -
it 'Is Null'.

So - your SQL needs to be something like

WHERE (columna is null and @origValue is null)
OR )columna = @origValue
...

Perhaps you are not understanding the issue. I can not say "is null"
in a .NET C# parametrized query.

Thanks

 

[Marc Gravell]

I can not say "is null" in a .NET C# parametrized query.
Why not? The query is just TSQL; you can say whatever you want...
under ANSI SQL nulls are never equal, so you have to do something...

the "([foo] IS NULL AND [bar] IS NULL) OR ([foo] = [bar])" is quite a
common bit of SQL (replace [foo] and [bar]...) - alternatively you can
build the SQL based on the values (via a StringBuilder etc) and append
only the correct construction. Or sometimes you can get creative with
ISNULL/NULLIF/COALESCE - but this isn't always a good idea ;-p

Of course, another option would be to use a timestamp for concurrency
checking, or any of the ORM tools - LINQ-to-SQL will do either
timestamp or column-based concurrency checking.

Marc