点击结束后,DAL里添加了一个TotalNumberOfProducts方法.这个方法返回的值可为空,而Count语句总是返回一个非空的值.
我们还需要在BLL中加一个方法.打开ProductsBLL类文件,添加一个TotalNumberOfProducts方法,这个方法要做的只是调用DAL的TotalNumberOfProducts方法.
public int TotalNumberOfProducts() { return Adapter.TotalNumberOfProducts().GetValueOrDefault(); }
DAL的TotalNumberOfProducts方法返回一个可空的整型,而需要ProductsBLL类的TotalNumberOfProducts方法返回一个标准的整型.调用GetValueOrDefault方法,如果可为空的整型为空,则返回默认值,0.
第三步: 返回需要的数据记录
下一步我们要在DAL和BLL里创建接受Start Row Index 和Maximum Rows 的方法,然后返回合适的记录.我们首先看看需要的SQL语句.我们面临的挑战是需要为整个分页的记录分配索引,用来返回从Start Row Index 开始的Maximum Records number of records条记录.
如果在数据库表里已经有一个列作为索引,那么一切会变的很简单.我们首先会想到Products表的ProductID字段可以满足这个条件,第一个Product的ProductID为1,第二个为2,以此类推.然而当一个product被删除后,这个序列会留下间隔来,所以这个方法不行.
有两种可以把整个要分页的数据和一个row index关联起来的方法.
１.使用SQL Server 2005的ROW_NUMBER() Keyword – SQL Server 2005的新特性,它可以将记录根据一定的顺序排列,每条记录和一个等级相关 这个等级可以用来作为每条记录的row index.
２.使用SET ROWCOUNT – SQL Server的 SET ROWCOUNT statement 可以用来指定有多少记录需要处理; table variables 是可以存放表格式的T-SQL 变量, 和temporary tables类似. 这个方法在Microsoft SQL Server 2005 和SQL Server 2000都可以用 (ROW_NUMBER() 方法只能在SQL Server 2005里用).
这个思路是,为要分页的数据创建一个table变量,这个table变量里有一个作为主健的IDENTITY列.这样需要分页的每条记录在table变量里就和一个row index(通过IDENTITY列)关联起来了.一旦table变量产生,连接数据库表的SELECT语句就被执行,获取需要的记录.SET ROWCOUNT用来限制放到table变量里的记录的数量.
当SET ROWCOUNT的值指定为Start Row Index 加上Maximum Rows时,这个方法的效率取决于被请求的页数.对于比较前面的页来说– 比如开始几页的数据– 这种方法非常有效. 但是对接近尾部的页来说,这种方法的效率和默认分页时差不多.
本教程用ROW_NUMBER()来实现自定义分页.如果需要知道更多的关于table变量和SET ROWCOUNT的技术,请看 A More Efficient Method for Paging Through Large Result Sets.
以下语句用来使用ROW_NUMBER()将一个等级和返回的每条记录关联:
SELECT columnList, ROW_NUMBER() OVER(orderByClause) FROM TableName
ROW_NUMBER()返回一个根据指定排序的表示每条记录的等级的值.比如,我们可以用以下居于查看根据价格来排序(降序)的每个product的等级:
SELECT ProductName, UnitPrice, ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank FROM Products
图5 是在Visual Studio里运行以上代码的结果. 注意product根据价格排序,每行有一个等级.
图 5: 返回的记录里每行有一个Price Rank
注意: ROW_NUMBER() 只是 SQL Server 2005里很多排级的功能中的一种. 想了解更多的ROW_NUMBER()的讨论,包括其它的排级功能,请看 Returning Ranked Results with Microsoft SQL Server 2005.
当使用OVER从句里的ORDER BY 列名(UnitPrice)来排级时,SQL Server会对结果排序.为了提升大数据量查询时的性能,可以为用来排序的列加上非聚集索引.更多的性能考虑参考Ranking Functions and Performance in SQL Server 2005.