ASP.NET和MSSQL高性能分页

大家好，又见面了，我是全栈君，今天给大家准备了Idea注册码。

此处内容已经被作者隐藏，请输入验证码查看内容

请关注本站微信公众号，回复“”，获取验证码。在微信里搜索“”或者“”或者微信扫描右侧二维码都可以关注本站微信公众号。

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

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;