对自定义分页数据进行排序

项目
10/20/2021

在上一篇教程中，我们学习了在网页上呈现数据时如何实现自定义分页。本教程中，我们将了解怎样扩展前面的示例以便支持排序和自定义分页。

简介

与默认分页相比，自定义分页可以将翻页显示数据（从一页转到另一页）的效率提高几个数量级，因此，当需要翻页显示大量数据时，我们实际需要考虑实现自定义分页。然而实现自定义分页比实现默认分页更为复杂一些，如果要加入排序功能就更是这样了。本教程中，我们将对上一教程中的示例进行扩展，使其同时支持排序和自定义分页。

注意：由于本教程以上一个教程为基础，我们先花些时间来将上一教程的网页 EfficientPaging.aspx 的 <asp:Content> 元素中的声明语句复制粘贴到本教程的 SortParameter.aspx 页面的 <asp:Content> 元素之间。有关将一个 ASP.NET 页的功能复制到另一个 ASP.NET 页的更详细的讨论，请参阅向编辑和插入界面添加校验控件的步骤 1 。

步骤1 ：回顾自定义分页技术

为了使自定义分页能正确地工作，我们需要使用某种技术，以根据给定的 Start Row Index 和 Maximum Rows 参数高效地获取一个特定的记录子集。有几个技术可以用来实现此目标。在上一篇教程中，我们看到可以使用 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 语句来提供基于 @sortExpression 输入参数的动态 ORDER BY 表达式；有关详细信息，请参阅 SQL CASE 语句的功能中的“用于动态排序查询结果” 一节。
在存储过程中将查询处理为一个字符串，然后使用 sp_executesql 系统存储过程来执行动态查询。

上述每一种实现方法都有一些缺点。第一种方法与其它两种方法相比可维护性比较差，因为它需要为每个可能的排序表达式创建一个查询。因此，如果以后决定向 GridView 添加新的可排序字段，还需要回到这里来修改存储过程。第二种方法在按非字符串类型的数据库列进行排序时会引入一定的效率问题，而且与第一种方法一样也存在可维护性问题。第三种方法使用动态 SQL ，如果攻击者能传入自己的输入参数值并执行存储过程的话则可引入 SQL 注入攻击的风险。

尽管这些方法都不完美，但我认为第三个是这三个之中最好的。因为第三种方法使用动态 SQL ，所以具有其它两种方法所没有的灵活性。此外，只有当攻击者能够将自己的输入参数传入要执行的存储过程时，才能进行 SQL 注入攻击。由于 DAL 使用参数化的查询， ADO.NET 将对那些通过系统架构发送到数据库的参数进行保护，这意味着只有在攻击者可以直接执行存储过程时，才存在 SQL 注入攻击漏洞。

要实现此功能，需要在 Northwind 数据库中新建一个名为GetProductsPagedAndSorted 的存储过程。此存储过程应接受三个输入参数：@sortExpression ，此为一个 nvarchar(100) 类型的输入参数，用来指定结果的排序方式，该参数直接加入到OVER 子句中的 “ORDER BY” 文本之后；@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 来代为完成，TableAdapter 不能自动为每个查询创建相关的 insert 、update 和 delete 方法。但是，GetProductsPagedAndSorted 存储过程必须使用 JOIN ，因为要按分类名称或供应商名称对结果进行排序。

此动态查询通过将静态查询部分与 @sortExpression 、@startRowIndex 、@maximumRows 参数相连接而建立。由于 @startRowIndex 和 @maximumRows 是整数参数，必须将它们转换为 nvarchars 类型以便正确地进行连接。此动态 SQL 查询构造完成后，通过 sp_executesql 来执行。

花费一些时间，使用各种 @sortExpression 、@startRowIndex 和 @maximumRows 参数值来测试此存储过程。在服务器资源管理器中，右键单击该存储过程的名称然后选择 Execute 。系统弹出 Run Stored Procedure 对话框，可以在该对话框中输入这些输入参数（参见图 1 ）。要按分类名称排序结果，将 @sortExpression 参数的值设置为 CategoryName ；要按供应商的公司名称排序，则设置为 CompanyName 。提供所有参数值后，单击 OK 。结果就会显示在 Output 窗口中。图 2 显示的结果是按 UnitPrice 降序排序时返回的排在 11 至 20 的产品。

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

图2 ：存储过程的结果显示在 Output 窗口中

注意：当按 OVER 子句中指定的 ORDER&bys;BY 列来排列结果时，SQL Server 必须对结果进行排序。如果在结果据此排序的列上有聚簇索引或者有覆盖索引，这将是一个快速操作，否则将是更费时的操作。为提高非常大量的查询的性能，可以考虑为据此对结果进行排序的列添加非聚簇索引。有关详细信息，请参阅SQL Server 2005 中的各种排序函数及其性能。

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

