Best way to check if a Data Table has a null value in it

what is the best way to check if a Data Table has a null value in it ?

Most of the time in our scenario, one column will have all null values.

(This datatable is returned by a 3rd party application - we are trying to put a valiadation before our application processes the data-table)


Try comparing the value of the column to the DBNull.Value value to filter and manage null values in whatever way you see fit.

foreach(DataRow row in table.Rows)
{
    object value = row["ColumnName"];
    if (value == DBNull.Value)
        // do something
    else
        // do something else
}

More information about the DBNull class


If you want to check if a null value exists in the table you can use this method:

public static bool HasNull(this DataTable table)
{
    foreach (DataColumn column in table.Columns)
    {
        if (table.Rows.OfType<DataRow>().Any(r => r.IsNull(column)))
            return true;
    }

    return false;
}

which will let you write this:

table.HasNull();

foreach(DataRow row in dataTable.Rows)
{
    if(row.IsNull("myColumn"))
        throw new Exception("Empty value!")
}

You can loop throw the rows and columns, checking for nulls, keeping track of whether there's a null with a bool, then check it after looping through the table and handle it.

//your DataTable, replace with table get code
DataTable table = new DataTable();
bool tableHasNull = false;

foreach (DataRow row in table.Rows)
{
    foreach (DataColumn col in table.Columns)
    {
        //test for null here
        if (row[col] == DBNull.Value)
        {
            tableHasNull = true;
        }
    }
}

if (tableHasNull)
{
    //handle null in table
}

You can also come out of the foreach loop with a break statement e.g.

//test for null here
if (row[col] == DBNull.Value)
{
    tableHasNull = true;
    break;
}

To save looping through the rest of the table.