排序自定义分页数据 (C#)

  • 项目

作者 ：Scott Mitchell

下载 PDF

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

简介

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

注意

由于本教程基于上一个教程，因此在开始之前，请花点时间从前面的教程网页复制 元素中的<asp:Content>声明性语法， (EfficientPaging.aspx) 并将其粘贴到SortParameter.aspx页面中的 <asp:Content> 元素之间。 有关将一个 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使用 语句提供基于 n 个输入参数的@sortExpressio动态ORDER BY表达式;有关详细信息，请参阅 T-SQL CASE 语句中的用于动态排序查询结果部分。
  • 在存储过程中以字符串的形式创建相应的查询 ， sp_executesql 然后使用系统存储过程 执行动态查询。

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

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

若要实现此功能，请在名为 GetProductsPagedAndSorted的 Northwind 数据库中创建新的存储过程。 此存储过程应接受三个输入参数：、类型nvarchar(100为) 的输入参数，用于指定结果的排序方式并直接在 子句中的OVER文本后面ORDER BY注入;和 @startRowIndex@maximumRows和 ，是上一教程中介绍的存储过程中的相同两个整数输入参数GetProductsPaged。 @sortExpression 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 无法自动为此类查询创建关联的插入、更新和删除方法。 GetProductsPagedAndSorted但是，存储过程必须使用 JOIN ，才能按类别或供应商名称对结果进行排序。

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

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

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

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

存储过程的结果显示在输出窗口中

图 2：存储过程的结果显示在输出窗口中 (单击以查看全尺寸图像)

注意

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

步骤 2：增强数据访问层和业务逻辑层

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

选择使用现有存储过程

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

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

使用 GetProductsPagedAndSorted 存储过程

图 4：使用 GetProductsPagedAndSorted 存储过程

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

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

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

最后，创建使用填充 DataTable 和返回 DataTable 模式的 DAL 方法，分别命名方法和 FillPagedAndSortedGetProductsPagedAndSorted。

选择方法名称

图 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 从 SelectMethodGetProductsPaged 更改为 GetProductsPagedAndSorted。 这可以通过“配置数据源”向导、属性窗口或直接通过声明性语法来完成。 接下来，我们需要为 ObjectDataSource 属性SortParameterName提供值。 如果设置了此属性，则 ObjectDataSource 会尝试将 GridView 的 SortExpression 属性传递给 SelectMethod。 特别是，ObjectDataSource 查找名称等于 属性值的 SortParameterName 输入参数。 由于 BLL 方法 GetProductsPagedAndSorted 具有名为 sortExpression的排序表达式输入参数，请将 ObjectDataSource 的 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 的 AllowSorting 属性设置为 true ，并使每列的标题文本呈现为 LinkButton。 当最终用户单击其中一个标头 LinkButtons 时，将随后发生回发，并发生以下步骤：

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

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

结果按 UnitPrice 排序

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

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

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

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

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

将 SupplierName BoundField s SortExpression 更改为 CompanyName

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

现在可以按供应商对结果进行排序

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

总结

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

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

编程愉快！

关于作者

Scott Mitchell 是七本 ASP/ASP.NET 书籍的作者， 4GuysFromRolla.com 的创始人，自 1998 年以来一直从事 Microsoft Web 技术工作。 Scott 担任独立顾问、培训师和作家。 他的最新书是 山姆斯在24小时内 ASP.NET 2.0自学。 可以在 上联系 mitchell@4GuysFromRolla.com他， 也可以通过他的博客联系到他，该博客可在 http://ScottOnWriting.NET中找到。

特别感谢

本教程系列由许多有用的审阅者查看。 本教程的首席审阅者是 Carlos Santos。 有兴趣查看我即将发布的 MSDN 文章？ 如果是，请在 处放置一行 mitchell@4GuysFromRolla.com。

上一页下一页