ASP.NET分页存储过程自定义用户控件
网上有很多分页存储过程,但是基本上都是提供一个单纯的存储过程,没有具体的怎样去实现。最近做一个项目用户的数数据相当大(一百万以上的数据),如果用.NET自带的分页基本上是跑不动了,不是提示超时就是死在那里。于是就想到用存储过程分页来实现,去网上逛了一大圈终于找了几个比较好的存储过程。接下去就开始做测试等等,最后就干脆把它做成用户控件算了,以后用直接拖到页面上,传几个属性进去就可以实现分页,免得每次都重复同样的code。
经本人测试,对于Sqlserver的效率相当快,而对于Oracle的效率(按某个字段倒序排)不是很理想,如果不排序效率很理想,这点没有深入研究(Oracle为什么按倒序排速度很慢,在PL/SQL里也一样)。
先发布SqlServer版的分页自定义存储过程,下载代码:
https://files.cnblogs.com/cherish58/sql%20serverå页åå¨è¿ç¨.rar
存储过程(该存储过程为网上下载):
下面为用户控件前台html代码:
下面为后台代码:
经本人测试,对于Sqlserver的效率相当快,而对于Oracle的效率(按某个字段倒序排)不是很理想,如果不排序效率很理想,这点没有深入研究(Oracle为什么按倒序排速度很慢,在PL/SQL里也一样)。
先发布SqlServer版的分页自定义存储过程,下载代码:
https://files.cnblogs.com/cherish58/sql%20serverå页åå¨è¿ç¨.rar
存储过程(该存储过程为网上下载):
复制 保存
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)
下面为用户控件前台html代码:
复制 保存
<%@ Control Language="c#" AutoEventWireup="false" Codebehind="GetPagerForSql.ascx.cs" Inherits="doHope.GetPagerForSql" TargetSchema=%> <asp:label id="Label2" runat="server" Font-Size="9pt">共</asp:label><FONT face="宋体"> </FONT></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">首页</asp:linkbutton><FONT face="宋体"> </FONT><asp:linkbutton id="lkbPre" runat="server" Font-Size="9pt" Enabled="False" ForeColor="Black" CommandArgument="Pre">上一页</asp:linkbutton><FONT face="宋体"> </FONT><asp:linkbutton id="lkbNext" runat="server" Font-Size="9pt" Enabled="False" ForeColor="Black" CommandArgument="Next">下一页</asp:linkbutton><FONT face="宋体"> </FONT><asp:linkbutton id="lkbLast" runat="server" Font-Size="9pt" Enabled="False" ForeColor="Black" CommandArgument="Last">末页</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 } }