[RESOLVED] How to use Nullable types?

[MarMan]

I'm converting an Access VBA app to VB.NET. It has dates defined as variants or objects to handle null values. I thought this would be a great chance to use the Nullable type. But I can't get it to work. Either it is inappropriate for what I am trying to use it for, or I am doing it wrong. Anyone know how to use it? Reader is a SqlDataReader.

mdtDeniedDate = DirectCast(Reader("DeniedDate"), DateTime?)

OR

mdtDeniedDate = CType(Reader("DeniedDate"), DateTime?)

The field returns

{System.DBNull}
System.DBNull: {System.DBNull}

And I just get the error "Specified cast is not valid."

Declaration:

Private mdtDeniedDate As DateTime?

Even the statement

mdtDeniedDate = Reader("DeniedDate")

Returns an error 'System.DBNull' cannot be converted to 'Date'.

I was hoping I didn't have to put a lot of If .. Null statements throughtout the code, but that is my only option unless someone has any other idea.

[stlaural]

Hey MarMan,

DBNull is not exactly the same as Null. Null really means that a reference-type variable has no reference while the DBNull value can also mean that a variable or a table field was never initialized, it represent the abscence of a known value. Its not a big difference but that's why you are getting the error you get. Have a look here for more info.

the simple solution is to use an "if" statement to check for DBNull value.

[Sitten Spynne]

*argh, beaten!*

DBNull isn't the same as Nothing. They express the same concept, but since they aren't the same thing you can't treat them as such. The documentation for DBNull tries to help, but it's still kind of weird:

Do not confuse the notion of nullNothingnullptra null reference (Nothing in Visual Basic) in an object-oriented programming language with a DBNull object. In an object-oriented programming language, nullNothingnullptra null reference (Nothing in Visual Basic) means the absence of a reference to an object. DBNull represents an uninitialized variant or nonexistent database column.

That doesn't mean you can't use nullable types, but it does mean it's not as straightforward. For example, this is what your code should end up looking like:

Dim rawValue As Object = Reader("DeniedDate")
Dim deniedDate As DateTime?

If rawValue Is DBNull.Value Then
    deniedDate = Nothing
Else
    deniedDate = CType(rawValue, DateTime?)
End If

The answer to this StackOverflow question seems to indicate that LINQ to DataSets could work in this scenario. It's not something I've tried extensively but it would definitely put some sugar around it. (There'd still be the if logic behind the scenes but it's hidden.)

[MarMan]

Thanks guys. I didn't want to use Nothiing because then I don't know if deniedDate was never set, or there is no date in the DB. So it sounds like Nullable is useless. If the following code is valid (it is):

Dim dtmDeniedDate1 as DateTime
Dim dtmDeniedDate2 as DateTime?

dtmDeniedDate1 = Nothing
dtmDeniedDate2 = Nothing

And the following is invalid:

Dim dtmDeniedDate1 as DateTime
Dim dtmDeniedDate2 as DateTime?

dtmDeniedDate1 = vbNull
dtmDeniedDate2 = vbNull

What use is Nullable then?

[stlaural]

I haven't used Nullables much, some times on variable and sometime on certain method parameters so I'm not the best person to say how usefull they are. Basically you use a nullable when you need your object to have a value that says "undefined". But as you just discovered they don't solve all your problems if you are using them in a Database oriented application.

[MarMan]

Do you know how to set them to be "undefined" as opposed to "uninitialized" (Nothing)?

[stlaural]

By "undefined" I mean the meaning of the value in a certain context. Let's say you are doing a questionnaire application with true/false questions and you wish to store all answers in a boolean variable. You could use Nullables in order to represent unanswered questions. As I said I didn't use it much yet but this is one example where they can be usefull.

[Sitten Spynne]

Nullables are what the database would likely return had they existed when ADO .NET was designed. They came much later.

I can't really defend or attack DBNull due to my lack of experience with database code, but I imagine the justification is the designers wanted a very clear separation between "this variable references no object" and "the value of this database column is null". Here's some speculation.

