How can I guard against "Object cannot be cast from DBNull to other types"?

Question

I've got this code, where ISTM I'm defensively coding against assigning nulls:

foreach (DataRow priceAndUsageVarianceRow in _dtUsage.Rows)
{
    //var pauv = new PriceAndUsageVariance
    //{
    //    Description = priceAndUsageVarianceRow["Description"].ToString(),
    //    Week1Usage = Convert.ToDouble(priceAndUsageVarianceRow["Week1Usage"]),
    //    Week2Usage = Convert.ToDouble(priceAndUsageVarianceRow["Week2Usage"]),
    //    UsageVariance = Convert.ToDouble(priceAndUsageVarianceRow["UsageVariance"]),
    //    Week1Price = Convert.ToDecimal(priceAndUsageVarianceRow["Week1Price"]),
    //    Week2Price = Convert.ToDecimal(priceAndUsageVarianceRow["Week2Price"]),
    //    PriceVariance = Convert.ToDecimal(priceAndUsageVarianceRow["PriceVariance"]),
    //    PriceVariancePercentage = Convert.ToDouble(priceAndUsageVarianceRow["PriceVariancePercentage"])
    //};
    // Got exception with the code above; trying to prevent it with this:
    var pauv = new PriceAndUsageVariance();
    pauv.Description = String.Empty;
    pauv.Week1Usage = 0.0;
    pauv.Week2Usage = 0.0;
    pauv.UsageVariance = 0.0;
    pauv.Week1Price = 0.00M;
    pauv.Week2Price = 0.00M;
    pauv.PriceVariance = 0.00M;
    pauv.PriceVariancePercentage = 0.0;
    if (null != priceAndUsageVarianceRow["Description"])
    {
        pauv.Description = priceAndUsageVarianceRow["Description"].ToString();
    }
    if (null != priceAndUsageVarianceRow["Week1Usage"])
    {
        pauv.Week1Usage = Convert.ToDouble(priceAndUsageVarianceRow["Week1Usage"]);
    }
    if (null != priceAndUsageVarianceRow["Week2Usage"])
    {
        pauv.Week2Usage = Convert.ToDouble(priceAndUsageVarianceRow["Week2Usage"]);
    }
    . . .

...yet I get, "Object cannot be cast from DBNull to other types" on a particular iteration on the last attempted assignment (to pauv.Week2Usage).

That field does appear in the class:

public class PriceAndUsageVariance
{
    public String Description { get; set; }
    public Double Week1Usage { get; set; }
    public Double Week2Usage { get; set; }
    public Double UsageVariance { get; set; }
    public Decimal Week1Price { get; set; }
    public Decimal Week2Price { get; set; }
    public Decimal PriceVariance { get; set; }
    public Double PriceVariancePercentage { get; set; }
}

...and the code runs fine for most of the records.

What would cause this exception, and how can I guard against it?

Jens Meinecke · Accepted Answer · 2016-01-20T00:49:17.180

The cause is that Data base NULL values are returned not as CLR null but as an object of type DBNull so you really want to test against that. The safe thing, depending on circumstances would be to test against both null and whether the object is of type DBNull

Write yourself a little helper Function:

public static class DbNullExt
{
    public static bool IsNullData(this object obj)
    {
        return obj == null || obj is DBNull;
    }
}

and modify your code thus:

if (!IsNullData(priceAndUsageVarianceRow["Description"]))
{
    pauv.Description = priceAndUsageVarianceRow["Description"].ToString();
}
if (!IsNullData(priceAndUsageVarianceRow["Week1Usage"]))
{
    pauv.Week1Usage = Convert.ToDouble(priceAndUsageVarianceRow["Week1Usage"]);
}
if (!IsNullData(priceAndUsageVarianceRow["Week2Usage"]))
{
    pauv.Week2Usage = Convert.ToDouble(priceAndUsageVarianceRow["Week2Usage"]);
}

As you need to do this sort of testing all the time you might want to borrow my DataRowEx class that simplfies things further:

public static class DataRowEx
{
    public static string String(this DataRow row, string columnName)
    {
        object obj = row[columnName];

        if (obj is DBNull)
            return null;

        return obj.ToString();
    }

    public static Int32 Int32(this DataRow row, string columnName)
    {
        object obj = row[columnName];

        if (obj is DBNull)
            return 0;

        return (Int32)obj;
    }

    public static Decimal Decimal(this DataRow row, string columnName)
    {
        object obj = row[columnName];

        if (obj is DBNull)
            return 0;

        return Convert.ToDecimal(obj);
    }

    public static Double Double(this DataRow row, string columnName)
    {
        object obj = row[columnName];

        if (obj is DBNull)
            return 0;

        return Convert.ToDouble(obj);
    }

    public static Single Single(this DataRow row, string columnName)
    {
        object obj = row[columnName];

        if (obj is DBNull)
            return 0;

        return Convert.ToSingle(obj);
    }

    public static bool Bool(this DataRow row, string columnName)
    {
        object obj = row[columnName];

        if (obj is DBNull)
            return false;

        if (obj is int)
            return (int) obj != 0;

        return (bool)obj;
    }

    public static DateTime DateTime(this DataRow row, string columnName)
    {
        object obj = row[columnName];

        if (obj is DBNull)
            return System.DateTime.MinValue;

        return (DateTime)obj;
    }

    public static object ToType(this DataRow row, Type targetType, string columnName)
    {
        if (targetType == typeof(Int32))
            return row.Int32(columnName);

        if (targetType == typeof(bool))
            return row.Bool(columnName);

        if (targetType == typeof(DateTime))
            return row.DateTime(columnName);

        if (targetType == typeof (Decimal))
            return row.Decimal(columnName);

        if (targetType == typeof(Single))
            return row.Double(columnName);

        if (targetType == typeof(Double))
            return row.Double(columnName);

        if (targetType == typeof(string))
            return row.String(columnName);

        return row.String(columnName);
    }

    public static string String(this DataRowView row, string columnName)
    {
        object obj = row[columnName];

        if (obj is DBNull)
            return null;

        return obj.ToString();
    }

    public static Int32 Int32(this DataRowView row, string columnName)
    {
        object obj = row[columnName];

        if (obj is DBNull)
            return 0;

        return (Int32)obj;
    }

    public static Decimal Decimal(this DataRowView row, string columnName)
    {
        object obj = row[columnName];

        if (obj is DBNull)
            return 0;

        return (Decimal)obj;
    }

    public static Double Double(this DataRowView row, string columnName)
    {
        object obj = row[columnName];

        if (obj is DBNull)
            return 0;

        return (Double)obj;
    }

    public static Single Single(this DataRowView row, string columnName)
    {
        object obj = row[columnName];

        if (obj is DBNull)
            return 0;

        return (Single)obj;
    }

    public static bool Bool(this DataRowView row, string columnName)
    {
        object obj = row[columnName];

        if (obj is DBNull)
            return false;

        return (bool)obj;
    }

    public static DateTime DateTime(this DataRowView row, string columnName)
    {
        object obj = row[columnName];

        if (obj is DBNull)
            return System.DateTime.MinValue;

        return (DateTime)obj;
    }

    public static object ToType(this DataRowView row, Type targetType, string columnName)
    {
        if (targetType == typeof(Int32))
            return row.Int32(columnName);

        if (targetType == typeof(bool))
            return row.Bool(columnName);

        if (targetType == typeof(DateTime))
            return row.DateTime(columnName);

        if (targetType == typeof(Decimal))
            return row.Decimal(columnName);

        if (targetType == typeof(Double))
            return row.Double(columnName);

        if (targetType == typeof(Single))
            return row.Single(columnName);

        return row.String(columnName);
    }
}

Then your code becomes:

pauv.Description = priceAndUsageVarianceRow.String("Description");
pauv.Week1Usage = priceAndUsageVarianceRow.Double("Week1Usage");
pauv.Week2Usage =  priceAndUsageVarianceRow.Double("Week2Usage");

You can improve this by using generics. you need to do something like `return (T)(row.IsDBNull(ordinal)? default(T): row.GetValue(ordinal))` in your extension method. — Kosala W, Jan 20 '16 at 01:16
@KosalaW I could not use generics, if I want to use this as an extension class. The syntax would get more contorted — Jens Meinecke, Jan 20 '16 at 02:35
Not really. Have a look at my answer. Well basically it's your answer. not my answer. :) — Kosala W, Jan 20 '16 at 03:03
@KosalaW - you are right - my understanding of the restrictions on generics in class extensions was incomplete. You learn something new every day! — Jens Meinecke, Jan 20 '16 at 05:26

score 3 · Answer 2 · answered Jan 20 '16 at 03:01

Just adding Generics to @Übercoder's answer.

Your extension method;

public static T GetValueOrDefault<T>(this IDataRecord row, string fieldName)
{
    int ordinal = row.GetOrdinal(fieldName);
    return row.GetValueOrDefault<T>(ordinal);
}

public static T GetValueOrDefault<T>(this IDataRecord row, int ordinal)
{
    return (T)((row.IsDBNull(ordinal) ? default(T) : row.GetValue(ordinal)));
}

Sample call;

DataReader reader = //your database call
var employees =  new List<Employee>();
while (reader.Read())
{
    var employee = new Employee
    {
        Id = reader.GetValueOrDefault<int>("EmpId"),
        Name = reader.GetValueOrDefault<string>("Name")
    };
    employees.Add(employee);
}

This is very impressive (really, I'm not being sarcastic), but does it have any advantage over Übercoder's answer (which works for me)? — B. Clay Shannon-B. Crow Raven, Jan 20 '16 at 16:21
@B.ClayShannon _does it have any advantage over Übercoder's answer`_. Performance wise, no. but LOC wise, yes. That's why I said, this is Übercoder's answer with generics. :) — Kosala W, Jan 20 '16 at 22:17

score 1 · Answer 3 · answered Jan 20 '16 at 00:36

1

Change the last if condition to

if (!(priceAndUsageVarianceRow["Week2Usage"] is DBNull) && null != priceAndUsageVarianceRow["Week2Usage"])
{
    pauv.Week2Usage = Convert.ToDouble(priceAndUsageVarianceRow["Week2Usage"]);
}

answered Jan 20 '16 at 00:36

Rahul

76,197
13
71
125

How can I guard against "Object cannot be cast from DBNull to other types"?

3 Answers3