C# DBNull and nullable Types - cleanest form of conversion

Answer 1

int? a = (int?)dr["A"]

Answer 2

Why not use LINQ? It does the conversion for you.

Answer 3

Extension methods!

Something like the following:

public static class DataRowExtensions
{
    public static Nullable<T> GetNullableValue<T>(this DataRow row, string columnName)
        where T : struct
    {
        object value = row[columnName];
        if (Convert.IsDBNull(value))
            return null;

        return (Nullable<T>)value;
    }

    public static T GetValue<T>(this DataRow row, string columnName)
        where T : class
    {
        object value = row[columnName];
        if (Convert.IsDBNull(value))
            return null;

        return (T)value;
    }
}

Use it like so:

int? a = dr.GetNullableValue<int>("A");

or

string b = dr.GetValue<string>("B");

Answer 4

The LINQ to DataSets chapter of LINQ in Action is a good read.

One thing you'll see is the Field<T> extension method, which is used as follows:-

int? x = dt.Field<int?>( "Field" );

Or

int y = dt.Field<int?>( "Field" ) ?? 0;

Or

var z = dt.Field<int?>( "Field" );

Answer 5

int? a = string.IsNullOrEmpty(dr["A"].ToString()) ? 0 : Convert.ToInt32(dr["A"]);

Answer 6

Following would work, safely:

Snip:

public static class SqlDataReaderEx
{
    public static int TryParse(SqlDataReader drReader, string strColumn, int nDefault)
    {
        int nOrdinal = drReader.GetOrdinal(strColumn);
        if (!drReader.IsDbNull(nOrdinal))
            return drReader.GetInt32(nOrdinal);
        else
            return nDefault;
    }
}

Usage:

SqlDataReaderEx.TryParse(drReader, "MyColumnName", -1);

Answer 7

This is the purpose of the DataRowExtensions class in .NET 3.5, which provides static Field<T> and SetField<T> methods for round-tripping nullable (and non-nullable) data between the DataRow and .NET types.

int? fld = row.Field<int?>("ColumnA")

will set fld to null if row["ColumnA"] contains DBNull.Value, to its value if it contains an integer, and throw an exception if it contains anything else. And on the way back,

row.SetField("ColumnA", fld);

does the same thing in reverse: if fld contains null, it sets row["ColumnA"] to DBNull.Value, and otherwise sets it to the value of fld.

There are overloads of Field and SetField for all of the value types that DataRow supports (including non-nullable types), so you can use the same mechanism for getting and setting fields irrespective their data type.