使用 SqlDataSource 进行带参数的查询

项目
10/20/2021

本教程中，我们继续了解 SqlDataSource 控件，并学习如何定义带参数的查询。参数既可以声明式指定，也可以通过编程指定，并且可从一系列位置（如查询字符串、Session 或者其它控件）提取。

简介

在前一篇教程中，我们了解了如何使用 SqlDataSource 控件直接从数据库检索数据。使用 Configure Data Source 向导，我们可以选择数据库，并进行下列操作：从表或视图中选择要返回的列；输入定制 SQL 语句；或者使用存储过程。无论是否从表或者数据库中选择列，或者数据定制 SQL 语句，SqlDataSource 控件的 SelectCommand 属性均会被分配一个 ad-hoc SQL SELECT 语句，这个 SELECT 语句是调用（既可以程序调用，也可以自动从数据Web 控件调用）SqlDataSource 的 Select() 方法时执行的语句。

前面的教程示例中使用的 SQL SELECT 语句缺少 WHERE 子句。在 SELECT 语句中，WHERE 子句可用于限制返回的结果。例如，要显示价格高于 $50.00 的产品，我们可以使用下列查询：

SELECT ProductName FROM Products WHERE UnitPrice > 50.00

通常情况下，WHERE 子句中使用的值由外部源（例如查询字符串值，Session变量或者页面上的 Web 控件的用户输入）确定。理想情况下，这些输出使用参数指定。在 Microsoft SQL Server 中，参数使用 @parameterName 表示，如下列所示：

SELECT ProductName FROM Products WHERE UnitPrice > @Price

SqlDataSource 不仅支持 SELECT 语句，还支持INSERT 、UPDATE 和 DELETE 语句。此外，参数值可自动从多种源（查询字符串、Session值和页面上的控件等）提取，也可以通过编程分配。在本教程中，我们将了解如何定义带参数的查询，以及如何声明式或者通过编程指定参数值。

注意：在前面的教程中，我们对 ObjectDataSource（这是我们在前面 46 个教程中选择使用的控件）和 SqlDataSource 进行了比较，指出了它们在概念上的相似性。这些相似性还可以拓展到参数。ObjectDataSource 的参数映射到业务逻辑层方法的输入参数。使用 SqlDataSource ，参数直接在 SQL 查询内部定义。两个控件均有其 Select() 、Insert() 、Update() 和 Delete() 方法的参数集合，均可从预定义源（查询字符串值、会话变量等）填充参数值。

创建带参数的查询

SqlDataSource 控件的 Configure Data Source 向导提供三种方式定义检索数据库记录需要执行的命令：

从现有的表或视图中选择列。
输入定制 SQL 语句。
选择存储过程

从现有的表或视图中选择列时，WHERE 子句的参数必须通过 Add WHERE Clause 对话框指定。但是，创建定制 SQL 语句时，您可以直接将参数输入到 WHERE 子句（使用 @parameterName 表示每个参数）。存储过程包含一条或者更多条 SQL 语句，这些语句均可带参数。但是，SQL 语句中使用的参数必须作为输入参数传递给存储过程。

由于创建带参数的查询取决于如何指定SqlDataSource 的 SelectCommand ，我们首先探讨一下指定 SqlDataSource 的 SelectCommand 的三种方法。首先，从SqlDataSource 文件夹打开 ParameterizedQueries.aspx ，从工具箱中拖拽 SqlDataSource 控件，将其至于设计器中，将其 ID 设置为Products25BucksAndUnderDataSource 。然后，从控件的智能标记中单击 “Configure Data Source” 链接。选择要使用的数据库(NORTHWINDConnectionString) ，单击 Next 。

步骤1 ：从表或视图中选择列时添加一条 WHERE 子句

使用 SqlDataSource 控件从数据库中选择要返回的数据时，Configure Data Source 向导保证我们可以非常轻松的从现有的表或视图中选择要返回的列。这样可以自动构建一条 SQL SELECT 语句，该语句在调用 SqlDataSource 的 Select() 方法时发送到数据库。和我们在前面的教程中的操作一样，从下拉列表选择 Products 表，选择 ProductID 、ProductName 和 UnitPrice 列。

