Asp.net(C#)基于存储过程分页的完整解决方案

先贴出效果图：

 

具体过程如下：

1.分页的存储过程

 

( @Tables varchar( 1000 ) = ' ', --表名称或级联名称 @PrimaryKey varchar(100)= ' ', --主键名称 @Sort varchar(200)= ' ', --排序列,含ASC/DESC @CurrentPage int = 1, --当前页码 @PageSize int = 10, --每页显示记录数 @Fields varchar(1000) =' ', --读取的字段列表 @Filter varchar(1000)= ' ', --查询条件 @Group varchar(1000) =' ' --聚合查询信息 ) AS /*存储查询字符串*/ DECLARE @strSQL nvarchar( 2048 ) set @strSQL = ' ' /*存储查询条件*/ DECLARE @strFilters nvarchar( 1000 ) set @strFilters = ' ' /*存储排序字段*/ DECLARE @strSort nvarchar( 300 ) set @strSort = ' ' /*存储聚合查询*/ DECLARE @strGroup nvarchar( 1100 ) set @strGroup = ' ' if( @Filter IS NOT NULL AND @Filter != '' ) BEGIN if( @CurrentPage = 1 ) set @strFilters = ' WHERE ( ' + @Filter + ' ) ' ELSE set @strFilters = ' ( ' + @Filter + ' ) ' END if( @Sort IS NOT NULL AND @Sort != '' ) BEGIN set @strSort = ' ORDER BY ' + @Sort END if( @Group IS not null AND @Group != '' ) BEGIN set @strGroup = ' Group By ' + @Group END ELSE BEGIN set @strGroup = ' ' END /*纠正查询字段信息*/ if( @Fields IS NULL OR @Fields = '' ) set @Fields = ' * ' /*存储每页提取的记录数*/ DECLARE @strTop nvarchar( 10 ) set @strTOP = Convert( nvarchar(4),@PageSize ) print @strTOP print @strGroup /*如果是第一页,则直接提取*/ if( @CurrentPage = 1 ) BEGIN set @strSQL = 'SELECT Top ' +@strTop + ' ' + @Fields + ' FROM ' + @Tables + @strFilters + @strSort + @strGroup END ELSE BEGIN set @strSQL = 'SELECT Top ' + convert( nvarchar(4),@PageSize ) + ' ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strFilters + ' AND (' + @PrimaryKey + ' NOT IN ( SELECT TOP ' + Convert( nvarchar(4),( ( @CurrentPage - 1 ) * @PageSize ) ) + ' ' + @PrimaryKey + ' FROM ' + @Tables + ' WHERE ' + @strFilters + @strSort + ' ) ) ' + @strSort END print @strSQL /*执行查询*/ exec sp_executesql @strSQL

 

2.分页的C# 源代码，注意：要继承 Control 类，相应的可以看注释。

 

public class C_UserPage : Control { /// <summary> /// 设置每一个页面显示的条数 /// </summary> public int EachPageItemCount = 0; /// <summary> /// 设置当前页 /// </summary> public int CurrentPage = 0; /// <summary> /// 设置总条目数 /// </summary> public int ItemCount = 0; /// <summary> /// 设置分页后的总页数 /// </summary> private int AllPageCount { get; set; } /// <summary> /// 设置第一页 /// </summary> private int FirstPage { get { return 1; } } /// <summary> /// 设置最后页 /// </summary> private int LastPage { get { return AllPageCount; } } /// <summary> /// 设置上一页 /// </summary> private int BackPage { get { return CurrentPage < 2 ? 1 : (CurrentPage - 1); } } /// <summary> /// 设置下一页 /// </summary> private int NextPage { get { return CurrentPage == AllPageCount ? AllPageCount : CurrentPage + 1; } } private int _PageNumber; /// <summary> /// 设置每一页显示的数字个数 必须大于0且为偶数 /// </summary> public int PageNumber { get { if (_PageNumber < 2) { return 0; } else if (_PageNumber % 2 != 0) { return _PageNumber - 1; } else { return _PageNumber; } } set { _PageNumber = value; } } private bool _IndexAndLastState = true; /// <summary> /// 是否显示首尾页 默认显示 /// </summary> public bool IndexAndLastState { get { return _IndexAndLastState; } set { _IndexAndLastState = value; } } private bool _NumberState = true; /// <summary> /// 是否显示数字分页 默认显示 /// </summary> public bool NumberState { get { return _NumberState; } set { _NumberState = value; } } private bool _PageInfoState = true; /// <summary> /// 是否显示分页信息（如页数 条数） /// </summary> public bool PageInfoState { get { return _PageInfoState; } set { _PageInfoState = value; } } /// <summary> /// 设置当前页面的Url /// </summary> public string PageUrl = ""; /// <summary> /// 重写Render方法 将需要信息写入html控件 /// </summary> /// <param name="writer"></param> protected override void Render(HtmlTextWriter writer) { if (ItemCount > EachPageItemCount) { AllPageCount = (ItemCount % EachPageItemCount) < 1 ? ItemCount / EachPageItemCount : (ItemCount / EachPageItemCount) + 1;//获取总页码数 } else { AllPageCount = 1; } int p = 0;/*页码（1 2 3 4 5.....）*/ string strText = string.Empty; for (int i = 1; i < PageNumber; i++) { if (CurrentPage <= (AllPageCount - PageNumber / 2)) { if (CurrentPage >= PageNumber / 2) { p = (CurrentPage + i) - PageNumber / 2; } else/*定义当当前页小于每页页码个数/2时同样显示出定义的页码个数*/ { int j = CurrentPage; p = CurrentPage + i - j; j++; } } else { int j = -1; p = AllPageCount - PageNumber + i - j; j++; } if (p > 0 && p <= AllPageCount) { if (p == CurrentPage) { strText += "<strong>" + p + "</strong>"; } else { strText += "<a href="/" mce_href="/""" + PageUrl + p + "/">" + p + "</a>"; } } } StringBuilder sb = new StringBuilder(); if (IndexAndLastState) { sb.Append("<a href="/" mce_href="/""" + PageUrl + FirstPage + "/" class=/"songti/"><<首页</a>"); } sb.Append("<a href="/" mce_href="/""" + PageUrl + BackPage + "/" class=/"songti/"><上一页</a>"); if (NumberState) { sb.Append(strText); } sb.Append("<a href="/" mce_href="/""" + PageUrl + NextPage + "/" class=/"songti/">下一页></a>"); if (IndexAndLastState) { sb.Append("<a href="/" mce_href="/""" + PageUrl + LastPage + "/" class=/"songti/">尾页>></a>"); } if (PageInfoState) { sb.Append("<span>共" + AllPageCount + "页 " + ItemCount + "条信息</span>"); } writer.Write(sb); base.Render(writer); } }

 

3. 从前一页面的URL传参数要这样传；

 

<asp:HyperLink ID="hlink_firCatalogID" runat="server" NavigateUrl='<%# "~/IVxWorksPortal/Portal/ShowAllList.aspx?firCatalogID="+ DataBinder.Eval(Container.DataItem,"f_firCatalogID")+"&page=1" %>' Text='<%# DataBinder.Eval(Container.DataItem,"f_firCatalogName") %>'></asp:HyperLink>

 

4.前台：在需要分页的.aspx页面顶部加入如下代码；

 

 <%@ Register Assembly="IVxWorksSYS" Namespace="IVxWorksSYS.IVxWorksCode.PublicClass" TagPrefix="Page" %>

 

5.前台：然后引用控件；

 

  <div class="UserPage"> <Page:C_UserPage ID="MyPage" runat="server"> </Page:C_UserPage> </div>

 

 

6. 后台代码如下，与分页相关的有5个参数，可能要做相应修改。其中Bind_AllListByFirCatalogID()函数是从后台获取过来的数据，不再写出。

 

  IVxWorksCode.IVxWorksPortal.C_Articles_BLL c_art = new IVxWorksCode.IVxWorksPortal.C_Articles_BLL(); string firCatalogID = Request.QueryString["firCatalogID"].ToString(); c_art.FirCatalogID = firCatalogID; /*参数开始*/ MyPage.ItemCount = Convert.ToInt32(c_art.Bind_CountByFirCatalogID()); MyPage.CurrentPage = int.Parse(Request["page"].ToString()); MyPage.PageNumber = 10; MyPage.EachPageItemCount = 20; MyPage.PageUrl = "ShowAllList.aspx?firCatalogID=" + firCatalogID + "&page="; /*参数结束*/ c_art.CurrentPage = int.Parse(Request["page"].ToString()); c_art.PageSize = 20; c_art.Gview_ShowAllListByFirCatalogID = this.gview_ShowAllListByFirCatalogID; c_art.Bind_AllListByFirCatalogID();

 

7.分页控件的样式

 

.UserPage /*分页样式*/ { margin-top:5px; padding-left: 0px; font-size: 15px; color: #333333; text-align:right; font-family: Arial, Helvetica, sans-serif; } .UserPage a /*分页样式*/ { display: block; text-decoration: none; margin-right:5px; padding-top: 2px; padding-right: 5px; padding-bottom: 2px; padding-left: 5px; border: 1px solid #88C7FB; float: left; font-size: 12px; font-weight: normal; } .UserPage strong /*分页样式*/ { display: block; text-decoration: none; margin-right:5px; padding-top: 2px; padding-right: 5px; padding-bottom: 2px; padding-left: 5px; border: 1px solid #88C7FB; float: left; background-color:Silver; font-size: 14px; font-weight:bold; } .UserPage a:hover /*分页样式*/ { display: block; text-decoration: none; border: 1px solid #CCCCCC; }

 

8.结束语；

 

        这个是我用.net以来觉得最好用的一种分页控件方法，使用存储过程分页大大的提高了数据访问效率。