Checking for DBNull with generics

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Leon Mayne - 31 Mar 2008 09:52 GMT

We currently have lots of checks in our businesslayer object's Load()
functions that look like:

If drCourse("txtTTMCnotes").Equals(DBNull.Value) Then
Me._txtTTMCnotes = ""
Else
Me._txtTTMCnotes = CStr(drCourse("txtTTMCnotes"))
End If

and

If drCourse("datTTMCarchived").Equals(DBNull.Value) Then
Me._datTTMCarchived = New Nullable(Of DateTime)
Else
Me._datTTMCarchived = CDate(drCourse("datTTMCarchived"))
End If

I'd like to tidy these up with a a static generic function that will check
for null, and then return either the value or a default value (e.g. int=0,
string="", nullables=new nullable) but am having problems with the return
type. I started with this:

Public Shared Function CheckDbNull(Of T)(ByVal pReaderVar As Object) As T
If pReaderVar.Equals(DBNull.Value) Then
Return Nothing
Else
Return CType(pReaderVar, T)
End If
End Function

Which works, but returns nothing, which is causing null reference
exceptions. I tried writing it like:

If pReaderVar.Equals(DBNull.Value) Then
Select Case GetType(T).FullName
Case "System.String"
Return ""
Case "System.Int32"
Return 0
' etc
End Select

But this doesn't compile, as the return type is not T. Also trying to use
e.g. Return CType("", T) doesn't work.

Does anyone know how to return a specific type based on the return type, or
do I have to create a separate function for each type I'm checking for?

Reply to this Message

Marc Gravell - 31 Mar 2008 10:03 GMT

Why not obtain the ordinal of the column (GetOrdinal), then use
IsDBNull to check for null on the column, then finally default(T)* to
use the default value for that type. The only exception you might want
here is string (since it looks like you want "") - although personally
I'd find the null more correct, since you can differentiate between
the null string and the empty string.

(*=this is the C# syntax; I'm sure there is a VB equivalent, but I
don't know what it is ;-p)

Re casting the string (as an exception) - I don't know enough about
VB, but in C# you can trick it by doing something like:
return (T)(object)"";

Marc

Reply to this Message

Leon Mayne - 31 Mar 2008 11:05 GMT

> Why not obtain the ordinal of the column (GetOrdinal), then use
> IsDBNull to check for null on the column, then finally default(T)* to
[quoted text clipped - 9 lines]
> VB, but in C# you can trick it by doing something like:
> return (T)(object)"";

Using a combination of your ideas I managed to come up with:

Public Shared Function CheckDbNull(Of T)(ByVal pReaderVar As Object)
As T
If pReaderVar.Equals(DBNull.Value) Then
Select Case GetType(T).ToString()
Case GetType(String).ToString()
Return CType(CType("", Object), T)
Case GetType(Nullable(Of DateTime)).ToString()
Return CType(CType(New Nullable(Of DateTime),
Object), T)
Case GetType(Nullable(Of Boolean)).ToString()
Return CType(CType(New Nullable(Of Boolean),
Object), T)
Case Else
Return Nothing
End Select
Else
Return CType(pReaderVar, T)
End If
End Function

Which seems to work great. Basically, I specify which types I want to add
explicit defaults for in the select case statement, and the case else
assigns nothing for the remaining types (which is the equivalent of
default() in C#), so for value types the default will be used (e.g.
integer=0).

Thanks for your help.

Reply to this Message

Marc Gravell - 31 Mar 2008 11:21 GMT

Just for the record, string-compare on GetType is a nasty way to do things -
surely comparing just the types [i.e. GetType(T) vs GetType(String) etc]
would be cleaner? Granted: you can't do this in a switch... but If, ElseIf,
etc should do.

I'm also a little confused as to what the Nullable<bool> etc is doing; it
*looks* like it is converting an empty Nullable<bool> to object (which
should yield null due to the boxing rules), then converting that null to a
T, which we already know if Nullable<bool> (hence becoming another empty
Nullable<bool>)- so we've got a complicated way of saying "null". In C#,
default(T) here would the same thing... I don't know what VB does, though.

Marc

Reply to this Message

Leon Mayne - 31 Mar 2008 13:44 GMT

> Just for the record, string-compare on GetType is a nasty way to do
> things - surely comparing just the types [i.e. GetType(T) vs
> GetType(String) etc] would be cleaner? Granted: you can't do this in a
> switch... but If, ElseIf, etc should do.

Yes, that's why I was doing a string compare. Perhaps I should rewrite it as
you suggest for performance.

> I'm also a little confused as to what the Nullable<bool> etc is doing; it
> *looks* like it is converting an empty Nullable<bool> to object (which
> should yield null due to the boxing rules), then converting that null to a
> T, which we already know if Nullable<bool> (hence becoming another empty
> Nullable<bool>)- so we've got a complicated way of saying "null". In C#,
> default(T) here would the same thing... I don't know what VB does, though.

I just checked this and it seems to work fine. It creates a new nullable(of
boolean) with no value, and HasValue = false. Is this different in C#?
*Leon checks
OK, I just tried:

public static T CheckDBNull<T>(object pReaderVar)
{
if (pReaderVar.Equals(DBNull.Value))
{
if (typeof(T) == typeof(string))
{
return (T)(object)"";
}
else if (typeof(T) == typeof(Nullable<bool>))
{
return (T)(object)new Nullable<bool>();
}
else
{
return default(T);
}
}
else
{
return (T)pReaderVar;
}
}

and yes, it returns null for a Nullable<bool>. I wonder why it's different
for VB.NET? Would be interesting to look at the MSIL.

Reply to this Message

Leon Mayne - 31 Mar 2008 14:01 GMT

> Would be interesting to look at the MSIL.

I just did this, and it looks like the VB version is using a different call
to unbox the object:

VB:
IL_00b9: box valuetype [mscorlib]System.Nullable`1<bool>
IL_00be: call !!0
[Microsoft.VisualBasic]Microsoft.VisualBasic.CompilerServices.Conversions::ToGenericParameter<!!0>(object)

C#:
IL_0066: box valuetype [mscorlib]System.Nullable`1<bool>
IL_006b: unbox.any !!T

So the Conversions::ToGenericParameter works but the direct unboxing
doesn't.

Reply to this Message

Leon Mayne - 31 Mar 2008 15:55 GMT

>> Would be interesting to look at the MSIL.
>
[quoted text clipped - 12 lines]
> So the Conversions::ToGenericParameter works but the direct unboxing
> doesn't.

Just tried it myself:

Return
Microsoft.VisualBasic.CompilerServices.Conversions.ToGenericParameter(Of
T)(New Nullable(Of Boolean))

Works fine in VB.NET, but not in C#:

return
Microsoft.VisualBasic.CompilerServices.Conversions.ToGenericParameter<T>(new
Nullable<Boolean>());

Something odd going on here!

Reply to this Message

Checking for DBNull with generics

Free Magazines