SQLServer2005及以上存儲過程分頁方法

 　　最近實習期間，項目開發過程中遇到了分頁問題，問題如下：

　　在項目開發過程中，往往會遇到展示展示內容的問題。當內容數量不多的時候，我們直接用一條“SELECT * FROM ...”將去不內容提取出來也無傷大雅。但是，隨著項目的不斷擴大，將過多的內容展示在一個頁面就顯得不合理了，此時，就要用到分頁技術。

　　其實分頁，包括前台分頁和後台分頁。

　　所謂前台分頁：就是一次性後天存儲設備取出多頁數據，傳到前台，再分頁展示。前台分頁的好處是：

　　1、當每次去適量數據時，取數據時間並沒有太大影響，前台分頁展示速度卻得到了明顯加快，只有當分頁到了一定頁數，再次從數據庫讀取時分頁才會有明顯停頓;

　　2、前台分頁，減少了數據庫的鏈接訪問次數，降低了數據庫的負荷。

　　其實，今天所分享的主要是後台分頁技術。這個分頁方法，是在實踐中經歷了多次慘痛的教訓後總結的。

　　主要解決了的問題：

　　1、按條件查詢分頁

　　2、多列排序分頁：分頁過程中最後取出數據的順序可有多列決定

　　3、不依賴主鍵分頁(這是之前到網上看到的分頁查詢遇到的最大瓶頸)

　　4、返回查詢總記錄數，便於分頁(在網上看到很多所謂存儲過程分頁查詢，竟然沒有返回總記錄數，不知道他們是如何實現分頁的)

　　5、分頁放在數據庫存儲過程中，減輕服務器的負擔，個人認為將分頁任務交個數據庫比較合適：第一可以避免上次代碼冗長的分頁邏輯;第二分頁速度有保證

　　本分頁查詢有這些好處，必然也有其缺陷

　　1、查詢速度不一定有絕對保證，因為我們的數據量有限，從15000+條數據中進行分頁查詢是0.158ms，但目前不知數據量擴大以後的查詢時間

　　2、查詢數據庫類型是：sqlserver2005及以上

　　我們開發的環境是：Visual Studio 2012, SQL Server 2008, 下面是sql存儲過程源代碼：(注：@sqlStr中不能有排序條件，排序條件一定要放在@sortStr裡面!!!下面有一個示例)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 CREATE PROCEDURE [dbo].[FinalSortingAndPaging]   @sqlStr varchar(2000) =  'SELECT  DISTINCT h_year , z_periods, h_periods from FertilizerHeader WHERE 1=1',--完整的sql查詢語句， @sortStr varchar(50) = 'h_year desc, z_periods desc',  --查詢排序條件 @pageIndex INT = 1, --頁號 @pageSize INT = 100 --分頁大小   AS BEGIN   -- routine body goes here, e.g.   -- SELECT 'Navicat for SQL Server'       SET NOCOUNT ON;       DECLARE @queryStr nvarchar(2000)     DECLARE @queryRecordStr nvarchar(2000)     DECLARE @recordCount INT --返回的記錄總數       SET @queryRecordStr = 'SELECT @recordCount=COUNT(*) FROM ('+@sqlStr+') AS c';       SET @queryStr = 'WITH tempTable AS (SELECT *, Row_number() OVER(ORDER BY '+@sortSt +') AS RowNumber FROM('+@sqlStr+') AS a) SELECT * FROM tempTable WHERE RowNumber BETWEEN '+ CAST((@pageIndex-1)*@pageSize + 1 AS VARCHAR(10))+' AND '+ CAST(@pageIndex * @pageSize AS VARCHAR(10)); END       EXEC sp_executesql @queryRecordStr, N'@recordCount INT OUTPUT ', @recordCount OUTPUT     EXEC(@queryStr)     --SELECT @recordCount AS RecordCount     RETURN @recordCount

　　下面再貼一個C#訪問該存儲過程的接口：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 private static readonly string connectionString = ConfigurationManager.                     ConnectionStrings["XXXX"].ToString();   public DataSet FinalSortingAndPagingQuery(string sqlStr, string sortStr,                 int pageIndex, int pageSize,out int recordCount) {     DataSet dataSet = new DataSet();     try     {         using (SqlConnection connection = new SqlConnection(connectionString))         {             SqlCommand sqlCommand = connection.CreateCommand();               // Define procedure             sqlCommand.CommandType = CommandType.StoredProcedure;             sqlCommand.CommandText = "FinalSortingAndPaging";               // SelectField sql parameter definition             SqlParameter sqlStrParameter = new SqlParameter("@sqlStr", SqlDbType.VarChar);             sqlStrParameter.Value = sqlStr;             sqlCommand.Parameters.Add(sqlStrParameter);               // attention: srot string should be like "h_year desc, z_periods desc"!!!             SqlParameter sortStrParameter = new SqlParameter("@sortStr", SqlDbType.VarChar);             sortStrParameter.Value = sortStr;             sqlCommand.Parameters.Add(sortStrParameter);               SqlParameter pageIndexParameter = new S