网上有很多分页存储过程,但是基本上都是提供一个单纯的存储过程,没有具体的怎样去实现。最近做一个项目用户的数数据相当大(一百万以上 的数据),如果用.NET自带的分页基本上是跑不动了,不是提示超时就是死在那里。于是就想到用存储过程分页来实现,去网上逛了一大圈终于找了几个比较好 的存储过程。接下去就开始做测试等等,最后就干脆把它做成用户控件算了,以后用直接拖到页面上,传几个属性进去就可以实现分页,免得每次都重复同样的 code。
经本人测试,对于Sqlserver的效率相当快,而对于Oracle的效率(按某个字段倒序排)不是很理想,如果不排序效率很理想,这点没有深入研究(Oracle为什么按倒序排速度很慢,在PL/SQL里也一样)。
先发布SqlServer版的分页自定义存储过程 下载代码
存储过程(该存储过程为网上下载):
CREATE
PROCEDURE
GetRecordFromPage
@tblName varchar ( 255 ), -- 表名
@RetColumns varchar ( 1000 ) = ' * ' , -- 需要返回的列,默认为全部
@Orderfld varchar ( 255 ), -- 排序字段名
@PageSize int = 10 , -- 页尺寸
@PageIndex int = 1 , -- 页码
@IsCount bit = 0 , -- 返回记录总数, 非 0 值则返回
@OrderType varchar ( 50 ) = ' asc ' , -- 设置排序类型, 非 asc 值则降序
@strWhere varchar ( 1000 ) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar ( 1000 ) -- 主语句
declare @strTmp varchar ( 300 ) -- 临时变量
declare @strOrder varchar ( 400 ) -- 排序类型
if @IsCount != 0 -- 执行总数统计
begin
if @strWhere != ''
set @strSQL = ' select count(*) as Total from [ ' + @tblName + ' ] where ' + @strWhere
else
set @strSQL = ' select count(*) as Total from [ ' + @tblName + ' ] '
end
else -- 执行查询操作
begin
if @OrderType != ' asc '
begin
set @strTmp = ' <(select min '
set @strOrder = ' order by [ ' + @Orderfld + ' ] desc '
end
else
begin
set @strTmp = ' >(select max '
set @strOrder = ' order by [ ' + @Orderfld + ' ] asc '
end
set @strSQL = ' select top ' + str ( @PageSize ) + ' ' + @RetColumns + ' from [ ' + @tblName + ' ] where [ ' + @Orderfld + ' ] ' + @strTmp + ' ([ ' + @Orderfld + ' ]) from (select top ' + str (( @PageIndex - 1 ) * @PageSize ) + ' [ ' + @Orderfld + ' ] from [ ' + @tblName + ' ] ' + @strOrder + ' ) as tblTmp) ' + @strOrder
if @strWhere != ''
set @strSQL = ' select top ' + str ( @PageSize ) + ' ' + @RetColumns + ' from [ ' + @tblName + ' ] where [ ' + @Orderfld + ' ] ' + @strTmp + ' ([ ' + @Orderfld + ' ]) from (select top ' + str (( @PageIndex - 1 ) * @PageSize ) + ' [ ' + @Orderfld + ' ] 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 ) + ' ' + @RetColumns + ' from [ ' + @tblName + ' ] ' + @strTmp + ' ' + @strOrder
end
end
exec ( @strSQL )
@tblName varchar ( 255 ), -- 表名
@RetColumns varchar ( 1000 ) = ' * ' , -- 需要返回的列,默认为全部
@Orderfld varchar ( 255 ), -- 排序字段名
@PageSize int = 10 , -- 页尺寸
@PageIndex int = 1 , -- 页码
@IsCount bit = 0 , -- 返回记录总数, 非 0 值则返回
@OrderType varchar ( 50 ) = ' asc ' , -- 设置排序类型, 非 asc 值则降序
@strWhere varchar ( 1000 ) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar ( 1000 ) -- 主语句
declare @strTmp varchar ( 300 ) -- 临时变量
declare @strOrder varchar ( 400 ) -- 排序类型
if @IsCount != 0 -- 执行总数统计
begin
if @strWhere != ''
set @strSQL = ' select count(*) as Total from [ ' + @tblName + ' ] where ' + @strWhere
else
set @strSQL = ' select count(*) as Total from [ ' + @tblName + ' ] '
end
else -- 执行查询操作
begin
if @OrderType != ' asc '
begin
set @strTmp = ' <(select min '
set @strOrder = ' order by [ ' + @Orderfld + ' ] desc '
end
else
begin
set @strTmp = ' >(select max '
set @strOrder = ' order by [ ' + @Orderfld + ' ] asc '
end
set @strSQL = ' select top ' + str ( @PageSize ) + ' ' + @RetColumns + ' from [ ' + @tblName + ' ] where [ ' + @Orderfld + ' ] ' + @strTmp + ' ([ ' + @Orderfld + ' ]) from (select top ' + str (( @PageIndex - 1 ) * @PageSize ) + ' [ ' + @Orderfld + ' ] from [ ' + @tblName + ' ] ' + @strOrder + ' ) as tblTmp) ' + @strOrder
if @strWhere != ''
set @strSQL = ' select top ' + str ( @PageSize ) + ' ' + @RetColumns + ' from [ ' + @tblName + ' ] where [ ' + @Orderfld + ' ] ' + @strTmp + ' ([ ' + @Orderfld + ' ]) from (select top ' + str (( @PageIndex - 1 ) * @PageSize ) + ' [ ' + @Orderfld + ' ] 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 ) + ' ' + @RetColumns + ' from [ ' + @tblName + ' ] ' + @strTmp + ' ' + @strOrder
end
end
exec ( @strSQL )
下面为用户控件前台html代码:
<%
@ Control Language="c#" AutoEventWireup="false" Codebehind="GetPagerForSql.ascx.cs" Inherits="doHope.GetPagerForSql" TargetSchema="http://schemas.microsoft.com/intellisense/ie5"
%>
< asp:label id ="Label2" runat ="server" Font-Size ="9pt" > 共 </ asp:label >< FONT face ="宋体" > </ FONT >< asp:label id ="lbl_RecordCnt" runat ="server" Font-Size ="9pt" ></ asp:label >< FONT face ="宋体" > </ FONT >< asp:label id ="Label3" runat ="server" Font-Size ="9pt" > 项 </ asp:label >< FONT face ="宋体" > </ FONT >< asp:label id ="Label4" runat ="server" Font-Size ="9pt" ForeColor ="Black" > | </ asp:label >< FONT face ="宋体" > </ FONT >< asp:linkbutton id ="lkbFirst" runat ="server" Font-Size ="9pt" Enabled ="False" ForeColor ="Black" CommandArgument ="First" OnCommand ="ChangePage" > 首页 </ asp:linkbutton >< FONT face ="宋体" > </ FONT >< asp:linkbutton id ="lkbPre" runat ="server" Font-Size ="9pt" Enabled ="False" ForeColor ="Black" CommandArgument ="Pre" OnCommand ="ChangePage" > 上一页 </ asp:linkbutton >< FONT face ="宋体" > </ FONT >< asp:linkbutton id ="lkbNext" runat ="server" Font-Size ="9pt" Enabled ="False" ForeColor ="Black" CommandArgument ="Next" OnCommand ="ChangePage" > 下一页 </ asp:linkbutton >< FONT face ="宋体" > </ FONT >< asp:linkbutton id ="lkbLast" runat ="server" Font-Size ="9pt" Enabled ="False" ForeColor ="Black" CommandArgument ="Last" OnCommand ="ChangePage" > 末页 </ asp:linkbutton >< FONT face ="宋体" > </ FONT >< asp:label id ="Label5" runat ="server" Font-Size ="9pt" ForeColor ="Black" > | </ asp:label >< FONT face ="宋体" > </ FONT >< asp:label id ="Label6" runat ="server" Font-Size ="9pt" > 转 </ asp:label >< asp:textbox id ="txt_CurrentPage" runat ="server" Enabled ="False" Width ="35px" Height ="18px" AutoPostBack ="True" ></ asp:textbox >< FONT face ="宋体" ></ FONT >
< asp:label id ="Label8" runat ="server" Font-Size ="9pt" ForeColor ="Black" > / </ asp:label >< FONT face ="宋体" > </ FONT >< asp:label id ="lbl_PageCnt" runat ="server" Font-Size ="9pt" ></ asp:label >< FONT face ="宋体" > </ FONT >< asp:label id ="Label9" runat ="server" Font-Size ="9pt" > 页 </ asp:label >
< asp:label id ="Label2" runat ="server" Font-Size ="9pt" > 共 </ asp:label >< FONT face ="宋体" > </ FONT >< asp:label id ="lbl_RecordCnt" runat ="server" Font-Size ="9pt" ></ asp:label >< FONT face ="宋体" > </ FONT >< asp:label id ="Label3" runat ="server" Font-Size ="9pt" > 项 </ asp:label >< FONT face ="宋体" > </ FONT >< asp:label id ="Label4" runat ="server" Font-Size ="9pt" ForeColor ="Black" > | </ asp:label >< FONT face ="宋体" > </ FONT >< asp:linkbutton id ="lkbFirst" runat ="server" Font-Size ="9pt" Enabled ="False" ForeColor ="Black" CommandArgument ="First" OnCommand ="ChangePage" > 首页 </ asp:linkbutton >< FONT face ="宋体" > </ FONT >< asp:linkbutton id ="lkbPre" runat ="server" Font-Size ="9pt" Enabled ="False" ForeColor ="Black" CommandArgument ="Pre" OnCommand ="ChangePage" > 上一页 </ asp:linkbutton >< FONT face ="宋体" > </ FONT >< asp:linkbutton id ="lkbNext" runat ="server" Font-Size ="9pt" Enabled ="False" ForeColor ="Black" CommandArgument ="Next" OnCommand ="ChangePage" > 下一页 </ asp:linkbutton >< FONT face ="宋体" > </ FONT >< asp:linkbutton id ="lkbLast" runat ="server" Font-Size ="9pt" Enabled ="False" ForeColor ="Black" CommandArgument ="Last" OnCommand ="ChangePage" > 末页 </ asp:linkbutton >< FONT face ="宋体" > </ FONT >< asp:label id ="Label5" runat ="server" Font-Size ="9pt" ForeColor ="Black" > | </ asp:label >< FONT face ="宋体" > </ FONT >< asp:label id ="Label6" runat ="server" Font-Size ="9pt" > 转 </ asp:label >< asp:textbox id ="txt_CurrentPage" runat ="server" Enabled ="False" Width ="35px" Height ="18px" AutoPostBack ="True" ></ asp:textbox >< FONT face ="宋体" ></ FONT >
< asp:label id ="Label8" runat ="server" Font-Size ="9pt" ForeColor ="Black" > / </ asp:label >< FONT face ="宋体" > </ FONT >< asp:label id ="lbl_PageCnt" runat ="server" Font-Size ="9pt" ></ asp:label >< FONT face ="宋体" > </ FONT >< asp:label id ="Label9" runat ="server" Font-Size ="9pt" > 页 </ asp:label >
下面为后台代码:
namespace
doHope
{
using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// 配合存储过程分页自定义控件(Sql Server)
/// By Cherish58
/// </summary>
public class GetPagerForSql : System.Web.UI.UserControl
{
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Label Label9;
protected System.Web.UI.WebControls.Label lbl_PageCnt;
protected System.Web.UI.WebControls.Label Label6;
protected System.Web.UI.WebControls.LinkButton lkbLast;
protected System.Web.UI.WebControls.LinkButton lkbNext;
protected System.Web.UI.WebControls.LinkButton lkbPre;
protected System.Web.UI.WebControls.LinkButton lkbFirst;
protected System.Web.UI.WebControls.Label Label3;
protected System.Web.UI.WebControls.Label lbl_RecordCnt;
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.Label Label4;
protected System.Web.UI.WebControls.Label Label5;
protected System.Web.UI.WebControls.Label Label7;
protected System.Web.UI.WebControls.Label Label8;
protected System.Web.UI.WebControls.TextBox txt_CurrentPage;
#region 全局变量
/// <summary>
/// 获得数据库连接字符
/// </summary>
protected string strconn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString();
/// <summary>
/// 初始登陆时是否绑定数据(是为true,否为false),默认为false
/// </summary>
public bool InitBindData = false;
#endregion
#region 属性
/// <summary>
/// 表名,必须赋初值
/// </summary>
public string TableName
{
get { return ViewState["TableName"].ToString(); }
set { ViewState["TableName"] = value; }
}
/// <summary>
/// 返回的列名,默认为全部
/// </summary>
public string RetColumns
{
get { return ViewState["RetColumns"].ToString(); }
set { ViewState["RetColumns"] = value; }
}
/// <summary>
/// 查询条件字符串,默认为空
/// </summary>
public string SqlWhere
{
get { return ViewState["SqlWhere"].ToString(); }
set { ViewState["SqlWhere"] = value; }
}
/// <summary>
/// 排序字段,必须赋初值
/// </summary>
public string OrderField
{
get { return ViewState["OrderField"].ToString(); }
set { ViewState["OrderField"] = value; }
}
/// <summary>
/// 排序类型(升序为asc,降序为desc),默认为升序
/// </summary>
public string OrderType
{
get { return ViewState["OrderType"].ToString(); }
set { ViewState["OrderType"] = value; }
}
/// <summary>
/// 每页显示记录数,默认为10条
/// </summary>
public int PageSize
{
get { return int.Parse(ViewState["PageSize"].ToString()); }
set { ViewState["PageSize"] = value; }
}
/// <summary>
/// 初始显示为第几页,默认为第1页
/// </summary>
public int CurrentPage
{
get { return int.Parse(ViewState["CurrentPage"].ToString()); }
set { ViewState["CurrentPage"] = value; }
}
/// <summary>
/// 数据列表控件名称,必须赋初值
/// </summary>
public string DataControlName
{
get { return ViewState["DataControlName"].ToString(); }
set { ViewState["DataControlName"] = value; }
}
#endregion
#region Page_Load
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
if (this.InitBindData)
{
//默认显示为第几页
ViewState["CurrentPage"] = ViewState["CurrentPage"] == null || ViewState["CurrentPage"].ToString() == "" ? "1" : ViewState["CurrentPage"].ToString();
//每页显示记录总数
ViewState["PageSize"] = ViewState["PageSize"] == null || ViewState["PageSize"].ToString() == "" ? 10 : int.Parse(ViewState["PageSize"].ToString());
this.BindGridData();
}
}
}
#endregion
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器
/// 修改此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.lkbFirst.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
this.lkbPre.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
this.lkbNext.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
this.lkbLast.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
this.txt_CurrentPage.TextChanged += new System.EventHandler(this.txt_CurrentPage_TextChanged);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
#region 分页 ChangePage
private void ChangePage(object sender, System.Web.UI.WebControls.CommandEventArgs e)
{
int PageCount = this.GetPageCount();
int CurrentPage = int.Parse(ViewState["CurrentPage"].ToString());
string Change = e.CommandArgument.ToString();
if (Change == "Pre") //上一页
{
if (CurrentPage <= 1)
{
ViewState["CurrentPage"] = 1;
}
else
{
ViewState["CurrentPage"] = CurrentPage - 1;
}
}
else if (Change == "Next") //下一页
{
if (CurrentPage >= PageCount)
{
ViewState["CurrentPage"] = PageCount;
}
else
{
ViewState["CurrentPage"] = CurrentPage + 1;
}
}
else if (Change == "First") //首页
{
ViewState["CurrentPage"] = 1;
}
else //末页
{
ViewState["CurrentPage"] = PageCount;
}
//显示当前页
this.txt_CurrentPage.Text = ViewState["CurrentPage"].ToString();
this.ProData();
}
#endregion
#region 绑定数据
/// <summary>
/// 设置分页相关的参数
/// </summary>
private void BindGridData()
{
//记录总数
this.lbl_RecordCnt.Text = this.GetRecordCount().ToString();
//总页数
this.lbl_PageCnt.Text = this.GetPageCount().ToString();
if (this.lbl_PageCnt.Text != "0")
{
//当前页
this.txt_CurrentPage.Text = ViewState["CurrentPage"].ToString();
}
else
this.txt_CurrentPage.Text = "0";
//避免翻页后再查询出现列表没记录的情况
if (int.Parse(this.lbl_RecordCnt.Text) <= int.Parse(ViewState["PageSize"].ToString()))
{
ViewState["CurrentPage"] = 1;
this.txt_CurrentPage.Text = "1";
}
//绑定数据
this.ProData();
}
#endregion
#region 处理数据集
/// <summary>
/// 处理数据集
/// </summary>
/// <returns></returns>
private void ProData()
{
SqlConnection conn = new SqlConnection(strconn);
SqlCommand cmd = new SqlCommand("GetRecordFromPage", conn);
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tblName", "" + ViewState["TableName"].ToString() + "");
string retcolumns = ViewState["RetColumns"] == null || ViewState["RetColumns"].ToString() == "" ? "*" : ViewState["RetColumns"].ToString();
cmd.Parameters.Add("@RetColumns", retcolumns);
string sqlwhere = ViewState["SqlWhere"] == null || ViewState["SqlWhere"].ToString() == "" ? "" : ViewState["SqlWhere"].ToString();
cmd.Parameters.Add("@strWhere", sqlwhere);
cmd.Parameters.Add("@Orderfld", "" + ViewState["OrderField"].ToString() + "");
cmd.Parameters.Add("@PageIndex", int.Parse(ViewState["CurrentPage"].ToString()));
cmd.Parameters.Add("@PageSize", "" + int.Parse(ViewState["PageSize"].ToString()) + "");
string ordertype = ViewState["OrderType"] == null || ViewState["OrderType"].ToString() == "" ? "asc" : ViewState["OrderType"].ToString();
cmd.Parameters.Add("@OrderType", ordertype);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
//找到父页面控件并绑定(这里只对DataGrid控件绑定)
DataGrid dg = (DataGrid)this.Page.FindControl("" + ViewState["DataControlName"].ToString() + "");
dg.DataSource = ds;
dg.DataBind();
da.Dispose();
cmd.Dispose();
conn.Close();
//控制分页按扭状态
this.StatsLinkButton();
}
#endregion
#region 控制分页按扭状态
private void StatsLinkButton()
{
int CurrentPage = int.Parse(ViewState["CurrentPage"].ToString());
int PageCount = this.GetPageCount();
if (PageCount > 0)
this.txt_CurrentPage.Enabled = true;
else
this.txt_CurrentPage.Enabled = false;
//若当前页为第一页
if (CurrentPage <= 1)
{
this.lkbFirst.Enabled = false;
this.lkbPre.Enabled = false;
}
else
{
this.lkbFirst.Enabled = true;
this.lkbPre.Enabled = true;
}
//若当前页为最后页
if (CurrentPage >= PageCount)
{
this.lkbLast.Enabled = false;
this.lkbNext.Enabled = false;
}
else
{
this.lkbLast.Enabled = true;
this.lkbNext.Enabled = true;
}
}
#endregion
#region 得到记录总数、总页数
//记录总数
private int GetRecordCount()
{
int RecordCount = 0;
string sql = "select count(*) from " + ViewState["TableName"].ToString() + " where 1=1";
if (ViewState["SqlWhere"] != null && ViewState["SqlWhere"].ToString() != "")
sql = sql + " and " + ViewState["SqlWhere"].ToString() + "";
SqlConnection conn = new SqlConnection(strconn);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
RecordCount = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
conn.Close();
return RecordCount;
}
//总页数
private int GetPageCount()
{
int RecordCount = 0;
int YeShu = 0;
int psize = int.Parse(ViewState["PageSize"].ToString());
string sql = "select count(*) from " + ViewState["TableName"].ToString() + " where 1=1";
if (ViewState["SqlWhere"] != null && ViewState["SqlWhere"].ToString() != "")
sql = sql + " and " + ViewState["SqlWhere"].ToString() + "";
SqlConnection conn = new SqlConnection(strconn);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
RecordCount = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
conn.Close();
YeShu = RecordCount % psize;
if (YeShu == 0)
{
return RecordCount / psize;
}
else
{
return RecordCount / psize + 1;
}
}
#endregion
#region 跳转
private void txt_CurrentPage_TextChanged(object sender, System.EventArgs e)
{
try
{
int num = Convert.ToInt32(this.txt_CurrentPage.Text);
if (num > this.GetPageCount())
{
Page.RegisterStartupScript("", "<script>alert('输入的页数已超出总页数,请重新输入!')</script>");
return;
}
ViewState["CurrentPage"] = num;
this.ProData();
}
catch (Exception ee)
{
Page.RegisterStartupScript("", "<script>alert('请输入正确的页数!')</script>");
return;
}
}
#endregion
#region 传值后再绑定,用于有条件查询(前台调用)
/// <summary>
/// 传值后再绑定,用于有条件查询
/// </summary>
public void GetDataByCond()
{
//默认显示为第1页
ViewState["CurrentPage"] = ViewState["CurrentPage"] == null || ViewState["CurrentPage"].ToString() == "" ? "1" : ViewState["CurrentPage"].ToString();
//每页显示记录总数
ViewState["PageSize"] = ViewState["PageSize"] == null || ViewState["PageSize"].ToString() == "" ? 10 : int.Parse(ViewState["PageSize"].ToString());
this.BindGridData();
}
#endregion
}
}
{
using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// 配合存储过程分页自定义控件(Sql Server)
/// By Cherish58
/// </summary>
public class GetPagerForSql : System.Web.UI.UserControl
{
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Label Label9;
protected System.Web.UI.WebControls.Label lbl_PageCnt;
protected System.Web.UI.WebControls.Label Label6;
protected System.Web.UI.WebControls.LinkButton lkbLast;
protected System.Web.UI.WebControls.LinkButton lkbNext;
protected System.Web.UI.WebControls.LinkButton lkbPre;
protected System.Web.UI.WebControls.LinkButton lkbFirst;
protected System.Web.UI.WebControls.Label Label3;
protected System.Web.UI.WebControls.Label lbl_RecordCnt;
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.Label Label4;
protected System.Web.UI.WebControls.Label Label5;
protected System.Web.UI.WebControls.Label Label7;
protected System.Web.UI.WebControls.Label Label8;
protected System.Web.UI.WebControls.TextBox txt_CurrentPage;
#region 全局变量
/// <summary>
/// 获得数据库连接字符
/// </summary>
protected string strconn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString();
/// <summary>
/// 初始登陆时是否绑定数据(是为true,否为false),默认为false
/// </summary>
public bool InitBindData = false;
#endregion
#region 属性
/// <summary>
/// 表名,必须赋初值
/// </summary>
public string TableName
{
get { return ViewState["TableName"].ToString(); }
set { ViewState["TableName"] = value; }
}
/// <summary>
/// 返回的列名,默认为全部
/// </summary>
public string RetColumns
{
get { return ViewState["RetColumns"].ToString(); }
set { ViewState["RetColumns"] = value; }
}
/// <summary>
/// 查询条件字符串,默认为空
/// </summary>
public string SqlWhere
{
get { return ViewState["SqlWhere"].ToString(); }
set { ViewState["SqlWhere"] = value; }
}
/// <summary>
/// 排序字段,必须赋初值
/// </summary>
public string OrderField
{
get { return ViewState["OrderField"].ToString(); }
set { ViewState["OrderField"] = value; }
}
/// <summary>
/// 排序类型(升序为asc,降序为desc),默认为升序
/// </summary>
public string OrderType
{
get { return ViewState["OrderType"].ToString(); }
set { ViewState["OrderType"] = value; }
}
/// <summary>
/// 每页显示记录数,默认为10条
/// </summary>
public int PageSize
{
get { return int.Parse(ViewState["PageSize"].ToString()); }
set { ViewState["PageSize"] = value; }
}
/// <summary>
/// 初始显示为第几页,默认为第1页
/// </summary>
public int CurrentPage
{
get { return int.Parse(ViewState["CurrentPage"].ToString()); }
set { ViewState["CurrentPage"] = value; }
}
/// <summary>
/// 数据列表控件名称,必须赋初值
/// </summary>
public string DataControlName
{
get { return ViewState["DataControlName"].ToString(); }
set { ViewState["DataControlName"] = value; }
}
#endregion
#region Page_Load
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
if (this.InitBindData)
{
//默认显示为第几页
ViewState["CurrentPage"] = ViewState["CurrentPage"] == null || ViewState["CurrentPage"].ToString() == "" ? "1" : ViewState["CurrentPage"].ToString();
//每页显示记录总数
ViewState["PageSize"] = ViewState["PageSize"] == null || ViewState["PageSize"].ToString() == "" ? 10 : int.Parse(ViewState["PageSize"].ToString());
this.BindGridData();
}
}
}
#endregion
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器
/// 修改此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.lkbFirst.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
this.lkbPre.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
this.lkbNext.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
this.lkbLast.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
this.txt_CurrentPage.TextChanged += new System.EventHandler(this.txt_CurrentPage_TextChanged);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
#region 分页 ChangePage
private void ChangePage(object sender, System.Web.UI.WebControls.CommandEventArgs e)
{
int PageCount = this.GetPageCount();
int CurrentPage = int.Parse(ViewState["CurrentPage"].ToString());
string Change = e.CommandArgument.ToString();
if (Change == "Pre") //上一页
{
if (CurrentPage <= 1)
{
ViewState["CurrentPage"] = 1;
}
else
{
ViewState["CurrentPage"] = CurrentPage - 1;
}
}
else if (Change == "Next") //下一页
{
if (CurrentPage >= PageCount)
{
ViewState["CurrentPage"] = PageCount;
}
else
{
ViewState["CurrentPage"] = CurrentPage + 1;
}
}
else if (Change == "First") //首页
{
ViewState["CurrentPage"] = 1;
}
else //末页
{
ViewState["CurrentPage"] = PageCount;
}
//显示当前页
this.txt_CurrentPage.Text = ViewState["CurrentPage"].ToString();
this.ProData();
}
#endregion
#region 绑定数据
/// <summary>
/// 设置分页相关的参数
/// </summary>
private void BindGridData()
{
//记录总数
this.lbl_RecordCnt.Text = this.GetRecordCount().ToString();
//总页数
this.lbl_PageCnt.Text = this.GetPageCount().ToString();
if (this.lbl_PageCnt.Text != "0")
{
//当前页
this.txt_CurrentPage.Text = ViewState["CurrentPage"].ToString();
}
else
this.txt_CurrentPage.Text = "0";
//避免翻页后再查询出现列表没记录的情况
if (int.Parse(this.lbl_RecordCnt.Text) <= int.Parse(ViewState["PageSize"].ToString()))
{
ViewState["CurrentPage"] = 1;
this.txt_CurrentPage.Text = "1";
}
//绑定数据
this.ProData();
}
#endregion
#region 处理数据集
/// <summary>
/// 处理数据集
/// </summary>
/// <returns></returns>
private void ProData()
{
SqlConnection conn = new SqlConnection(strconn);
SqlCommand cmd = new SqlCommand("GetRecordFromPage", conn);
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tblName", "" + ViewState["TableName"].ToString() + "");
string retcolumns = ViewState["RetColumns"] == null || ViewState["RetColumns"].ToString() == "" ? "*" : ViewState["RetColumns"].ToString();
cmd.Parameters.Add("@RetColumns", retcolumns);
string sqlwhere = ViewState["SqlWhere"] == null || ViewState["SqlWhere"].ToString() == "" ? "" : ViewState["SqlWhere"].ToString();
cmd.Parameters.Add("@strWhere", sqlwhere);
cmd.Parameters.Add("@Orderfld", "" + ViewState["OrderField"].ToString() + "");
cmd.Parameters.Add("@PageIndex", int.Parse(ViewState["CurrentPage"].ToString()));
cmd.Parameters.Add("@PageSize", "" + int.Parse(ViewState["PageSize"].ToString()) + "");
string ordertype = ViewState["OrderType"] == null || ViewState["OrderType"].ToString() == "" ? "asc" : ViewState["OrderType"].ToString();
cmd.Parameters.Add("@OrderType", ordertype);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
//找到父页面控件并绑定(这里只对DataGrid控件绑定)
DataGrid dg = (DataGrid)this.Page.FindControl("" + ViewState["DataControlName"].ToString() + "");
dg.DataSource = ds;
dg.DataBind();
da.Dispose();
cmd.Dispose();
conn.Close();
//控制分页按扭状态
this.StatsLinkButton();
}
#endregion
#region 控制分页按扭状态
private void StatsLinkButton()
{
int CurrentPage = int.Parse(ViewState["CurrentPage"].ToString());
int PageCount = this.GetPageCount();
if (PageCount > 0)
this.txt_CurrentPage.Enabled = true;
else
this.txt_CurrentPage.Enabled = false;
//若当前页为第一页
if (CurrentPage <= 1)
{
this.lkbFirst.Enabled = false;
this.lkbPre.Enabled = false;
}
else
{
this.lkbFirst.Enabled = true;
this.lkbPre.Enabled = true;
}
//若当前页为最后页
if (CurrentPage >= PageCount)
{
this.lkbLast.Enabled = false;
this.lkbNext.Enabled = false;
}
else
{
this.lkbLast.Enabled = true;
this.lkbNext.Enabled = true;
}
}
#endregion
#region 得到记录总数、总页数
//记录总数
private int GetRecordCount()
{
int RecordCount = 0;
string sql = "select count(*) from " + ViewState["TableName"].ToString() + " where 1=1";
if (ViewState["SqlWhere"] != null && ViewState["SqlWhere"].ToString() != "")
sql = sql + " and " + ViewState["SqlWhere"].ToString() + "";
SqlConnection conn = new SqlConnection(strconn);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
RecordCount = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
conn.Close();
return RecordCount;
}
//总页数
private int GetPageCount()
{
int RecordCount = 0;
int YeShu = 0;
int psize = int.Parse(ViewState["PageSize"].ToString());
string sql = "select count(*) from " + ViewState["TableName"].ToString() + " where 1=1";
if (ViewState["SqlWhere"] != null && ViewState["SqlWhere"].ToString() != "")
sql = sql + " and " + ViewState["SqlWhere"].ToString() + "";
SqlConnection conn = new SqlConnection(strconn);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
RecordCount = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
conn.Close();
YeShu = RecordCount % psize;
if (YeShu == 0)
{
return RecordCount / psize;
}
else
{
return RecordCount / psize + 1;
}
}
#endregion
#region 跳转
private void txt_CurrentPage_TextChanged(object sender, System.EventArgs e)
{
try
{
int num = Convert.ToInt32(this.txt_CurrentPage.Text);
if (num > this.GetPageCount())
{
Page.RegisterStartupScript("", "<script>alert('输入的页数已超出总页数,请重新输入!')</script>");
return;
}
ViewState["CurrentPage"] = num;
this.ProData();
}
catch (Exception ee)
{
Page.RegisterStartupScript("", "<script>alert('请输入正确的页数!')</script>");
return;
}
}
#endregion
#region 传值后再绑定,用于有条件查询(前台调用)
/// <summary>
/// 传值后再绑定,用于有条件查询
/// </summary>
public void GetDataByCond()
{
//默认显示为第1页
ViewState["CurrentPage"] = ViewState["CurrentPage"] == null || ViewState["CurrentPage"].ToString() == "" ? "1" : ViewState["CurrentPage"].ToString();
//每页显示记录总数
ViewState["PageSize"] = ViewState["PageSize"] == null || ViewState["PageSize"].ToString() == "" ? 10 : int.Parse(ViewState["PageSize"].ToString());
this.BindGridData();
}
#endregion
}
}
使用时,只需传几个必须赋初值的属性即可:TableName为表或视图名,OrderField为排序字段(该存储过程只对一个字段进行排序),DataControlName为数据列表控件名称(这里默认是DataGrid控件,根据需要自己修改)。
里面有个InitBindData属性:初始登陆时是否绑定数据(是为true,否为false),默认为false。
简单示例:
//
声明自定义控件
protected GetPagerForSql GetPagerForSql1;
#region Page_Load
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
ViewState["sqlcond"] = "";
ViewState["key"] = "";
this.BindGrid(true);
}
}
#endregion
#region 绑定列表
/// <summary>
/// 初始绑定为true,否则为false
/// </summary>
/// <param name="flag"></param>
private void BindGrid(bool flag)
{
GetPagerForSql1.TableName = "dh_UserInfo"; //表名
GetPagerForSql1.SqlWhere = ViewState["sqlcond"].ToString(); //查询条件
GetPagerForSql1.OrderField = "Uid"; //排序字段
GetPagerForSql1.PageSize = 12; //设置每页显示12条记录
GetPagerForSql1.DataControlName = this.DataGrid1.ID; //绑定列表控件的ID
if (flag == true) //初始登陆绑定
{
GetPagerForSql1.InitBindData = true;
}
else //其它绑定
{
GetPagerForSql1.GetDataByCond();
}
}
protected GetPagerForSql GetPagerForSql1;
#region Page_Load
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
ViewState["sqlcond"] = "";
ViewState["key"] = "";
this.BindGrid(true);
}
}
#endregion
#region 绑定列表
/// <summary>
/// 初始绑定为true,否则为false
/// </summary>
/// <param name="flag"></param>
private void BindGrid(bool flag)
{
GetPagerForSql1.TableName = "dh_UserInfo"; //表名
GetPagerForSql1.SqlWhere = ViewState["sqlcond"].ToString(); //查询条件
GetPagerForSql1.OrderField = "Uid"; //排序字段
GetPagerForSql1.PageSize = 12; //设置每页显示12条记录
GetPagerForSql1.DataControlName = this.DataGrid1.ID; //绑定列表控件的ID
if (flag == true) //初始登陆绑定
{
GetPagerForSql1.InitBindData = true;
}
else //其它绑定
{
GetPagerForSql1.GetDataByCond();
}
}