TECHNOLOGIES: ASP.NET | ADO.NET | SQL Server
Too Much Information!
Filter data for your DataGrid.
By Wayne S. Freeze
If you have looked at the sample app (available for download) used in the last few DataStream columns, you might have noticed that there are more than 500 rows of sample data displayed in the DataGrid. That's more than 50 pages of data, displayed 10 rows at a time. Finding a specific row of data in this volume of data isn't easy, even with the sorting and paging techniques I've covered in the past.
The real solution involves finding a way to hide irrelevant information. This technique is known as filtering. As its name implies, filtering allows you to define a filter that hides some rows while letting other rows to pass through.
To use a filter, your data must be accessed through a DataView object. The DataView object maintains a list of pointers to the actual data stored in the underlying DataTable object. By specifying a filter, you can modify this list of pointers to control which rows will be visible. It's important to note that the rows are not removed from the underlying DataTable object, which means you can change the filter criteria to show a different set of rows without going back to the database to get a fresh copy of the data.
You can specify a filter using the RowFilter property, which accepts expressions similar to a SQL Where clause. The following expression restricts the DataView's contents to those rows where the CustomerId value is less than 20:
CustomerId < 20
Although the documentation suggests you only can use
expressions such as
Name Like 'A%'
You also can use complex expressions to retrieve the rows containing a particular city and state:
State = 'GA' And City = 'Columbus'
Add Filters to Your Application
Figure 1 shows how you easily can add a filter to your application. This application is built on the last DataStream application, which showed you how to implement your own paging logic (One Page at a Time). This version adds a textbox named NameFilter, which contains the value to filter the DataView, and a Button control named Filter, which triggers the filter process.
Figure 1. The user can search for a particular customer by entering a value in the TextBox control and then clicking on the Filter button.
The Filter_Click event contains the following single line of code that calls the FilterGrid subroutine using the contents of the NameFilter text box:
The FilterGrid subroutine (see Figure 2) takes a single parameter - filter - that is the filter value. FilterGrid begins by getting a copy of the DataView being displayed on the page from the Session variable Customers. If the DataView doesn't exist, the FilterGrid subroutine leaves the page unchanged. Next, if filter contains an empty string, the RowFilter property of the DataView is set to an empty string. This disables the filter, which means all the rows will be displayed in the DataGrid.
Sub FilterGrid(filter As String)
Dim dv As DataView
dv = Session("Customers")
If dv Is Nothing Then
If filter.Length = 0 Then
dv.RowFilter = ""
dv.RowFilter = "Name Like '" & filter & "'"
DataGrid1.DataSource = dv
Figure 2. The FilterGrid subroutine takes the value you want to filter using the CustomerName column and re-binds the DataView to the DataGrid.
If filter contains a value, create a filter expression: Combine the Name column with the Like operator and the value in filter, and assign it to the DataView's RowFilter property. Assuming the user entered the value De% into the textbox, the expression assigned to RowFilter would look like this:
Name Like 'De%'
Now, the DataView is assigned to the DataGrid's DataSource property, and the DataBind method is called to bind the data to the grid. Finally, the UpdatePageLocation routine I discussed in the previous DataStream article is called to update the paging controls at the bottom of the grid.
Figure 3 shows another approach to filtering data. This approach uses the same basic technique I described already, but it uses a pre-built set of filter criteria. By providing the predefined filter criteria, users will find this application easier to use. This program uses a series of LinkButton controls, each of which contains a call to the FilterGrid subroutine similar to this:
Figure 3. Context-based filtering involves creating a set of controls the user can select to apply a predefined filter.
Although this technique is very simple, it is extremely powerful. For example, you could create a series of tabs, with each tab containing three letters of the alphabet, and call the FilterGrid routine with this string to display the information:
You even could analyze the data in the underlying DataTable for various breakdowns for your data and adjust the content of these tabs dynamically, which would make an interesting article all by itself.
The sample code used in this article is available for download.
Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including Windows Game Programming with Visual Basic and DirectX (Que) and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send him e-mail at mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.
Sift through your data to create manageable displays.