How to filter data with the ADO.NET DataView in C#

If in your project you want to include only certain rows in the display you can use a DataView to apply custom filtering. To accomplish this, you should use the RowFilter property which acts like a WHERE clause in a SQL query. You can limit the final results by using it and by applying logical operators, described in the next table:

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 next example page includes three GridView controls. Each one is bound to the same DataTable but with different filter settings:

// Step 1. Retrieve the data into a DataSet

 

// Create the Connection, DataAdapter, and DataSet.

string connectionString = WebConfigurationManager.ConnectionStrings[“SomeCompany”].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”);

 

//Step 2. Filter each DataView for different criterion

 

// Filter for the Computer product

DataView view1 = new DataView(ds.Tables[“Products”]);

view1.RowFilter = “ProductName = ‘Computer'”;

grid1.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”;

grid2.DataSource = view2;

 

// Filter for products starting with the letter K.

DataView view3 = new DataView(ds.Tables[“Products”]);

view3.RowFilter = “ProductName LIKE ‘K%'”;

grid3.DataSource = view3;

 

//Step 3. Copy the values form the DataTable into control separately or for entire page by calling Page.DataBind().

Page.DataBind();