XtremeData
LANGUAGES: C#
ASP.NET VERSIONS: 1.0 |1.1
Find and Filter Relational Data
Search, filter, and sort data from relational data sources to give your users more choice and control.
By Dan Wahlin
Today's desktop and Web-based applications rely upon the ability to find and filter data from a variety of distributed data sources. These sources can be relational data stores such as SQL Server or Oracle, XML documents, or even data retrieved from Web services. Fortunately, the .NET platform contains many different built-in classes that make it easy to locate specific data without being forced to write a lot of code. Many of these classes are flexible enough to handle loading both relational and XML data and provide methods, properties, and child objects that allow for easy data manipulation. In this article I'll demonstrate how to use several different .NET data objects to search, filter, and even sort data returned from relational data sources to provide end users with more choice and control.
Before exploring some of the different .NET data classes that can be used to find and filter data, it is worthwhile to consider alternatives. After all, any serious application developer must evaluate and understand the various alternatives so the application's architecture is built to be as secure, scalable, and efficient as possible. Failure to explore alternatives can lead to ugly consequences down the road.
In the case of data searching and filtering, relational databases tend to offer the best performance - given that they are designed for these types of activities. The question then becomes, why not always search and filter data using native database techniques, such as Structured Query Language's WHERE and ORDER BY keywords? This question is especially important since filters, sorts, and searches can easily be incorporated into database stored procedures that can be called from ASP.NET applications.
While data searching and filtering should be done using a database whenever possible, there are cases where it may be more efficient to load data into an application, cache the data, and then manipulate the data as dictated by the end user. Doing this can enhance the application's performance and alleviate some of the load on the database server. In some cases the data may not actually come from a relational database, so the application must be responsible for handling any data manipulation. This is often the case when data is accessed from an XML document or is returned from a Web Service. The next few sections will provide examples of using .NET classes to search, filter, and sort data. In a future article I'll discuss different ways XML data can be searched, filtered, and sorted.
Search and Sort with the DataTable Class
The DataSet class has become a favorite of many .NET developers, due to its simple object model and robust feature set. However, the main purpose of the DataSet is to act as a container capable of holding one or more DataTable object instances. It can, of course, be used for several other things, such as establishing relationships between DataTables, cloning tables, copying tables, plus more. However, in cases where you need to search, filter, or sort data, you don't use the DataSet directly. Instead, you can access a specific DataTable instance and use the appropriate properties and methods. You can also use other classes, such as the DataView (discussed next).
Searching and sorting data within a DataTable can be accomplished by using the overloaded Select method shown in Figure 1.
|
Overload Method |
Description |
|
Select |
Accepts no parameters that can be used to filter or sort data. This overload is used to access all rows as a DataRow array. |
|
Select(filterString) |
Accepts a string containing filter syntax used to filter out unwanted rows. Returns a DataRow[] array containing the rows that match the filter expression. |
|
Select(filterString, sortString) |
Accepts two parameters that specify the filter expression, as well as the sort expression. Returns a sorted DataRow[] array containing the rows that match the filter expression. |
|
Select(filterString, sortString, DataViewRowState |
Accepts three parameters that specify the filter expression, the sort expression, and the row state to select. Returns a sorted DataRow[] array containing the rows that match the filter expression. Possible DataViewRowState enumeration members include Added, CurrentRows, Deleted, ModifiedCurrent, ModifiedOriginal, None, OriginalRows, and Unchanged. |
Figure 1. The Select method of the DataTable class contains several different overloads that return an array of DataRow objects.
This method returns an array of DataRow objects that can be iterated through in order to access column data. Figure 2 shows an example of passing a filter expression into Select and then iterating through the returned DataRow objects. Using the different overloads you can narrow the search to only new data, data that has been modified, or even deleted data.
private void Page_Load(object sender, System.EventArgs e) {
if (Cache.Get("CustomersDS") == null) {
string connStr =
ConfigurationSettings.AppSettings["connStr"];
string sql = "SELECT * FROM Customers";
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter da = new SqlDataAdapter(sql,conn);
DataSet ds = new DataSet();
da.Fill(ds,"Customers");
Cache.Insert("CustomersDS",ds,null,
DateTime.Now.AddMinutes(30),TimeSpan.Zero);
}
DisplayData();
}
private void DisplayData() {
StringBuilder sb = new StringBuilder();
string filterText = "ContactName LIKE 'A%'";
//Grab DataSet from Cache
DataSet ds = (DataSet)Cache.Get("CustomersDS");
//Use Select() to access only those rows where the
//ContactName field starts with 'A'.
//Sort returned rows by ContactName in Descending order
DataRow[] rows =
ds.Tables[0].Select(filterText,"ContactName DESC");
if (rows.Length > 0) {
foreach (DataRow row in rows) {
sb.Append(row["ContactName"].ToString());
sb.Append("
");
}
this.lblOutput.Text = sb.ToString();
} else {
this.lblOutput.Text = "No records found.";
}
}
Figure 2. The DataTable's Select method makes it easy to filter and sort data. The code shown here queries the Northwind database's Customers table and returns all customers whose contact name starts with the letter "A". The resulting rows are iterated through and written out to a Label control. Although this example shows how a DataSet can be cached, since there is only one DataTable involved, it could be cached instead to minimize unnecessary memory use.
Looking through the code in Figure 2 you can see that using the DataTable class' Select method is fairly straightforward (you can read more about the different filter expressions that can be used with Select at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataColumnClassExpressionTopic.asp). However, there's a problem that occurs when trying to bind a DataRow array returned from calling the Select method to ASP.NET Web server controls. This problem is easy to see when you try to bind to a DropDownList control using the following code:
DataRow[] rows =
ds.Tables[0].Select(filterText,"ContactName DESC");
this.ddCustomers.DataTextField = "ContactName";
this.ddCustomers.DataValueField = "CustomerID";
this.ddCustomers.DataSource = rows;
this.ddCustomers.DataBind();
Running this code (without wrapping a try...catch block around it) will generate the following error in the browser:
DataBinder.Eval: 'System.Data.DataRow' does
not contain a property with the name ContactName.
The easiest solution to this problem (and one that keeps code to a minimum) is to handle the data binding operation yourself by iterating through the DataRow array and creating new ListItem objects (there is, of course, an easier way to do this, which will be covered in the next section):
DataRow[] rows =
ds.Tables[0].Select(filterText,"ContactName DESC");
foreach (DataRow row in rows) {
this.ddCustomers.Items.Add(
new ListItem(row["ContactName"].ToString(),
row["CustomerID"].ToString()));
}
Fortunately, the DataGrid provides a fairly easy (although less known) mechanism for binding a DataRow array using the DataSource property and DataBind method. To make this work a minor change must be made in the DataGrid's template code (in the .aspx page) for the columns being bound to the grid. Normally, the following works to bind a column to the DataGrid:
<%@ DataBind.Eval(Container.DataItem,"CustomerID") %>
When binding a DataRow array to a DataGrid (using C#) you'll need to change the above code so that the DataGrid knows to access the column using the DataRow object's default indexer. This change is shown below (notice that the quoted value now contains square brackets around it):
<%# DataBinder.Eval(Container.DataItem,
"[\"CustomerID\"]") %>
An example of using the Select method to create a basic contact list for viewing customer details is shown in Figure 3. You'll find the code for this page in the article's downloadable code (see DataTableSelectBind.aspx).
Figure 3. By caching a DataSet or
DataTable the load on the database can be minimized. Using the DataTable's
Select method makes it easy to filter customers based on name or any other
field.
Search and Sort with the DataView Class
Although using the Select method may be desirable in specific situations, there are many cases where using a DataView can simplify filtering and sorting data. Because a DataView can be bound to several different ASP.NET controls it can also simplify data binding as compared to binding a DataRow array returned from calling the DataTable class' Select method.
So what's the difference between a DataTable and a DataView? First, a DataTable acts as the source container for data. Although you can access a filtered set of rows, you can't change the actual data within the DataTable without inserting, modifying, or deleting a row. Second, DataTables don't have a native Sort method. Sorts are performed by calling Select.
DataViews allow multiple "views" of a single data source (such as rows held within a DataTable) to be searched, filtered, and sorted. Instead of returning an array of rows, however, a DataView changes its view of the data to match whatever the end user or developer desires. As mentioned earlier, it is also much easier to bind a DataView to different Web server controls.
DataViews are typically created by using one of the techniques shown below:
DataView view = new DataView(dataTable);
DataView view = dataSet.Tables[0].DefaultView;
The first line of code shown above passes a DataTable instance into the DataView's constructor while the second uses the DataTable's DefaultView property to create the view. A third way exists as well, which allows a DataView object to be created that is automatically filtered and sorted as desired. This is accomplished by passing the filter and sort strings to the DataView's constructor along with the type of rows that should be acted upon:
string filter = "ContactName LIKE '%A'";
string sort = "ContactName, Region DESC";
DataView view =
new DataView(dataTable,filter,sort,
DataViewRowState.CurrentRows);
Note that there are other ways to create a DataView as well, such as using the DataRowView object's CreateChildView method along with a DataRelation. Although these techniques won't be covered here, the .NET SDK provides additional details.
Once a DataView is created it can be further modified to match different program scenarios. For example, to filter out undesirable rows the DataView's RowFilter property can be called. This property takes a string containing the filter details. Sorts can also be performed by assigning a string containing a comma separated list of one or more columns to sort to the Sort property.
In addition to these features, the DataView also allows rows to be found using the Find and FindRows methods. The Find method locates a specific row based upon the column specified by the Sort property. It returns an integer representing the position of the row within the DataView. FindRows functions in a similar manner to Find except that it returns a DataRowView array of the rows matching the query. Figure 4 shows an example of creating a DataView and automatically adding a filter and sort as well as using the Find method to locate a unique row.
private void hl_Click(object sender, System.EventArgs e) {
LinkButton btn = (LinkButton)sender;
this.ddCustomers.Items.Clear();
string filterText = "ContactName LIKE '" +
btn.CommandArgument + "%'";
DataSet ds = (DataSet)Cache.Get("CustomersDS");
DataView view = new DataView(ds.Tables[0],filterText,
"ContactName DESC",DataViewRowState.CurrentRows);
if (view.Count > 0) {
this.ddCustomers.Visible = true;
this.Label1.Visible = false;
this.ddCustomers.DataSource = view;
this.ddCustomers.DataBind();
this.ddCustomers.Items.Insert(0,
new ListItem("Select One:",""));
} else {
this.ddCustomers.Visible = false;
this.Label1.Visible = true;
this.Label1.Text = "No records found.";
}
}
private void ddCustomers_SelectedIndexChanged(object sender,
System.EventArgs e) {
this.pnlDetails.Visible = true;
DataSet ds = (DataSet)Cache.Get("CustomersDS");
//We could filter rows using RowFilter as well.
//This example uses the Find() method, however.
DataView view = new DataView(ds.Tables[0]);
view.Sort = "CustomerID";
int index = view.Find(this.ddCustomers.SelectedValue);
//Ensure we found a valid row in the DataView
if (index > -1) {
//Bind specific DataRowView columns to labels
DataRowView row = view[index];
this.lblCustomerID.Text = row["CustomerID"].ToString();
this.lblContactName.Text = row["ContactName"].ToString();
this.lblCompanyName.Text = row["CompanyName"].ToString();
this.lblPhone.Text = row["Phone"].ToString();
}
}
Figure 4. Searching a DataView object is handled by using the Find or FindRows method. This code shows how to use the integer value returned from calling Find to locate a DataRowView object within a DataView. It also demonstrates binding a DataRowView array to multiple Label controls.
Figure 5 shows the output generated after binding the row's different columns to ASP.NET label controls.
Figure 5. This graphic shows the
output generated from calling the DataView's Find method. The returned
DataRowView is bound to several different ASP.NET Label controls.
Knowing the different ADO.NET options for searching, sorting, and filtering data makes creating flexible ASP.NET Web applications a snap. In this article you've seen several different techniques for accomplishing these tasks using both the DataTable and DataView classes. Although it is recommended that searches, filters, and sorts be performed at the database whenever possible, there are cases where manipulating data on the Web server may be necessary.
The sample code in this article is available for download.
Dan Wahlin (Microsoft Most Valuable Professional for ASP.NET and XML Web services) is the president of Wahlin Consulting and founded the XML for ASP.NET Developer's Web site (http://www.XMLforASP.NET), which focuses on using XML and Web services in Microsoft's .NET platform. He's also a corporate trainer and speaker, and teaches XML and .NET training courses around the US. Dan co-authored Professional Windows DNA (Wrox, 2000) and ASP.NET: Tips, Tutorials and Code (Sams, 2001), and authored XML for ASP.NET Developers (Sams, 2001).
