Accessing and Updating Data in ASP.NET: Filtering Database Data with Parameters
By Scott Mitchell
| A Multipart Series on ASP.NET's Data Source Controls |
|---|
ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data.
These controls allow page developers to declaratively access and modify data without writing any
code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.
Parameter controls for use in the data source controls' parameters collections.IDENTITY column value for the just-inserted record. |
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.1 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!
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...
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...
|
(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)
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
|
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
|
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,
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
<asp:Parameter> 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:
<asp:SqlDataSource ID="ID" runat="server" ConnectionString="connectionString"
|
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 <asp:QueryStringParameter> instance in its SelectParameters
collection. The <asp:QueryStringParameter> instance, as shown below, provides the parameter name (Name),
the name of the querystring value (QueryStringField) and the Type:
<asp:SqlDataSource ID="ID" runat="server" ConnectionString="connectionString"
|
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 <asp:ControlParameter> instance in its SelectParameters
collection. The <asp:ControlParameter> 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:
<asp:SqlDataSource ID="ID" runat="server" ConnectionString="connectionString"
|
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.
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!
Attachments
Further Reading
| A Multipart Series on ASP.NET's Data Source Controls |
|---|
ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data.
These controls allow page developers to declaratively access and modify data without writing any
code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.
Parameter controls for use in the data source controls' parameters collections.IDENTITY column value for the just-inserted record. |
