Finding rows with DBNull.Value from a Data Table using Select statement

Question

I have a data table with only one column containing strings. What is the best way to get a collection of rows having DBNull.Value using DataTable.Select() method?

DataTable dataTable = new DataTable("names");
DataColumn newColumn = new DataColumn("Name", System.Type.GetType("System.String")
{
AllowDBNull = true
};
DataRow row1 = dataTable.NewRow();
row1["Name"] = "John";
dataTable.Rows.Add(row1);
DataRow row2 = dataTable.NewRow();
row2["Name"] = DBNull.Value;
dataTable.Rows.Add(row2);

I tried dataTable.Select("Name is null") and it returned row2. But aren't null and DBNull.Value different as in null meaning an invalid reference and DBNull.Value representing a non existent value?

Answer 1

Use:

var nullRows = dataTable.Select("[Name] is null");

Another option is to use LINQ

var query = dataTable.AsEnumerable()
    .Where(r => r.Field<string>("Name") == null);

Make sure that you add the column to your DataTable

dataTable.Columns.Add(newColumn);

Answer 2

Maybe something like:

var NullRows = dataTable.Select("[Name]=''");