Nullable Data Extension Methods

[jmcilhinney]

Inspired by a recent thread regarding nullable value types and data readers, I decided to create some extension methods that would make working with nullable data easier. There are two types of methods in the attached file:

1. It has always annoyed me that the Item property of a data reader allows you to specify the column by ordinal or name, while the type-specific methods for retrieving data only support ordinals. For instance, you can do this:

Dim b1 As Boolean = myDataReader(0)
Dim b2 As Boolean = myDataReader("Flag")

where the equivalent type-specific method doesn't work the same:

Dim b1 As Boolean = myDataReader.GetBoolean(0) 'This works
Dim b2 As Boolean = myDataReader.GetBoolean("Flag") 'This doesn't compile

To remedy this, for each type-specific method of a data reader that accepts an ordinal, I've created a corresponding method that accepts a name, e.g.

<Extension()>
Public Function GetBoolean(ByVal source As DbDataReader, ByVal name As String) As Boolean
    Return source.GetBoolean(source.GetOrdinal(name))
End Function

Using this method, this code now compiles and runs as expected:

Dim b2 As Boolean = myDataReader.GetBoolean("Flag")

Note that these methods have nothing to do with nullable data. They just make working with data readers a little easier as I think that it's more natural to use column names than ordinals.

2. The next type of method does deal with nullable data; specifically, reading nullable data from a data reader. Using GetBoolean as an example again, you can do this:

Dim b1 As Boolean = myDataReader.GetBoolean(0)

and it will work fine, as long as column 0 contains a value. If it doesn't than that code will throw and exception. Previously, if you wanted to get a nullable Boolean from a data reader it would look like this:

Dim b1 As Boolean?
 
If myDataReader.IsDBNull(0) Then
    b1 = Nothing
Else
    b1 = myDataReader.GetBoolean(0)
End If

or, more succinctly:

Dim b1 As Boolean? = If(myDataReader.IsDBNull(0), DirectCast(Nothing, Boolean?), myDataReader.GetBoolean(0))

Note that the cast is required in that last code snippet because the If operator requires that both possible results are, or can be converted to, the same type. I have encapsulated that last code snippet into my extension methods:

<Extension()>
Public Function GetNullableBoolean(ByVal source As DbDataReader, ByVal ordinal As Integer) As Boolean?
    Return If(source.IsDBNull(ordinal), DirectCast(Nothing, Boolean?), source.GetBoolean(ordinal))
End Function

so now you can just do this:

Dim b1 As Boolean? = myDataReader.GetNullableBoolean(0)

I've also created a corresponding overload that takes a name rather than an ordinal:

<Extension()>
Public Function GetNullableBoolean(ByVal source As DbDataReader, ByVal name As String) As Boolean?
    Dim ordinal As Integer = source.GetOrdinal(name)
 
    Return If(source.IsDBNull(ordinal), DirectCast(Nothing, Boolean?), source.GetBoolean(ordinal))
End Function

so you can also do this:

Dim b1 As Boolean? = myDataReader.GetNullableBoolean("Flag")

[jmcilhinney]

Note that this code was written in VB 2010. To use it in older versions:

VB 2008: Add a line continuation character after each Extension attribute.

.NET 3.0 or earlier: Remove the Extension attributes and pass the data reader as an argument when calling.

VB 2005: Use conventional If statements instead of the If operator.

[ForumAccount]

John, was there a reason that you didn't use a generic method? Such as:

Public Function GetNullable(Of T As Structure)(ByVal source As Common.DbDataReader, _
                                               ByVal name As String) As T?
    Dim ordinal As Integer = source.GetOrdinal(name)
 
    Return If(source.IsDBNull(ordinal), _
              New T?(), _
              DirectCast(source.GetValue(ordinal), T?))
 
End Function

[jmcilhinney]

John, was there a reason that you didn't use a generic method?

Yes. Data readers already have type-specific methods for dedicated types and I wanted to match them. I was considering adding a generic Field method, a la LINQ to DataSet, and also a NullableField method for both the DataRow and DbDataReader classes, which would do as you suggest.