Taking a NULL value from the database and assigning to a Date variable

Question

A stored procedure returns data about a user record including a nullable datetime column for their last login date. Which one is the better choice in dealing with the possibility for NULL values when trying to assign a .Net date variable?

    Try
        _LastLogin = CDate(DT.Rows(0)("LastLogin"))
    Catch ex As InvalidCastException
        _LastLogin = Nothing
    End Try

or

    If DT.Rows(0)("LastLogin") Is DBNull.Value Then
        _LastLogin = Nothing
    Else
        _LastLogin = CDate(DT.Rows(0)("LastLogin"))
    End If

Edit: I also forgot about the possibility of using a TryParse

    If Not Date.TryParse(DT.Rows(0)("LastLogin").ToString, _LastLogin) Then
        _LastLogin = Nothing
    End If

Which is the preferred method of handling possible NULL values from the database? Is there a better way than the three listed?

Edit #2: I have noticed that the TryParse method does not play nice when trying to assign to a Nullable type.

Answer 1

The second code snippet is better. Exceptions are for exceptional cases, not cases you expect to happen. Plus, the intent is much better. It's clear that you are expecting DBNull to be a possible value and you want to handle it accordingly.

Also, you may have unintended consequences if the value is not null but not parseable (although this is likely to never happen).

Answer 2

The second method is preferable because of the cost involved in throwing exceptions (especially if you expect the LastLogin field to be NULL on a regular basis).

Here is a good blog post with more details about exceptions (see the part about Exceptions and Performance).

Answer 3

in VB.net, it has to be Nullable(of Date)

here’s one way:

dim theDate as nullable(of Date)

    If theDate.HasValue Then
      'you can proceed
    Else
      'you must assign with DBNull.value
    End If

The database will also likely factor into what you can do. MS Access will squalk about dates no matter what. I eventually got the above to work, but I usually prefer to save dates as strings in Access. It just makes for less hassle down the road.

Answer 4

I would use TryCast, which'll look something like this:

_LastLogin = TryCast(DT.Rows(0)("LastLogin"), Date?)

Answer 5

I use the FixDBNull class:

Public Class FixDBNull(Of ItemType)    
    Public Shared Function Convert(ByVal data As Object) As ItemType
        If data IsNot System.DBNull.Value Then
            Dim obj As ItemType = Nothing

            Try
               obj = CType(data, ItemType)    
            Catch ex As Exception
                'do something with the conversion error
            End Try

            Return obj
        Else
            Return Nothing
        End If

End Function

End Class

then I can call it like this:

_LastLogin = FixDBNull(Of date).Convert( DT.Rows(0)("LastLogin"))

which will return either nothing or a date.