Accessing and Updating Data in ASP.NET: Filtering Database Data with Parameters

Introduction
In Part 2 of this article series we looked at how to access relational database data using the AccessDataSource and SqlDataSource controls. However, in Part 2 we only looked at how to retrieve all of the records from a table, view, stored procedure, or ad-hoc SQL query. Often, we only want to retrieve a subset of data based on various criteria. When searching for a book on Amazon.com, for example, the search page retrieves only the data from the database that matches the search terms you entered; when viewing the details for a particular book, only the particular book's information is queried from the database.

Results are filtered in a SQL statement using the WHERE clause, which contains a boolean expression. For each record in the table being queried, the WHERE expression is evaluated to determine whether or not to include that record in the resultset.¹ For example, a SQL query like: SELECT ProductID, ProductName FROM Products WHERE UnitPrice < 15.00 will return the ProductID and ProductName column values for all records in the Products table for which the statement UnitPrice < 15.00 evaluates to True. In English, it returns all products that cost strictly less than fifteen bucks.

The SqlDataSource and AccessDataSource controls can define the value by which the query is filtered by using parameters, and these parameters can retrieve their values from a variety of sources, including: the querystring, session variables, other Web controls on the page, and so on. Like the connection string and SELECT query, the filter parameters values for a data source control can be set declaratively, meaning you do not need to write a line of code. These parameter values can also be set programmatically, though, for cases where you might need to base the filter value on programmatic logic (such as the currently logged-in user's username).

In this article we will examine how to declaratively filter the results of a SQL query using parameters in a SqlDataSource. We'll see examples of how to use hard-coded filter values, and well as examples with filter values taken from the querystring and other Web controls on the page. Read on to learn more!

¹ - this is a gross oversimplification of how the database engine determines what rows, exactly, to return, but is an accurate description from a high-level perspective...

Filtering Basics

When filtering results there are two components of interest: the columns being filtered on, and the value being filtered by. In the example in the Introduction, the UnitPrice column is being filtered on, and 15.00 is the value being filtered by. Typically the value to filter by is not a hard-coded value, but depends on some sort of user input. To allow for a variable filter by value, SQL statements typically use parameters that indicate placeholders where a value can later be inserted. Parameters typically have the format @ParameterName or ?, depending on the underlying database being used. For example, when using parameterized queries with Microsoft Access databases, use ?; when using parameterized queries for Microsoft SQL Server, use @ParameterName. Rewriting the earlier SQL query to use parameters would result in:

-- For Microsoft SQL Server... SELECT ProductID, ProductName FROM Products WHERE UnitPrice < @PriceFilter -- For Microsoft Access... SELECT ProductID, ProductName FROM Products WHERE UnitPrice < ?

(The ParameterName can be any value; here I chose to use PriceFilter.)

With the parameter defined, its value can be set programmatically prior to executing the statement. If you're familiar with ADO.NET code, you know that when writing code to issue a query to a database you create a command object (such as SqlCommand) that specifies the SQL query or stored procedure to execute (via the CommandText property) as well as the connection object to use (via the Connection property). The command object also has a Parameters collection that allows you to programmatically specify the values for the parameters defined in the query (or used in the stored procedure). For more information on using parameterized queries with SQL Server see this article; for examples for parameterized queries with Microsoft Access, refer to this one.

With the data source controls you don't need to programmatically set the parameter (unless you want/need to), as the parameter value can be set declaratively, as we'll see shortly. When you set the parameter value declaratively, however, do understand that the data source control, behind the curtains, is programmatically adding the declaratively specified value to its underlying command object's Parameters collection. In short, the data source control is executing the same ADO.NET code you likely would if you were accessing the data programmatically.

Let's now turn our attention to filtering data with the SqlDataSource. In the following demos (which are downloadable at the end of this article), we'll be using a slimmed-down version of the Northwind Access database (as discussed in Part 2). We'll see how to specify the filter parameters both through the SqlDataSource's wizard and directly through its properties.

Getting Started with Filtering with the SqlDataSource

To start, create an ASP.NET page and go to the Design view. Next, drag on a SqlDataSource control from the Toolbox. As we saw in Part 2, you can configure the database to connect to and the SQL SELECT query to execute by clicking on the "Configure Data Source" link in the SqlDataSource control's smart tag. Recall that there are two screens for specifying the SELECT query:

• Specify columns from a table or view - here you chose the table or view from a drop-down list and check those columns you want to return, or
• Specify a custom SQL statement or stored procedure - with this option you can either pick a stored procedure from a drop-down list of manually type in your SQL query (or use the Query Builder)

If you use the "Specify columns from a table or view" option, after selecting the table or view to query and choosing the columns to return, click the WHERE button to add a filter parameter. This brings up the Add WHERE Clause dialog box (shown below), where you can specify the column to filter on, the operator to use (=, <, <=, >, and so on), and the source of the filter value (a hard-coded value, from the querystring, from another Web control on the page, and so on). We'll see how to specify different types of parameter values in a bit.

Limitations with the Add WHERE Clause Dialog Box

Filtering using the "Specify columns from a table or view" option has one potentially show-stopping drawback - while you can add multiple filter parameters, these parameters are joined using the AND operator. So if you need to filter values based on multiple clauses and need them joined together with ORs as well (such as SELECT * FROM Products WHERE UnitPrice < 15.00 OR UnitPrice > 25.00), you need to manually add the SELECT query and WHERE clause through the "Specify a custom SQL statement or stored procedure" option.

If you use the "Specify a custom SQL statement or stored procedure" option you can either type in a SQL query by hand or pick a stored procedure from a drop-down list. To add filter parameters in the ad-hoc SQL query, simply add the parameters using the appropriate syntax. Since the Northwind database used in my demos is a Microsoft Access database I would use the ? character to indentify the parameters. For example, if I wanted to use an ad-hoc SQL statement I'd type into the SELECT textbox: SELECT ProductID, ProductName FROM Products WHERE UnitPrice < ?. If, however, you are using a SQL Server database, use the @ParameterName syntax instead. After specifying your parameters using the correct syntax, hit Next and you will see a screen that prompts you for the parameter values.

Step 1: Specify the Parameterized Ad-Hoc SQL Statement

Step 2: Click Next and Specify the Values for the Parameter(s)

If you are using a database that supports parameterized stored procedures (such as Microsoft SQL Server), you can select the stored procedure to use from the drop-down list and click next. In the next screen (the one shown above) you will see the parameters for the stored procedure and be able to define how their values are obtained.

Regardless of what approach you use, once you specify the parameter values and complete the SqlDataSource wizard, the SqlDataSource will have updated its properties. Specifically, the SelectCommand will now be a parameterized query, and there will be a series of SelectParameter instances. Since the types of SelectParameter instances vary depending on how the parameter value is obtained, we'll examine the resulting SqlDataSource markup in detail in each example.

If you are wizard-averse, you can always specify the parameterized query and SelectParameters by hand (through entering the markup directly), or by clicking on the SelectQuery property in the SqlDataSource's Properties pane in the Design view. Doing so will display the Command and Parameter Editor (see screen shot below).

Why Aren't We Using FilterExpression and FilterParameters?

If you explore the SqlDataSource's properties you'll notice the FilterExpression and FilterParameters properties. You may be wondering why we aren't using these two properties and how the FilterParameters collection differs from the SelectParameters collection. The FilterExpression and FilterParameters are properties designed for filtering the results returned by the database. That is, with these two properties, after the records have been returned from the backend database, these results are further filtered by the FilterExpression and FilterParameters before being handed over to the data Web control or programmer whose code is requesting the data source's data. Using a parameterized query and SelectParameters, on the other hand, performs the filtering on the database side. As you probably can guess, filtering on the database side is much more efficient than bringing back all data to the data source control and then having it filter the results. However, there are times when would want to use the FilterExpression and FilterParameters properties - in fact, both techniques can be used in tandem. For our examples now, stick with the parameterized queries and SelectParameters and just remember that the SelectParameters specify the parameter values that are sent in the database's SELECT query whereas the FilterParameters are those parameters used to filter the data returned from the database (and would only be set if there was a FilterExpression value defined).

Filtering Based on a Hard-Coded Value

In both the "Specify columns from a table or view" and "Specify a custom SQL statement or stored procedure" options, when specifying the parameter value you are asked to choose the "Parameter source" with a drop-down list containing the following choices:

• None
• Cookie
• Control
• Form
• Profile
• QueryString
• Session

To specify a hard-coded value, select the None option and then, in the value textbox, enter the hard-coded value. Once you have specified the parameter value and complete the wizard, the SqlDataSource control's declarative markup will look like:

ID" runat="server" ConnectionString="connectionString" ProviderName="providerName" SelectCommand="parameterizedQuery"> hardCodedValue" Name="parameterName" Type="dataType" />

Specifically, there's one instance for each hard-coded parameter value you specified in the wizard. The parameter's DefaultValue attribute holds the value you entered into the value textbox, while the Name attribute refers to the parameter's name. (For Microsoft Access, which uses ? for the parameters, the Name attribute refers to the database column that the parameter applies to, although this value can be changed since the parameter name is really ?. The only important thing to note here is that the instances are applied in order to the ? parameters.) Finally, the Type parameter specifies the data type of the parameter value.

In the download available at the end of this article you'll find a demo called "Filter on Hard-Coded Value Demo." In this demo, the SqlDataSource is configured to retrieve all products, filtering them such that only those with a UnitPrice < 15.00 are returned. The resulting SqlDataSource markup, and a screen shot of the results when bound to a GridView, are shown below:

ID" runat="server" ConnectionString="connectionString" ProviderName="providerName" SelectCommand="SELECT [ProductID], [ProductName], [UnitPrice] FROM [Products] WHERE ([UnitPrice] = ?)"> 15.00" Name="UnitPrice" Type="Decimal" />

Filtering Based on a QueryString Value

In the Northwind database each product belongs to a category. Therefore, we might want a page that lists all of the categories in the database with each category having a hyperlink to a page that displays the products in the selected category. One way to accomplish this is to create two pages: Categories.aspx, which lists all of the categories, and ProductsInCategory.aspx, which lists all products for a category. The challenge here is how to specify what category's products to display in the ProductsInCategory.aspx page. While there are many techniques for passing information between pages in a website, a common one is to use the querystring. With this approach we'd visit the products page using the URL ProductsInCategory.aspx?CategoryID=categoryID and would then display the products in the categoryID category. (See Passing Parameters from One Page to Another for a class to help streamline the code needed to pass parameters from one page to another via the querystring.)

To use a querystring value to filter results, simply choose the QueryString option from the "Parameter source" drop-down list. Then, enter the name of the querystring value you want to use in the querystring field textbox. After doing so, the SqlDataSource's declarative markup will include an instance in its SelectParameters collection. The instance, as shown below, provides the parameter name (Name), the name of the querystring value (QueryStringField) and the Type:

ID" runat="server" ConnectionString="connectionString" ProviderName="providerName" SelectCommand="SELECT [ProductID], [ProductName], [UnitPrice] FROM [Products] WHERE ([CategoryID] = ?)"> Int32" Name="CategoryID" QueryStringField="CategoryID" />

The following screen shots show the Categories.aspx page, which lists all categories along with a View Products link, and the ProductsInCategory.aspx page, when visited as ProductsInCategory.aspx?CategoryID=1 (viewing products in the Beverages category).

The Complete List of Categories, Each With a View Products Link

The Products in the Beverages Category (ProductsInCategory.aspx?CategoryID=1)

Filtering Based on a Web Control Value

The parameter values for a SqlDataSource can also be set based on a value from a Web control on the page. Returning to the Categories/Products master-detail example from before, rather than having two pages we might want to have one page that uses a DropDownList control to list all categories and shows the Products for the selected category in a GridView. To accomplish this, start by creating a page that displays a DropDownList that contains the categories. Next, add a SqlDataSource to return the products whose CategoryID equals the selected category in the DropDownList. To accomplish this, choose Control as the "Parameter source." This will display a drop-down list of controls on the page, allowing your to choose which control's value should be used. Pick the DropDownList you added moments ago.

After performing these steps, the SqlDataSource's declarative markup will include an instance in its SelectParameters collection. The instance, as shown below, provides the parameter name (Name), the ID of the control to use (ControlID), the control property whose value will be used as the parameter's value (PropertyName), and the Type:

ID" runat="server" ConnectionString="connectionString" ProviderName="providerName" SelectCommand="SELECT [ProductID], [ProductName], [UnitPrice] FROM [Products] WHERE ([CategoryID] = ?)"> Int32" Name="CategoryID" ControlID="categoriesDDL" PropertyName="SelectedValue" />

The following screen shot shows a page that lists the categories in a DropDownList and the selected categories corresponding products in a GridView. This page uses two SqlDataSource controls - one to retrieve all catgories (which is bound to the DropDownList) and one to return the corresponding products (which uses the and is bound to the GridView). I set the DropDownList's AutoPostBack property to True so that whenever the DropDownList changes a postback ensues and the GridView is updated to show the applicable products.

Conclusion

In this article we saw how to use parameterized queries along with the SqlDataSource's SelectParameters collection to filter the results returned from a database query. The SqlDataSource allows the values used in the parameters to come from a bevy of sources, including hard-coded values, querystring values, Web control property values, session variables, and so on. These parameter values can be specified declaratively, requiring no code on our part. These parameter values can also be set programmatically, a topic we'll address in a future article in this series!

Happy Programming!