在ASP.NET 2.0中操作数据之二十六：排序自定义分页数据

导言

　　和默认翻页方式相比，自定义分页能提高几个数量级的效率。当我们的需要对大量数据分页的时候就需要考虑自定义分页，然而实现自定义分页相比默认分页需要做更多工作。对于排序自定义分页数据也是这样，在本教程中我们就会扩展前面的例子来实现自定义分页数据的排序。

　　注意：既然本教程是基于前一个的，因此我们需要把前面教程示例页面EfficientPaging.aspx的asp:Content>元素中的代码复制到本教程SortParameter.aspx示例页面中。关于如何进行这样的复制操作请参看为删除数据添加客户端确认

Step 1: 回顾自定义分页技术

　　要实现自定义分页，我们需要使用一些方法根据开始行索引和最大行参数返回一个记录的子集。在前面的教程中，我们看了如何使用微软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表达式，详细请看The Power of SQL CASE Statements中的Used to Dynamically Sort Query Results部分。

　　使用字符串来保存查询语句然后使用sp_executesql系统存储过程来动态执行查询

　　上述每一种实现方法都有各自的缺点。第一个方案和其余两个相比可维护性比较差，因为它需要为每一个可能的查新表达式创建一句查询。因此，如果你又在GridView中加入了一个允许排序的字段，还需要去修改存储过程。对于第二个方案如果我们的数据库列不是字符串类型的话，排序就会引发一定的效率问题，而且可维护性和第一种一个一样也不是很好。至于最后一个动态组合SQL语句的方案，如果你允许用户自己输入参数并传入存储过程的话则可能带来SQL注入攻击的危害。

　　虽然没有一种方案是完美的，但是我认识第三种是这三个方案中最佳的。因为它是使用动态SQL语句的，所以灵活性比前两者都好。而且，只有当攻击者能随意把参数传入存储过程才能进行SQL注入攻击。既然DAL使用参数化查询，ADO.NET会防止这些恶意参数传入数据库，也就是说只有当攻击者人直接执行存储过程的时候才会有SQL注入的隐患。

　　要实现这个功能，让我们在Northwind数据库中新建称作GetProductsPagedAndSorted的一个存储过程。这个存储过程接受三个参数：@sortExpression，nvarchar(100)类型的输入参数，用来指定排序方式，它会直接拼接在ORDER BY子句后面。@startRowIndex 和 @maximumRows都是整数输入参数，和前面教程中的一样。你可以参考下面的脚本建立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表获取所有行的查询有些不同。在前面的例子中，我们使用子查询获取每一个产品关联的分类和供应商名。在GetProductsPagedAndSorted中我们只能使用JOINS因为结果需要根据分类或者供应商名来排序。

　　我们通过连接静态的查询语句和@sortExpression, @startRowIndex, @maximumRows参数来组成动态查询。因为@startRowIndex和@maximumRows是整数参数，所以必须在连接前把它们转化为nvarchar类型。在动态SQL查询连接完毕后就可以使用sp_executesql来执行。

　　先来花一些时间使用各种@sortExpression、@startRowIndex和@maximumRows参数的值来测试存储过程。在服务器资源管理器中右键点击存储过程然后选择执行。IDE会启动运行存储过程对话框，我们输入各种输入参数（见图1）。比如，要让结果按照分类名排序，就把@sortExpression参数的值设置为CategoryName；如果要按照公司名排序就用CompanyName。所有参数的值都正确设置后点击OK。结果就会在输出窗口中显示。图2显示了按照UnitPrice倒序，从11到20的记录。

图1：试着设置存储过程的三个输入参数

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

Step 2: 添加数据访问和业务逻辑层

既然我们已经建立了GetProductsPagedAndSorted存储过程，下一步就是要通过我们的应用程序构架来执行它。我们需要为DAL和BLL添加一个正确的方法。首先让我们为DAL添加一个方法。打开Northwind.xsd强类型DataSet，右键点击ProductsTableAdapter，从菜单中选择添加查询选项。和前面教程中做的一样，我们需要配置一个新的DAL方法来使用建立的存储过程－GetProductsPagedAndSorted。选择使用已有存储过程选项。

图3：选择一个已有的存储过程

在下一步中，我们通过从下拉列表中选择GetProductsPagedAndSorted存储过程来使用它。

图4：使用GetProductsPagedAndSorted存储过程

在下一屏幕中，我们选择它返回表格信息。

图5：指示存储过程返回表格信息

最后，我们创建DAL方法来填充DataTable和返回DataTable，分别命名为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);
}

Step 3: 配置ObjectDataSource来传入SortExpression参数

　　好了，我们已经为DAL和BLL添加了方法来调用GetProductsPagedAndSorted存储过程。剩下的工作就是配置SortParameter.aspx页面的ObjectDataSource来根据用户请求的排序为新的BLL方法传入SortExpression参数。

　　首先，我们把ObjectDataSource的SelectMethod从GetProductsPaged修改为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开启排序，请首先检查Sorting多选框是否已经选中。把GridView的AllowSorting属性设置为true以后就能让每列的标题文字呈现为LinkButton。用户点击标题的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，BLL返回到ObjectDataSource；数据被绑定到GridView之后渲染成HTML显示给用户。

图7显示了按照UnitPrice正序排列地第一页记录集。

图7：按照UnitPrice排列的果

虽然现在我们的程序能正确按照产品名、分类名、位数量和价格进行排序，但是如果我们选择按照供应商名来排序会得到一个运行时异常，如图8。

图8：按照供应商名排序会得到一个运行时异常

之所以会引发这个异常时因为GridView的SupplierName BoundField绑定列的SortExpression设置为SupplierName。然而，这列在供应商表中实际叫做CompanyName，SupplierName是我们为这个列起的别名。因为ROW_NUMBER()功能只能使用真实列名，所以，我们需要把BoundField的SortExpression从“SupplierName”修改为“CompanyName”（如图9），图10显示了修改后按照供应商排序的记录。

图9：把SupplierName BoundField的SortExpression修改为“CompanyName” （译者注：图片可能不对）

图10：结果现在能按照供应商名排序了

总结

前面教程中我们实现了自定义分页，只能在设计时固定一种排序方式。简单来说要想又自定义分页又提供自定义排序实现不了。在本教程中，我们通过引入@sortExpression来扩展存储过程解决了这个限制。

在创建了存储过程和DAL、BLL中的新方法后，我们就能通过配置ObjectDataSource把GridView当前SortExpression的值传入BLL的SelectMethod中来实现排序和自定义分页。

编程快乐！

关于作者

Scott Mitchell，著有六本ASP/ASP.NET方面的书，是4GuysFromRolla.com的创始人，自1998年以来一直应用微软Web技术。Scott是个独立的技 术咨询顾问，培训师，作家，最近完成了将由Sams出版社出版的新作，24小时内精通ASP.NET 2.0。他的联系电邮为mitchell@4guysfromrolla.com，也可以通过他的博客http://ScottOnWriting.NET与他联系。