asp.net和MSSQL高性能分頁

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

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Clear-- Create date: 2007-01-30-- Description: 高性能分頁-- http://www.survivalescaperooms.com/roucheng/-- =============================================Alter PROCEDURE [dbo].[Tag_Page_Name_Select]-- 傳入最大顯示紀錄數和當前頁碼    @MaxPageSize int,    @PageNum int,-- 設置一個輸出參數返回總紀錄數供分頁列表使用    @Count int outputASBEGIN    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 0END

然后是分頁控件(... 為省略的生成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         get101         {102             return PrivateCount;103         }104         set105         {106             PrivateCount = value;107         }108     }109     /// <summary>110     /// 獲取數據總頁數111     /// </summary>112     public int AllPage113     {114         get115         {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         else142         {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             else154             {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         else170         {171             ...172         }173 //如果在第一組分頁174         if (PrivatePageNum > PrivateMaxPages)175         {176             ...177         }178         else179         {180             ...181         }182         PageString.Append(PreviousString);183 //生成中間頁 http://www.survivalescaperooms.com/roucheng/184         for (int i = LeftStart; i <= RightEnd; i++)185         {186 //為當前頁時187             if (i == PrivatePageNum)188             {189                 ...190             }191             else192             {193                 ...194             }195         }196         StringBuilder LastString = new StringBuilder("");197 //如果在最后一頁198         if (PrivatePageNum < PrivateAllPage)199         {200             ...201         }202         else203         {204             ...205         }206 //如果在最后一組207         if ((PrivatePageNum + PrivateMaxPages) < PrivateAllPage)208         {209             ...210         }211         else212         {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;