ASP.NET 2.0运营数据第26期:定制分页数据排序

介绍

与默认的翻页方法相比，自定义分页可以将效率提高几个数量级。当我们需要分页大量数据时，我们需要考虑自定义分页。但是，与默认分页相比，我们需要做更多的工作来实现自定义分页。对自定义分页数据进行排序也是如此。在本教程中，我们将扩展前面的示例来对自定义分页数据进行排序。

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

步骤1:查看自定义分页技术。

为了实现自定义分页，我们需要使用一些方法根据起始行索引和最大行参数返回记录子集。在上一个教程中，我们看到了如何使用Microsoft SQL SERVER 2005的ROW_NUMBER()。简而言之，ROW_NUMBER()为查询返回的每一行分配一个行号。下面的查询演示了如何使用这种技术将ProductName获得的产品数据从11排序到20。

选择产品标识，产品名称，发件人(选择产品标识，产品名称，Row _ number () over(按产品名称排序)as Row rank from products)as products swithrownnumberswhere Row rank 10和rowrank=20根据固定的排序规则进行分页，可以满足上面的技术(例如按产品名称排序)，但是如果我们想要获得按不同排序表达式排序的记录，理想情况下，我们应该用over子句中的参数重写上面的查询，代码如下：

选择产品标识，产品名称，发件人(选择产品标识，产品名称，Row _ number () over (ORDER BY @ sort表达式)作为来自产品的行秩)作为productsswithrownumberwhere其中行秩为10，行秩=20遗憾的是，参数不能在ORDER BY子句中使用。相反，我们只能创建一个存储过程来接受@sortExpression输入参数，使用以下任一方法：

对于所有排序表达式的硬编码查询，使用IF/ELSE T-SQL语句来决定执行哪个查询。使用CASE语句根据输入参数@sortExpression实现动态ORDER BY表达式。有关详细信息，请参考“SQL CASE语句的威力”中的“用于动态排序查询结果”部分。

使用string保存查询语句，然后使用sp_executesql系统存储过程动态执行查询。

以上每一种方法都有自己的缺点。与其他两种方案相比，第一种方案的可维护性较差，因为它需要为每个可能的新颖性搜索表达式创建一个查询。因此，如果在GridView中添加了允许排序的字段，则需要修改存储过程。对于第二种方案，如果我们的数据库列不是字符串类型，排序会造成一些效率问题，可维护性不如第一种。至于最后一种动态组合SQL语句的方案，如果允许用户自己输入参数并传递到存储过程中，可能会给SQL注入攻击带来危害。

虽然没有一个方案是完美的，但我知道第三个方案是三个方案中最好的。因为它使用动态SQL语句，所以它的灵活性比前两者更好。而且，只有当攻击者可以随意将参数传入存储过程时，才能进行SQL注入攻击。由于DAL使用参数化查询，ADO.NET将防止这些恶意参数引入数据库，这意味着只有当攻击者直接执行存储过程时，才会有SQL注入的隐患。

为了实现这个功能，让我们在罗斯文数据库中创建一个名为GetProductsPagedAndSorted的新存储过程。这个存储过程接受三个参数：@sortExpression，nvarchar(100)类型输入参数，用于指定排序方法，它将直接拼接在ORDER BY子句之后。@ startRowIndex和@maximumRows是整数输入参数，与前面的教程一样。您可以参考以下脚本来建立GetProductPage和Sorted存储过程：

创建过程数据库。getproductpagesandsorted(@sortExpression nvarchar(100)，@ startRowIndex int，@maximumRows int)AS -确保指定了一个@ sort expression定义LEN(@ sort expression)=0 SET @ sort expression=' product id '-发出查询声明@ SQL nvarchar(4000)SET @ SQL=' SELECT product id，ProductName，supplierierid，CategoryID，quantityperrunit，UnitPrice，UnitsInStock，UnitsOnOrder，ReorderLevel，continuated，categorname，supplier ier ROW _ NUMBER()OVER(ORDER BY ' @ sort expression ')AS ROW rank FROM Products AS p INNER JOIN Categories AS c ON . CategoryID=p . CategoryID INNER JOIN supplieriers AS s ON . supplierieid=p . supplierieid)AS Products with ROW numbers其中RowRank ' CONVERT(nvarchar(10)，@startRowIndex ')和RowRank=(' CONVERT(nvarchar(10)，@ startrow index)')'-执行SQL查询exec sp 如果未指定，则按ProductID排序。接下来，开始构建动态SQL查询。请注意，这里的动态SQL查询与以前用于从产品表中获取所有行的查询有些不同。在前面的示例中，我们使用子查询来获取与每个产品相关联的分类和供应商名称。在GetProductsPagedAndSorted中，我们只能使用JOINS，因为结果需要根据分类或供应商名称进行排序。

我们通过将静态查询语句与@ sortexpression、@ startrowindex、@ maximumrows参数相连接来构建动态查询。因为@ startRowIndex和@maximumRows是整数参数，所以在连接之前必须将其转换为nvarchar类型。连接后，可以使用sp_executesql执行动态sql查询。

让我们花一些时间用各种@sortExpression、@ startRowIndex和@maximumRows参数的值来测试存储过程。右键单击服务器资源管理器中的存储过程，然后选择执行。集成开发环境启动运行存储过程对话框，我们输入各种输入参数(见图1)。例如，要按类别名称对结果进行排序，请将@sortExpression参数的值设置为CategoryName；如果要按公司名称排序，请使用公司名称。正确设置所有参数后，单击确定。结果显示在输出窗口中。图2以单价的相反顺序显示了从11到20的记录。

图1:尝试设置存储过程的三个输入参数。

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

步骤2:添加数据访问和业务逻辑层。

现在我们已经建立了GetProductsPagedAndSorted存储过程，下一步是通过我们的应用程序框架来执行它。我们需要为DAL和BLL添加一个正确的方法。首先，让我们为DAL添加一个方法。打开Northwind.xsd强类型数据集，右键单击ProductsTableAdapter，然后从菜单中选择添加查询选项。正如我们在前面的教程中所做的那样，我们需要配置一个新的DAL方法来使用已建立的存储过程——getproductpageandsorted。选择“使用现有存储过程”选项。

图3:选择一个现有的存储过程。

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

图4:使用getproductpages和Sorted存储过程。

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

图5:表示存储过程返回表信息。

最后，我们创建了填充和返回数据表的DAL方法，分别命名为FillPagedAndSorted和GetProductsPagedAndSorted。

图6:选择方法名。

现在我们已经扩展了DAL，让我们看看BLL。打开ProductsBLL类文件，并添加一个新的方法GetProductsPagedAndSorted。该方法接受三个参数——排序表达式、起始行索引和最大行。只需调用DAL的GetProductsPagedAndSorted方法，代码如下：

[系统。组件模型。component model . DataObjectMethodType . select，false)]公共北风。productdatatable getproductpagesandsorted(字符串sortExpression，int startRowIndex，int maximumRows){返回适配器。getproductpagesandsorted(sort expression，startRowIndex，maximumRows)；}步骤3:将ObjectDataSource配置为传入SortExpression参数。

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