It's easy to imagine a code construct like this:

Dim result As Object = Nothing
Try
    ' do some stuff
    result = reader("Name")
Catch ex As Exception
    ' Do something about the error, then continue
    result = Nothing
End Try

If result Is Nothing Then
...

If it were valid for "the database value is null" to return Nothing, we'd be in a pickle here. Is result Nothing because an exception was thrown, or is it Nothing because the internal value is null? To know for sure, we'd have to add some kind of extra flag:

Dim result As Object = Nothing
Dim exceptionWasThrown As Boolean = False
Try
    ...
Catch ...
    exceptionWasThrown = True
    ...
End Try

If exceptionWasThrown Then
    
ElseIf result = Nothing Then
...

It's not that big of a deal until you want to return result from a function call; since you can't return two values, you'd have to rely on a ByRef parameter and those are generally discouraged in API design.

Now let's approach the situation when DBNull is present:

Dim result As Object = Nothing
Try
    ' do some stuff
    result = reader("Name")
Catch ex As Exception
    ' Do something about the error, then continue
    result = Nothing
End Try

If IsDBNull(result) Then
    ...
ElseIf result Is Nothing then
    ...

Now we don't need the extra information to tell us if an exception was thrown or if the column just had no value. DBNull serves us for the "no value" case. A nullable value wouldn't really serve this purpose; you'd need the flag in order to decide whether a value of Nothing meant "no value" or "something got borked".

Nullables are better when you want to express a "value or failure" scenario. In the scenarios above, I've described a three-state "value, no value, or failure" scenario since Nothing has to be treated like a special value. Nullables don't handle the three-state scenario since they can only communicate "value or no value" with no extra information.

Why wasn't ADO .NET updated to use nullables? Beats me. I could speculate that it's because nullables are still kind of a pain to work with, but seeing that LINQ example makes me feel like MS felt the LINQ support for nullables was adequate.

[jmcilhinney]

Nullable value types are just like reference types in that they either contain a value or they don't. There's no way to differentiate between a value not having been assigned and a null value having specifically been assigned with nullable value type any more than there is with reference types. If you're reading data from a database and you want to turn DBNull.Value into nothing then, assuming VB 2008 or later, you can do this:

Dim myDate As Date? = If(myDataReader.IsDBNull(index), DirectCast(Nothing, Date?), myDataReader.GetDateTime(index))

As you can see, the data reader provides a specific method for determining if a field is NULL, as does the DataRow class.

[jmcilhinney]

Note that, if you need to go the other way, you can do something like this:

myDataRow("DateColumn") = If(myDate.HasValue, CObj(myDate.Value), DBNull.Value)

The If operator is much more succinct than an If statement in such situations.

[jmcilhinney]

I've changed the code in post #9 to use the GetDateTime method and therefore remove the need for a cast. Given that each data reader class has methods like GetDateTime, wouldn't it be nice if it had similar methods for nullable types, e.g. GetNullableDateTime? It doesn't, but you can easily create your own extension methods, e.g.

Imports System.Data.Common
Imports System.Runtime.CompilerServices
 
Public Module DataReaderExtensions
 
    <Extension()> _
    Public Function GetNullableDateTime(ByVal sender As DbDataReader, ByVal i As Integer) As DateTime?
        Return If(sender.IsDBNull(i), Nothing, sender.GetDateTime(i))
    End Function
 
End Module

Now you can use that just like the GetDateTime method, e.g.

Dim myDate As Date? = myDataReader.GetNullableDateTime(columnIndex)

You might also create an extension method for all nullable value types:

Public Module NullableExtensions
 
    <Extension()> _
    Public Function GetDataValue(Of T As Structure)(ByVal sender As T?) As Object
        Return If(sender.HasValue, CObj(sender.Value), DBNull.Value)
    End Function
 
End Module

so the code in post #10 would then become:

myDataRow("DateColumn") = myDate.GetDataValue()

[MarMan]

Thanks everyone. I'm a little disappointed with the nullable type. My imagination gave it more strength than it supplies.