ASP.NET分页存储过程自定义用户控件-优快云博客

本文介绍了一种基于存储过程的高效SQL分页方案，并提供了一个.NET用户控件实例，可方便地应用于拥有大量数据的场景中，实现快速分页加载。

网上有很多分页存储过程，但是基本上都是提供一个单纯的存储过程，没有具体的怎样去实现。最近做一个项目用户的数数据相当大（一百万以上的数据），如果用.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)

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

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 >& nbsp; < asp:label id = " lbl_RecordCnt " runat = " server " Font - Size = " 9pt " ></ asp:label >& nbsp; < asp:label id = " Label3 " runat = " server " Font - Size = " 9pt " > 项 </ asp:label >& nbsp; < asp:label id = " Label4 " runat = " server " Font - Size = " 9pt " ForeColor = " Black " >|</ asp:label >& nbsp; < asp:linkbutton id = " lkbFirst " runat = " server " Font - Size = " 9pt " Enabled = " False " ForeColor = " Black " CommandArgument = " First " > 首页 </ asp:linkbutton >& nbsp; < asp:linkbutton id = " lkbPre " runat = " server " Font - Size = " 9pt " Enabled = " False " ForeColor = " Black " CommandArgument = " Pre " > 上一页 </ asp:linkbutton >& nbsp; < asp:linkbutton id = " lkbNext " runat = " server " Font - Size = " 9pt " Enabled = " False " ForeColor = " Black " CommandArgument = " Next " > 下一页 </ asp:linkbutton >& nbsp; < asp:linkbutton id = " lkbLast " runat = " server " Font - Size = " 9pt " Enabled = " False " ForeColor = " Black " CommandArgument = " Last " > 末页 </ asp:linkbutton >& nbsp; < asp:label id = " Label5 " runat = " server " Font - Size = " 9pt " ForeColor = " Black " >|</ asp:label >& nbsp; < 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 >

< asp:label id = " Label8 " runat = " server " Font - Size = " 9pt " ForeColor = " Black " >/</ asp:label >& nbsp; < asp:label id = " lbl_PageCnt " runat = " server " Font - Size = " 9pt " ></ asp:label >& nbsp; < 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

Page_Load#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

Web 窗体设计器生成的代码#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

分页 ChangePage#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() ;

//每页显示记录总数