Get an int? from db using DbDataReader

Question

Is there any smart way to abbreviate this obscenity? It works, but it's unreadable...

int? myVal = r.IsDBNull(r.GetOrdinal("colName")) ? (int?)null : r.GetInt32(r.GetOrdinal("colName"));

Thanks :)

Answer 1

Not really. I often put this code in a method and call it. That method could end up in a common "helper" class or I suppose even an extension method (though I'm not personally a fan of those because they're hard for other people to discover).

Answer 2

You could put that code into an extension method on the DbDataReader (or even better on the IDataReader interface that it implements). That way, the horrible-looking code is hidden away behind a nicer looking GetNullableInt32() call:

public static class IDataReaderExtensions
{
    public static int? GetNullableInt32(this IDataReader r, string columnName)
    {
        int? myVal = r.IsDBNull(r.GetOrdinal(columnName)) 
                        ? (int?) null : 
                        r.GetInt32(r.GetOrdinal(columnName));
        return myVal;
    }
}

You can then call it using:

IDataReader reader = ...;
int? value = reader.GetNullableInt32("colName");

As Ian points out, it would be better to call r.GetOrdinal() just the once:

public static class IDataReaderExtensions
{
    public static int? GetNullableInt32(this IDataReader r, string columnName)
    {
        int ordinal = r.GetOrdinal(columnName);
        int? myVal = r.IsDBNull(ordinal) 
                        ? (int?) null : 
                        r.GetInt32(ordinal);
        return myVal;
    }
}

Answer 3

Further to Adrian's answer, you could make a generic extension method to cater for all types:

int? myVal = r.GetValue<int?>("colName");

// ...

public static class DataReaderExtensions
{
    public static T GetValue<T>(this IDataReader dr, string columnName)
    {
        return dr.GetValue<T>(dr.GetOrdinal(columnName));
    }

    public static T GetValue<T>(this IDataReader dr, int columnOrdinal)
    {
        // does the column contain null?
        if (dr.IsDBNull(columnOrdinal))
        {
            // is T either a ref type or a nullable value type?
            Type t = typeof(T);
            if (!t.IsValueType || (Nullable.GetUnderlyingType(t) != null))
            {
                // return null
                return default(T);
            }
        }

        // get the column value, cast it as T and return it
        // this will throw if the value isn't convertible/unboxable to T
        return (T)dr.GetValue(columnOrdinal);
   }
}