ASP.NET经典分页

本分页方式采用存储过程分页，性能非常好，适用于大型网站，本方法执行的结果分为两部分，第一部分是数据显示，第二部分是翻页功能。两个组合在一起形成强大的大量数据翻页功能(此方法具有通用性)

如有疑问，可以给我提问，我会经常来博客，如果有什么更好的方法也希望大家一起分享，共同进步

一：存储过程

代码

 1 
 2 
 3  set ANSI_NULLS ON
 4  set QUOTED_IDENTIFIER ON
 5  go
 6 
 7 ALTER PROCEDURE [dbo].[GetRecordFromPage] 
 8     @SelectList   VARCHAR(1000),   --待选择字段列表
 9     @TableSource        VARCHAR(100),    --表名或视图表
10     @SearchCondition    VARCHAR(2000),   --查询条件
11     @OrderExpression VARCHAR(100),   --排序表达式
12     @PageIndex   INT = 1,   --页号,从0开始
13     @PageSize   INT = 10,        --页尺寸
14  @ResultCount  BIGINT = 0 Output--总记录数
15 AS
16 BEGIN
17     IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ''
18     BEGIN
19         SET @SelectList = '*'
20     END
21 
22     SET @SearchCondition = ISNULL(@SearchCondition,'')
23     SET @SearchCondition = LTRIM(RTRIM(@SearchCondition))
24     IF @SearchCondition <> ''
25     BEGIN
26         IF UPPER(SUBSTRING(@SearchCondition,1,5)) <> 'WHERE'
27         BEGIN
28             SET @SearchCondition = 'WHERE ' + @SearchCondition
29         END
30     END
31 
32     SET @OrderExpression = ISNULL(@OrderExpression,'')
33     SET @OrderExpression = LTRIM(RTRIM(@OrderExpression))
34     IF @OrderExpression <> ''
35     BEGIN
36         IF UPPER(SUBSTRING(@OrderExpression,1,5)) <> 'WHERE'
37         BEGIN
38             SET @OrderExpression = 'ORDER BY ' + @OrderExpression
39         END
40     END
41 
42     IF @PageIndex IS NULL OR @PageIndex < 1
43     BEGIN
44         SET @PageIndex = 1
45     END
46 
47     IF @PageSize IS NULL OR @PageSize < 1
48     BEGIN
49         SET @PageSize = 10
50     END
51 
52     DECLARE @SqlQuery VARCHAR(8000),
53    @SQLCount NVARCHAR(4000)
54 
55     SET @SqlQuery='SELECT ' + Replace(@SelectList, @TableSource, 'RowNumberTableSource') + '
56     FROM 
57         (SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber 
58           FROM '+@TableSource+' '+ @SearchCondition +') AS RowNumberTableSource 
59     WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR) 
60     + ' AND ' + 
61     CAST((@PageIndex * @PageSize) AS VARCHAR)
62 
63     IF @SearchCondition <> ''
64     BEGIN
65   SET @SQLCount = 'SELECT @a=COUNT(1) FROM ' + @TableSource + ' ' + @SearchCondition
66     END
67   ELSE
68  BEGIN
69   SET @SQLCount = 'SELECT @a=COUNT(1) FROM ' + @TableSource
70  END
71 print @SqlQuery;
72     SET NOCOUNT ON
73   EXEC sp_executesql @SQLCount,N'@a BIGINT Output',@ResultCount Output
74   EXECUTE(@SqlQuery)
75     SET NOCOUNT OFF
76 END
77 
78

二.如何获取数据 (CS文件中获取数据列表的方式，并返回总数量)

代码

 1 /// <summary>
 2         /// 获得分页数据
 3         /// </summary>
 4         /// <param name="filedes">选取字段</param>
 5         /// <param name="tableName">表名称</param>
 6         /// <param name="strWhere">查询条件，可为空</param>
 7         /// <param name="filedOrder">排序表达式</param>
 8         /// <param name="pageIndex">当前页码</param>
 9         /// <param name="pageSize">每页记录数</param>
