Automatically Checking for DBNull

I was getting a bit tired of checking all nullable database columns in a SqlDataReader for DBNull before attempting to cast it:

    Dim drRecords As SqlDataReader = .ExecuteReader()
    Dim strDescription As String
    While drRecords.Read()
        If drRecords("Description").Equals(DBNull.Value) Then
            strDescription = ""
        Else
            strDescription = CStr(drRecords("Description"))
        End If
    End While 

The obvious way round this would be to use Iif:

strDescription = CStr(IIf(drRecords("Description").Equals(DBNull.Value), "", CStr(drRecords("Description"))))

Unfortunately Iif always evaluates both parts, regardless of the initial result which leaves us with an invalid cast exception again. We could use If instead, which short circuits, but then our code would get a bit messy anyway.

So I thought of utilising generics to create a function which will test for DBNull and cast the value to the correct type if it has a real value. This is what I came up with:

    Public Shared Function CheckDBNull(Of T)(ByVal pReaderVar As Object) As T
            If pReaderVar.Equals(DBNull.Value) Then
                ' Value is null, return default
                Return Nothing
            Else
                ' Cast the value into the correct return type
                Return CType(pReaderVar, T)
            End If
        End Function
    
Which can then be called like so:

strDescription = CheckDBNull(Of String)(drRecords("Description"))
Due to the fact you are always passing in a parameter of type object, T cannot be inferred, and so this method will trigger CA1004, if that's a problem for your organisation.

For me, this still wasn't perfect, as I use nullable types in VB.NET to represent certain nullable T-SQL types, such as Nullable(Of DateTime), and if the database value is null then I want this to be represented as a New Nullable(Of DateTime) with no value. Therefore for each type that I required a value for, I specified this in the method:

    Public Shared Function CheckDBNull(Of T)(ByVal pReaderVar As Object) As T
            If pReaderVar.Equals(DBNull.Value) Then
                ' Value is null, determine the return type for a default
                If GetType(T).Equals(GetType(String)) Then
                    Return CType(CType("", Object), T)
                ElseIf GetType(T).Equals(GetType(Nullable(Of DateTime))) Then
                    Return CType(CType(New Nullable(Of DateTime), Object), T)
                Else
                    ' If it's anything else just return nothing
                    Return Nothing
                End If
            Else
                ' Cast the value into the correct return type
                Return CType(pReaderVar, T)
            End If
        End Function
    

Which works as expected, but note that this only works for VB.NET and the equivalent C# code does not work. This is because the generated MSIL is different, with the C# version boxing and unboxing correctly, leaving null, and the VB version using Microsoft.VisualBasic.CompilerServices.Conversions.ToGenericParameter(Of T), which manages to cast the instantiated object.

Home