我们已经创建了 GetProductsPagedAndSorted 存储过程，下一步是使该存储过程能够通过我们的应用程序架构来执行。为此需要向 DAL 和 BLL 添加适当的方法。首先向 DAL 添加一个方法。打开 Northwind.xsd 强类型 DataSet ，右键单击 ProductsTableAdapter ，然后从关联菜单中选择 Add Query 选项。如同在上一教程中所做的那样，我们要配置这个新的 DAL 方法，使其使用一个现有的存储过程— 在此例中是 GetProductsPagedAndSorted 。首先选择一个选项以指示新的 TableAdapter 方法使用现有的存储过程。

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

从下一屏幕的下拉列表中选择 GetProductsPagedAndSorted 存储过程来指定使用它。

图4 ：使用 GetProductsPagedAndSorted 存储过程

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

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

最后，创建使用 Fill a DataTable 和 Return a DataTable 模式的两个 DAL 方法，将它们分别命名为 FillPagedAndSorted 和 GetProductsPagedAndSorted 。

图6 ：选择方法的名称

现在我们已经扩展了DAL ，下面可以扩展 BLL 了。打开 ProductsBLL 类文件，添加一个新的方法 GetProductsPagedAndSorted 。此方法需要接受三个输入参数 — sortExpression 、startRowIndex 和 maximumRows ，并且应该只是简单地向下调用 DAL 的 GetProductsPagedAndSorted 方法，如下所示：

[System.ComponentModel.DataObjectMethodAttribute(
    System.ComponentModel.DataObjectMethodType.Select, false)]
public Northwind.ProductsDataTable GetProductsPagedAndSorted(
    string sortExpression, int startRowIndex, int maximumRows)
{
    return Adapter.GetProductsPagedAndSorted
        (sortExpression, startRowIndex, maximumRows);
}

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

我们已为 DAL 和 BLL 添加了方法来调用 GetProductsPagedAndSorted 存储过程，剩下的工作就是配置 SortParameter.aspx 页面的 ObjectDataSource ，使其使用新的 BLL 方法并根据用户请求的对结果进行排序的列传递SortExpression 参数。

首先，将 ObjectDataSource 的 SelectMethod 从 GetProductsPaged 更改为 GetProductsPagedAndSorted 。这可以通过 Configure Data Source 向导，Properties 窗口，或直接通过声明语句来完成。接下来，需要为 ObjectDataSource 的 SortParameterName 属性提供一值。设置了此属性后， ObjectDataSource 才会尝试将 GridView 的 SortExpression 属性传入 SelectMethod 。特别地，ObjectDataSource 会根据 SortParameterName 的属性值寻找一个同名的输入参数。既然 BLL 的 GetProductsPagedAndSorted 方法的排序表达式输入参数名为 sortExpression ，在此将 ObjectDataSource 的SortParameterName 属性设置为“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 的智能标签中选中 Enable Sorting 复选框，此操作将 GridView 的 AllowSorting 属性设置为 true 并使每列的标题文本呈现为 LinkButton 。当最终用户单击某个标题 LinkButton 时，会引发回传和以下几个步骤：

GridView 将其 SortExpression属性更新为当前单击的标题链接所在字段的SortExpression 的值。
ObjectDataSource 调用 BLL 的 GetProductsPagedAndSorted 方法，将 GridView 的 SortExpression 属性作为该方法的 sortExpression 输入参数的值传入该方法（同时传入的还有相应的 startRowIndex 和 maximumRows 输入参数值）。
BLL 调用 DAL 的 GetProductsPagedAndSorted 方法
DAL 执行 GetProductsPagedAndSorted 存储过程，传入 @sortExpression 参数值（同时传入 @startRowIndex 和 @maximumRows 输入参数值）。
存储过程将相应的数据子集返回 BLL ，BLL 又将该数据子集返回 ObjectDataSource ；然后此数据绑定到 GridView ，转换为 HTML 后传给最终用户。

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

图7 ：结果按单价排序

虽然当前的实现能正确地按照产品名称、分类名称、每单位数量和单价对结果进行排序，但是如果按照供应商名称排序会导致运行时异常（参见图 8）。

图8 ：按照供应商对结果进行排序导致运行时异常

出现此异常的原因是，GridView 的 SupplierName BoundField 的 SortExpression 设置为了 SupplierName 。然而，Suppliers 表中的供应商名称实际上为 CompanyName ，SupplierName 是我们为此列取的别名。而 ROW_NUMBER() 函数使用的 OVER 子句不能使用别名，只能使用真实的列名。因此，需要将 SupplierName BoundField 的 SortExpression 从 “SupplierName” 更改为 “CompanyName” （参见图 9 ）。如图 10 所示，更改之后，就可以按照供应商对结果进行排序了。