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 != '' )BEGINif( @CurrentPage = 1 ) set @strFilters = ' WHERE ( ' + @Filter + ' ) 'ELSE set @strFilters = ' ( ' + @Filter + ' ) 'ENDif( @Sort IS NOT NULL AND @Sort != '' )BEGINset @strSort = ' ORDER BY ' + @SortENDif( @Group IS not null AND @Group != '' )BEGIN set @strGroup = ' Group By ' + @GroupENDELSEBEGIN set @strGroup = ' 'END/*纠正查询字段信息*/if( @Fields IS NULL OR @Fields = '' )set @Fields = ' * '/*存储每页提取的记录数*/DECLARE @strTop nvarchar( 10 )set @strTOP = Convert( nvarchar(4),@PageSize )print @strTOPprint @strGroup/*如果是第一页,则直接提取*/if( @CurrentPage = 1 )BEGIN set @strSQL = 'SELECT Top ' +@strTop + ' ' + @Fields + ' FROM ' + @Tables + @strFilters + @strSort + @strGroup ENDELSEBEGINset @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 + ' ) ) ' + @strSortENDprint @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以来觉得最好用的一种分页控件方法，使用存储过程分页大大的提高了数据访问效率。