ASP.Net + SQL Server 存储过程实现分页排序

问题提出:

在应用程序中经常需要查询数据。当查询结果数据量比较大的时候，检索结果、界面显示都需要花费大量的时间。为了避免这个问题，应该每次只检索部分数据，也就是使用常见的分页方式来处理。分页的问题在asp.net中好像非常简单，只要在GridView中启用分页就可以了。启用分页后，GridView关联数据源控件，依旧会加载所有的数据。这个解决方法只是“掩耳盗铃” ，会导致在大数据量的情况下导致查询的效率变低。

解决方法：

使用GridView的自定义分页功能。使用自定义分页功能需要实现两个逻辑：得到结果集的总数、查找自定范围的数据。

GridView 分页实现，可以参考 Scott Mitchell 文章 Efficiently Paging Through Large Amounts of Data

本文组要介绍如何在使用SQL Server 实现排序获取分页数据。

1、使用子查询+TOP关键字方式

if object_id('GetStudentPaged') is not null
    drop procedure GetStudentPaged;
go
/************************************
* 描述： 查找指定范围的记录
* 参数：@startRow 其实记录
*           @maximumRows 最大的记录数量
*************************************/

create procedure GetStudentPaged
    @startRow int,
    @maxmimumRows int
as
select top (@maxmimumRows) * from Student
where StudentId NOT IN( select top(@startRow) StudentId from Student )
go

如果需要按条件查找，条件可能会有所不同。通过参数将where条件传入到存储过程中。由于where子句不支持使用变量，所以需要将在存储过程中组合SQL语句，通过动态SQL方式执行。

create procedure GetStudentPaged
    @startRow int,
    @maxmimumRows int,
    @whereExpression nvarchar(512)
as
--存储SQL语句的字符串
declare @sql nvarchar(max)

set @sql = N'Select Top(' + ltrim(str(@maxmimumRows)) + N') * '
set @sql = @sql + N' from Student where '

--判断是否有查询条件
if @whereExpression is not null and @whereExpression <> N''
begin
   set @sql = @sql + @whereExpression + N' and '
end

set @sql = @sql + N'StudentId NOT IN ('
set @sql = @sql + N'Select Top('+ ltrim(str(@startRow)) + ') StudentId from Student '

if @whereExpression is not null and @whereExpression <> N''
begin
   set @sql = @sql + N' where ' + @whereExpression
end
set @sql = @sql + ')'

--print @sql
execute sp_executesql @sql

go

现在就能在条件检索的情况下也实现分页了。下面的问题是，如果使用GridView时需要支持排序功能，那么还需要添加对排序的支持。其实这个问题就比较简单了，只要增加一个排序参数就可以了。

create procedure GetStudentPaged
    @startRow int,
    @maxmimumRows int,
    @whereExpression nvarchar(512),
    @sortExpression nvarchar(512)
as
--存储SQL语句的字符串
declare @sql nvarchar(max)

set @sql = N'Select Top(' + ltrim(str(@maxmimumRows)) + N') * '
set @sql = @sql + N' from Student where '

--判断是否有查询条件
if @whereExpression is not null and @whereExpression <> N''
begin
   set @sql = @sql + @whereExpression + N' and '
end

set @sql = @sql + N'StudentId NOT IN ('
set @sql = @sql + N'Select Top('+ ltrim(str(@startRow)) + ') StudentId from Student '

if @whereExpression is not null and @whereExpression <> N''
begin
   set @sql = @sql + N' where ' + @whereExpression
end

if @sortExpression is not null and @sortExpression <> N''
begin
   set @sql = @sql + N' order by ' + @sortExpression
end

set @sql = @sql + ')'

if @sortExpression is not null and @sortExpression <> N''
begin
   set @sql = @sql + N' order by ' + @sortExpression
end

--print @sql
execute sp_executesql @sql

go

这里给出我使用Student表定义

create table Student (
   StudentId            uniqueidentifier     not null,
   Name                 varchar(128)         not null,
   Sex                  bit                  not null,
   BirthDate            datetime             not null,
   Nation               varchar(128)         not null,
   NativePlace          varchar(128)         not null,
   Address              varchar(256)         null,
   Photo                image                null,
   Memo                 varchar(512)         null,
   StudentStatus        varchar(56)          null
)
go

使用T-SQL产生大量数据

declare @i int
set @i = 0;
while(@i<10000)
begin
  insert into Student(StudentId,[Name],Sex,BirthDate,
      Nation,NativePlace,[Address],Photo,Memo,StudentStatus)
     values(NewId(),'Rain' + str(@i),floor(rand(2)),DateAdd(year,-10,getdate()),'汉族','四川','Now address',null,'test','在读')
  set @i = @i + 1
end

2、使用临时表实现

根据筛选、排序条件将符合条件的数据保存到临时表，并在临时表中增加一个自动增长的辅助列，用于获取指定范围的数据。

use TeachMis;
go

if object_id('GetStudentPaged') is not null
    drop procedure GetStudentPaged;
go

create procedure GetStudentPaged
    @startRow int,
    @maxmimumRows int,
    @whereExpression nvarchar(512),
    @sortExpression nvarchar(512)
as

if (object_id('tempdb..#Student') is not null)
    drop table #Student;
go

--产生没有记录的临时表
SELECT identity(int,1,1) as RowId
,[StudentId]
,[Name]
,[Sex]
,[BirthDate]
,[Nation]
,[NativePlace]
,[Address]
,[Photo]
,[Memo]
,[StudentStatus]
into #Student
FROM [Student] where 1=0

declare @sql nvarchar(max)
set @sql = 'insert into #Student
SELECT
[StudentId]
,[Name]
,[Sex]
,[BirthDate]
,[Nation]
,[NativePlace]
,[Address]
,[Photo]
,[Memo]
,[StudentStatus]
FROM [Student]'

if @whereExpression is not null and @whereExpression <> ''
begin
    set @sql = @sql + ' where ' + @whereExpression
end

if @sortExpression is not null and @sortExpression <> ''
begin
  set @sql = @sql + 'order by ' + @sortExpression
end

execute sp_executesql @sql

select
[StudentId]
,[Name]
,[Sex]
,[BirthDate]
,[Nation]
,[NativePlace]
,[Address]
,[Photo]
,[Memo]
,[StudentStatus]
from #Student
where RowId between @startRow and @startRow + @maximumRows -1
order by RowId

go

3.使用 SQL Server 2005 的 CTE 表达式实现。

具体可以参考 Scott Mitchell 文章 Efficiently Paging Through Large Amounts of Data