ASP.NET和MSSQL高性能分页

首先是存储过程,只取出我需要的那段数据,如果页数超过数据总数,自动返回最后一页的纪录:

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO-- =============================================-- 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 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代码方法):

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.cnblogs.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     }

调用方法:

//得到分页设置并放入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;