Find and Filter Relational Data

XtremeData

LANGUAGES: C#

ASP.NET VERSIONS: 1.0 |1.1

Find and Filter Relational Data

Search, filter, and sort data from relational datasources to give your users more choice and control.

By Dan Wahlin

Today's desktop and Web-basedapplications rely upon the ability to find and filter data from a variety ofdistributed data sources. These sources can be relational data stores such asSQL Server or Oracle, XML documents, or even data retrieved from Web services.Fortunately, the .NET platform contains many different built-in classes thatmake it easy to locate specific data without being forced to write a lot ofcode. Many of these classes are flexible enough to handle loading bothrelational and XML data and provide methods, properties, and child objects thatallow for easy data manipulation. In this article I'll demonstrate how to useseveral different .NET data objects to search, filter, and even sort datareturned from relational data sources to provide end users with more choice andcontrol.

Before exploring some of thedifferent .NET data classes that can be used to find and filter data, it isworthwhile to consider alternatives. After all, any serious applicationdeveloper must evaluate and understand the various alternatives so theapplication's architecture is built to be as secure, scalable, and efficient aspossible. Failure to explore alternatives can lead to ugly consequences downthe road.

In the case of data searching andfiltering, relational databases tend to offer the best performance - given thatthey are designed for these types of activities. The question then becomes, whynot always search and filter data using native database techniques, such asStructured Query Language's WHERE and ORDER BY keywords? This question isespecially important since filters, sorts, and searches can easily beincorporated into database stored procedures that can be called from ASP.NETapplications.

While data searching and filteringshould be done using a database whenever possible, there are cases where it maybe more efficient to load data into an application, cache the data, and thenmanipulate the data as dictated by the end user. Doing this can enhance theapplication's performance and alleviate some of the load on the databaseserver. In some cases the data may not actually come from a relationaldatabase, so the application must be responsible for handling any datamanipulation. This is often the case when data is accessed from an XML documentor is returned from a Web Service. The next few sections will provide examplesof using .NET classes to search, filter, and sort data. In a future articleI'll discuss different ways XML data can be searched, filtered, and sorted.

Search and Sort with theDataTable Class

The DataSet class has become afavorite of many .NET developers, due to its simple object model and robustfeature set. However, the main purpose of the DataSet is to act as a containercapable of holding one or more DataTable object instances. It can, of course,be used for several other things, such as establishing relationships betweenDataTables, cloning tables, copying tables, plus more. However, in cases whereyou 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 appropriateproperties and methods. You can also use other classes, such as the DataView(discussed next).

Searching and sorting data within aDataTable can be accomplished by using the overloaded Select method shown inFigure 1.

Figure 1. TheSelect method of the DataTable class contains several different overloads thatreturn an array of DataRow objects.

This method returns an array ofDataRow 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 theniterating through the returned DataRow objects. Using the different overloadsyou can narrow the search to only new data, data that has been modified, oreven 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 = newDataSet();

    da.Fill(ds,"Customers");

    Cache.Insert("CustomersDS",ds,null,

      DateTime.Now.AddMinutes(30),TimeSpan.Zero);

  }

  DisplayData();

}

