排序自定义分页数据 (VB)

作者 ：斯科特·米切尔

下载 PDF

在前面的教程中，我们学习了如何在网页上呈现数据时实现自定义分页。 本教程介绍如何扩展前面的示例，以包括对自定义分页进行排序的支持。

介绍

与默认分页相比，自定义分页可以将分页性能提高几个数量级，因此在处理大量数据时，自定义分页成为事实上的分页实现选择。 但是，实现自定义分页比实现默认分页更为复杂，尤其是在向混合添加排序时。 在本教程中，我们将扩展上述示例，包括对排序 和 自定义分页的支持。

注释

由于本教程基于前面的教程，因此在开始之前，请花点时间从前面的教程网页（<asp:Content>）中复制元素中的EfficientPaging.aspx声明性语法，并将其粘贴到页面中的<asp:Content>SortParameter.aspx元素之间。 有关将一个 ASP.NET 页面的功能复制到另一个页面的更详细讨论，请参阅 “将验证控件添加到编辑和插入接口 ”教程的步骤 1。

步骤 1：重新审视自定义分页技术

若要使自定义分页正常工作，我们必须实现一些技术，这些技术可以有效地获取给定起始行索引和最大行参数的特定记录子集。 有一些技术可用于实现这一目标。 在前面的教程中，我们介绍了如何使用 Microsoft SQL Server 2005 的新 ROW_NUMBER() 排名函数来完成此作。 简言之， ROW_NUMBER() 排名函数将行号分配给按指定排序顺序排名的查询返回的每一行。 然后，通过返回编号结果的特定部分来获取相应的记录子集。 以下查询演示如何使用此技术返回按字母顺序 ProductName排序的结果时编号为 11 到 20 的产品：

SELECT ProductID, ProductName, ...
FROM
   (SELECT ProductID, ProductName, ..., ROW_NUMBER() OVER
    (ORDER BY ProductName) AS RowRank
    FROM Products) AS ProductsWithRowNumbers
WHERE RowRank > 10 AND RowRank <= 20

此方法适用于使用特定排序顺序（ProductName 在本例中按字母顺序排序）进行分页，但需要修改查询以显示按其他排序表达式排序的结果。 理想情况下，可以重写上述查询以在 OVER 子句中使用参数，如下所示：

SELECT ProductID, ProductName, ...
FROM
   (SELECT ProductID, ProductName, ..., ROW_NUMBER() OVER
    (ORDER BY @sortExpression) AS RowRank
    FROM Products) AS ProductsWithRowNumbers
WHERE RowRank > 10 AND RowRank <= 20

很遗憾，不允许参数化ORDER BY 子句。 相反，我们必须创建一个接受 @sortExpression 输入参数的存储过程，但使用以下解决方法之一：

• 为可能使用的每个排序表达式编写硬编码查询;然后，使用 IF/ELSE T-SQL 语句确定要执行的查询。
• 使用CASE语句来提供基于ORDER BY n 输入参数的动态@sortExpressio表达式；有关详细信息，请参阅T-SQL CASE语句中的“用于动态排序查询结果”章节。
• 在存储过程中将适当的查询创建为字符串，然后使用sp_executesql系统存储过程执行动态查询。

其中每个解决方法都有一些缺点。 第一个选项不像其他两个选项一样可维护，因为它要求你为每个可能的排序表达式创建查询。 因此，如果以后决定向 GridView 添加新的可排序字段，则还需要返回并更新存储过程。 第二种方法有一些微妙之处，在按非字符串数据库列排序时引入性能问题，并且也遭受与第一个相同的可维护性问题。 使用动态 SQL 的第三个选项会引入 SQL 注入攻击的风险，如果攻击者能够执行传入其选择的输入参数值的存储过程。

虽然这些方法都不是完美的，但我认为第三个选项是三种方法中最好的。 通过使用动态 SQL，它提供了一个其他两个都没有的灵活性级别。 此外，仅当攻击者能够执行传入所选输入参数的存储过程时，才能利用 SQL 注入攻击。 由于 DAL 使用参数化查询，因此 ADO.NET 将保护通过体系结构发送到数据库的这些参数，这意味着仅当攻击者可以直接执行存储过程时，SQL 注入攻击漏洞才存在。

