1 Attachment(s)
Nullable Data Extension Methods
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:
vb.net Code:
Dim b1 As Boolean = myDataReader(0)
Dim b2 As Boolean = myDataReader("Flag")
where the equivalent type-specific method doesn't work the same:
vb.net Code:
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.
vb.net Code:
<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:
vb.net Code:
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:
vb.net Code:
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:
vb.net Code:
Dim b1 As Boolean?
If myDataReader.IsDBNull(0) Then
b1 = Nothing
Else
b1 = myDataReader.GetBoolean(0)
End If
or, more succinctly:
vb.net Code:
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:
vb.net Code:
<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:
vb.net Code:
Dim b1 As Boolean? = myDataReader.GetNullableBoolean(0)
I've also created a corresponding overload that takes a name rather than an ordinal:
vb.net Code:
<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:
vb.net Code:
Dim b1 As Boolean? = myDataReader.GetNullableBoolean("Flag")
Re: Nullable Data Extension Methods
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.
Re: Nullable Data Extension Methods
John, was there a reason that you didn't use a generic method? Such as:
vb.net Code:
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
Re: Nullable Data Extension Methods
Quote:
Originally Posted by
ForumAccount
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.