using System;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using System.Collections.Specialized;
using System.Web.UI.WebControls;
using System.Web.UI;
namespace PagingControl
{
/// <summary>
/// 分页控件
/// </summary>
public class Paging : WebControl, INamingContainer
{
#region 定义控件
Label lblCount = new Label();
Label lblPageIndex = new Label();
Label lblPageSize = new Label();
Label lblPageSelect = new Label();
DropDownList dropPageSelect = new DropDownList();
private LinkButton lbtnFirst = new LinkButton();
private LinkButton lbtnPrevious = new LinkButton();
private LinkButton lbtnNext = new LinkButton();
private LinkButton lbtnLast = new LinkButton();
#endregion
#region 标题
/// <summary>
/// 标题
/// </summary>
[
Category("MyNet：基本属性"),
Description("标题")
]
private string _TotalText = " 共 ";
public string TotalText
{
get
{
return _TotalText;
}
set
{
_TotalText = value;
}
}
#endregion
#region 单位
/// <summary>
/// 单位
/// </summary>
[
Category("MyNet：基本属性"),
Description("单位")
]
private string _Unit = " 条 ";
public string Unit
{
get
{
return _Unit;
}
set
{
_Unit = value;
}
}
#endregion
#region 记录数
/// <summary>
/// 记录数
/// </summary>
[
Category("MyNet：记录数"),
Description("记录数")
]
public int RecordCount
{
get
{
if (this.ViewState["RecordCount"] == null)
{
return 0;
}
return (int)this.ViewState["RecordCount"];
}
set
{
this.ViewState["RecordCount"] = value;
SetState();
}
}
#endregion
#region 每页条数
/// <summary>
/// 每页条数
/// </summary>
[
Category("MyNet：每页条数"),
Description("每页条数")
]
public int PageSize
{
get
{
if (this.ViewState["PageSize"] == null)
{
return 0;
}
return (int)this.ViewState["PageSize"];
}
set
{
this.ViewState["PageSize"] = value;
}
}
#endregion
#region 当前页索引
/// <summary>
/// 当前页索引
/// </summary>
[
Category("MyNet：当前页索引"),
Description("当前页索引")
]
public int PageIndex
{
get
{
if (this.ViewState["PageIndex"] == null)
{
return 0;
}
return (int)this.ViewState["PageIndex"];
}
set
{
this.ViewState["PageIndex"] = value;
}
}
#endregion
#region 页数
/// <summary>
/// 页数
/// </summary>
private int PageCount
{
get
{
if ((RecordCount % PageSize) > 0)
{
return (RecordCount / PageSize) + 1;
}
else
{
return (RecordCount / PageSize);
}
}
}
#endregion
////事件
#region 事件声明
/// <summary>
/// 事件声明
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public delegate void PagerCommandEventHandler(object sender, EventArgs e);
public event PagerCommandEventHandler PagerCommand;
public void OnPagerCommand()
{
//设置状态
SetState();
if (PagerCommand != null)
{
PagerCommand(this, new EventArgs());
}
}
#endregion
#region 创建子控件
/// <summary>
/// 创建子控件
/// </summary>
protected override void CreateChildControls()
{
//清除模块
Controls.Clear();
//加放总计
Controls.Add(lblCount);
//加放按钮
lbtnFirst.Text = " 首页 ";
lbtnFirst.Enabled = false;
lbtnFirst.CommandName = "First";
lbtnFirst.Click += new EventHandler(this.linkButtonClick);
Controls.Add(lbtnFirst);
lbtnPrevious.Text = " 上一页 ";
lbtnPrevious.Enabled = false;
lbtnPrevious.CommandName = "Previous";
lbtnPrevious.Click += new EventHandler(this.linkButtonClick);
Controls.Add(lbtnPrevious);
lbtnNext.Text = " 下一页 ";
lbtnNext.Enabled = false;
lbtnNext.CommandName = "Next";
lbtnNext.Click += new EventHandler(this.linkButtonClick);
Controls.Add(lbtnNext);
lbtnLast.Text = " 尾页 ";
lbtnLast.Enabled = false;
lbtnLast.CommandName = "Last";
lbtnLast.Click += new EventHandler(this.linkButtonClick);
Controls.Add(lbtnLast);
//去除超链接下的横杠
lbtnFirst.Style.Value = "text-decoration:none";
lbtnPrevious.Style.Value = "text-decoration:none";
lbtnNext.Style.Value = "text-decoration:none";
lbtnLast.Style.Value = "text-decoration:none";
//加放页码
Controls.Add(lblPageSize);
Controls.Add(lblPageIndex);
//加放下拉框
lblPageSelect.Text = " 转到： ";
Controls.Add(lblPageSelect);
//dropPageSelect.Items.Clear();
//for (int i = 1; i <= PageCount; i++)
//{
// dropPageSelect.Items.Add(new ListItem(i.ToString()));
//}
dropPageSelect.AutoPostBack = true;
dropPageSelect.SelectedIndexChanged += new EventHandler(this.DropDownListSelectedIndexChanged);
Controls.Add(dropPageSelect);
//设置状态
SetState();
}
#endregion
#region 设置状态
/// <summary>
/// 设置状态
/// </summary>
private void SetState()
{
if (PageCount > 1 && PageIndex != 0)
{
lbtnFirst.Enabled = true;
lbtnPrevious.Enabled = true;
}
else
{
lbtnFirst.Enabled = false;
lbtnPrevious.Enabled = false;
}
if (PageCount > 1 && (PageIndex + 1) != PageCount)
{
lbtnLast.Enabled = true;
lbtnNext.Enabled = true;
}
else
{
lbtnLast.Enabled = false;
lbtnNext.Enabled = false;
}
lblCount.Text = TotalText + RecordCount.ToString() + Unit;
lblPageIndex.Text = " 页次： " + (PageIndex + 1).ToString() + "/" + PageCount + " 页 ";
lblPageSize.Text = PageSize.ToString() + Unit + "/页 ";
if (PageCount > 0)
{
//先创建项
dropPageSelect.Items.Clear();
for (int i = 1; i <= PageCount; i++)
{
dropPageSelect.Items.Add(new ListItem(i.ToString()));
}
dropPageSelect.SelectedIndex = PageIndex;
}
}
#endregion
#region 上下页事件
/// <summary>
/// 上下页事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void linkButtonClick(object sender, EventArgs e)
{
if (((LinkButton)sender).CommandName == "First")
{
this.PageIndex = 0;
}
if (((LinkButton)sender).CommandName == "Previous")
{
this.PageIndex--;
}
if (((LinkButton)sender).CommandName == "Next")
{
this.PageIndex++;
}
if (((LinkButton)sender).CommandName == "Last")
{
this.PageIndex = PageCount - 1;
}
//分页事件
OnPagerCommand();
}
#endregion
#region 下拉框选择事件
/// <summary>
/// 下拉框选择事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void DropDownListSelectedIndexChanged(object sender, EventArgs e)
{
this.PageIndex = dropPageSelect.SelectedIndex;
//分页事件
OnPagerCommand();
}
#endregion
}
}
USE [CYF_STS]
GO
/****** Object: StoredProcedure [dbo].[sp_Paging] Script Date: 07/12/2012 12:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 对象: 存储过程 dbo.sp_Paging 脚本日期: 2008-8-12 14:31:36 ******/
/****** 对象: 存储过程 dbo.sp_Paging 脚本日期: 2008-8-12 14:07:09 ******/
/****** 对象: 存储过程 dbo.sp_Paging 脚本日期: 2008-7-7 14:34:33 ******/
/****** 对象: 存储过程 dbo.sp_Paging 脚本日期: 2007-11-27 9:43:17 ******/
/****** 对象: 存储过程 dbo.sp_Paging 脚本日期: 2007-4-27 13:19:03 ******/
/*
分页存储过程(来源于网络,经过修改以适应多表联合查询)
注意:该存储过程和和网络上其他分页存储过程一样,存在经典的SQL注入漏洞; 不论在asp/asp.net等中调用时,
都要把 @strWhere参数中的非法字符(逗号过滤掉)
*/
ALTER PROC [dbo].[sp_Paging]
@tblName varchar(1000) , -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 主关键字字段名
@orderfield varchar(255)='', -- 排序的字段名
@pageSize int = 10, -- 页尺寸
@pageIndex int = 1, -- 页码
@recordCount int = 0 output, -- 返回记录总数
@pageCount int = 0 output, --分页总数
@orderType int = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL nvarchar(3000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
begin
if @strWhere !=''
set @strSQL = 'select @recordCount = count(*) from ' + @tblName + ' where '+@strWhere
else
set @strSQL = 'select @recordCount = count(*) from ' + @tblName
end
exec sp_executesql @strSQL,N'@recordCount int out ',@recordCount out
if @recordCount % @pageSize = 0
set @pageCount = @recordCount / @pageSize
else
set @pageCount = @recordCount / @pageSize + 1
--以上代码的意思是计算出记录是总数和求出总共的页数
begin
if @orderType != 0
begin
set @strTmp = ' not in (select '
set @strOrder = ' order by ' + @orderfield +' desc'
--如果@orderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = ' not in (select '
set @strOrder = ' order by ' + @orderfield +' asc'
end
if @pageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from '+ @tblName + ' '+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
+ substring(@fldName, charindex('.',@fldName)+1, len(@fldName)) + ') from (select top ' + str((@pageIndex-1)*@pageSize) + ' '
+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
+ substring(@fldName, charindex('.',@fldName)+1, len(@fldName)) + ') from (select top ' + str((@pageIndex-1)*@pageSize) + ' '
+ @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'
+ @strOrder
end
end
print @strSQL
exec sp_executesql @strSQL
