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; ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.