Handling Null Values ​​with ADO.NET and AddWithValue ()

Question

I have a control that, after postback, saves the form results back to the database. It fills in the values ​​that need to be saved, iterating through the query. So, for the following SQL statement (greatly simplified for discussion) ...

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

... it cycles through the request keys:

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

HOWEVER, now I am faced with a situation where, under certain circumstances, some of these variables may not be present in querystring. If I do not pass on val2 in the querystring, I get an error message: System.Data.SqlClient.SqlException: Must declare the scalar value "@val2".

Trying to detect missing value in SQL statement ...

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

... failed.

? SQL RegEx, , ? , ?

: VB . , .

Answer 1

SQL- :

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

, SQL-.

Answer 2

, querystring , , , .

,

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

:

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

Answer 3

. , , , . Null SQL proc?

sql, , null .

Answer 4

AddWithValue.

SQL "" . , , ADO.NET , .

/ DBNull.Value .