102

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)

Ananth
  • 10,330
  • 24
  • 82
  • 109
  • possible duplicate of [Most efficient way to check for DBNull and then assign to a variable?](http://stackoverflow.com/questions/221582/most-efficient-way-to-check-for-dbnull-and-then-assign-to-a-variable) – nawfal Dec 11 '13 at 16:40

7 Answers7

191

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();
hunter
  • 62,308
  • 19
  • 113
  • 113
  • 2
    What's best practice for where this extension method should go? – StuperUser Jan 05 '11 at 14:09
  • 6
    I usually create an "Extensions" folder in some common library or in my DAL. Create a file called "DataTableExtensions.cs" and add that method. Next you would just add "using Name.Space.Extensions" to your cs files and have access to all extension methods defined. – hunter Jan 05 '11 at 14:13
  • 3
    You can (now) use `table.AsEnumerable()` instead of `table.Rows.OfType()` – Teejay Dec 14 '16 at 16:08
24
foreach(DataRow row in dataTable.Rows)
{
    if(row.IsNull("myColumn"))
        throw new Exception("Empty value!")
}
zavaz
  • 745
  • 4
  • 13
14

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.

StuperUser
  • 10,555
  • 13
  • 78
  • 137
2
DataTable dt = new DataTable();
foreach (DataRow dr in dt.Rows)
{
    if (dr["Column_Name"] == DBNull.Value)
    {
        //Do something
    }
    else
    {
        //Do something
    }
}
Pang
  • 9,564
  • 146
  • 81
  • 122
Sush
  • 21
  • 3
  • 5
    While this code may answer the question, it is better to explain how to solve the problem and provide the code as an example or reference. Code-only answers can be confusing and lack context. – Robert Columbia Sep 06 '18 at 21:21
1

I will do like....

(!DBNull.Value.Equals(dataSet.Tables[6].Rows[0]["_id"]))
Shailendra Mishra
  • 71
  • 10
1
public static class DataRowExtensions
{
    public static T GetValue<T>(this DataRow row, string fieldName)
    {
        if (row.IsNull(fieldName))
        {
            return default(T);
        }

        var value = row[fieldName];
        if (value == DBNull.Value)
        {
            return default(T);
        }

        if (typeof(T) == typeof(string))
        {
            return (T)Convert.ChangeType(value.ToString(), typeof(T));
        }

        return (T)Convert.ChangeType((T)value, typeof(T));
    }
}

Usage:

string value = row.GetValue<string>("ColumnName");
Alper Ebicoglu
  • 8,884
  • 1
  • 49
  • 55
0

You can null/blank/space Etc value using LinQ Use Following Query 

   var BlankValueRows = (from dr1 in Dt.AsEnumerable()
                                  where dr1["Columnname"].ToString() == ""
                                  || dr1["Columnname"].ToString() == ""
                                   || dr1["Columnname"].ToString() == ""
                                  select Columnname);

Here Replace Columnname with table column name and "" your search item in above code we looking null value.

Sandy Sing
  • 11
  • 2