虽然微软提供了一个DataGrid的翻页功能,除了其外观不容易被编辑外,起性能上的开销也是很大的,比如说数据库里有几十万条数据,每页只要求显示10条记录,那每一次翻页的时候我们完全没有必要全部读出来,然后再将10条记录取出显示出来。
以下是我自己做的一个翻页控件,它是结合存储过程实现的:
存储过程:
页面:
代码:
以下是我自己做的一个翻页控件,它是结合存储过程实现的:
存储过程:
------------------------------------
--用途:分页存储过程(对有主键的表效率极高)
--说明:
--修改:继荣
--修改时间:2006-03-08
--修改说明:当@IsReCount非0时同时返回两个表,第一个为查询出的结果,第二个为总记录数
------------------------------------
CREATE PROCEDURE UP_GetRecordByPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
if @IsReCount != 0
set @strSQL = @strSQL + ' select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere
exec (@strSQL)
GO
--用途:分页存储过程(对有主键的表效率极高)
--说明:
--修改:继荣
--修改时间:2006-03-08
--修改说明:当@IsReCount非0时同时返回两个表,第一个为查询出的结果,第二个为总记录数
------------------------------------
CREATE PROCEDURE UP_GetRecordByPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
if @IsReCount != 0
set @strSQL = @strSQL + ' select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere
exec (@strSQL)
GO
页面:
<%@ Control Language="c#" AutoEventWireup="false" Codebehind="PageLetNew.ascx.cs" Inherits="QuMeiXJ.Webs.Controls.PageLetNew" TargetSchema="http://schemas.microsoft.com/intellisense/ie5" %>
当前是第<asp:label id="lblPageIndex" runat="server">1</asp:label>页 共
<asp:label id="lblPageCount" runat="server"></asp:label>页
<asp:LinkButton id="lbtnFirst" runat="server">首页</asp:LinkButton>
<asp:LinkButton id="lbtnPrev" runat="server">上一页</asp:LinkButton>
<asp:LinkButton id="lbtnNext" runat="server">下一页</asp:LinkButton>
<asp:LinkButton id="lbtnLast" runat="server">末页</asp:LinkButton>>| 跳到
<asp:dropdownlist id="ddlPageSelect" runat="server" AutoPostBack="True"></asp:dropdownlist>页
当前是第<asp:label id="lblPageIndex" runat="server">1</asp:label>页 共
<asp:label id="lblPageCount" runat="server"></asp:label>页
<asp:LinkButton id="lbtnFirst" runat="server">首页</asp:LinkButton>
<asp:LinkButton id="lbtnPrev" runat="server">上一页</asp:LinkButton>
<asp:LinkButton id="lbtnNext" runat="server">下一页</asp:LinkButton>
<asp:LinkButton id="lbtnLast" runat="server">末页</asp:LinkButton>>| 跳到
<asp:dropdownlist id="ddlPageSelect" runat="server" AutoPostBack="True"></asp:dropdownlist>页
代码:
namespace QuMeiXJ.Webs.Controls
{
using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Web.UI;
/// <summary>
/// 作 者:继荣
/// 创建时间:2006-04-12
/// 用 途:翻页控件
/// 使用方法:
/// <1>翻页控件中的事件说明
/// PageLetNew1.PageIndexChanged += new QuMeiXJ.Webs.Controls.PageLetNew.PageIndexChangedEventHandler(this.PageButtonClick);
/// <2>翻页处理
/// private void PageButtonClick(Object sender,System.EventArgs e)
/// {
/// dgBind();
/// }
/// <3>DataGrid绑定 和翻页控件设定
/// private void dgdBind()
/// {
/// QuMeiXJ.BLL .pageTable bd = new QuMeiXJ.BLL.pageTable ();
/// DataSet ds = bd.GetList(3,Convert.ToInt32(PageLetNew1.PageIndex),"1=1");
/// dgdpage.DataSource = ds;
/// dgdpage.DataBind ();
/// PageLetNew1.PageSize = 3;
/// PageLetNew1.ItemCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
/// PageLetNew1.SetPageUrl();
/// }
/// </summary>
public class PageLetNew : System.Web.UI.UserControl
{
protected System.Web.UI.WebControls.Label lblPageIndex;
protected System.Web.UI.WebControls.Label lblPageCount;
protected System.Web.UI.WebControls.DropDownList ddlPageSelect;
protected System.Web.UI.WebControls.LinkButton lbtnFirst;
protected System.Web.UI.WebControls.LinkButton lbtnPrev;
protected System.Web.UI.WebControls.LinkButton lbtnNext;
protected System.Web.UI.WebControls.LinkButton lbtnLast;
//声明翻页事件
public delegate void PageIndexChangedEventHandler(object sender, System.EventArgs e);
public event PageIndexChangedEventHandler PageIndexChanged;
private int _itemCount;
private int _pageCount;
private int _pageSize=20;
protected virtual void OnPageIndexChanged(object sender,System.EventArgs e)
{
string strNewPageIndex = "1";
Control ctrl = (Control)sender;
if(ctrl.GetType().Name == "DropDownList")
{
strNewPageIndex = ddlPageSelect.SelectedValue;
}
else if(ctrl.GetType().Name == "LinkButton")
{
strNewPageIndex = ((LinkButton)sender).CommandName;
}
if(PageIndex != strNewPageIndex)
{
PageIndex = strNewPageIndex;
if (PageIndexChanged != null)
PageIndexChanged(sender, e);
}
ddlPageSelect.SelectedValue = strNewPageIndex;
}
/// <summary>
/// 当前页数
/// </summary>
public string PageIndex
{
set
{
lblPageIndex.Text = value;
}
get
{
return lblPageIndex.Text;
}
}
/// <summary>
/// 记录总数
/// </summary>
public int ItemCount
{
set
{
_itemCount = value;
_pageCount = GetPageCount(_pageSize,_itemCount);
lblPageCount.Text = _pageCount.ToString();
}
}
/// <summary>
/// 每页显示记录数
/// </summary>
public int PageSize
{
set
{
_pageSize = value;
}
}
private void Page_Load(object sender, System.EventArgs e)
{
}
/// <summary>
/// 得到总页数
/// </summary>
/// <param name="PageSize">每页显示数据个数</param>
/// <param name="ItemCount">数据总个数</param>
/// <returns>总夜数</returns>
private int GetPageCount(int PageSize,int ItemCount)
{
if (ItemCount == 0)
{
return 1;
}
else
{
if(ItemCount % PageSize ==0)
{
return ItemCount / PageSize;
}
else
{
return ItemCount / PageSize + 1;
}
}
}
public void SetPageUrl()
{
lbtnFirst.CommandName = "1";
lbtnLast.CommandName = _pageCount.ToString();
int pagePrev = Convert.ToInt32(lblPageIndex.Text) - 1;
int pageNext = Convert.ToInt32(lblPageIndex.Text) + 1;
if (pagePrev > 0)
{
lbtnPrev.CommandName = pagePrev.ToString();
}
else
{
lbtnPrev.CommandName = "1";
}
if (pageNext > _pageCount)
{
lbtnNext.CommandName = _pageCount.ToString();
}
else
{
lbtnNext.CommandName = pageNext.ToString();
}
ddlPageSelect.Items.Clear();
for(int i = 1; i < _pageCount+1; i++)
{
ddlPageSelect.Items.Add(new ListItem(i.ToString(),i.ToString()));
}
}
Web 窗体设计器生成的代码
private void lbtnFirst_Click(object sender, System.EventArgs e)
{
OnPageIndexChanged( sender , e );
}
private void lbtnPrev_Click(object sender, System.EventArgs e)
{
OnPageIndexChanged( sender , e );
}
private void lbtnNext_Click(object sender, System.EventArgs e)
{
OnPageIndexChanged( sender , e );
}
private void lbtnLast_Click(object sender, System.EventArgs e)
{
OnPageIndexChanged( sender , e );
}
private void ddlPageSelect_SelectedIndexChanged(object sender, System.EventArgs e)
{
OnPageIndexChanged( sender , e );
}
}
}
{
using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Web.UI;
/// <summary>
/// 作 者:继荣
/// 创建时间:2006-04-12
/// 用 途:翻页控件
/// 使用方法:
/// <1>翻页控件中的事件说明
/// PageLetNew1.PageIndexChanged += new QuMeiXJ.Webs.Controls.PageLetNew.PageIndexChangedEventHandler(this.PageButtonClick);
/// <2>翻页处理
/// private void PageButtonClick(Object sender,System.EventArgs e)
/// {
/// dgBind();
/// }
/// <3>DataGrid绑定 和翻页控件设定
/// private void dgdBind()
/// {
/// QuMeiXJ.BLL .pageTable bd = new QuMeiXJ.BLL.pageTable ();
/// DataSet ds = bd.GetList(3,Convert.ToInt32(PageLetNew1.PageIndex),"1=1");
/// dgdpage.DataSource = ds;
/// dgdpage.DataBind ();
/// PageLetNew1.PageSize = 3;
/// PageLetNew1.ItemCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
/// PageLetNew1.SetPageUrl();
/// }
/// </summary>
public class PageLetNew : System.Web.UI.UserControl
{
protected System.Web.UI.WebControls.Label lblPageIndex;
protected System.Web.UI.WebControls.Label lblPageCount;
protected System.Web.UI.WebControls.DropDownList ddlPageSelect;
protected System.Web.UI.WebControls.LinkButton lbtnFirst;
protected System.Web.UI.WebControls.LinkButton lbtnPrev;
protected System.Web.UI.WebControls.LinkButton lbtnNext;
protected System.Web.UI.WebControls.LinkButton lbtnLast;
//声明翻页事件
public delegate void PageIndexChangedEventHandler(object sender, System.EventArgs e);
public event PageIndexChangedEventHandler PageIndexChanged;
private int _itemCount;
private int _pageCount;
private int _pageSize=20;
protected virtual void OnPageIndexChanged(object sender,System.EventArgs e)
{
string strNewPageIndex = "1";
Control ctrl = (Control)sender;
if(ctrl.GetType().Name == "DropDownList")
{
strNewPageIndex = ddlPageSelect.SelectedValue;
}
else if(ctrl.GetType().Name == "LinkButton")
{
strNewPageIndex = ((LinkButton)sender).CommandName;
}
if(PageIndex != strNewPageIndex)
{
PageIndex = strNewPageIndex;
if (PageIndexChanged != null)
PageIndexChanged(sender, e);
}
ddlPageSelect.SelectedValue = strNewPageIndex;
}
/// <summary>
/// 当前页数
/// </summary>
public string PageIndex
{
set
{
lblPageIndex.Text = value;
}
get
{
return lblPageIndex.Text;
}
}
/// <summary>
/// 记录总数
/// </summary>
public int ItemCount
{
set
{
_itemCount = value;
_pageCount = GetPageCount(_pageSize,_itemCount);
lblPageCount.Text = _pageCount.ToString();
}
}
/// <summary>
/// 每页显示记录数
/// </summary>
public int PageSize
{
set
{
_pageSize = value;
}
}
private void Page_Load(object sender, System.EventArgs e)
{
}
/// <summary>
/// 得到总页数
/// </summary>
/// <param name="PageSize">每页显示数据个数</param>
/// <param name="ItemCount">数据总个数</param>
/// <returns>总夜数</returns>
private int GetPageCount(int PageSize,int ItemCount)
{
if (ItemCount == 0)
{
return 1;
}
else
{
if(ItemCount % PageSize ==0)
{
return ItemCount / PageSize;
}
else
{
return ItemCount / PageSize + 1;
}
}
}
public void SetPageUrl()
{
lbtnFirst.CommandName = "1";
lbtnLast.CommandName = _pageCount.ToString();
int pagePrev = Convert.ToInt32(lblPageIndex.Text) - 1;
int pageNext = Convert.ToInt32(lblPageIndex.Text) + 1;
if (pagePrev > 0)
{
lbtnPrev.CommandName = pagePrev.ToString();
}
else
{
lbtnPrev.CommandName = "1";
}
if (pageNext > _pageCount)
{
lbtnNext.CommandName = _pageCount.ToString();
}
else
{
lbtnNext.CommandName = pageNext.ToString();
}
ddlPageSelect.Items.Clear();
for(int i = 1; i < _pageCount+1; i++)
{
ddlPageSelect.Items.Add(new ListItem(i.ToString(),i.ToString()));
}
}
Web 窗体设计器生成的代码
private void lbtnFirst_Click(object sender, System.EventArgs e)
{
OnPageIndexChanged( sender , e );
}
private void lbtnPrev_Click(object sender, System.EventArgs e)
{
OnPageIndexChanged( sender , e );
}
private void lbtnNext_Click(object sender, System.EventArgs e)
{
OnPageIndexChanged( sender , e );
}
private void lbtnLast_Click(object sender, System.EventArgs e)
{
OnPageIndexChanged( sender , e );
}
private void ddlPageSelect_SelectedIndexChanged(object sender, System.EventArgs e)
{
OnPageIndexChanged( sender , e );
}
}
}