Scrubbing Null Values from a DataReader

Friday, July 11 2008 - , ,

A buddy of mine and I were tossing around some ideas for scrubbing DBNull values so we could more easily grab values from a DataReader. Our first pass resulted with something like this:

T NullScrubber<T>(object checkValue) 
{
    T outValue;
    if (checkValue == DBNull.Value)
        outValue = default(T);
    else
        outValue = (T)checkValue;
    return outValue;
}

Basically, the NullScrubber method accepts am object value, since that is what the DataReader provides. Then it checks if the value is DBNull.Value. If it is, then ir returns that datatype’s default value. If not, it returns the value that was passed in. Either way, the value passed out is cast to the proper datatype.

You can test this with some code like this, which tests a null and non null value for 3 types:

object o1 = 123;
object o2 = DBNull.Value;
int x1 = NullScrubber<int>(o1);
int x2 = NullScrubber<int>(o2);
 
object o3 = "hello";
object o4 = DBNull.Value;
string x3 = NullScrubber<string>(o3);
string x4 = NullScrubber<string>(o4);
 
object o5 = true;
object o6 = DBNull.Value;
bool x5 = NullScrubber<bool>(o5);
bool x6 = NullScrubber<bool>(o6);
 

When applied to a DataReader usage, the o1 variable, for example, would be replaced with rdr[“UnitsInStock”]  

This works, and its a first pass … but I’d love to hear any thoughts/improvements anyone might have.

For you VB guys/gals, here is the VB version …

Private Function NullScrubber(Of T)(ByVal checkValue As Object) As T
    Dim outValue As T
    If checkValue Is DBNull.Value Then
        outValue = Nothing
    Else
        outValue = CType(checkValue, T)
    End If
    Return outValue
End Function

… and the VB test code …

Dim o1 As Object = 123
Dim o2 As Object = DBNull.Value
Dim x1 As Integer = NullScrubber(Of Integer)(o1)
Dim x2 As Integer = NullScrubber(Of Integer)(o2)
 
Dim o3 As Object = "hello"
Dim o4 As Object = DBNull.Value
Dim x3 As String = NullScrubber(Of String)(o3)
Dim x4 As String = NullScrubber(Of String)(o4)
 
Dim o5 As Object = True
Dim o6 As Object = DBNull.Value
Dim x5 As Boolean = NullScrubber(Of Boolean)(o5)
Dim x6 As Boolean = NullScrubber(Of Boolean)(o6)

4 comment(s)

Dew Drop - July 11, 2008 | Alvin Ashcraft's Morning Dew wrote on Friday, July 11 2008

Pingback from Dew Drop - July 11, 2008 | Alvin Ashcraft's Morning Dew

Speednet wrote on Saturday, July 12 2008

Nice post, a couple of comments:

1. In VB, the easiest way to test for a NULL would be the built-in IsDBNull() function. (In VB, Microsoft normally recommends trying to use a VB library function when it does the same thing as a .NET library function.)

So the third line *COULD* be:

If IsDBNull(checkValue) Then

2. It would be neat to create a new class inherited from the SqlDataReader class, with a new property that overrides NULL values if you wish. (You could also create a new constructor so that existing code could just add a new parameter when creating an instance of the class.)

The thing I like about this is that without this idea you've come up with, you're forced to write If/Thens to accomodate NULL values, which can be a pain. Or else you have to put ISNULL() functions in your query. This idea is easier.

(Does anyone else have a tough time typing comments in these dark gray comment boxes?)

Weekly Link Post 50 « Rhonda Tipton’s WebLog wrote on Sunday, July 13 2008

Pingback from Weekly Link Post 50 « Rhonda Tipton’s WebLog

Trax72 wrote on Monday, February 23 2009

Nice little function, I made a slightly different version where you can pass your own default value to it as the 2nd parameter and another overload that does the same as the your function by calling the modified version with default(T) as the 2nd parameter.