程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQLServer2005及以上存儲過程分頁方法

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

編輯:關於SqlServer

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

      在項目開發過程中,往往會遇到展示展示內容的問題。當內容數量不多的時候,我們直接用一條“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 SqlParameter("@pageIndex", SqlDbType.Int);             pageIndexParameter.Value = pageIndex;             sqlCommand.Parameters.Add(pageIndexParameter);               SqlParameter pageSizeParameter = new SqlParameter("@pageSize", SqlDbType.Int);             pageSizeParameter.Value = pageSize;             sqlCommand.Parameters.Add(pageSizeParameter);               SqlParameter recordCountParameter = new SqlParameter("@recordCount", SqlDbType.Int);             recordCountParameter.Direction = ParameterDirection.ReturnValue; sqlCommand.Parameters.Add(recordCountParameter);               connection.Open();               SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);             sqlDataAdapter.Fill(dataSet);               recordCount = Convert.ToInt32(recordCountParameter.Value);         }     }     catch (Exception)     {         dataSet = null;         recordCount = 0;     }       return dataSet; }

      總結:個人認為這個存儲過程在小型項目中,還是比較實用的。發現問題請一定直言不諱,歡迎討論交流。

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved