DBNull is stupid

January 28, 2011 § 1 Comment

DBNull is stupid. If you’re using it, stop.

Take this basic code:

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim obj1 As Object

        Dim obj2 As Object

 

        obj1 = "a"

        obj2 = "b"

 

        Try

            AssignValue(obj1, obj2)

            MessageBox.Show(obj2.ToString())

        Catch ex As Exception

            MessageBox.Show(ex.ToString())

        End Try

    End Sub

 

    Public Sub AssignValue(ByVal obj1 As Object, ByRef obj2 As Object)

        If (obj1 <> obj2) Then

            obj2 = obj1

        End If

    End Sub

End Class

 

This is a somewhat contrived example, but basically, we want to give obj2 the value that obj1 has. We do a simple check to make sure that they aren’t the same before bothering to do so. This is more important in code where you’re setting a property (rather than just a variable) which may execute some other code, like setting a dirty flag or even writing to persistent storage.

Anyway, the code executes, and obj2 is assigned the value of obj1 (“a”). So what’s the problem? Let’s change the value of obj1 to DBNull.Value.

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim obj1 As Object

        Dim obj2 As Object

 

        obj1 = DBNull.Value

        obj2 = "b"

 

        Try

            AssignValue(obj1, obj2)

            MessageBox.Show(obj2.ToString())

        Catch ex As Exception

            MessageBox.Show(ex.ToString())

        End Try

    End Sub

 

    Public Sub AssignValue(ByVal obj1 As Object, ByRef obj2 As Object)

        If (obj1 <> obj2) Then

            obj2 = obj1

        End If

    End Sub

End Class

 

Should be no problem, right? The AssignValue sub will be called and obj2 will be assigned a value of DBNull.Value. Except that it doesn’t. Actually, what you get is an exception that states:

{“Operator ” is not defined for type ‘DBNull’ and string “b”.”}

Well, crap. Ok, we’ll check for DBNull.Value in our AssignValue sub.

    Public Sub AssignValue(ByVal obj1 As Object, ByRef obj2 As Object)

        If (IsDBNull(obj1) AndAlso Not IsDBNull(obj2)) OrElse

            (Not IsDBNull(obj1) AndAlso IsDBNull(obj2)) OrElse

            obj1 <> obj2 Then

 

            obj2 = obj1

        End If

    End Sub

Run our code again and everything works great. Wait, what if obj1 and obj2 are both set to DBNull.Value?

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim obj1 As Object

        Dim obj2 As Object

 

        obj1 = DBNull.Value

        obj2 = DBNull.Value

 

        Try

            AssignValue(obj1, obj2)

            MessageBox.Show(obj2.ToString())

        Catch ex As Exception

            MessageBox.Show(ex.ToString())

        End Try

    End Sub

Another exception, of course!

{“Operator ” is not defined for type ‘DBNull’ and type ‘DBNull’.”}

Ack! Seriously? Ok, let’s change our AssignValue sub again. We’ll check to see if obj1 is DBNull while obj2 is not, or obj2 is DBNull while obj1 is not, or both obj1 and obj2 are not DBNull and not equal to each other.

    Public Sub AssignValue(ByVal obj1 As Object, ByRef obj2 As Object)

        If (IsDBNull(obj1) AndAlso Not IsDBNull(obj2)) OrElse

            (Not IsDBNull(obj1) AndAlso IsDBNull(obj2)) OrElse

            (Not IsDBNull(obj1) AndAlso Not IsDBNull(obj2) AndAlso obj1 <> obj2) Then

 

            obj2 = obj1

        End If

    End Sub

Wow, we’ve finally managed to implement what should be a simple comparison and we only had to do 5 comparisons to do it. Maybe it’s just me, but this seems excessive for what we’re trying to do. I understand that it’s necessary to represent null database values in our objects, but generics (and therefore Nullables) were introduced in .NET 2.0 – stop using DBNull.

Advertisements

Tagged: ,

§ One Response to DBNull is stupid

  • Jerome says:

    Mike, thanks for your article. It was really helpful. I managed get around the “Operator ” is not defined for type ‘DBNull’ and type ‘DBNull'” error message. Thanks again, keep up the good work!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

What’s this?

You are currently reading DBNull is stupid at Mike Vallotton's Blog.

meta

%d bloggers like this: