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?

Possible duplicate of [Best way to check if a Data Table has a null value in it](http://stackoverflow.com/questions/4604414/best-way-to-check-if-a-data-table-has-a-null-value-in-it). Or specifically with `Select()`: http://stackoverflow.com/q/9324088/1132334 — Cee McSharpface, Mar 28 '17 at 16:35

score 1 · Answer 1 · edited Mar 28 '17 at 16:39

1

Maybe something like:

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

edited Mar 28 '17 at 16:39

Habib

219,104
29
407
436

answered Mar 28 '17 at 16:33

Cristian Szpisjak

2,429
2
19
32

score 1 · Answer 2 · answered Mar 28 '17 at 16:34

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);

answered Mar 28 '17 at 16:34

Habib

219,104
29
407
436

Thanks. Can you also please clarify if null and DBNull.Value are the same when using the select statement? – Krishnaveni B Mar 28 '17 at 17:19
@KrishnaveniBujaranpally, yes. – Habib Mar 28 '17 at 17:31

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

2 Answers2