Filtering with a DataView

You can a DataView to apply custom filtering so that only certain rows are included in the
display. To accomplish this feat, you use the RowFilter property. The RowFilter property acts like a WHERE clause in a SQL query. Using it, you can limit results using logical operators (such as <, >, and =) and a wide range of criteria. Bellow the most common filter operators.

Filter Operators

Operator Description
<, >, <=, and >= Performs comparison of more than one value. These comparisons can be
numeric (with number data types) or alphabetic dictionary comparisons
(with string data types).
<> and = Performs equality testing.
NOT Reverses an expression. Can be used in conjunction with any other clause.
BETWEEN Specifies an inclusive range. For example, “Units BETWEEN 5 AND 15”
selects rows that have a value in the Units column from 5 to 15.
IS NULL Tests the column for a null value.
IN(a,b,c) A short form for using an OR clause with the same field. Tests for equality
between a column and the specified values (a, b, and c).
LIKE Performs pattern matching with string data types.
+ Adds two numeric values or concatenates a string.
- Subtracts one numeric value from another.
* Multiplies two numeric values.
/ Divides one numeric value by another.
% Finds the modulus (the remainder after one number is divided by another).
AND Combines more than one clause. Records must match all criteria to be
displayed.
OR Combines more than one clause. Records must match at least one of the
filter expressions to be displayed.
The following example page includes three GridView controls. Each one is bound to the same
DataTable but with different filter settings.

string connectionString =
WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
string sql = "SELECT ProductID, ProductName, UnitsInStock, UnitsOnOrder, " +
Discontinued FROM Products";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
da.Fill(ds, "Products");
// Filter for the Chocolade product.
DataView view1 = new DataView(ds.Tables["Products"]);
view1.RowFilter = "ProductName = 'Chocolade'";
GridView1.DataSource = view1;

// Filter for products that aren't on order or in stock.
DataView view2 = new DataView(ds.Tables["Products"]);
view2.RowFilter = "UnitsInStock = 0 AND UnitsOnOrder = 0";
GridView2.DataSource = view2;
// Filter for products starting with the letter P.
DataView view3 = new DataView(ds.Tables[“Products”]);
view3.RowFilter = "ProductName LIKE 'P%'";
GridView3.DataSource = view3;
this.DataBind();