ADO.NET 3.5 Cookbook, 2nd Edition

4.6. Filtering Null Field Values in a DataTable

Problem

You want to filter a DataTable or DataView for rows that have null field values.

Solutions

Choose from the two techniques shown in the solution to find data in the table with null field values.

The solution creates a DataTable containing data from the Person.Contact table in AdventureWorks. The solution uses two different techniques—the Sort property of the DataView class or the DataTable.Select() method—to filter records containing null values for user-specified fields.

The C# code in Program.cs in project FilterNullValues is shown in Example 4-7.

Example 4-7. File: Program.cs for FilterNullValues solution

using System; using System.Data; using System.Data.SqlClient; namespace FilterNullValues { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT * FROM Person.Contact"; string filter = "Title IS NULL"; DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); da.Fill(dt); // Filter the rows using Select() method of DataTable DataRow[] rows = dt.Select(filter); // Output the top 5 rows Console.WriteLine("---DataTable.Select()---"); for (int i = 0; i <= 5; i++) { Console.WriteLine("{0}\tTitle = '{1}'\t{2}, {3}", rows[i]["ContactID"], rows[i]["Title"], rows[i]["LastName"], rows[i]["FirstName"]); } // Filter the rows using the Filter property of the DataView ...