private void DisplayData() {

  StringBuilder sb = newStringBuilder();

  string filterText ="ContactName LIKE 'A%'";

  //Grab DataSet fromCache

  DataSet ds =(DataSet)Cache.Get("CustomersDS");

  //Use Select() to accessonly those rows where the

  //ContactName fieldstarts with 'A'.

  //Sort returned rows byContactName in Descending order

  DataRow[] rows =

    ds.Tables[0].Select(filterText,"ContactNameDESC");

  if (rows.Length > 0){

    foreach (DataRow rowin rows) {

      sb.Append(row["ContactName"].ToString());

      sb.Append("
");

    }

    this.lblOutput.Text =sb.ToString();

  } else {

    this.lblOutput.Text ="No records found.";

  }

}

Figure 2. TheDataTable's Select method makes it easy to filter and sort data. The code shownhere queries the Northwind database's Customers table and returns all customerswhose contact name starts with the letter "A". The resulting rows are iteratedthrough and written out to a Label control. Although this example shows how aDataSet can be cached, since there is only one DataTable involved, it could becached instead to minimize unnecessary memory use.

Looking through the code in Figure2 you can see that using the DataTable class' Select method is fairlystraightforward (you can read more about the different filter expressions thatcan 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 arrayreturned from calling the Select method to ASP.NET Web server controls. Thisproblem is easy to see when you try to bind to a DropDownList control using thefollowing 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 wrappinga try...catch block around it) will generate the following error in thebrowser:

DataBinder.Eval:'System.Data.DataRow' does

not contain a property with the name ContactName.

The easiest solution to thisproblem (and one that keeps code to a minimum) is to handle the data bindingoperation yourself by iterating through the DataRow array and creating newListItem objects (there is, of course, an easier way to do this, which will becovered in the next section):

DataRow[] rows =

  ds.Tables[0].Select(filterText,"ContactName DESC");

foreach (DataRow row in rows) {

    this.ddCustomers.Items.Add(

        newListItem(row["ContactName"].ToString(),

          row["CustomerID"].ToString()));

}

Fortunately, the DataGrid providesa fairly easy (although less known) mechanism for binding a DataRow array usingthe DataSource property and DataBind method. To make this work a minor changemust be made in the DataGrid's template code (in the .aspx page) for thecolumns being bound to the grid. Normally, the following works to bind a columnto the DataGrid:

<%@DataBind.Eval(Container.DataItem,"CustomerID") %>

When binding a DataRow array to aDataGrid (using C#) you'll need to change the above code so that the DataGridknows to access the column using the DataRow object's default indexer. This changeis shown below (notice that the quoted value now contains square bracketsaround it):

<%#DataBinder.Eval(Container.DataItem,

    "["CustomerID"]")%>

An example of using the Selectmethod to create a basic contact list for viewing customer details is shown inFigure 3. You'll find the code for this page in the article's downloadable code(see DataTableSelectBind.aspx).

Figure 3. By caching a DataSet orDataTable the load on the database can be minimized. Using the DataTable'sSelect method makes it easy to filter customers based on name or any otherfield.

Search and Sort with the DataViewClass

Although using the Select methodmay be desirable in specific situations, there are many cases where using aDataView can simplify filtering and sorting data. Because a DataView can bebound to several different ASP.NET controls it can also simplify data bindingas compared to binding a DataRow array returned from calling the DataTableclass' Select method.

So what's the difference between aDataTable and a DataView? First, a DataTable acts as the source container fordata. Although you can access a filtered set of rows, you can't change theactual data within the DataTable without inserting, modifying, or deleting arow. Second, DataTables don't have a native Sort method. Sorts are performed bycalling Select.

DataViews allow multiple "views" ofa single data source (such as rows held within a DataTable) to be searched,filtered, and sorted. Instead of returning an array of rows, however, aDataView changes its view of the data to match whatever the end user ordeveloper desires. As mentioned earlier, it is also much easier to bind aDataView to different Web server controls.

DataViews are typically created byusing one of the techniques shown below:

DataView view = new DataView(dataTable);

DataView view = dataSet.Tables[0].DefaultView;

The first line of code shown abovepasses a DataTable instance into the DataView's constructor while the seconduses the DataTable's DefaultView property to create the view. A third wayexists as well, which allows a DataView object to be created that isautomatically filtered and sorted as desired. This is accomplished by passingthe filter and sort strings to the DataView's constructor along with the typeof rows that should be acted upon:

string filter = "ContactName LIKE '%A'";

string sort = "ContactName, Region DESC";

DataView view =

  newDataView(dataTable,filter,sort,

    DataViewRowState.CurrentRows);

Note that there are other ways tocreate a DataView as well, such as using the DataRowView object'sCreateChildView method along with a DataRelation. Although these techniqueswon't be covered here, the .NET SDK provides additional details.

Once a DataView is created it canbe further modified to match different program scenarios. For example, tofilter out undesirable rows the DataView's RowFilter property can be called.This property takes a string containing the filter details. Sorts can also beperformed by assigning a string containing a comma separated list of one ormore columns to sort to the Sort property.

In addition to these features, theDataView also allows rows to be found using the Find and FindRows methods. TheFind method locates a specific row based upon the column specified by the Sortproperty. It returns an integer representing the position of the row within theDataView. FindRows functions in a similar manner to Find except that it returnsa DataRowView array of the rows matching the query. Figure 4 shows an exampleof creating a DataView and automatically adding a filter and sort as well asusing 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 = newDataView(ds.Tables[0],filterText,

    "ContactNameDESC",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,

       newListItem("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 rowsusing RowFilter as well.  

  //This example uses theFind() method, however.

  DataView view = newDataView(ds.Tables[0]);

  view.Sort ="CustomerID";

  int index =view.Find(this.ddCustomers.SelectedValue);

  //Ensure we found avalid row in the DataView

  if (index > -1) {

    //Bind specificDataRowView 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. Searchinga DataView object is handled by using the Find or FindRows method. This codeshows how to use the integer value returned from calling Find to locate aDataRowView object within a DataView. It also demonstrates binding aDataRowView array to multiple Label controls.

Figure 5 shows the output generatedafter binding the row's different columns to ASP.NET label controls.

Figure 5. This graphic shows theoutput generated from calling the DataView's Find method. The returnedDataRowView is bound to several different ASP.NET Label controls.

Knowing the different ADO.NEToptions for searching, sorting, and filtering data makes creating flexibleASP.NET Web applications a snap. In this article you've seen several differenttechniques for accomplishing these tasks using both the DataTable and DataViewclasses. Although it is recommended that searches, filters, and sorts beperformed at the database whenever possible, there are cases where manipulatingdata on the Web server may be necessary.

The samplecode in this article is available for download.

Dan Wahlin(Microsoft Most Valuable Professional for ASP.NET and XML Web services) is thepresident of Wahlin Consulting and founded the XML for ASP.NET Developer's Website (http://www.XMLforASP.NET), whichfocuses on using XML and Web services in Microsoft's .NET platform. He's also acorporate trainer and speaker, and teaches XML and .NET training courses aroundthe US. Dan co-authored Professional Windows DNA (Wrox, 2000) and ASP.NET: Tips, Tutorialsand Code (Sams, 2001), and authored XML for ASP.NET Developers (Sams, 2001).