ASP.NET C# Programmer Kerala: Advanced Filtering with Relationships
The DataView allows for some surprisingly complex filter expressions. One of its little-known features is the ability to filter rows based on relationships. For example, you could display categories that contain more than 20 products, or you could display customers who have made a certain
number of total purchases. In both of these examples, you need to filter one table based on theinformation in a related table.
• A table relationship that links two tables.
• An aggregate function such as AVG(), MAX(), MIN(), or COUNT(). This function acts on the
data in the related records.
For example, suppose you’ve filled a DataSet with the Categories and Products tables and
defined this relationship:
// Define the relationship between Categories and Products.
DataRelation relat = new DataRelation("CatProds",
ds.Tables["Categories"].Columns["CategoryID"],
ds.Tables["Products"].Columns["CategoryID"]);
// Add the relationship to the DataSet.
ds.Relations.Add(relat);
You can now filter the display of the Categories table using a filter expression based on the
Products table. For example, imagine you want to show only category records that have at least one
product worth more than $50. To accomplish this, you use the COUNT() function, along with the
name of the table relationships (CatProds).
Here’s the filter string you need:
MAX(Child(CatProds).UnitPrice) > 50
And here’s the code that applies this filter string to the DataView:
DataView view1 = new DataView(ds.Tables["Categories"]);
view1.RowFilter = "MAX(Child(CatProds).UnitPrice) > 50";
GridView1.DataSource = view1;
The end result is that the GridView shows only the categories that have a product worth more
than $50.
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();
The next example uses a page with three GridView controls. When the page loads, it binds the same
DataTable to each of the grids. However, it uses three different views, each of which sorts the results
using a different field.
The code begins by retrieving the list of employees into a DataSet:
// Create the Connection, DataAdapter, and DataSet.
string connectionString =
WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
string sql =
"SELECT TOP 5 EmployeeID, TitleOfCourtesy, LastName, FirstName FROM Employees";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
// Fill the DataSet.
da.Fill(ds, "Employees");
The next step is to fill the GridView controls through data binding. To bind the first grid, you
can simply use the DataTable directly, which uses the default DataView and displays all the data.
For the other two grids, you must create a new DataView object. You can then set its Sort property explicitly.
// Bind the original data to #1.
grid1.DataSource = ds.Tables["Employees"];
// Sort by last name and bind it to #2.
DataView view2 = new DataView(ds.Tables["Employees"]);
view2.Sort = "LastName";
grid2.DataSource = view2;
// Sort by first name and bind it to #3.
DataView view3 = new DataView(ds.Tables["Employees"]);
view3.Sort = "FirstName";
grid3.DataSource = view3;
Sorting a grid is simply a matter of setting the DataView.Sort property to a valid sorting expression.
This example sorts by each view using a single field, but you could also sort by multiple fields,
by specifying a comma-separated list. Here’s an example:
view2.Sort = "LastName, FirstName";
■Note The sort is according to the data type of the column. Numeric and date columns are ordered from
smallest to largest. String columns are sorted alphanumerically without regard to case, assuming the
DataTable.CaseSensitive property is false (the default). Columns that contain binary data cannot be sorted.
You can also use the ASC and DESC attributes to sort in ascending or descending order.
Once you’ve bound the grids, you still need to trigger the data binding process that copies the
values from the DataTable into the control. You can do this for each control separately or for the
entire page by calling Page.DataBind(), as in this example:
Page.DataBind();
The next example shows a more advanced use of the DataSet that, in addition to providing disconnected
data, uses table relationships. This example demonstrates how to retrieve some records from
the Categories and Products tables of the Northwind database and how to create a relationship
between them so that it’s easy to navigate from a category record to all of its child products and
create a simple report.
The first step is to initialize the ADO.NET objects and declare the two SQL queries (for retrieving
categories and products), as shown here:
string connectionString =
WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
string sqlCat = "SELECT CategoryID, CategoryName FROM Categories";
string sqlProd = "SELECT ProductName, CategoryID FROM Products";
SqlDataAdapter da = new SqlDataAdapter(sqlCat, con);
DataSet ds = new DataSet();
Next, the code executes both queries, adding two tables to the DataSet. Note that the connection
is explicitly opened at the beginning and closed after the two operations, ensuring the best
possible performance.
try
{
con.Open();
// Fill the DataSet with the Categories table.
da.Fill(ds, "Categories");
// Change the command text and retrieve the Products table.
// You could also use another DataAdapter object for this task.
da.SelectCommand.CommandText = sqlProd;
da.Fill(ds, "Products");
}
finally
{
con.Close();
}
In this example, the same DataAdapter is used to fill both tables. This technique is perfectly
legitimate, and it makes sense in this scenario because you don’t need to reuse the DataAdapter
to update the data source. However, if you were using the DataAdapter both to query data and to
commit changes, you probably wouldn’t use this approach. Instead, you would use a separate
DataAdapter for each table so that you could make sure each DataAdapter has the appropriate
insert, update, and delete commands for the corresponding table.
At this point you have a DataSet with two tables. These two tables are linked in the Northwind
database by a relationship against the CategoryID field. This field is the primary key for the Categories
table and the foreign key in the Products table. Unfortunately, ADO.NET does not provide
any way to read a relationship from the data source and apply it to your DataSet automatically.
Instead, you need to manually create a DataRelation that represents the relationship.
A relationship is created by defining a DataRelation object and adding it to the DataSet.
Relations collection. When you create the DataRelation, you specify three constructor arguments:
the name of the relationship, the DataColumn for the primary key in the parent table, and the
DataColumn for the foreign key in the child table.
Here’s the code you need for this example:
// Define the relationship between Categories and Products.
DataRelation relat = new DataRelation("CatProds",
ds.Tables["Categories"].Columns["CategoryID"],
ds.Tables["Products"].Columns["CategoryID"]);
// Add the relationship to the DataSet.
ds.Relations.Add(relat);
Once you’ve retrieved all the data, you can loop through the records of the Categories table and
add the name of each category to the HTML string:
StringBuilder htmlStr = new StringBuilder("");
// Loop through the category records and build the HTML string.
foreach (DataRow row in ds.Tables["Categories"].Rows)
{
htmlStr.Append("");
htmlStr.Append(row["CategoryName"].ToString());
htmlStr.Append("
- ");
- ");
...
Here’s the interesting part. Inside this block, you can access the related product records for the
current category by calling the DataRow.GetChildRows() method. Once you have this array of product
records, you can loop through it using a nested foreach loop. This is far simpler than the code
you’d need in order to look up this information in a separate object or to execute multiple queries
with traditional connection-based access.
The following piece of code demonstrates this approach, retrieving the child records and completing
the outer foreach loop:
...
// Get the children (products) for this parent (category).
DataRow[] childRows = row.GetChildRows(relat);
// Loop through all the products in this category.
foreach (DataRow childRow in childRows)
{
htmlStr.Append("
htmlStr.Append(childRow["ProductName"].ToString());
htmlStr.Append("");
}
htmlStr.Append("
}
The last step is to display the HTML string on the page:
HtmlContent.Text = htmlStr.ToString();