首先，我们将对象数据源的SelectMethod从GetProductsPaged更改为GetProductsPaged和Sorted。您可以通过配置数据源向导的属性窗口或直接在声明代码中修改它。在下一步中，我们需要提供ObjectDataSource的SortParameterName属性。属性已设置，ObjectDataSource将把GridView的SortExpression属性传递给SelectMethod。特别是，ObjectDataSource将根据SortParameterName的值查找输入仓库。由于BLL的GetProductsPagedAndSorted方法的输入参数叫做sortExpression，因此我们的ObjectDataSource的SortExpression属性也应该设置为“sortExpression”。

经过这两次修改后，ObjectDataSource的声明应该如下：

asp: ObjectDataSource ID=' objectdata source 1 ' runat=' server ' oldvaluesparametertformatstring=' original _ { 0 } ' type name=' products bll ' SelectMethod=' getproductpageand sorted ' EnablePaging=' True ' selectcount method=' TotalNumberOfProducts ' sort parameter name=' sort Expression '/ASP : objectdata source注意：如前一教程所述，请确保sort Expression、startRowIndex和maximumRows显示objectdata source的SelectParameters集合中的输入参数。

要在GridView中打开排序，首先检查是否选中了排序复选框。将GridView的AllowSorting属性设置为true会将每列的标题文本呈现为链接按钮。单击标题的链接按钮将触发以下步骤：

1.GridView将其SortExpression属性值更改为当前单击标题所在列的SortExpression值。

2.ObjectDataSource调用BLL的GetProductsPagedAndSorted方法，并将GridView的sortExpression属性值作为SortExpression参数(以及startRowIndex和maximumRows输入参数的正确值)传递给该方法。

3.BLL调用DAL的getproductpage和Sorted方法。

4.DAL执行getproductpage和Sorted存储过程，并传入@sortExpression参数(和@ startRowIndex、@maximumRows输入参数)。

5.存储过程将正确的记录子集数据返回给BLL，BLL返回给对象数据源；数据被绑定到GridView，然后呈现为HTML以显示给用户。

图7显示了按单价正序排列的第一页记录集。

图7:按单价排列的结果。

虽然我们的程序可以正确地按照产品名称、分类名称、比特数量和价格进行排序，但是如果我们选择按照供应商名称进行排序，我们将会得到一个运行时异常，如图8所示。

图8:按供应商名称排序将导致运行时异常。

引发此异常的原因是因为GridView的SupplierName BoundField的绑定列中的排序表达式设置为SupplierName。但是，这个列在供应商表中实际上叫做CompanyName，SupplierName是我们对这个列的别名。由于ROW_NUMBER()函数只能使用实列名，我们需要将BoundField的SortExpression从“SupplierName”修改为“CompanyName”(如图9所示)，图10显示了修改后按供应商排序的记录。

图9:将供应商名称边界字段的排序表达式更改为“公司名称”。

图10:结果现在可以按供应商名称排序。

摘要

在上一个教程中，我们实现了自定义分页，并且在设计过程中只修复了一种排序模式。简单来说，就是无法定制分页和提供定制排序。在本教程中，我们通过引入@sortExpression来扩展存储过程，从而解决了这个限制。

在DAL和BLL创建存储过程和新方法后，我们可以配置ObjectDataSource，将GridView当前的SortExpression值转移到BLL的SelectMethod中，实现排序和自定义分页。

编程快乐！

关于作者

斯科特米切尔，六本关于ASP/ASP的书的作者。NET，是4GuysFromRolla.com的创始人，自1998年以来一直使用微软的网络技术。Scott是一名独立的技术顾问、培训师和作家，最近完成了一部即将由Sams出版社出版的新作，24小时内精通ASP.NET 2.0。他的联系电子邮件是[emailprotected]，也可以通过他的博客http://ScottOnWriting.NET联系到他。