Accessing and Updating Data in ASP.NET: Filtering Database Data with ParametersBy Scott Mitchell
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.1 For example, a SQL query like:
SELECT ProductID, ProductName FROM Products WHERE
UnitPrice < 15.00 will return the
ProductName column values for all records
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!
1 - 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...
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
UnitPricecolumn is being filtered on, and
15.00is 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
?, 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:
(The ParameterName can be any value; here I chose to use
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
that specifies the SQL query or stored procedure to execute
CommandText property) as well as the connection object to use (via the
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
SELECTquery 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
- 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)
WHEREClause 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 |
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 |
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
to indentify the parameters. For example, if I wanted to use an ad-hoc SQL statement I'd type into the
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
vary depending on how the parameter value is obtained, we'll examine the resulting SqlDataSource markup in detail in each
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 |
If you explore the SqlDataSource's properties you'll notice the |
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
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:
Specifically, there's one
<asp:Parameter> 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,
Name attribute refers to the parameter's name. (For Microsoft Access, which uses
? for 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
<asp:Parameter> instances are applied in order to the
? parameters.) Finally, the
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:
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.aspxpage. 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=categoryIDand 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
<asp:QueryStringParameter> instance in its
<asp:QueryStringParameter> instance, as shown below, provides the parameter name (
the name of the querystring value (
QueryStringField) and the
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
products in the Beverages category).
The Complete List of Categories, Each With a View Products Link
The Products in the Beverages Category (
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
CategoryIDequals 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
<asp:ControlParameter> instance in its
<asp:ControlParameter> instance, as shown below, provides the parameter name (
ID of the control to use (
ControlID), the control property whose value will be used as the
parameter's value (
PropertyName), and the
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
<asp:ControlParameter> 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.
In this article we saw how to use parameterized queries along with the SqlDataSource's
SelectParameterscollection 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!