若要实现此功能，请在名为 GetProductsPagedAndSortedNorthwind 数据库中创建新的存储过程。 此存储过程应接受三个输入参数：@sortExpression、类型为 nvarchar(100 的输入参数，用于指定结果的排序方式，并直接在 ORDER BY 子句中的 OVER 文本后插入；以及 @startRowIndex 和 @maximumRows，它们是前面教程中检查的 GetProductsPaged 存储过程中的两个相同的整数输入参数。 使用以下脚本创建GetProductsPagedAndSorted存储过程：

CREATE PROCEDURE dbo.GetProductsPagedAndSorted
(
    @sortExpression nvarchar(100),
    @startRowIndex int,
    @maximumRows int
)
AS
-- Make sure a @sortExpression is specified
IF LEN(@sortExpression) = 0
    SET @sortExpression = 'ProductID'
-- Issue query
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
                   UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
                   CategoryName, SupplierName
            FROM (SELECT ProductID, ProductName, p.SupplierID, p.CategoryID,
                         QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
                         ReorderLevel, Discontinued,
                  c.CategoryName, s.CompanyName AS SupplierName,
                   ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowRank
            FROM Products AS p
                    INNER JOIN Categories AS c ON
                        c.CategoryID = p.CategoryID
                    INNER JOIN Suppliers AS s ON
                        s.SupplierID = p.SupplierID) AS ProductsWithRowNumbers
            WHERE     RowRank > ' + CONVERT(nvarchar(10), @startRowIndex) +
                ' AND RowRank <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
                + CONVERT(nvarchar(10), @maximumRows) + ')'
-- Execute the SQL query
EXEC sp_executesql @sql

存储过程首先确保已经为 @sortExpression 参数设置了一个值。 如果缺少，则结果根据ProductID进行排名。 接下来，将构造动态 SQL 查询。 请注意，此处的动态 SQL 查询与以前用于从 Products 表中检索所有行的查询略有不同。 在前面的示例中，我们使用子查询获取了每个产品关联的类别和供应商名称。 此决定是在 “创建数据访问层 ”教程中做出的，而不是使用 JOIN S，因为 TableAdapter 无法自动为此类查询创建关联的插入、更新和删除方法。 GetProductsPagedAndSorted存储过程必须使用JOIN来按类别或供应商名称对结果进行排序。

此动态查询是通过将静态查询部分与参数@sortExpression、@startRowIndex和@maximumRows连接起来构建的。 由于 @startRowIndex 是 @maximumRows 整数参数，因此必须将其转换为 nvarchars 才能正确连接。 构造此动态 SQL 查询后，将通过 sp_executesql 执行该查询。

请花点时间使用不同的@sortExpression、@startRowIndex和@maximumRows参数值测试这个存储过程。 在服务器资源管理器中，右键单击存储过程名称，然后选择“执行”。 这将打开“运行存储过程”对话框，你可以在其中输入输入参数（请参阅图 1）。 若要按类别名称对结果进行排序，请使用 CategoryName 获取 @sortExpression 参数值;若要按供应商的公司名称进行排序，请使用 CompanyName。 提供参数值后，单击“确定”。 结果将显示在“输出”窗口中。 图 2 显示了按降序排序 UnitPrice 时返回产品排名 11 到 20 的结果。

图 1：尝试存储过程的三个输入参数的不同值

图 2：“输出”窗口中显示了存储过程的结果（单击以查看全尺寸图像）

注释

指定按照ORDER BY子句中的OVER列对结果进行排名时，SQL Server 必须对结果进行排序。 如果结果要按列上的聚集索引或者覆盖索引排序，这是一种快速操作，否则可能成本更高。 为了提高大型查询的性能，请考虑为用于结果排序的列添加一个非聚集索引。 有关更多详细信息 ，请参阅 SQL Server 2005 中的排名函数和性能 。

步骤 2：扩充数据访问和业务逻辑层

创建了 GetProductsPagedAndSorted 存储过程之后，我们下一步是通过应用程序架构来执行该存储过程的方法。 这需要向 DAL 和 BLL 添加适当的方法。 首先，将方法添加到 DAL。 Northwind.xsd打开类型化数据集，右键单击ProductsTableAdapter，然后从上下文菜单中选择“添加查询”选项。 正如在前面的教程中所做的那样，我们希望将此新的 DAL 方法配置为使用现有的存储过程， GetProductsPagedAndSorted在本例中。 首先指示希望新的 TableAdapter 方法使用现有的存储过程。

图 3：选择使用现有存储过程

要指定要使用的存储过程，请在下一个屏幕中的下拉列表中选择 GetProductsPagedAndSorted 存储过程。

图 4：使用 GetProductsPagedAndSorted 存储过程

此存储过程返回一组记录作为其结果，因此，在下一个屏幕中，指示它返回表格数据。

图 5：指示存储过程返回表格数据

最后，创建使用“填充 DataTable”和“返回 DataTable”模式的 DAL 方法，并分别将其命名为FillPagedAndSorted和GetProductsPagedAndSorted。

图 6：选择方法名称

现在，我们已经扩展了 DAL，我们准备转向 BLL。 打开 ProductsBLL 类文件并添加新方法 GetProductsPagedAndSorted。 此方法需要接受三个输入参数sortExpression、startRowIndex和maximumRows，然后直接调用DAL的GetProductsPagedAndSorted方法，如下所示：

<System.ComponentModel.DataObjectMethodAttribute( _
    System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsPagedAndSorted(ByVal sortExpression As String, _
    ByVal startRowIndex As Integer, ByVal maximumRows As Integer) _
    As Northwind.ProductsDataTable
    Return Adapter.GetProductsPagedAndSorted(sortExpression, startRowIndex, maximumRows)
End Function

步骤 3：配置 ObjectDataSource 以传入 SortExpression 参数

扩充 DAL 和 BLL 以包含利用 GetProductsPagedAndSorted 存储过程的方法后，剩下的唯一任务就是在 SortParameter.aspx 页面中配置 ObjectDataSource，以使用新的 BLL 方法，并根据用户选择的排序结果列传入 SortExpression 参数。

首先将 ObjectDataSource 的 SelectMethod 从 GetProductsPaged 更改为 GetProductsPagedAndSorted。 这可以通过“配置数据源”向导、“属性”窗口或通过声明性语法直接完成。 接下来，我们需要提供 ObjectDataSource 属性SortParameterName的值。 如果设置了此属性，ObjectDataSource 将尝试将 GridView 属性SortExpression传入到 .SelectMethod 具体而言，ObjectDataSource 会查找其名称等于SortParameterName属性值的输入参数。 由于 BLL s GetProductsPagedAndSorted 方法具有命名 sortExpression的排序表达式输入参数，请将 ObjectDataSource s SortExpression 属性设置为 sortExpression。

进行这两项更改后，ObjectDataSource 的声明性语法应如下所示：

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
    OldValuesParameterFormatString="original_{0}" TypeName="ProductsBLL"
    SelectMethod="GetProductsPagedAndSorted" EnablePaging="True"
    SelectCountMethod="TotalNumberOfProducts" SortParameterName="sortExpression">
</asp:ObjectDataSource>

注释

与前面的教程一样，请确保 ObjectDataSource 在其 SelectParameters 集合中 不包含 sortExpression、startRowIndex 或 maximumRows 输入参数。

若要在 GridView 中启用排序，只需选中 GridView 智能标记中的“启用排序”复选框，该复选框会将 GridView 的属性AllowSortingtrue设置为并导致每个列的标题文本呈现为 LinkButton。 当最终用户单击其中一个标题链接按钮时，会进行回发，然后执行以下步骤：

1. GridView 将其SortExpression属性更新为单击其标头链接的字段的值SortExpression
2. ObjectDataSource 调用 BLL 的 GetProductsPagedAndSorted 方法，将 GridView 的属性 SortExpression 作为该方法输入参数 sortExpression 的值，同时传入适当的 startRowIndex 和 maximumRows 输入参数值。
3. BLL 调用 DAL 的 GetProductsPagedAndSorted 方法
4. DAL 执行GetProductsPagedAndSorted存储过程，传入@sortExpression参数（以及@startRowIndex@maximumRows输入参数值）
5. 存储过程将适当的数据子集返回到 BLL，该子集将其返回到 ObjectDataSource;然后，此数据绑定到 GridView，呈现为 HTML，并发送到最终用户

图 7 显示按 UnitPrice 升序排序的结果第一页。

图 7：结果按 UnitPrice 排序（单击以查看全尺寸图像）

虽然当前实现可以按产品名称、类别名称、单位数量和单价正确对结果进行排序，但尝试按供应商名称对结果进行排序会导致运行时异常（请参阅图 8）。

图 8：尝试按供应商结果在以下运行时异常中对结果进行排序

发生此异常的原因是 SortExpression 的 GridView SupplierName BoundField 设置为 SupplierName。 但是，表中的供应商名称 Suppliers 实际上称为 CompanyName 我们已被将此列名别名命名为 SupplierName。 但是，OVER 函数所用的 ROW_NUMBER() 子句不能使用别名，必须使用实际的列名。 因此，将 SupplierName BoundField s SortExpression 从 SupplierName 更改为 CompanyName（请参阅图 9）。 如图 10 所示，在此更改后，结果可以按供应商排序。

图 9：将 SupplierName BoundField s SortExpression 更改为 CompanyName

图 10：结果现在可以按供应商排序（单击以查看全尺寸图像）

概要

我们在前面的教程中检查的自定义分页实现要求在设计时按顺序对结果进行排序。 简言之，这意味着我们实现的自定义分页实现不能同时提供排序功能。 在本教程中，我们通过将存储过程从第一个 @sortExpression 扩展为包含输入参数来克服此限制，以便对结果进行排序。

在 DAL 和 BLL 中创建此存储过程并创建新方法后，我们可以通过配置 ObjectDataSource 将 GridView 的当前 SortExpression 属性传递给 BLL SelectMethod来实现排序和自定义分页。

快乐编程！

关于作者

斯科特·米切尔，七本 ASP/ASP.NET 书籍的作者和 4GuysFromRolla.com 的创始人，自1998年以来一直在与Microsoft Web 技术合作。 斯科特担任独立顾问、教练和作家。 他的最新书是 《Sams 自学 ASP.NET 2.0 只需 24 小时》。 可以通过 mitchell@4GuysFromRolla.com 联系到他。

特别致谢

本教程系列由许多有用的审阅者审阅。 本教程的主要审阅者是 Carlos Santos。 有兴趣查看即将发布的 MSDN 文章？ 如果是这样，请给我写信。mitchell@4GuysFromRolla.com

上一步下一步