图1 ：从表或视图中选择要返回的列

要在 SELECT 语句中包含一条 WHERE 子句，单击 WHERE 按钮，将显示 Add WHERE Clause 对话框（见图2 ）。要添加限制 SELECT 查询返回结果的参数，请首先选择要过滤数据的列。接下来，选择过滤的运算符（ = 、 < 、 <= 、 > 等）。最后，选择参数值源（如查询字符串或会话状态）。在配置完参数之后，单击 Add 按钮，将其包含到 SELECT 查询中。

在此例中，我们仅返回 UnitPrice 值小于等于 $25.00 的那些结果。因此，从 Column 下拉列表中选择 UnitPrice ，从运算符下拉列表选择 <= 。使用固定参数值（如 $25.00 ）或者通过编程指定参数值时，请从 Source 下拉列表中选择 None 。接下来，在 Value 文本框中输入固定参数值 " 25.00 "，单击 Add 按钮完成此过程。

图2 ：限制从 Add WHERE Clause 对话框返回的结果（单击此处查看实际大小的图像）。

在添加完参数之后，单击 OK，返回 Configure Data Source 向导。现在，向导底部的 SELECT 语句应包含一条参数名为 @UnitPrice 的 WHERE 子句：

SELECT [ProductID], [ProductName], [UnitPrice] FROM [Products] WHERE ([UnitPrice] <= @UnitPrice)

注意：如果您从 Add WHERE Clause 对话框在 WHERE 子句中指定了多个条件，向导将使用 AND 运算符加入到它们之中。如果您需要在 WHERE 子句中包含 OR （例如，WHERE UnitPrice <= @UnitPrice OR Discontinued = 1 ），那么您必须通过定制 SQL 语句屏幕构建 SELECT 语句。

完成配置 SqlDataSource （单击 Next ，然后再单击 Finish ），然后检查 SqlDataSource 的声明性标记。现在，标记包含一个<SelectParameters> 集合，该集合在 SelectCommand 中明确的说明了参数源。

<asp:SqlDataSource ID="Products25BucksAndUnderDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
    SelectCommand=
        "SELECT [ProductID], [ProductName], [UnitPrice]
        FROM [Products] WHERE ([UnitPrice] <= @UnitPrice)">
    <SelectParameters>
        <asp:Parameter DefaultValue="25.00" Name="UnitPrice" Type="Decimal" />
    </SelectParameters>
</asp:SqlDataSource>

调用 SqlDataSource 的 Select() 方法时，在发送到数据库之前，UnitPrice 参数值 (25.00) 将首先在 SelectCommand 中应用到 @UnitPrice 参数。最终结果为，只有价格小于等于 $25.00 的产品从 Products 表中返回。要证实这一情况，您可以给页面添加一个 GridView ，将其绑定到此数据源，然后通过浏览器查看页面。您会发现表中仅列出了价格小于等于 $25.00 的产品，验证结果如图 3 所示。

图3 ：仅显示价格小于等于 $25.00 的产品

步骤2：给定制 SQL 语句添加参数

添加定制 SQL 语句时，您可以明确的输入 WHERE 子句，或者在 Query Builder 的 Filter 单元格中指定值。要演示这种情况，我们要在 GridView 中显示那些低于某一阈值的产品。首先，向 ParameterizedQueries.aspx 页添加 TextBox ，该 TextBox 用于从用户收集阈值。将 TextBox 的ID 属性设置为 MaxPrice 。添加 Button Web 控件，将其 Text 属性设置为 "Display Matching Products" 。

接下来，将 GridView 拖拽到页面上，从其智能标记选择创建一个新的名为ProductsFilteredByPriceDataSource 的SqlDataSource 。从 Configure Data Source 向导执行“指定定制SQL 语句或存储过程屏幕”（见图4 ），并输入下列查询：

SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice <= @MaximumPrice OR @MaximumPrice = -1.0

在输入查询（既可以手动输入，也可以通过 Query Builder 创建）之后，单击 Next 。

图4：仅返回小于等于参数值的产品

