2
\$\begingroup\$

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?

\$\endgroup\$
  • 2
    \$\begingroup\$ You should probably use nullable types for the inputs. \$\endgroup\$ – SLaks Nov 9 '11 at 23:04
  • \$\begingroup\$ You might want to check out Nullable Types and ?? Operator. You can significantly decrease the no of lines and improve your code. \$\endgroup\$ – Nilesh Thakkar Jan 2 '12 at 8:24
  • \$\begingroup\$ I definitely agree with using nullable types. Just to clarify that further, the SqlParameter class should have no problem with taking a plain old .NET null / Nothing value and automatically translating that into a database null value. DBNull generally comes into play when you are reading data out of a database, rather than writing to it. \$\endgroup\$ – Dr. Wily's Apprentice Jan 3 '12 at 20:00
  • \$\begingroup\$ I like this answer. Would upvote if it provided just a little more specifics. Additionally, the ?? operator would not be applicable in this specific situation. Finally, ?? is the C# coalesce operator; the VB.NET equivalent would be to call the If function with only 2 arguments. \$\endgroup\$ – Dr. Wily's Apprentice Jan 3 '12 at 20:13
  • \$\begingroup\$ Actually, .NET will not translate null/nothing into a DBNull.Value. A SqlParameter with a null value will not be sent to the database at all. You can easily test this by creating a stored proc (or possibly prepared statement) with a parameter (with no default value) and try to send it in as a .NET null. You should get an exception saying stored procedure expected parameter but it was not passed in. if you have nulls that you want to send in you must explicitly set them to DBNull.Value. \$\endgroup\$ – schlechtums Mar 29 '12 at 12:36
3
\$\begingroup\$

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)
| improve this answer | |
\$\endgroup\$
1
\$\begingroup\$

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()
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ And also add an extension method to add the parameter. This is so repetitive! \$\endgroup\$ – usr Mar 30 '12 at 22:14
0
\$\begingroup\$

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
| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.