Passing Null Values via a SQLCommand.Parameter

[Thomas Ficker]

I use stored procedures in most of my programs and in many instances one or
more of the parameters being passed can be null. I am looking for the most
efficent way to test for a null value then pass it to the parameter object.
Here is an example of what I do now, paying special attention to the
Parameter functions:

Sub AddRecord (byval FirstName as string, byval LastName as string)
Dim cnn as new SQLClient.SQLConnection(strcnn)
Dim cmd as new SQLClient.SQLCommand

cnn.open : cmd.Connection = cnn : cmd.CommandType =
adStoredProcedure
cmd.CommandText = "spAddRecord"

If Trim(FirstName) <> "" Then
cmd.Parameters.Add("@FirstName",sqldbtype.varchar).Value =
FirstName
Else
cmd.Parameters.Add("@FirstName",sqldbtype.varchar).Value =
System.DBNull.Value
End If

If Trim(LastName) <> "" Then
cmd.Parameters.Add("@LastName",sqldbtype.varchar).Value =
LastName
Else
cmd.Parameters.Add("@LastName",sqldbtype.varchar).Value =
System.DBNull.Value
End IF

cmd.ExecuteNonQuery
cnn.close : cnn = Nothing : cmd=Nothing

End Sub


My questions are:
1. How can I test for Null values in other data type.
2. Is there a better way to do this?

I haven't found any real soild documentation on this on the web or in my
VB.Net and VS.Net books.
Thanks,

Tom

 

[Teemu Keiski]

Well,

as a tip you could try creating a global helper function that would deal
with the proposed value that is it accepted "as is" or should it return the
DbNull.Value that indicates the proposed value is "empty" or null
(respective to it's datatype of course). That way you wouldn't at least need
to repeat the same logic over and over again for every parameter.

The function could take the proposed value as parameter and if you want the
function to be very flexible, overload it for different datatypes.

Bur for string types it could be:

Public Shared Function ConvertHelper(ByVal param As String) As Object
If param.Trim.Length = 0 Then Return DBNull.Value
Return param
End Function

(DbNull.Value requires Object type to be return type, this is no problem
with reference types like String, but with value types like Integer it
causes boxing to happen)

Then you could use it like:

**
cmd.Parameters.Add("@Firstname",sqldbtype.varchar).Value =
ConvertHelper(FirstName)
**
and same way for the Lastname. As a result it removes the need for checking
of string value for every parameter and you can do the same thing with one
line of code.

The checking for other types can follow similar logic but there's the
distinction when you have value types and reference types in question. For
example Integer is value type and if it has not been set, it is 0. So the
problem can be to detect that was the value actually assinged or not i.e was
0 intentional or not, but I think that isn't great problem if 0 can clearly
indicate NULL in the database (set using DBNull.Value) in any case.

--
Teemu Keiski
MCP, Designer/Developer
Mansoft tietotekniikka Oy
http://www.mansoft.fi

AspInsiders Member, www.aspinsiders.com
ASP.NET Forums Moderator, www.asp.net
AspAlliance Columnist, www.aspalliance.com