10         /// <param name="pageCount">总记录数【out】</param>
11         /// <returns></returns>
12 
13  public static DataSet GetList(string filedes, string tableName, string strWhere, string filedOrder, int pageIndex, int pageSize, out long pageCount)
14         {
15             SqlParameter parameter = new SqlParameter();
16             parameter.ParameterName = "@ResultCount";
17             parameter.Direction = ParameterDirection.Output;
18             parameter.DbType = DbType.Int64;
19 
20             SqlParameter[] parameters = {
21      new SqlParameter("@SelectList", SqlDbType.VarChar,1000),
22      new SqlParameter("@TableSource", SqlDbType.VarChar,100),
23      new SqlParameter("@SearchCondition", SqlDbType.VarChar,2000),
24      new SqlParameter("@OrderExpression", SqlDbType.VarChar, 100),
25      new SqlParameter("@PageIndex", SqlDbType.Int, 4),
26      new SqlParameter("@PageSize", SqlDbType.Int,4),
27      parameter};
28             parameters[0].Value = filedes;
29             parameters[1].Value = tableName;
30             parameters[2].Value = strWhere;
31             parameters[3].Value = filedOrder;
32             parameters[4].Value = pageIndex;
33             parameters[5].Value = pageSize;
34 
35             DataSet dataSet = DbHelperSQL.RunProcedure("GetRecordFromPage", parameters, tableName);
36             object obj = parameters[6].Value;
37             if (obj != null)
38             {
39                 pageCount = Convert.ToInt64(obj);
40             }
41             else
42             {
43                 pageCount = 0;
44             }
45 
46             return dataSet;
47         }
48

三.如何定义前台的翻页方式？

代码

 1 /// <summary>
 2         /// 简单翻页代码(count:数据总数量，由上面的方法返回，在页面上用参数接收，在下面会提到这个参数)
 3 
 4         ///page:当前页码； pagesize：每页大小(自定义)；claName：样式名称，翻页功能的样式名称，最下面会提供一　　　　        ///个a12，也可以自己写;pageUrl：要实现翻页功能的页面名称；curClaName:最下面提供一个a9的样式，也可以自　　        ///己写  
 5         /// </summary>
 6         /// <returns></returns>
 7         public static string NextPageHtml(long count, int page, int pageSize, string claName(a12), string pageUrl, string curClaName)
 8         {
 9             int pageCount = 0;// 页面总数
10             int nextPage = page + 1;
11             int previousPage = 1;
12 
13             if ((count % pageSize) == 0)
14             {
15                 pageCount = (int)(count / pageSize);
16             }
17             else
18             {
19                 pageCount = (int)(count / pageSize + 1);
20             }
21             if (pageCount == 0) { pageCount = 1; }
22             if (page - 1 < 1)
23             {
24                 previousPage = 1;
25             }
26             else
27             {
28                 previousPage = page - 1;
29             }
30 
31             // 如果当前页码大于总数，则当前页码默认为总数
32             if (page > pageCount)
33                 page = pageCount;
34 
35             // 如果下一页页码大于总数，则下一页页码默认为总数
36             if (nextPage > pageCount)
37                 nextPage = pageCount;
38 
39             StringBuilder strBuilder = new StringBuilder();
40 
41             strBuilder.AppendFormat("<div class='{0}'>", claName);
42             pageUrl = Regex.Replace(pageUrl, "[&?]page=\\d*", "");
43             string previousString = String.Empty;
44             string nextString = String.Empty;
45             string endString = String.Empty;
46 
47             if (page == 1)
48             {
49                 strBuilder.Append("首页 ");
50             }
51             else
52             {
53                 strBuilder.AppendFormat("<a href='{0}' target='_self'>首页</a> ", pageUrl);
54             }
55 
56             // 第一页外的链接地址
57             if (pageUrl.IndexOf('?') > 0)
58             {
59                 pageUrl += "&page=";
60             }
61             else
62             {
63                 pageUrl += "?page=";
64             }
65 
66             if (pageCount == 1)
67             {
68                 previousString = "上一页 ";
69                 nextString = "下一页 ";
70                 endString = "尾页 ";
71             }
72             else
73             {
74                 if (page == 1)
75                     previousString = "上一页 ";
76                 else
77                     previousString = "<a href='" + pageUrl + previousPage.ToString() + "' target='_self'>上一页</a> ";
78 
79                 if (page == pageCount)
80                     nextString = "下一页 ";
81                 else
82                     nextString = "<a href='" + pageUrl + nextPage.ToString() + "' target='_self'>下一页</a> ";
83 
84                 if (page == pageCount)
85                     endString = "尾页 ";
86                 else
87                     endString = "<a href='" + pageUrl + pageCount.ToString() + "' target='_self'>尾页</a> "; ;
88             }
89 
90             strBuilder.AppendFormat("{3}{4}{5}共<span class='{6}'>{0}</span>项 第<b><span class='{6}'>{1}</span>/{2}</b>页</div>", count, page, pageCount, previousString, nextString, endString, curClaName);
91 
92             return strBuilder.ToString();
93         }
94 
95

