ADO.NET Cookbook

3.12. Filtering for Null Values

Problem

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

Solutions

Use the IS NULL clause with the RowFilters property of the DataView.

The sample code contains two event handlers:

Form.Load

Sets up the sample by creating a DataTable containing the Orders table from the Northwind sample database. The default view of the table is bound to the data grid on the form.

Filter Button.Click

Toggles on and off the filter on the data view that selects only rows with a null ShipRegion.

The C# code is shown in Example 3-12.

Example 3-12. File: FilterNullValuesForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Data; using System.Data.SqlClient; // Table name constants private const String ORDERS_TABLE = "Orders"; // Field name constants private const String SHIPREGION_FIELD = "ShipRegion"; // . . . private void FilterNullValuesForm_Load(object sender, System.EventArgs e) { // Create and fill the Orders table. DataTable dt = new DataTable(ORDERS_TABLE); SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders", ConfigurationSettings.AppSettings["Sql_ConnectString"]); da.Fill(dt); // Bind the default view to the grid. dataGrid.DataSource = dt.DefaultView; } private void filterButton_Click(object sender, System.EventArgs e) { String filter = SHIPREGION_FIELD + " IS NULL"; DataView dv = (DataView)dataGrid.DataSource; if(filterButton.Text == "Apply Filter") { // Apply the filter. dv.RowFilter = filter; ...