Calling stored procedure with a sqlCommand

Question

I have the following code as part of an sqlCommand which is calling a stored procedure:

Dim groupObj

If groupId = 0 Then
    groupObj = DBNull.Value
Else
    groupObj = groupId
End If

Dim siteObj

If siteId = 0 Then
    siteObj = DBNull.Value
Else
    siteObj = siteId
End If

sqlCommand.Parameters.Add(New SqlClient.SqlParameter("GroupID", SqlDbType.Decimal)).Value = groupObj
sqlCommand.Parameters.Add(New SqlClient.SqlParameter("SiteID", SqlDbType.Decimal)).Value = siteObj

I basically want to check for a condition and (if true) send a DBNull instead. This code looks awful however - is there a way to succinct this up?

Answer 1

You can use the ternary operator If():

sqlCommand.Parameters.Add(New SqlClient.SqlParameter("GroupID", SqlDbType.Decimal)).Value = If(groupID = 0, DBNull.Value, groupID)
sqlCommand.Parameters.Add(New SqlClient.SqlParameter("SiteID", SqlDbType.Decimal)).Value = If(siteId = 0, DBNull.Value, siteId)

Be aware that this can cause a compiler error if you have Option Strict turned on. The following is Option Strict compatible...

sqlCommand.Parameters.Add(New SqlClient.SqlParameter("GroupID", SqlDbType.Decimal)).Value = If(groupID = 0, DBNull.Value, CObj(groupID))
sqlCommand.Parameters.Add(New SqlClient.SqlParameter("SiteID", SqlDbType.Decimal)).Value = If(siteId = 0, DBNull.Value, CObj(groupID))

The If ternary operator was added in .Net 3.5/Visual Basic 2008. If you're using and older version you'll need to use the IIF function. The syntax is almost the same, just replace if with iif

sqlCommand.Parameters.Add(New SqlClient.SqlParameter("GroupID", SqlDbType.Decimal)).Value = IIF(groupID = 0, DBNull.Value, groupID)
sqlCommand.Parameters.Add(New SqlClient.SqlParameter("SiteID", SqlDbType.Decimal)).Value = IIF(siteId = 0, DBNull.Value, siteId)

Answer 2

You could define an extension method like this (I'm not a VB programmer so please excuse the syntax errors):

<Extension()> 
Public Sub OrDBNull(ByVal val As Decimal)
    If val = 0
        return DBNull.Value
    End If

    return val ;
End Sub

So your command parameters would become:

sqlCommand.Parameters.Add(New SqlParameter("GroupID", SqlDbType.Decimal)).Value = groupObj.OrDBBull()
sqlCommand.Parameters.Add(New SqlParameter("SiteID", SqlDbType.Decimal)).Value = siteObj.OrDBNull()

Answer 3

You should write an extension method for SqlCommand class

<Extension()>
Public Sub AddParamWhereZeroIsNull(command As SqlCommand, name As String, value As Decimal?)
    command.Parameters.Add(New SqlParameter(name, SqlDbType.Decimal) With {.Value = If(value.GetValueOrDefault() = 0, DbNull.Value, value)})
End Sub