四：前台的后台文件cs文件中如何使用(此处提供一例)

代码

 1 /// <summary>
 2         /// 回答列表
 3         /// </summary>
 4         private void ReplyListBind()
 5         {
 6             #region ui:此处的UI可以把美工做的前台直接放入此处，其中需要动态显示的用参数显示
 7 
 8             const string ui = @"<tr>
 9                           <td width='569' height='24' align='left' valign='middle' bgcolor='#E1E1E1'><span class='a10'>&nbsp;{0}{1} <span class='a2'>&nbsp;{2}</span>&nbsp; </span><span class='a6'>发表于{3}</span></td>
10                         </tr>
11                         <tr>
12                           <td height='34' align='left' valign='middle' class='css'>&nbsp;{4}</td>
13                         </tr>";
14 
15             #endregion
16 
17             StringBuilder strBuild = new StringBuilder();
18             long pageCount = 0;
19             int pageSize = 5;
20             int page = FormatString.ConvertInt(PageHelp.GetRequestAllNoFiltrate("page", ""), 1);
21             QuestionReply teacher = new QuestionReply();
22 
23             //调用上面获取列表的方法获取分页数据
24             List<Model.QuestionReply> list = teacher.GetList("where qid="+qid, "IsInvited,ReplyDate desc", pageSize, page, out pageCount);
25             if (list.Count > 0)
26             {
27                 foreach (Model.QuestionReply model in list)
28                 {//此处给UI负值，不知道大家这种用法用过没有，不过很好用，建议多用用
29                     strBuild.AppendFormat(ui, model.TrueName, model.IsInvited ? "(老师)" : "", model.SchoolName, model.ReplyTime, model.Contents);
30                 }
31                 list.Clear();
32             }
33             else
34             {
35                 strBuild.Append("<tr><td height=\"30\" colspan=\"3\"class=\"css\">暂无数据</td></tr>");
36             }
37             //ltrReplyList是前台显示数据的控件asp:Literal控件
38             this.ltrReplyList.Text = strBuild.ToString();
39 
40             //ltrPageHTML是前台显示分页模块的控件asp:Literal控件(此处调用上面的显示分页控件的方法)
41 
42             //"Test.aspx?qid="+ID,     Test.aspx是要分页的页面名称，qid代表参数，如果你跳转到这个页面有参数你就  带过去,本方法中teacher.GetList（）方法中的第一个参数就是代表你传过去的参数，作为查询语句的where条件
43             this.ltrPageHTML.Text = NextPageHtml(pageCount, page, pageSize, "a12", "Test.aspx?qid=" +qid, "a9");
44         }
45 
46

五.在pageload事件中调用ReplyListBind()即可。

此处提供一下提供的样式：

代码

 1 .a9 {
 2 font-family: "宋体";
 3 font-size: 12px;
 4 color: #FF8C00;
 5 }
 6 
 7 .a12 {
 8 font-family: "宋体";
 9 font-size: 12px;
10 color: #525252;
11 }
12 .a12 a{text-decoration: none;
13 color:#525252;
14 text-decoration:underline;
15 }
16 .a12 a:hover {color: #525252;
17  text-decoration:
18 underline;}
19

posted on 2010-08-04 11:42 第一印象阅读(515) 评论(1) 编辑收藏举报