ASP.NET和MSSQL高性能分页
首先是存储过程,只取出我需要的那段数据,如果页数超过数据总数,自动返回最后一页的纪录:
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;
