Handling empty values with ADO.NET and AddWithValue()

Question

I have a control that, upon postback, saves form results back to the database. It populates the values to be saved by iterating through the querystring. So, for the following SQL statement (vastly simplified for the sake of discussion)...

UPDATE MyTable
SET MyVal1 = @val1,
    MyVal2 = @val2
WHERE @id = @id

...it would cycle through the querystring keys thusly:

For Each Key As String In Request.QueryString.Keys
    Command.Parameters.AddWithValue("@" & Key, Request.QueryString(Key))
Next

HOWEVER, I'm now running into a situation where, under certain circumstances, some of these variables may not be present in the querystring. If I don't pass along val2 in the querystring, I get an error: System.Data.SqlClient.SqlException: Must declare the scalar value "@val2".

Attempts to detect the missing value in the SQL statement...

IF @val2 IS NOT NULL
UPDATE MyTable
SET MyVal1 = @val1,
    MyVal2 = @val2
WHERE @id = @id

... have failed.

What's the best way to attack this? Must I parse the SQL block with RegEx, scanning for variable names not present in the querystring? Or, is there a more elegant way to approach?

UPDATE: Detecting null values in the VB codebehind defeats the purpose of decoupling the code from its context. I'd rather not litter my function with conditions for every conceivable variable that might be passed, or not passed.

Answer 1

First of all, I would suggest against adding all entries on the querystring as parameter names, I'm not sure this is unsafe, but I wouldn't take that chance.

The problem is you're calling

Command.Parameters.AddWithValue("@val2", null)

Instead of this you should be calling:

If MyValue Is Nothing Then
    Command.Parameters.AddWithValue("@val2", DBNull.Value)
Else
    Command.Parameters.AddWithValue("@val2", MyValue)
End If

Answer 2

Update: The solution I gave is based on the assumption that it is a stored proc. Will giving a default value of Null to the SQL Stored proc parameters work?

If it is dynamic sql, always pass the correct number of params, whether it is null or the actual value or specify default values.

Answer 3

I like using the AddWithValue method.

I always specify default SQL parameters for the "optional" parameters. That way, if it is empty, ADO.NET will not include the parameter, and the stored procedure will use it's default value.

I don't have to deal with checking/passing in DBNull.Value that way.

Answer 4

After struggling to find a simpler solution, I gave up and wrote a routine to parse my SQL query for variable names:

Dim FieldRegEx As New Regex("@([A-Z_]+)", RegexOptions.IgnoreCase)
Dim Fields As Match = FieldRegEx.Match(Query)
Dim Processed As New ArrayList

While Fields.Success
    Dim Key As String = Fields.Groups(1).Value
    Dim Val As Object = Request.QueryString(Key)
    If Val = "" Then Val = DBNull.Value
    If Not Processed.Contains(Key) Then
        Command.Parameters.AddWithValue("@" & Key, Val)
        Processed.Add(Key)
    End If
    Fields = Fields.NextMatch()
End While

It's a bit of a hack, but it allows me to keep my code blissfully ignorant of the context of my SQL query.