Introduction
This article describes the use of filter expressions in junction with an SQL Data Source. Filter expressions may be applied to the data returned from the use of an SQL Data Source to limit what is displayed; the filter expression itself is quite similar in nature to a ‘where' clause but there isn't any need to generate a new query or stored procedure as the filter expression is applied to the data collected using the SQL Data Source. For example, if one had an SQL Data Source configured to work with a stored procedure that pulled back all of the accounts for one zip code, the developer could use filter expressions to limit the account data shown by a range to dates, or a last name. This is not a replacement for parameterized queries, just another tool you can add to your arsenal.
Figure 1: Filtering Data in Grid View Control
Getting Started:
In order to get started, unzip the included project and open the solution in the Visual Studio 2008 IDE. In the solution explorer, you should note these files (Figure 2):
Figure 2: Solution Explorer
The solution contains a single web application project called "FilteredSourceVB"; the site contains a master page and a default web page.
Code: Master Page (FsSite.Master)
The master page contains a single table (2x2) with the top two cells merged to form an area for a banner, and a side bar on the left. The side bar contains a few hyperlink controls used to navigate to other pages. The lower right hand cell contains a single content panel which is used to display the default web page.
The code behind does not manage any data or perform any particular function and so there is no code to speak of; the html defines the layout of the master page and handles a few hyperlinks in the side bar:
<%@ Master Language="VB" AutoEventWireup="false" CodeBehind="FsSite.master.vb" Inherits="FilteredSourceVB.FsSite" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>SQL Data Source Filters</title>
<style type="text/css">
.style1
{
width: 100%;
}
</style>
</head>
<body>
<form id="form2" runat="server"
style="font-family: Arial, Helvetica, sans-serif; font-size: small">
<table class="style1">
<tr>
<td bgcolor="#000066" colspan="2"
height="50px">
<asp:Label ID="Label1" runat="server" Font-Bold="True" Font-
Names="Arial"
ForeColor="White" Text="SQL Data Source
Filters"></asp:Label>
</td>
</tr>
<tr>
<td align="left" bgcolor="#6699FF" height="400px" valign="top"
width="120px">
<br />
<asp:HyperLink ID="HyperLink1" runat="server"
ForeColor="White"
NavigateUrl="http://www.stop1080poison.com/">Stop 1080
Poison</asp:HyperLink>
<br />
<br />
<asp:HyperLink ID="HyperLink2" runat="server"
ForeColor="White"
NavigateUrl="http://www.thegrafboys.org/">A Shadow of
Doubt</asp:HyperLink>
<br />
<br />
<asp:HyperLink ID="HyperLink3" runat="server"
ForeColor="White"
NavigateUrl="http://kaka1080.co.nz/">Kaka
1080</asp:HyperLink>
</td>
<td align="left" valign="top">
<asp:ContentPlaceHolder ID="ContentPlaceHolder1"
runat="server">
</asp:ContentPlaceHolder>
</td>
</tr>
</table>
</form>
</body>
</html>
Code: Default.aspx
This default web page is used to display a GridView control and a few controls used to filter the data displayed in that GridView. The page also contains two SQLDataSource controls; one of which is used to populate the grid and the other to populate a drop down list of cities. Both data source controls bind to the example Northwind database in SQL Server 2005 for this example. One data source control is used to populate a drop down list with a distinct list of cities while the other is used to bind to the grid view which serves as the primary means for displaying the data; it is the second data source that is subject to filtering.
The class begins with the class declaration.
Public Partial Class WebForm1
Inherits System.Web.UI.Page
The next section of code is used to handle the page load event. To retain filtering between post backs, I opted to store the filter expression into the session and to reload to reset the filter expression if one should exist.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
If (Session("FiltExp") <> Nothing) Then
SqlDataSource1.FilterExpression = Session("FiltExp").ToString()
End If
End Sub
The next bit of code is a drop down list selected index changed even handler. This drop down list is populated using a secondary SQLDataSource which has been configured to do a select distinct query against the City field of the employee table of the Northwind example database. When the user selects a city name from the drop down list, the handler sets the SQLDataSource filter property to filter the SqlDataSource1 data source to only show those employees that reside in the selected city. After setting the expression, the filter expression is saved to the session to support reapplication of the filter during post backs.
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object,
ByVal e As EventArgs) Handles DropDownList1.SelectedIndexChanged
SqlDataSource1.FilterExpression = "city='" & _
DropDownList1.SelectedValue & "'"
Session("FiltExp") = "city='" & DropDownList1.SelectedValue & "'"
End Sub
The next bit of code it used to handle the Show By Date button click event; this method just shows a separate example of applying a filter to the same data source in order to modify the visible results of an identical set of data through the application of a filter expression. In the demonstration web application, the user may type in a date and click this button to filter the results to show only employees born after the user supplied date.
Protected Sub btnShowByDate_Click(ByVal sender As Object, ByVal e As
EventArgs) Handles btnShowByDate.Click
If (Not String.IsNullOrEmpty(txtDay.Text) And _
Not String.IsNullOrEmpty(txtMonth.Text) And _
Not String.IsNullOrEmpty(txtYear.Text)) Then
Dim dt As New DateTime(Convert.ToInt32(txtYear.Text), _
Convert.ToInt32(txtMonth.Text), _
Convert.ToInt32(txtDay.Text))
SqlDataSource1.FilterExpression = "BirthDate > #" + dt + "#"
Session("FiltExp") = "BirthDate > #" + dt + "#"
End If
End Sub
The last example use of filters in this demonstration merely shows that they can be removed entirely; the show all button click event hander merely sets the filter expression to null; doing show will result in the redisplay of entire result set without any filtering.
Protected Sub btnShowAll_Click(ByVal sender As Object, ByVal e As
EventArgs) Handles btnShowAll.Click
SqlDataSource1.FilterExpression = Nothing
Session("FiltExp") = Nothing
End Sub
End Class
Summary
The article is pretty short and simple. The intent was only to show how an SQLDataSource may be filtered on the fly using the Filter Expression property. This is not the only way to do this, one could for example use parameterized queries instead.