[轉貼] ASP.NET和MSSQL高性能分頁

出處：http://www.cnblogs.com/roucheng/p/3519180.html

首先是存儲過程,只取出我需要的那段數據,如果頁數超過數據總數,自動返回最後一頁的紀錄:
set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Clear
-- Create date: 2007-01-30
-- Description: 高性能分頁
-- http://www.cnblogs.com/roucheng/
-- =============================================
Alter PROCEDURE [dbo].[Tag_Page_Name_Select]
-- 傳入最大顯示紀錄數和當前頁碼
    @MaxPageSize int,
    @PageNum int,
-- 設置一個輸出參數返回總紀錄數供分頁列表使用
    @Count int output
AS
BEGIN
    SET NOCOUNT ON;

  DECLARE
-- 定義排序名稱參數
        @Name nvarchar(50),
-- 定義游標位置
        @Cursor int
-- 首先得到紀錄總數
  Select @Count = count(tag_Name)
    FROM [viewdatabase0716].[dbo].[view_tag];
-- 定義游標需要開始的位置
    Set @Cursor = @MaxPageSize*(@PageNum-1)+1
-- 如果游標大於紀錄總數將游標放到最後一頁開始的位置
    IF @Cursor > @Count
    BEGIN
-- 如果最後一頁與最大每次紀錄數相等，返回最後整頁
        IF @Count % @MaxPageSize = 0
        BEGIN
            IF @Cursor > @MaxPageSize
                Set @Cursor = @Count - @MaxPageSize + 1
            ELSE
                Set @Cursor = 1
        END
-- 否則返回最後一頁剩下的紀錄
        ELSE
            Set @Cursor = @Count - (@Count % @MaxPageSize) + 1
    END
-- 將指針指到該頁開始
    Set Rowcount @Cursor
-- 得到紀錄開始的位置
    Select @Name = tag_Name
    FROM [viewdatabase0716].[dbo].[view_tag]
    orDER BY tag_Name;
-- 設置開始位置
    Set Rowcount @MaxPageSize
-- 得到該頁紀錄
        Select * 
        From [viewdatabase0716].[dbo].[view_tag]
        Where tag_Name >= @Name
        order By tag_Name

    Set Rowcount 0
END

