How to check IS NULL on DataTable?

Question

In my case i am passing a sql query and getting data in dataset, but problem occurs when i try to get the rows where ParentId column contain NULL.This is the piece of code.

   DataSet ds = GetDataSet("Select ProductId,ProductName,ParentId from ProductTable");

   //ds is not blank and it has 2 rows in which ParentId is NULL

   DataRow[] Rows = ds.Tables[0].Select("ParentId IS NULL");

But still i am not getting any rows. Need help. Thanx.

Solution 1

Use the strongly typed DataRow extension method Field which also supports nullable types:

IEnumerable<DataRow> rows = ds.Tables[0].AsEnumerable()
    .Where(r => !r.Field<int?>("ParentId").HasValue);

Note that i've used Enumerable.Where which is a Linq extension method to filter the table.

If you want an array use ToArray, if you want a new DataTable use CopyToDataTable. If you simply want to enumerate the result use foreach.

foreach(DataRow row in rows)
{
    // ...
}

Solution 2

var rowsWithoutParent = dt.AsEnumerable().Where(r => r["ParentId"] == null);

var rowsWithParent = dt.AsEnumerable().Where(r => r["ParentId"] != null);

Solution 3

var rows = ds.Tables[0].AsEnumerable()
    .Where(r => r.IsNull("ParentId"));