Using Filter Expressions with an SQL Data Source in ASP.NET C#

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 "FilteredSource"; 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. The class begins with the default imports (all of the imports are set by default): 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace FilteredSource
{
    public partial class FsSite : System.Web.UI.MasterPage
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
    }
}

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 default imports.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

The next section contains the class declaration and default constructor and the namespace.

namespace FilteredSource
{
   public partial class Default : 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 void Page_Load(object sender, EventArgs e)
{
    if(Session["FiltExp"] != null)
        SqlDataSource1.FilterExpression = Session["FiltExp"].ToString();
}

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 void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
    SqlDataSource1.FilterExpression = "city='" + DropDownList1.SelectedValue + "'";
    Session["FiltExp"] = "city='" + DropDownList1.SelectedValue + "'";
}

The next bit of code it used to handle the Show By Data 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 void btnShowByDate_Click(object sender, EventArgs e)
{
    if (!String.IsNullOrEmpty(txtDay.Text) && !String.IsNullOrEmpty(txtMonth.Text) && !String.IsNullOrEmpty(txtYear.Text))
    {
        DateTime dt = new DateTime(Convert.ToInt32(txtYear.Text), Convert.ToInt32(txtMonth.Text), Convert.ToInt32(txtDay.Text));
        SqlDataSource1.FilterExpression = "BirthDate > #" + dt + "#";
        Session["FiltExp"] = "BirthDate > #" + dt + "#";
    }
}

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 void btnShowAll_Click(object sender, EventArgs e)
{
    SqlDataSource1.FilterExpression = null;
    Session["FiltExp"] = null;
}

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.