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