ORACLE自定义分页处理

<%...@ Control Language="c#" AutoEventWireup="false" Codebehind="UCPaginationDG.ascx.cs" Inherits="XHQ.HbKyHR.WebHbKyHR.UserControl.UCPaginationDG" TargetSchema="http://schemas.microsoft.com/intellisense/ie5" %>
<table cellSpacing="1" cellPadding="0" width="100%" bgColor="#ffa553" border="0">
    <tr align="left" bgColor="#ffdbbb">
        <td colSpan="2" height="22" vAlign="bottom">共
            <asp:label id="lblRecordsCount" runat="server">0</asp:label>条 分
            <asp:label id="lblPageCount" runat="server">0</asp:label>页 当前第
            <asp:label id="lblCurrPage" runat="server">1</asp:label>页
            <asp:ImageButton id="imgBtn_FirstPage" runat="server" ImageUrl="../Images/fy1.gif" CausesValidation="False"
                CommandName="First" OnCommand="Page_OnClick"></asp:ImageButton>
            <asp:linkbutton id="ctrl_PrevPage" runat="server" OnCommand="Page_OnClick" CausesValidation="False"
                CommandName="prev">上一页</asp:linkbutton>&nbsp;<asp:linkbutton id="ctrl_NextPage" runat="server" OnCommand="Page_OnClick" CausesValidation="False"
                CommandName="next">下一页</asp:linkbutton>
            <asp:ImageButton id="imgBtn_LastPage" runat="server" ImageUrl="../Images/fy2.gif" CausesValidation="False"
                CommandName="Last" OnCommand="Page_OnClick"></asp:ImageButton>
            转到
            <asp:textbox id="txtToPage" runat="server" Width="25px" CssClass="InputCss" MaxLength="4"></asp:textbox>页
            <asp:RequiredFieldValidator id="RequiredFieldValidator1" runat="server" ErrorMessage="请填写跳转得页码" Display="Dynamic"
                ControlToValidate="txtToPage"></asp:RequiredFieldValidator>
            <asp:regularexpressionvalidator id="RegularExpressionValidator1" ErrorMessage="跳转页码必须为正整数" Display="Dynamic" ValidationExpression="^[1-9]{1,3}"
                ControlToValidate="txtToPage" Runat="server"></asp:regularexpressionvalidator>&nbsp;
            <asp:ImageButton id="imgBtnSubmitPage" runat="server" ImageAlign="Bottom" ImageUrl="../Images/go.gif"></asp:ImageButton>
        </td>
    </tr>
</table>

