Finding null value in Dataset - DataRow.IsNull method vs ==DbNull.Value - c#

Question

What are the benefits of using the c# method DataRow.IsNull to determine a null value over checking if the row equals DbNull.value?

if(ds.Tables[0].Rows[0].IsNull("ROWNAME")) {do stuff}

vs

if(ds.Tables[0].Rows[0]["ROWNAME"] == DbNull.value) {do stuff}

Answer 1

There is no real practical benefit. Use whichever one seems more readable to you.

As to the particular differences between them, the basic answer is that IsNull queries the null state for a particular record within a column. Using == DBNull.Value actually retrieves the value and does substitution in the case that it's actually null. In other words, IsNull checks the state without actually retrieving the value, and thus is slightly faster (in theory, at least).

It's theoretically possible for a column to return something other than DBNull.Value for a null value if you were to use a custom storage type, but this is never done (in my experience). If this were the case, IsNull would handle the case where the storage type used something other than DBNull.Value, but, again, I've never seen this done.

Answer 2

DBNull.Value != null

DBNull.Value stands for a column having the value <NULL>. Pop open a table and return some rows, see if any column in any row contains the <NULL>(ctrl 0) value. If you see one that is equivalent to DBNull.Value.

if you set a value to null or DBNull.Value then you will want to use IsNull(). That returns true if the value was set to either null or DBNull.Value.

Consider the following:

row["myCol"] = null;

row["myCol"] = DBNull.Value

if (row["myCol"] == DBNull.Value) //returns true

if (row["myCol"] == null) //returns false

if (row.IsNull("myCol")) //returns true

The point is if you are just checking for null or DBNull.Value use IsNull, if you are only checking for DBNull.Value explicitly say so and use that.

Answer 3

For one it's less typing. Other than that I think they are equivalent.

To try and clarify why I say they are equivalent.

[Test()]
public void test() {
    var t = new System.Data.DataTable();
    t.Columns.Add("col1");
    var r = t.NewRow();

        // null is converted to DBNull.Value by DataRow
        r["col1"] = null;
        Assert.IsFalse(r["col1"] == null);
        Assert.IsTrue(r["col1"] == DBNull.Value);
        Assert.IsTrue(r.IsNull("col1"));

        // nullable types w/o values are also converted
        int? val = null;
        Assert.IsFalse(val.HasValue);
        r["col1"] = val;
        Assert.IsTrue(r["col1"] == DBNull.Value);
        Assert.IsTrue(r.IsNull("col1"));


}

Answer 4

FWIW, I wrote a bunch of DataRow extension methods — CastAsXXX() — to avoid having to deal with DB nullability...or at least defer it a bit B^). Here's my CastAsInt() and CastAsIntNullable() methods:

#region downcast to int

public static int CastAsInt( this DataRow row , int index )
{
  return toInt( row[index] ) ;
}
public static int CastAsInt( this DataRow row , string columnName )
{
  return toInt( row[columnName] ) ;
}

public static int? CastAsIntNullable( this DataRow row , int index )
{
  return toIntNullable( row[index] );
}
public static int? CastAsIntNullable( this DataRow row , string columnName )
{
  return toIntNullable( row[columnName] ) ;
}

#region conversion helpers

private static int toInt( object o )
{
  int value = (int)o;
  return value;
}

private static int? toIntNullable( object o )
{
  bool hasValue = !( o is DBNull );
  int? value    = ( hasValue ? (int?) o : (int?) null ) ;
  return value;
}

#endregion conversion helpers

#endregion downcast to int

Usage is pretty straightforward. You just need to state your expectations up front.

DataRow dr = GetADataRowFromSomewhere() ;
// Throws NullReferenceException if the column is null
int     x  = dr.CastAsInt(         "column_1" ) ;
// Is perfectly happy with nulls (as it should be)
int?    y  = dr.CastAsIntNullable( "column_1" ) ;

I tried to make them generic, but no dice unless I'm willing to correlate NULLs from the database with the default value for the type (e.g., 0 for numeric types), which I'm not.