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.

dummy · Answer 1 · 2015-02-10T18:34:11.750

5

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.

edited Feb 10 '15 at 18:34

answered Oct 02 '13 at 12:57

dummy

4,256
3
25
36

I liked this idea, but in the function change "Return = checkValue" to "Return checkValue" (minor) – Darkloki Feb 10 '15 at 16:17

score 3 · Answer 2 · edited May 23 '17 at 12:06

3

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.

edited May 23 '17 at 12:06

Community

1
1

answered Oct 02 '13 at 09:23

Steve

213,761
22
232
286

Yeah it's not terribly elegant as you point out, I was hoping there would be something neater. – Robin G Brown Oct 02 '13 at 09:25

score 1 · Answer 3 · answered Aug 22 '14 at 19:06

1

Public Function NotNull(Of T)(ByVal Value As T, ByVal DefaultValue As T) As T
    If Value Is Nothing OrElse IsDBNull(Value) Then
        Return DefaultValue
    Else
        Return Value
    End If
End Function

answered Aug 22 '14 at 19:06

john rains

321
3
8

1

While this code may answer the question, you should also provide *how* the code answers the question, and in this case implementation steps for the OP's question. – Grinn Aug 22 '14 at 19:39

SQL Data Reader - How to handle Null column values elegantly

3 Answers3