由于查询包含参数，因此向导的下一个屏幕将提示我们参数值源。从 Parameter 源下拉列表选择 "Control" ，从 ControlID 下拉列表中选择 MaxPrice（TextBox 控件的 ID 值）。您还可以输入可选默认值，此默认值在用户未向 MaxPrice 文本框输入任何文本时使用。暂时先不输入默认值。

图5 ：MaxPrice 文本框的 Text 属性用作参数源

单击Next ，然后单击 Finish ，完成Configure Data Source 向导，GridView 、TextBox 、Button 和SqlDataSource 的声明性标记如下所示：

Maximum price:
$<asp:TextBox ID="MaxPrice" runat="server" Columns="5" />
 
<asp:Button ID="DisplayProductsLessThanButton" runat="server"
    Text="Display Matching Products" />
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"
    DataSourceID="ProductsFilteredByPriceDataSource" EnableViewState="False">
    <Columns>
        <asp:BoundField DataField="ProductName" HeaderText="Product"
            SortExpression="ProductName" />
        <asp:BoundField DataField="UnitPrice" HeaderText="Price"
            HtmlEncode="False" DataFormatString="{0:c}"
            SortExpression="UnitPrice" />
    </Columns>
</asp:GridView>
<asp:SqlDataSource ID="ProductsFilteredByPriceDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
    SelectCommand=
        "SELECT ProductName, UnitPrice
        FROM Products
WHERE UnitPrice <= @MaximumPrice">
    <SelectParameters>
        <asp:ControlParameter ControlID="MaxPrice" Name="MaximumPrice"
            PropertyName="Text" />
    </SelectParameters>
</asp:SqlDataSource>

请注意，SqlDataSource 的 <SelectParameters> 部分中的参数是一个 ControlParameter ，此 ControlParameter 包含 ControlID 和 PropertyName 等其它属性。调用 SqlDataSource 的 Select() 方法时，ControlParameter 从特定的 Web 控件属性获取值，并将其分配给 SelectCommand 中相应的参数。在本例中， MaxPrice 的 Text 属性用作 @MaxPrice 参数值。

花点时间在浏览器中查看本页。首次访问页面时（或者 MaxPrice 文本框缺少值时），GridView 将不显示任何值。

图6 ：MaxPrice 文本框为空时不显示任何记录

默认情况下，不显示任何产品的原因是参数值空字符串将被转化为数据库NULL 值。由于比较 [UnitPrice] <= NULL 的返回值始终为 False ，因此不显示任何结果。

在文本框中输入一个值，如 5.00 ，并单击 “Display Matching Products ” 按钮。回传时，SqlDataSource 将通知 GridView 其参数源之一已经更改。因此，GridView 重新绑定到 SqlDataSource ，并显示价格小于等于 $5.00 的产品。

图7：显示价格小于等于 $5.00 的产品

初始显示所有产品

页面首次加载时，我们可能希望显示所有产品，而不是不显示任何产品。列出所有产品的一种方法是 MaxPrice 文本框为空时，由于 Northwind Traders 库存的单价不可能超过 $1,000,000 ，所以将参数的默认值设置为极高值，如 1000000 。但是，此方法目光短浅，可能并不适用于其它情况。

在前面的教程 “声明性参数和有DropDownList 的主/明细筛选” 中我们也遇到了相似的问题。我们的解决方法是将此逻辑放入业务逻辑层。具体说来， BLL 检查接收值，如果该值为 NULL 或者某些保留值，则调用返回所有记录的 DAL 方法。如果接收值为正常过滤值，将调用执行 SQL 语句的 DAL 方法，该 SQL 语句使用带参数的 WHERE 子句。

但不幸的是，我们在使用 SqlDataSource 时放弃了此结构。作为替代选择，如果 @MaximumPrice 参数值为 NULL 或某个保留值，我们需要定制 SQL 语句，使之智能获取所有记录。在此练习中，我们将 @MaximumPrice 参数值设为保留值，因为如果其值等于 -1.0 ，则返回所有的记录（-1.0 是保留值，因为所有产品的价值均不会出现负 UnitPrice 值）。要完成此操作，我们可以使用下列 SQL 语句：

SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice <= @MaximumPrice OR @MaximumPrice = -1.0

如果 @MaximumPrice 参数值等于 -1.0 ，WHERE 子句将返回所有记录。如果参数值不为 -1.0 ，则只返回那些 UnitPrice 值小于等于 @MaximumPrice 参数值的产品。参数的默认值设为 -1.0 ，首次加载页面时（或者 MaxPrice 文本框为空时），@MaximumPric 的值为 -1.0 ，并且显示所有的产品。

图8 ：MaxPrice 文本框为空时显示所有产品

使用此方法有多个注意事项。首先，请注意参数的数据类型可由其在 SQL 查询中的应用进行推断。如果您将 WHERE 语句从“@MaximumPrice = -1.0”更改为“ @MaximumPrice = -1 ”，运行时将把此参数按照整数处理。如果您尝试为 MaxPrice 文本框分配小数值（如 5.00 ），因为它无法将 5.00 转换为整数，因此会出现错误。要修正这种情况，请确定您在WHERE 子句中使用了 “@MaximumPrice = -1.0 ”，或者采取更好的方法，将 ControlParameter 对象的 Type 属性设置为 “Decimal”

其次，通过给 WHERE 子句添加 “OR @MaximumPrice = -1.0 ”，查询引擎无法在 UnitPrice（假设存在）上使用索引，因此会导致表扫描。如果在 Products 表中没有足够数量的记录，此操作将会影响性能。一个更好的方法是将此逻辑转到存储过程，在此存储过程中，我们可以通过使用IF 语句，在需要返回所有记录时，从不包含WHERE 子句的 Products 表完成 SELECT 查询，也可以从仅包含一条 UnitPrice 查询条件的SELECT 语句完成查询，从而保证使用索引。

步骤3 ：创建和使用带参数的存储过程

存储过程可包含一系列输入参数，这些参数可在存储过程中定义的 SQL 语句中使用。在配置 SqlDataSource 使用接收输入参数的存储过程时，这些参数值可使用与 ad-hoc SQL 语句相同的技术指定。

为了演示在 SqlDataSource 中使用存储过程，我们将在Northwind 数据库中，创建一个名为 GetProductsByCategory 的新的存储过程，此存储过程接受一个名为 @CategoryID 的参数，并返回所有 CategoryID 列与 @CategoryID 匹配的产品。要创建存储过程，请转到 Server Explorer ，查看 NORTHWND.MDF 数据库。（如果您没有看到 Server Explorer ，您可以转到 View 菜单，选择 Server Explorer 选项，则会显示 Server Explorer 。

在 NORTHWND.MDF 数据库中，右键单击 Stored Procedures 文件夹，选择 “Add New Stored Procedure （添加新的存储过程”，并输入下列语法：

CREATE PROCEDURE dbo.GetProductsByCategory ( @CategoryID int ) AS SELECT * FROM Products WHERE CategoryID = @CategoryID

单击 Save 图标（或者 Ctrl+S ），保存存储过程。您可以从 Stored Procedures 文件夹右键单击存储过程并选择 Execute 完成对存储过程的测试。它将提示您输入存储过程的参数（在此实例中为 @CategoryID ），输入完成后，Output 窗口将显示结果。

图9：@CategoryID 为1 时执行GetProductsByCategory 存储过程

下面，我们在 GridView 中使用此存储过程显示 Beverages 类别中所有产品。在页面上添加一个新的 GridView ，并将其绑定到名为 BeverageProductsDataSource 的新建 SqlDataSource 。接下来，从 “Specify a custom SQL statement or stored procedure” 屏幕中选择 “Stored procedure ” 按钮，从下拉列表中选择 GetProductsByCategory 存储过程。

图10 ：从下拉列表中选择 GetProductsByCategory 存储过程

由于存储过程接收输入参数 (@ CategoryID) ，因此单击Next 将提示我们指定此参数值源。Beverages CategoryID 为1 ，因此，保留 Parameter source 下拉列表值为 “None ”，并将1 输入到 DefaultValue 文本框中。

