Wednesday, March 21, 2007

Handling DBNull in .NET

The following is an email I sent to my development department. I found the basis for this code somewhere else on the interweb, but I can't find the link. I do not claim this as my own invention, especially since the functionality is built into .NET.
If you're like me, you've come across the issue of being unable to cast System.DBNull to any datatype. To get around this, I would check for DBNull, then conditionally do the cast:
' assuming "row" is  a datarow with data from the SQL Server
Dim office_code As Integer
If Not row("office_code") Is System.DBNull.Value Then
office_code = CInt(row("office_code"))
End If
This gets quite verbose, especially if you have many columns. Instead, using the "Of" keyword, we can create a generic-typed function that works just like TryCast works but works for reference and value types and handles DBNull:
Private Function SafelyConvertFromDBNull(Of thisType)(ByVal item As Object) As thisType
If item Is System.DBNull.Value Then
item = Nothing
End If
Return CType(item, thisType)
End Function
Which is called like so:
Dim office_code As Integer = SafelyConvertFromDBNull(Of  Integer)(row("office_code"))
Dim partner_name As String = SafelyConvertFromDBNull(Of String)(row("partner_name"))
Dim start_date As Date = SafelyConvertFromDBNull(Of Date)(row("start_date"))
Dim monthly_revenue As Decimal = SafelyConvertFromDBNull(Of Decimal)(row("monthly_revenue"))
This function saved me many lines of code, so I thought I'd share it with you all.

1 comment:

Unknown said...

Thanks.

C#

public static T myTryCast(object item)
{
if (Object.ReferenceEquals(item, System.DBNull.Value))
{
item = default(T);
}

return (T)item;
}

Rick Boardman
http://www.rickboardman.com

All rights reserved. Take that!