DataViews and Data Binding—Filter operators

Microsoft .NET Framework, ASP.NET, Visual C# (CSharp, C Sharp, C-Sharp) Developer Training, Visual Studio

Filter operators

Like the WHERE clause, the RowFilter property allows a wide range of operators and functions for both numeric and string data types. Consider some of the following examples:

// Find all the rows that match 
// one of the three specified countries.
ds.Tables["Customers"].DefaultView.RowFilter =
  "Country IN ('Argentina', 'Canada', 'Japan')";
 
// Find all the rows where a 
// Country isn’t specified.
ds.Tables["Customers"].DefaultView.RowFilter 
  = "Country IS NULL";
 
// Use alphabetic comparison to find all 
// the rows where the Country
// starts with S or any letter after it in 
// the alphabet (including Switzerland, USA, UK, 
// Venezuela, and so on).
ds.Tables["Customers"].DefaultView.RowFilter = 
  "Country > 'S'";

After you apply a sort, you can read the DataView.Count property to determine how many rows meet the criteria and will be displayed in data bound controls.

With numeric values, you can use ranges or mathematical operators to filter rows. For example, here are some filters for the Products table:

// Find all the rows where UnitPrice is greater than 10.
ds.Tables["Products"].DefaultView.RowFilter = "UnitPrice > 10";
// Find all the rows where UnitPrice is above 10 but below 15.
// This is an exclusive range.
ds.Tables["Products"].DefaultView.RowFilter =
  "UnitPrice > 10 AND UnitPrice < 15";
 
// Find all the rows where UnitPrice is anywhere from 10 to 15.
// This is an inclusive range.
ds.Tables["Products"].DefaultView.RowFilter =
  "UnitPrice BETWEEN 10 AND 15";
 
// Find all prodcuts where the total stock value is at least $1000.
ds.Tables["Products"].DefaultView.RowFilter =
  "UnitPrice * UnitsInStock > 1000";

Table 12-1 lists the most common filter operators.

Table 12-1. Filter operators