VB.NET 2010 and MySql - handling DB NULLs for lazy people

Question

Hi,

I want to initialize a class with data coming from a MySql db. Some fields can be null:

Dim dr As MySqlDataReader = ...
Dim item As New Item(dr.GetInt16(0), dr.GetString(1), dr.GetString(2))

Suppose the last two fields could be NULL In the db, so that calling GetString on that field causes an exception.

I could certainly write code to test for NULLs before I get each field:

dim field1 as String 
if ( dr.IsDbNull(1) )
   field1 = Nothing                  ' or even ""
else
   field1 = dr.GetString(1)

But if you have many fields this is an "ifs" nightmare.

To this purpose I rewrote the IIf VB function to make it more typed, thus to avoid casts:

Namespace Util

Public Shared Function IIf(Of T)(ByVal condition As Boolean, ByVal iftrue As T, ByVal iffalse As T) As T
        If condition Then Return iftrue Else Return iffalse
End Function

So that I could write something like:

Dim item As New Item(
     dr.GetInt16(0), 
     Util.IIf(dr.IsDbNull(1), "", dr.GetString(1), 
     Util.IIf(dr.IsDbNull(2), "", dr.GetString(2))

The typed IIf works well in other cases, but unfortunately it doesn't in this instance, because being it a normal function and not a language keyword, each inpout parameter is evaluated during the call, and when the field is NULL the exception is raised.

Can you think of an elegant if-less solution?

Answer 1

First off, I'd recommend you to use an ORM mapper - there are very few cases nowadays when you have to do manual "mapping".

If this is one of these cases, I'd recommend you to use field names instead of indexes while accessing Data Reader.

And to answer your original question: try extension methods. Sorry for C#, but VB.NET syntax drives me nuts:

public static class DbDataReaderExtensions
{
    public static T GetField<T>(this DbDataReader dbDataReader, string fieldName, 
        T defaultValue)
    {
        if(dbDataReader.IsDBNull(fieldName))
            return defaultValue;
        return (T)dbDataReader[fieldName];
    }
}

Answer 2

Thanks.

I have looked at many ORMs and I don't like them for one reason or another, so I decided to call plain stored procedures to get data. Can you advice something powerful yet simple?

You're right about to use field names, it is safer, even if a little bit slower.

I had just arrived to the same conclusion with the method, but what I still don't like is the type conversion:

Public Shared Function IfNull(Of T)(ByVal dr As MySqlDataReader, ByVal index As Integer, ByVal _default As T) As T
        If dr.IsDBNull(index) Then
            Return _default
        Else
            Return CType(dr.GetValue(index), T)
        End If

End Function

I would like to do something more elegant to get the "real" data type from the reader.