DBNull can’t be converted to Null? .NET 2.0 drops the ball on Nullable Types06.27.2007
Nullable Generic Structure
One giant leap for mankind that Microsoft proposed for .NET 2.0 (specifically VB) was the concept of Nullable types. Unfortunately, reportedly due to time constraints, Microsoft was unable to implement full support for them. To understand the concept, you can just google “Nullable Types” or check out the link above. I’ll give you the quick, trimmed-down version.
In .NET, there are two types of variables: value types and reference types. Integers, booleans, dates, and others fall into the value type category, while strings, objects, and others fall into the reference type category. Reference types can be null (or “nothing” in VB), while value types cannot. In other words, if you have an integer, it has to be something, so some people initially set it to a “magic number” like -1 so they know it’s not a value with meaning–it’s more of a default value. This becomes problematic because, when you go to store the values of a business object into a database, you have two options, neither of which are pretty:
- Have a bunch of -1′s sitting in the database, which is ugly and takes up space
- Check every variable before it goes into the database and see if it’s set to your magic number. If it is, you then substitute the -1 or whatever it is with DBNull.
So what do we do? Behold, the power of nullables. Basically, if I declare my variable like so…
Dim foofarginbar As Nullable(Of Integer)
…I can then set my variable to null (nothing) without any problems. That way, when I go to save my business object into the database, I don’t have to worry about checking any “magic number” because, hey, it’s null, and it’ll be null in the database.
Oh but wait! How did Microsoft ever drop the ball on reading values from a database? Check this out. With what I have explained in mind, you would think this would work just fine:
Dim foofarginbar As Nullable(Of Integer) foofarginbar = myTableRow.SomeIntegerColumn
If the value from myTableRow.SomeIntegerColumn is null (in this case it would actually be of type DBNull), you would think .NET would have no problem setting it to a nullable variable. I mean, a nullable variable is getting set to null…not a big deal right? Wrong. .NET 2.0 drops the ball. Instead, we have to still check for a null value and act accordingly:
1 2 3 4 5 6
Dim foofarginbar As Nullable(Of Integer) If IsDBNull(myTableRow("SomeIntegerColumn")) Then foofarginbar = Nothing Else foofarginbar = myTableRow.SomeIntegerColumn End If
I’m amazed this was left out of .NET 2.0 as I think it’s a huge component of nullable types. Without it, some developers still may be hesitant to adopt the whole idea quite yet.
So, what do you think? Please…join the intimate conversation.