然後是分頁控件(... 為省略的生成HTML代碼方法):
1 using System.Data;

  2 using System.Configuration;
  3 using System.Web;
  4 using System.Web.Security;
  5 using System.Web.UI;
  6 using System.Web.UI.WebControls;
  7 using System.Web.UI.WebControls.WebParts;
  8 using System.Web.UI.HtmlControls;
  9 using System.Text;
 10 
 11 /// <summary>
 12 /// 擴展連接字符串
 13 /// </summary>
 14 public class ExStringBuilder
 15 {
 16     private StringBuilder InsertString;
 17     private StringBuilder PageString;
 18     private int PrivatePageNum = 1;
 19     private int PrivateMaxPageSize = 25;
 20     private int PrivateMaxPages = 10;
 21     private int PrivateCount;
 22     private int PrivateAllPage;
 23     public ExStringBuilder()
 24     {
 25         InsertString = new StringBuilder("");
 26     }
 27     /// <summary>
 28     /// 得到生成的HTML
 29     /// </summary>
 30     public string GetHtml
 31     {
 32         get
 33         {
 34             return InsertString.ToString();
 35         }
 36     }
 37     /// <summary>
 38     /// 得到生成的分頁HTML
 39     /// </summary>
 40     public string GetPageHtml
 41     {
 42         get
 43         {
 44             return PageString.ToString();
 45         }
 46     }
 47     /// <summary>
 48     /// 設置或獲取目前頁數
 49     /// </summary>
 50     public int PageNum
 51     {
 52         get
 53         {
 54             return PrivatePageNum;
 55         }
 56         set
 57         {
 58             if (value >= 1)
 59             {
 60                 PrivatePageNum = value;
 61             }
 62         }
 63     }
 64     /// <summary>
 65     /// 設置或獲取最大分頁數
 66     /// </summary>
 67     public int MaxPageSize
 68     {
 69         get
 70         {
 71             return PrivateMaxPageSize;
 72         }
 73         set
 74         {
 75             if (value >= 1)
 76             {
 77                 PrivateMaxPageSize = value;
 78             }
 79         }
 80     }
 81     /// <summary>
 82     /// 設置或獲取每次顯示最大頁數
 83     /// </summary>
 84     public int MaxPages
 85     {
 86         get
 87         {
 88             return PrivateMaxPages;
 89         }
 90         set
 91         {
 92             PrivateMaxPages = value;
 93         }
 94     }
 95     /// <summary>
 96     /// 設置或獲取數據總數
 97     /// </summary>
 98     public int DateCount
 99     {
100         get
101         {
102             return PrivateCount;
103         }
104         set
105         {
106             PrivateCount = value;
107         }
108     }
109     /// <summary>
110     /// 獲取數據總頁數
111     /// </summary>
112     public int AllPage
113     {
114         get
115         {
116             return PrivateAllPage;
117         }
118     }
119     /// <summary>
120     /// 初始化分頁
121     /// </summary>
122     public void Pagination()
123     {
124         PageString = new StringBuilder("");
125 //得到總頁數
126         PrivateAllPage = (int)Math.Ceiling((decimal)PrivateCount / (decimal)PrivateMaxPageSize);
127 //防止上標或下標越界
128         if (PrivatePageNum > PrivateAllPage)
129         {
130             PrivatePageNum = PrivateAllPage;
131         }
132 //滾動游標分頁方式
133         int LeftRange, RightRange, LeftStart, RightEnd;
134         LeftRange = (PrivateMaxPages + 1) / 2-1;
135         RightRange = (PrivateMaxPages + 1) / 2;
136         if (PrivateMaxPages >= PrivateAllPage)
137         {
138             LeftStart = 1;
139             RightEnd = PrivateAllPage;
140         }
141         else
142         {
143             if (PrivatePageNum <= LeftRange)
144             {
145                 LeftStart = 1;
146                 RightEnd = LeftStart + PrivateMaxPages - 1;
147             }
148             else if (PrivateAllPage - PrivatePageNum < RightRange)
149             {
150                 RightEnd = PrivateAllPage;
151                 LeftStart = RightEnd - PrivateMaxPages + 1;
152             }
153             else
154             {
155                 LeftStart = PrivatePageNum - LeftRange;
156                 RightEnd = PrivatePageNum + RightRange;
157             }
158         }
159 
160 //生成頁碼列表統計
161         PageString.Append(...);
162 
163         StringBuilder PreviousString = new StringBuilder("");
164 //如果在第一頁
165         if (PrivatePageNum > 1)
166         {
167             ...
168         }
169         else
170         {
171             ...
172         }
173 //如果在第一組分頁
174         if (PrivatePageNum > PrivateMaxPages)
175         {
176             ...
177         }
178         else
179         {
180             ...
181         }
182         PageString.Append(PreviousString);
183 //生成中間頁 http://www.cnblogs.com/roucheng/
184         for (int i = LeftStart; i <= RightEnd; i++)
185         {
186 //為當前頁時
187             if (i == PrivatePageNum)
188             {
189                 ...
190             }
191             else
192             {
193                 ...
194             }
195         }
196         StringBuilder LastString = new StringBuilder("");
197 //如果在最後一頁
198         if (PrivatePageNum < PrivateAllPage)
199         {
200             ...
201         }
202         else
203         {
204             ...
205         }
206 //如果在最後一組
207         if ((PrivatePageNum + PrivateMaxPages) < PrivateAllPage)
208         {
209             ...
210         }
211         else
212         {
213             ...
214         }
215         PageString.Append(LastString);
216     }
217     /// <summary>
218     /// 生成Tag分類表格
219     /// </summary>
220     public void TagTable(ExDataRow myExDataRow)
221     {
222         InsertString.Append(...);
223     }

調用方法:
1 //得到分頁設置並放入Session

 2         ExRequest myExRequest = new ExRequest();
 3         myExRequest.PageSession("Tag_", new string[] { "page", "size" });
 4 //生成Tag分頁
 5         ExStringBuilder Tag = new ExStringBuilder();
 6         //設置每次顯示多少條紀錄
 7         Tag.MaxPageSize = Convert.ToInt32(Session["Tag_size"]);
 8         //設置最多顯示多少頁碼
 9         Tag.MaxPages = 9;
10         //設置當前為第幾頁
11         Tag.PageNum = Convert.ToInt32(Session["Tag_page"]);
12         string[][] myNamenValue = new string[2][]{
13             new string[]{"MaxPageSize","PageNum","Count"},
14             new string[]{Tag.MaxPageSize.ToString(),Tag.PageNum.ToString()}
15         };
16 //調用存儲過程
17         DataTable myDataTable = mySQL.BatchGetDB("Tag_Page_Name_Select", myNamenValue, "Count");
18         Tag.DateCount = (int)mySQL.OutputCommand.Parameters["@Count"].Value;
19         Tag.Pagination();
20 
21         HeadPage.InnerHtml = FootPage.InnerHtml = Tag.GetPageHtml;
22 
23         for (int i = 0, j = myDataTable.Rows.Count; i < j; i++)
24         {
25             Tag.TagTable(new ExDataRow(myDataTable.Rows));
26         }
27         TagBox.InnerHtml = Tag.GetHtml;