图11 ：使用固定值1 来返回 Beverages 类别中的产品

如下面的声明式标记所示，使用存储过程时，SqlDataSource 的 SelectCommand 属性设置为存储过程的名称，SelectCommandType 属性被设置为 StoredProcedure ，表示 SelectCommand 是存储过程的名称，而不是 ad-hoc SQL 语句。

<asp:SqlDataSource ID="BeverageProductsDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
    SelectCommand="GetProductsByCategory" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:Parameter DefaultValue="1" Name="CategoryID" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

在浏览器中对页面进行测试。页面上显示属于 Beverages （饮料）类别的产品。由于GetProductsByCategory 存储过程从 Products 表返回所有列，所以页面上显示了所有的产品字段，当然，我们可以从 GridView 的 Edit Columns 对话框限制或定制 GridView 中显示字段。

图12 ：页面上显示了所有的饮料类产品

步骤4 ：通过编程调用 SqlDataSource 的Select() 语句

到目前为止，我们在前面的教程和本教程中所看到的所有范例均直接将SqlDataSource 控件绑定到 GridView 。但是，SqlDataSource 控件的数据可通过编程进行访问，并且以代码形式进行枚举。当您只需要查询和检索数据，但是并不需要这些数据时，这种情况相当有用。无需编写那些死板固定的 ADO.NET 代码来连接数据库，只要指定命令，并检索结果，让 SqlDataSource 来处理这些单调的代码。

要演示通过编程处理 SqlDataSource 的数据，让我们假设你的老板向你提出创建显示随机选择类别及其相关产品的 web 页。也就是说，用户访问此页面时，我们希望从 Categories 表中随机选择一个类别，显示类别名称，然后列出此类别的产品。

要完成这个任务，我们需要两个 SqlDataSource 控件 —— 一个用来从 Categories 表中随机获取类别，另外一个则获取此类别的产品。我们将在本步骤中创建检索随机类别记录的 SqlDataSource ；步骤 5 则探讨如何制作检索类别产品的 SqlDataSource 。

首先，在 ParameterizedQueries.aspx 上添加一个 SqlDataSource ，并将其 ID 设置为 RandomCategoryDataSource 。对其进行配置以便使用下列 SQL 查询

SELECT TOP 1 CategoryID, CategoryName FROM Categories ORDER BY NEWID()

“ORDER BY NEWID() ” 以随机顺序返回记录（见使用 NEWID()随机排序记录）。“SELECT TOP 1 ” 从结果集中返回第一个记录。总之，此查询从单个随机选择的类别返回 CategoryID 和 CategoryName 列值。

要显示类别的 CategoryName 值，请在页面上添加一个 Label Web 控件，并将其ID 属性设置为 CategoryNameLabel ，清除其 Text 属性。要采用通过编程从 SqlDataSource 控件检索数据，我们需要调用其 Select() 方法。Select() 方法需要一个类型为 DataSourceSelectArguments 的参数。它指定数据在返回之前的传递方式。这包括排序和过滤数据指令，数据 Web 控件在 SqlDataSource 控件中排序或者翻页数据时使用。在本例中，我们不需要数据在返回之前进行修改，因此，DataSourceSelectArguments.Empty 对象会被传入。

Select() 方法返回一个对象来实现 IEnumerable 。返回的类型取决于SqlDataSource 控件的 DataSourceMode 属性值。和我们在前面的教程中讨论的一样，此属性既可以设置为 DataSet 值，也可以设置为 DataReader 值。如果设置为 DataSet ，Select() 方法将返回一个 DataView 对象；如果设置为 DataReader ，它将返回实现 IDataReader 的对象。由于 RandomCategoryDataSource SqlDataSource 有其自己的设置为 DataSet （默认情况下）的 DataSourceMode 属性，我们将使用 DataView 对象。

下列代码介绍了如何RandomCategoryDataSource SqlDataSource 检索记录生成DataView ，以及如何从第一个 DataView 行读取CategoryName 值：

protected void Page_Load(object sender, EventArgs e)
{
    // Get the data from the SqlDataSource as a DataView
    DataView randomCategoryView =
        (DataView)RandomCategoryDataSource.Select(DataSourceSelectArguments.Empty);
    if (randomCategoryView.Count > 0)
    {
        // Assign the CategoryName value to the Label
        CategoryNameLabel.Text =
            string.Format("Here are Products in the {0} Category...",
                randomCategoryView[0]["CategoryName"].ToString());
    }
}

randomCategoryView(0) 在 DataView 中返回第一个 DataRowView 。randomCategoryView(0)(" CategoryName ") 返回第一行中 CategoryName 列的值。请注意，DataView 是弱类型的。要引用特定列的值，我们需要以字符串传入列的名称（在本例中为 CategoryName）。图 13 显示了查看页面时在 CategoryNameLabel 中显示的信息。当然，页面上显示的实际类别名称是每次访问页面时（包括回传）由 RandomCategoryDataSource SqlDataSource 随机选择的。

图13 ：页面上显示随机选择的类别名称

注意：如果 SqlDataSource 控件的 DataSourceMode 属性已经设置为 DataReader ，需要将 Select() 方法的返回值转换为 IdataReader 要从第一行读取 CategoryName 列值，代码如下：

if (randomCategoryReader.Read())
{
   string categoryName = randomCategoryReader["CategoryName"].ToString();
   ...
}

通过 SqlDataSource 随机选择类别，我们可以实现添加列出类别产品的 GridView 。

注意：我们可以向页面上添加 FormView 或 DetailsView ，将其绑定到 SqlDataSource ，而不是使用 Label Web 控件来显示类别名称。但是，我们可以使用 Label 来探讨如何通过编程调用 SqlDataSource 的 Select() 语句，并以代码处理其结果数据。

步骤5 ：通过编程分配参数值

目前为止，我们看到的所有示例要么使用固定参数值，要么从预定义的参数源（查询字符串值、页面上的 Web 控件等）提取参数值。但是，SqlDataSource 控件的参数也可通过编程设置。要想完成当前示例，我们还需要一个SqlDataSource ，用来返回指定类别的所有产品。这个 SqlDataSource 具有一个CategoryID 参数，其值需要根据 Page_Load event handler 中返回的 RandomCategoryDataSource SqlDataSource 的 CategoryID 列的值进行设定。

首先，向页面添加一个 GridView ，并将其绑定到一个新的名为 ProductsByCategoryDataSource 的 SqlDataSource 。和我们在步骤 3 中的操作非常相似，配置 SqlDataSource ，使其调用 GetProductsByCategory 存储过程。保持参数源下拉列表的“ None ”设置不变，由于我们将通过编程设置默认值，因此请不要输入默认值。

图14 ：不要指定参数源或默认值

在完成 SqlDataSource 向导之后，声明式标记应如下面所示：

<asp:SqlDataSource ID="ProductsByCategoryDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
    SelectCommand="GetProductsByCategory" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:Parameter Name="CategoryID" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

我们可以在 Page_Load event handler 中通过编程分配CategoryID 参数的DefaultValue 。

// Assign the ProductsByCategoryDataSource's
// CategoryID parameter's DefaultValue property
ProductsByCategoryDataSource.SelectParameters["CategoryID"].DefaultValue =
    randomCategoryView[0]["CategoryID"].ToString();

除此之外，页面上包含了一个显示随机选择类别相关产品的GridView 。

图15 ：不要指定参数源或默认值

小结

SqlDataSource 使页面开发人员可以定义带参数的查询，其参数值可以为固定、从预定义参数源读取或者通过编程分配。在本教程中，我们了解了如何从 Configure Data Source 向导中为 ad-hoc SQL 查询和存储过程制作带参数的查询。我们还了解了如何使用固定参数源和 Web 控件作为参数源，以及如何通过编程指定参数值。

类似于ObjectDataSource ，SqlDataSource 还提供了修改基本数据的能力。在下一篇教程中，我们将了解如何使用 SqlDataSource 定义 INSERT 、UPDATE 和 DELETE 语句。一旦添加了这些语句，我们就可以使用GridView 、DetailsView 和FormView 内置的插入、编辑和删除功能了。

快乐编程！

下一篇教程