Is there a better way to do this: Convert.IsDBNull(row[somecolumn]) checking

Question

Hi all,

Is there a better way to write this code..

MyObject pymt = new MyObject();
pymt.xcol1id= Convert.IsDBNull(row["col1id"]) ? 0 : (int)row["col1id"];
pymt.xcold2id= Convert.IsDBNull(row["col2id"]) ? String.Empty : (string)row["col2id"];
pymt.xcold3id = Convert.IsDBNull(row["CustNum"]) ? 0 : (decimal)row["xcold3id"];

could this be done in a cleaner way .. like generic methods etc??

Answer 1

 static ToType GenericConvert<ToType>(object value)
        {
            ToType convValue = default(ToType);

            if (!Convert.IsDBNull(value))
                convValue = (ToType)value;

            return convValue;
        }


MyObject pymt = new MyObject();
pymt.xcol1id= GenericConvert<int>(row["col1id"]);
pymt.xcold2id= GenericConvert<string>(row["col2id"]) ?? String.Empty;
pymt.xcold3id = GenericConvert<decimal>(row["CustNum"]);

Answer 2

I actually like that. Our team had been using if/else statements which are more difficult to read in my opinion.

if (row["col1id"] == dbnull.value)
   pymt.xcol1id = 0;
else
   pymt.xcol1id = (int)row["col1id"];

Your code is easier to read because it places each assignment on individual lines.

A function as suggested by Stan might make the lines shorter but it hides the fact that nulls are being replaced unless you give it a very verbose name.

Answer 3

You could make generic extension methods like this:

public static class DataRowExtensions {

   public static T GetValueOrDefault<T>(this DataRow row, string key) {
      return row.GetValueOrDefault(key, default(T));
   }

   public static T GetValueOrDefault<T>(this DataRow row, string key, T defaultValue) {
      if (row.IsNull(key)) {
         return defaultValue;
      } else {
         return (T)row[key];
      }
   }

}

Usage:

MyObject pymt = new MyObject();
pymt.xcol1id = row.GetValueOrDefault<int>("col1id");
pymt.xcold2id = row.GetValueOrDefault<string>("col2id", String.Empty);
pymt.xcold3id = row.GetValueOrDefault<int>("CustNum"]);

Answer 4

I tend to use the null coalescing operator in situations like those. Also, DBNull.ToString returns string.Empty, so you don't have to do anything fancy there.

MyObject pymt = new MyObject();
pymt.xcol1id= row["col1id"] as int? ?? 0;
pymt.xcold2id= row["col2id"].ToString();
pymt.xcold3id = row["CustNum"] as decimal? ?? 0;

Answer 5

Some alternatives I can quickly think of:

1. Strongly typed datasets
2. Use the SqlTypes readers like ReadSqlInt32 since they all implement INullable.
3. Generate the code with XSLT transformation from XML, so the checking code is easy to change and maintain.

Answer 6

Better yet, add it as an extension method to the DataRow:

static T GetValueOrDefault<T>(this DataRow row, string columnName)
{
    if (!row.IsNull(columnName))
    {
        // Might want to support type conversion using Convert.ChangeType().
        return (T)row[columnName]
    }

    return default(T);
}

You can use it like:

pymt.xcol1id = row.GetValueOrDefault<int>("col1id");
pymt.xcol2id = row.GetValueOrDefault<string>("col2id");
pymt.xcol3id = row.GetValueOrDefault<decimal>("col3id");

Answer 7

Absolutely there's a cleaner way to write that code if you're using .NET 3.5, and without re-inventing extension methods that Microsoft already wrote for you. Just add a reference to System.Data.DataSetExtensions.dll, and you'll be able to do this:

MyObject pymt = new MyObject
                {
                    xcol1id = row.Field<int?>("col1id") ?? 0,
                    xcol2id = row.Field<string>("col2id") ?? String.Empty,
                    xcol3id = row.Field<decimal?>("col3id") ?? 0M
                };