asp.net 2.0 Data Tutorial 48: Using Parameterized queries in SqlDataSource

Return to the "ASP.net 2.0 Data Tutorial directory"

Introduction

In the previous tutorial, we saw how to use the SqlDataSource control to get data directly from the database. With the Configure Data Source Wizard, we select a specific database, and then we can: Select columns from a table or view, enter a custom SQL statement, and use a stored procedure. Whether you manually enter the SQL statement or select a bunch of columns in the wizard page, you ultimately assign a SELECT statement to the SelectCommand property of the SqlDataSource control, and when the SqlDataSource Select () method is invoked, This is the statement that is executed, whether programmatically or by the data Web control, as it is called.

The SELECT statement used by the example in the previous tutorial lacks a WHERE clause. In the SELECT statement, the WHERE clause can be used to limit the records returned. For example, we want to show the name of a product that costs more than 50 U.S. knives, and the corresponding query statement should be:

1SELECT ProductName
2FROM Products
3WHERE UnitPrice > 50.00

In practice, the WHERE clause uses values that depend on an external factor, such as QueryString, session, or user input in a control on the page, and so on. Typically, we specify these inputs by using parameters. In Microsoft SQL Server, parameters are in the form of @parametername, such as:

1SELECT ProductName
2FROM Products
3WHERE UnitPrice > @Price

For SELECT, INSERT, UPDATE, and DELETE statements, SqlDataSource supports their parameterized queries. In addition, these parameters can be automatically obtained from a variety of sources (such as QueryString, session, controls on the page, and so on), and can be assigned programmatically. In this tutorial, we'll see how to define parameterized queries and how to specify them declaratively or programmatically.

Note: In the previous tutorial we compared the ObjectDataSource (which is used as our tool in the previous 46 sections) with SqlDataSource and noted that they are conceptually similar. These similarities also extend to parametric aspects. The parameters of the ObjectDataSource are mapped to the input parameters of the methods in the business logic layer, while the SqlDataSource parameters are defined directly in the SQL statement. Their select (), Insert (), Update (), and Delete () methods all have a bunch of parameters that can be obtained or programmatically assigned from predefined sources such as QueryString, session, and so on.

Create a parameterized query

The Configuration Data Source wizard for the SqlDataSource control provides three ways to define the command to obtain database records:

Select some columns from a table or view that already exists

Enter a custom SQL statement

Select a stored procedure

If you select some columns from an existing table or view, the parameters of the WHERE clause must be specified through the Add WHERE clause dialog box. If you are creating a custom SQL statement, you can add parameters directly to the WHERE clause (remember to use this format, @parameterName). Stored procedures are composed of one or more SQL statements, and these statements can be parameterized. The parameters of the SQL statement must be passed in like the input parameters of the stored procedure.

Since creating a parameterized query depends on how the SqlDataSource SelectCommand is specified, let's take a look at all of these three ways first. First, open the parameterizedqueries.aspx in the SqlDataSource folder and drag a SqlDataSource control from the Toolbox to the designer. and set its ID to Products25bucksandunderdatasource. Then, click the "Configure Data source" link in its smart tag. Select the database (using NorthwindConnectionString) and click Next.