SQL Data Reader - How to handle Null column values elegantly

Question

I'm using an SQLDataReader to retrieve values from a database which may be null. I've worked out how to handle Null string values but can't get the same trick to work with integers or booleans:

Using cmd As DbCommand = store.GetStoredProcCommand("RetrievePOCO")
    store.AddInParameter(cmd, "ID", DbType.Int32, ID)
    Using reader As IDataReader = store.ExecuteReader(cmd)
        If reader.Read() = True Then
            Dim newPOCO As New POCO()
            With newPOCO
                'If the source column is null TryCast will return nothing without throwing an error
                .StatusXML = TryCast(reader.GetString(reader.GetOrdinal("StatusXML")), String)
                'How can a null integer or boolean be set elegantly?
                .AppType = TryCast(reader.GetInt32(reader.GetOrdinal("AppType")), System.Nullable(Of Integer))
                .Archived = TryCast(reader.GetBoolean(reader.GetOrdinal("Archived")), Boolean)

So how can a null integer or boolean be set elegantly? I've seen suggestions in C# but they don't translate correctly to VB giving a 'TryCast operand must be reference type, but integer? is a value type' compiler errors.

Answer 1

I use the following function in this scenario:

Public Shared Function NoNull(ByVal checkValue As Object, ByVal returnIfNull As Object) As Object
    If checkValue Is DBNull.Value Then
        Return returnIfNull
    Else
        Return checkValue
    End If
End Function

Your code would look something like this:

With newPOCO
    .StatusXML = NoNull(reader("StatusXML"), "")
    .AppType = NoNull(reader("AppType"), -1)
    .Archived = NoNull(reader("Archived"), False)
End With

Note that this function requires you to pass the value which should be used if the value is DbNUll as the second Parameter.

Answer 2

You can take advantage of the IsDBNull method of the SqlDataReader and use the VB.NET ternary operator to assign a default value to your poco object

.StatusXML = If(reader.IsDBNull(reader.GetOrdinal("StatusXML")), _
             "",reader.GetString(reader.GetOrdinal("StatusXML")))

It is just one line, not very elegant because you need to call two times the GetOrdinal method.