此分页控件是采用后台存储过程分页,每次只取一页的数据。当然,物不尽美,同样数据量很大的时候,也会增加数据库的负担,因每次翻页的时候,都要与数据库进行交互!
一、存储过程
CREATE PROCEDURE [dbo].[PageData_P]
@sqlstr nvarchar(3500), --SQL字符串
@curpage int, --第N页
@pagesize int --每页行数
AS
begin
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int, @pagecount int
SET @sqlstr = RTRIM(@sqlstr)
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
set @pagecount = ceiling(1.0*@rowcount/@pagesize)
if @curpage<=1
set @curpage =1
if @curpage>@pagecount
set @curpage=@pagecount
select @rowcount as Total, @pagecount as PageCount,@curpage as CurPage
set @curpage=(@curpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@curpage,@pagesize
exec sp_cursorclose @P1
end
GO
二、在项目中新增一用户控件,命名为Pager.ascx
1、页面代码为:
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
<%
@ Control Language="c#" AutoEventWireup="false" Codebehind="Pager.ascx.cs" Inherits="IncludeLib.UserControl.Pager" TargetSchema="http://schemas.microsoft.com/intellisense/ie5" %>
<asp:Table width="100%" runat="server" id="tblPager" HorizontalAlign="Center">
<asp:TableRow>
<asp:TableCell HorizontalAlign="Center">
<asp:linkbutton id="btnFirst" runat="server" CommandArgument="fist" Font-Size="10">首页</asp:linkbutton> |
<asp:linkbutton id="btnPrev" runat="server" CommandArgument="prev" Font-Size="10">
上一页</asp:linkbutton> |
<asp:linkbutton id="btnNext" runat="server" CommandArgument="next" Font-Size="10">
下一页</asp:linkbutton> |
<asp:linkbutton id="btnLast" runat="server" CommandArgument="last" Font-Size="10">
末页</asp:linkbutton> <FONT size="2">到第
<asp:textbox id="txtGoPage" runat="server" MaxLength="4" AutoPostBack="True" Width="36px">1</asp:textbox>页
</FONT><FONT size="2">第
<asp:label id="lblCurrentIndex" runat="server">1</asp:label>页/共
<asp:label id="lblPageCount" runat="server">0</asp:label>页(
<asp:label id="lblRecordCount" runat="server">0</asp:label>)条 </FONT>
</asp:TableCell>
</asp:TableRow>
</asp:Table>
2、CS代码部分:
namespace MyLibrary.UserControl
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.ComponentModel;
using System.Web.UI;
using System.Collections;
public class Pager : System.Web.UI.UserControl
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
变量定义#region 变量定义
protected System.Web.UI.WebControls.LinkButton btnFirst;
protected System.Web.UI.WebControls.LinkButton btnPrev;
protected System.Web.UI.WebControls.LinkButton btnNext;
protected System.Web.UI.WebControls.LinkButton btnLast;
protected System.Web.UI.WebControls.TextBox txtGoPage;
protected System.Web.UI.WebControls.Label lblCurrentIndex;
protected System.Web.UI.WebControls.Label lblRecordCount;
protected System.Web.UI.WebControls.Table tblPager;
protected System.Web.UI.WebControls.Label lblPageCount;
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
数据绑定事件#region 数据绑定事件
//public delegate void DataListPageChangedEventHandler(object sender , DataGridPageChangedEventArgs e);
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 数据绑定事件
/// </summary>
public delegate void DataBindEventHandler();
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
初始化网页#region 初始化网页
private void Page_Load(object sender, System.EventArgs e)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
// 在这里放置使用者程序代码以初始化网页
if(!Page.IsPostBack)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
txtGoPage.Attributes.Add("onclick", "this.select()");
txtGoPage.Style.Add("text-align", "center");
}
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
Web Form 设计工具产生的程序代码#region Web Form 设计工具产生的程序代码
override protected void OnInit(EventArgs e)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//
// CODEGEN: 此为 ASP.NET Web Form 设计工具所需的调用。
//
this.PageIndexChanged+=new DataGridPageChangedEventHandler(OnPageIndexChanged);
InitializeComponent();
base.OnInit(e);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 此为设计工具支持所必须的方法 - 请勿使用程序代码编辑器修改
/// 这个方法的内容。
/// </summary>
private void InitializeComponent()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Load += new System.EventHandler(this.Page_Load);
this.btnFirst.Click +=new EventHandler(btnFirst_Click);
this.btnLast.Click +=new EventHandler(btnLast_Click);
this.btnNext.Click +=new EventHandler(btnNext_Click);
this.btnPrev.Click +=new EventHandler(btnPrev_Click);
this.txtGoPage.TextChanged +=new EventHandler(txtGoPage_TextChanged);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
变量定义#region 变量定义
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 绑定数据
/// </summary>
public event DataBindEventHandler BindData;
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 换页事件
/// </summary>
private event DataGridPageChangedEventHandler PageIndexChanged;
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
第一页#region 第一页
private void btnFirst_Click(object sender, System.EventArgs e)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
int intNewPage = 1;
this.PageIndexChanged(this, new DataGridPageChangedEventArgs(sender, intNewPage));
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
前一页#region 前一页
private void btnPrev_Click(object sender, System.EventArgs e)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
int intNewPage = Convert.ToInt32(lblCurrentIndex.Text) - 1;
this.PageIndexChanged(this, new DataGridPageChangedEventArgs(sender, intNewPage));
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
下一页#region 下一页
private void btnNext_Click(object sender, System.EventArgs e)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
int intNewPage = Convert.ToInt32(lblCurrentIndex.Text) + 1;
this.PageIndexChanged(this, new DataGridPageChangedEventArgs(sender, intNewPage));
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
最后一页#region 最后一页
private void btnLast_Click(object sender, System.EventArgs e)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
int intNewPage = Convert.ToInt32(lblPageCount.Text);
this.PageIndexChanged(this, new DataGridPageChangedEventArgs(sender, intNewPage));
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
改变第几页的textbox值#region 改变第几页的textbox值
private void txtGoPage_TextChanged(object sender, System.EventArgs e)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
int intNewPage = Convert.ToInt32(txtGoPage.Text);
this.PageIndexChanged(this, new DataGridPageChangedEventArgs(sender, intNewPage));
}
catch
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
int intNewPage = Convert.ToInt32(lblCurrentIndex.Text);
this.PageIndexChanged(this, new DataGridPageChangedEventArgs(sender, intNewPage));
}
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
设定link是否可以点选#region 设定link是否可以点选
protected override void Render(System.Web.UI.HtmlTextWriter writer)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if(this.CurrentPageIndex<=1)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
btnFirst.Enabled = false;
btnPrev.Enabled = false;
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
btnFirst.Enabled = true;
btnPrev.Enabled = true;
}
if(this.CurrentPageIndex>=this.PageCount)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
btnNext.Enabled = false;
btnLast.Enabled = false;
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
btnNext.Enabled = true;
btnLast.Enabled = true;
}
if(this.PageCount<=1)
txtGoPage.Enabled = false;
else
txtGoPage.Enabled = true;
base.Render (writer);
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
取得或设定当前页#region 取得或设定当前页
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 取得或设定当前页
/// </summary>
[Category("其它"), Description("当前页"), Browsable(false), DefaultValue(1) ]
public int CurrentPageIndex
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return Convert.ToInt32(lblCurrentIndex.Text);
}
set
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
lblCurrentIndex.Text = value.ToString();
txtGoPage.Text = lblCurrentIndex.Text;
}
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
取得或设定总页数#region 取得或设定总页数
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 取得或设定总页数
/// </summary>
[Category(""), Description("总页数"), Browsable(false), DefaultValue(0) ]
public int PageCount
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return Convert.ToInt32(lblPageCount.Text);
}
set
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
lblPageCount.Text = value.ToString();
}
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
取得或设定总记录数#region 取得或设定总记录数
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 取得或设定总记录数
/// </summary>
[Category("分页"), Description("总记录数"), Browsable(false), DefaultValue(0) ]
public int RecordCount
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return Convert.ToInt32(lblRecordCount.Text);
}
set
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
lblRecordCount.Text = value.ToString();
}
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
改变页码#region 改变页码
private void OnPageIndexChanged(object source, DataGridPageChangedEventArgs e)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.CurrentPageIndex = e.NewPageIndex;
this.BindData();
}
#endregion
}
}三、用法与事例
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
使用说明--举例#region 使用说明--举例
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// Pager 的摘要描述。
/// 实现自定义分页,前端要实现BindData()事件
/// </summary>
/// <remarks>
/// <pre>
/// 例﹕
/// aspx:(如果不需要显示分页控件,可以用<!-- //-->把分页控件注释起来
///<%@ Register TagPrefix="uc1" TagName="Pager" Src="../MyLibrary/UserControl/Pager.ascx" %>
///
///<form name="Form1" id="Form1" runat="server">
///<br>
///<div align="center">
/// <p><uc1:pager id="Pager1" runat="server"></uc1:pager></p>
///</div>
///</form> 以上部分可以把该控件直接拖到页面,自动生成
/// cs:
/// protected IncludeLib.UserControl.Pager Pager1;
/// private void Page_Load(object sender, System.EventArgs e)
/// {
/// // 在这里放置使用者程序代码以初始化网页
/// if (!IsPostBack)
/// {
/// BindData();
/// }
/// this.Pager1.BindData +=new IncludeLib.UserControl.Pager.DataBindEventHandler(this.BindData);
/// }
/// private void BindData()
/// {
/// MAXFC.DataBase.DataConn dbConn=new MAXFC.DataBase.DataConn();
/// string strSql="SQL语句";
/// strSql="exec [P_PageData] '"+strSql+"','"+Pager1.CurrentPageIndex+"','10'";
/// DataSet ds = dbConn.GetDataSet(strSql);
/// DataList1.DataSource = ds.Tables[2].DefaultView;
/// DataList1.DataBind();
/// Pager1.CurrentPageIndex = Convert.ToInt32(ds.Tables[1].Rows[0]["CurPage"]);
/// Pager1.PageCount = Convert.ToInt32(ds.Tables[1].Rows[0]["PageCount"]);
/// Pager1.RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0]["Total"]);
/// }
///
/// html分页:
/// <%
/// for(int i=0;ds!=null && i<ds.Tables[2].Rows.Count;i++)
/// {
/// %>
/// <%=ds.Tables[2].Rows[i]["article_sender"].ToString()%>
/// <%
/// }
/// %>
///
/// </pre>
/// </remarks>
///
#endregion