namespace UserControl
...{
    using System;
    using System.Data;
    using System.Drawing;
    using System.Web;
    using System.Web.UI.WebControls;
    using System.Web.UI.HtmlControls;
    using XHQ.HbKyHR.MyComponet.DycnamicalSystem;

    /**//// <summary>
    /// UCPaginationDG 的摘要说明。
    /// </summary>
    public class UCPaginationDG : System.Web.UI.UserControl
    ...{
        protected System.Web.UI.WebControls.LinkButton ctrl_PrevPage;
        protected System.Web.UI.WebControls.LinkButton ctrl_NextPage;
        protected System.Web.UI.WebControls.RegularExpressionValidator RegularExpressionValidator1;

        //查询数据参数
        private string _sourceid;  //数据源表
        private int _dbNameid;     //数据类型ID
        private int _sysid;        //所属系统ID
        private string _condition; //查询条件

        //翻页所需参数
        private DataSet _dataSource;            //绑定数据源
        private System.Web.UI.Control _ctrlData;//需要绑定得控件
        private int _pageNo = 1;                //当前页码
        private int _pageSize = 15;                //分页大小
        private int _recordCount = 0;           //记录条数
        protected System.Web.UI.WebControls.Label lblRecordsCount; //总得记录数
        protected System.Web.UI.WebControls.Label lblPageCount;    //总得页数
        protected System.Web.UI.WebControls.Label lblCurrPage;     //当前页码
        private bool _isAllDisplay = true; //是否显示全部列
        protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;
        protected System.Web.UI.WebControls.TextBox txtToPage;
        protected System.Web.UI.WebControls.ImageButton imgBtnSubmitPage;
        protected System.Web.UI.WebControls.ImageButton imgBtn_LastPage;
        protected System.Web.UI.WebControls.ImageButton imgBtn_FirstPage; //是否显示全部列
        private bool _isConvertColName = true; //是否转换列名

        翻页控件属性集合#region 翻页控件属性集合

        /**//// <summary>
        /// 设置要绑定得控件：DataGrid，DataList,Repeater
        /// </summary>
        public System.Web.UI.Control CtrlData
        ...{
            get
            ...{
                return _ctrlData;
            }
            set
            ...{
                _ctrlData = value;
            }
        }

        /**//// <summary>
        /// 查询条件
        /// </summary>
        public string Condition
        ...{
            set
            ...{
                if(value != String.Empty)
                ...{
                    _condition = value;
                }
                else
                ...{
                    _condition = null;
                }
            }
        }

        /**//// <summary>
        /// 是否显示全部列
        /// </summary>
        public bool IsAllDisplay
        ...{
            set
            ...{
                _isAllDisplay = value;
            }
        }

        /**//// <summary>
        /// 是否转换列名
        /// </summary>
        public bool IsConvertColName
        ...{
            set
            ...{
                _isConvertColName = value;
            }
        }

        /**//// <summary>
        /// 数据源ID
        /// </summary>
        public string SourceId
        ...{
            set
            ...{
                _sourceid = value;
            }
        }

        /**//// <summary>
        /// 数据类型
        /// </summary>
        public int DbNameId
        ...{
            set
            ...{
                _dbNameid = value;
            }
        }

        /**//// <summary>
        /// 所属系统ID
        /// </summary>
        public int SysId
        ...{
            set
            ...{
                _sysid = value;
            }
        }

        /**//// <summary>
        /// 当前页码(查询时，若不对此参数赋值，则是在当前页中进行查找)
        /// </summary>
        public int PageCurrNo
        ...{
            get
            ...{
                if(ViewState["CurrentPage"] == null)
                ...{
                    _pageNo = 1;
                    ViewState["CurrentPage"] = 1;
                }

                return _pageNo;
            }
            set
            ...{
                _pageNo = value;
                ViewState["CurrentPage"] = value;
            }
        }

        /**//// <summary>
        /// 分页大小（默认为15）
        /// </summary>
        public int PageSize
        ...{
            set
            ...{
                _pageSize = value;
            }
        }

        #endregion

        private void Page_Load(object sender, System.EventArgs e)
        ...{
            if(!this.IsPostBack)
            ...{
                //初始化页码值
                ViewState["CurrentPage"] = _pageNo;
                //第一绑定值
                BindDataToControl();
            }
        }

        绑定数据到指定的控件：DataGrid,DataList,Repeater#region 绑定数据到指定的控件：DataGrid,DataList,Repeater

        /**//// <summary>
        /// 绑定数据到指定控件
        /// </summary>
        public void BindDataToControl()
        ...{
            string strControlType = ""; //控件类型

            //取得绑定数据，并设置翻页信息
            GetBindData();

            if(_ctrlData != null)
            ...{
                strControlType = _ctrlData.GetType().ToString();

                //根据控件类型绑定数据
                switch(strControlType)
                ...{
                    case "System.Web.UI.WebControls.DataGrid": 
                        ((DataGrid)_ctrlData).DataSource = _dataSource;
                        ((DataGrid)_ctrlData).DataBind();
                        break;
                    case "System.Web.UI.WebControls.DataList":
                        ((DataList)_ctrlData).DataSource = _dataSource;
                        ((DataList)_ctrlData).DataBind();
                        break;
                    case "System.Web.UI.WebControls.Repeater":
                        ((Repeater)_ctrlData).DataSource = _dataSource;
                        ((Repeater)_ctrlData).DataBind();
                        break;
                }
            }
            else
            ...{
                throw new ArgumentNullException("CtrlData","未设置需绑定控件参数或者该参数无效！");
            }
        }

        #endregion

        取得绑定的数据源,并设置页面分页属性#region 取得绑定的数据源,并设置页面分页属性

        /**//// <summary>
        /// 取得绑定数据
        /// </summary>
        private void GetBindData()
        ...{
            //需要绑定的页码
            int iNextPageIndex = (int)ViewState["CurrentPage"]; 

            //判断必须的参数是_sourceid,_sysid,_dbNameid否赋值
            if(_sourceid == null || _sourceid == String.Empty)
            ...{
                throw new ArgumentNullException("参数不能为空","SourceId");
            }
            
            if(_sysid == 0)
            ...{
                throw new ArgumentNullException("参数不能为空","SysId");
            }

            if(_dbNameid == 0)
            ...{
                throw new ArgumentNullException("参数不能为空","DbNameId");
            }

            DynamicalBindDataGrid dycBindGrid = new DynamicalBindDataGrid();
            dycBindGrid.PageSize = this._pageSize;
            dycBindGrid.PageNo = iNextPageIndex;
            dycBindGrid.IsPagination = true;
            dycBindGrid.IsAllDisplay = _isAllDisplay;
            dycBindGrid.IsConvertColumnName = _isConvertColName;
            dycBindGrid.Sourceid = this._sourceid;
            dycBindGrid.SysId = _sysid;
            dycBindGrid.DbNameID = this._dbNameid;
            dycBindGrid.Condition = _condition;

            //取得数据源
            _dataSource = dycBindGrid.QueryData();

            //取得记录数
            this._recordCount = dycBindGrid.RecordCount;

            this.lblRecordsCount.Text = _recordCount.ToString();

            //计算页数
            int iPageCount = 0;
            iPageCount = (int)_recordCount/_pageSize;
            int iResi = _recordCount%_pageSize; //取余数

            if(iResi != 0)
            ...{
                iPageCount += 1;
            }

            this.lblPageCount.Text = iPageCount.ToString();
            this.lblCurrPage.Text = iNextPageIndex.ToString();

            //判断是否是第一页
            if(iNextPageIndex == 1)
            ...{
                ctrl_PrevPage.Enabled = false;
                imgBtn_FirstPage.Enabled = false;
            }
            else
            ...{
                ctrl_PrevPage.Enabled = true;
                imgBtn_FirstPage.Enabled = true;
                imgBtnSubmitPage.Enabled = true;
            }

            //判断是否到达最后一页
            if(iNextPageIndex == iPageCount)
            ...{
                ctrl_NextPage.Enabled = false;
                imgBtn_LastPage.Enabled = false;
            }
            else
            ...{
                ctrl_NextPage.Enabled = true;
                imgBtn_LastPage.Enabled = true;
            }

            //只有一页，跳转按钮不可用
            if(iPageCount == 1)
            ...{
                imgBtn_FirstPage.Enabled = false;
                imgBtn_LastPage.Enabled = false;
                this.imgBtnSubmitPage.Enabled = false;
            }
        }

        #endregion

        自定义翻页事件(上一页，下一页事件)#region 自定义翻页事件(上一页，下一页事件)

        /**//// <summary>
        /// 自定义分页事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public void Page_OnClick(Object sender,CommandEventArgs e)
        ...{    
            //当前页号    
            int iPageNo; 

            iPageNo = (int)ViewState["CurrentPage"];

            //事件命令参数：prev 上一页，next 下一页
            string strCmdArgs = e.CommandName.ToUpper();

            //根据cmd判定翻页方向
            switch(strCmdArgs)
            ...{
                case "NEXT":
                    iPageNo ++;
                    break;
                case "PREV":
                    iPageNo --;
                    break;
                case "LAST":
                    iPageNo = int.Parse(lblPageCount.Text);
                    break;
                case "FIRST":
                    iPageNo = 1;
                    break;
            }

            this.ViewState["CurrentPage"] = iPageNo;
            //重新绑定数据
            BindDataToControl();
        }

        #endregion

        Web 窗体设计器生成的代码#region Web 窗体设计器生成的代码
        override protected void OnInit(EventArgs e)
        ...{
            //
            // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
            //
            InitializeComponent();
            base.OnInit(e);
        }
        
        /**//// <summary>
        ///        设计器支持所需的方法 - 不要使用代码编辑器
        ///        修改此方法的内容。
        /// </summary>
        private void InitializeComponent()
        ...{
            this.imgBtnSubmitPage.Click += new System.Web.UI.ImageClickEventHandler(this.imgBtnSubmitPage_Click);
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion

        private void imgBtnSubmitPage_Click(object sender, System.Web.UI.ImageClickEventArgs e)
        ...{
            //跳转页
            int iPageNo = Convert.ToInt32(txtToPage.Text);
            this.ViewState["CurrentPage"] = iPageNo;

            BindDataToControl();
        }
    }
}

存储过程：

  PROCEDURE p_pagequery (
    p_pagesize               INT,   --每页记录数
    p_pageno                 INT,   --当前页码,从 1 开始
    p_sqlselect              VARCHAR2,   --查询语句,含排序部分
    p_selcolumn              VARCHAR2,   --查询列信息
    p_sqlcount               VARCHAR2,   --获取记录总数的查询语句
    p_outrecordcount   OUT   INT,   --返回总记录数
    cur_out            OUT   pkg_admin.T_RefCursor
  )
  IS
/*********************************************************************
    1.  名称     【p_pagequery】
    2.  用途        B/S, C/S 分页查询
    3.  版本
         版本号     日期            执行人    日志记录           当前状态
         ---------------------------------------------------------------------------
            1>     
            2>    
    4.  备注

**********************************************************************/
    v_sql         VARCHAR2 (3000);
    v_count       INT;
    v_heirownum   INT;
    v_lowrownum   INT;
  BEGIN
    ----取记录总数
    EXECUTE IMMEDIATE p_sqlcount
                 INTO v_count;

    p_outrecordcount := v_count;
    ----执行分页查询
    v_heirownum := p_pageno * p_pagesize;
    v_lowrownum := v_heirownum - p_pagesize + 1;
    v_sql :=
         'SELECT '
      || p_selcolumn
      || '
            FROM (
                  SELECT A.*, rownum rn
                  FROM  ('
      || p_sqlselect
      || ') A
                  WHERE rownum <= '
      || TO_CHAR (v_heirownum)
      || '
                 ) B
            WHERE rn >= '
      || TO_CHAR (v_lowrownum);
    DBMS_OUTPUT.put_line (v_sql);
    OPEN cur_out FOR v_sql;
  END p_pagequery;