Oracle通用分页存储过程和AspNetPage结合

--包头部分

create or replace package JT_P_page is
type type_cur is ref cursor;                    --定义游标变量用于返回记录集
procedure Pagination (Pindex in number,        --要显示的页数索引，从0开始
                       Psql in varchar2,        --产生分页数据的查询语句
                       Psize in number,         --每页显示记录数
                       Pcount out number,       --返回的分页数
                       Prowcount out number,    --返回的记录数
                       v_cur out type_cur      --返回分页数据的游标
                       );
end JT_P_page;  

 

--包体部分
create or replace package body JT_P_page is
procedure Pagination(Pindex in number, --要显示的页数索引，从0开始
                       Psql in varchar2, --产生分页数据的查询语句
                       Psize in number, --每页显示记录数
                       Pcount out number, --返回的分页数
                       Prowcount out number, --返回的记录数
                       v_cur out type_cur --返回分页数据的游标
                       ) AS
    v_sql VARCHAR2(1000);
    v_Pbegin number;
    v_Pend number;
begin
    v_sql := 'select count(*) from (' || Psql || ')';
    execute immediate v_sql into Prowcount; --计算记录总数
    Pcount := ceil(Prowcount / Psize); --计算分页总数
    --显示任意页内容
    v_Pend := Pindex * Psize + Psize;
    v_Pbegin := v_Pend - Psize + 1;    
    v_sql := 'SELECT * FROM (SELECT a.*, ROWNUM rn FROM (' || Psql || ') a) WHERE rn >= ' || v_Pbegin || ' AND rn <= ' || v_Pend;
    DBMS_OUTPUT.put_line(v_sql);
    open v_cur for v_sql;
end Pagination;
end JT_P_page;

 

  protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGridView(0, "DD_Demo", AspNetPager1.PageSize);
            
        }
    }
    /// <summary>
    /// 数据绑定
    /// </summary>
    /// <param name="index">当前页码</param>
    /// <param name="sql">表名或者视图名</param>
    /// <param name="pageSize">每页显示记录数</param>
    private void BindGridView(int index, string sql, int pageSize)
    {
        int totalCount = 0;
        int pageCount = 0;
        DataTable dt = ReturnDataTable(index, sql, pageSize, out totalCount, out pageCount);
        GridView1.DataSource = dt;
        GridView1.DataBind();
        AspNetPager1.RecordCount = totalCount;
        AspNetPager1.PageSize = pageSize;
        AspNetPager1.CustomInfoHTML = "  共<font color='#FF8000'><b>" + AspNetPager1.RecordCount.ToString() + "</b></font>条记录";        
        AspNetPager1.CustomInfoHTML += " 当前第<font color=\"red\"><b>" + AspNetPager1.CurrentPageIndex.ToString() + "</b></font>页";
        AspNetPager1.CustomInfoHTML += "/共<font color=#FF8000'><b>" + AspNetPager1.PageCount.ToString() + "</b></font>页";
    }
    /// <summary>
    /// 执行存储过程返回分页数据
    /// </summary>
    /// <param name="index">当前页码</param>
    /// <param name="sql">表名或者视图名</param>
    /// <param name="pageSize">每页显示记录数</param>
    /// <param name="totalCount">返回记录总数</param>
    /// <param name="pageCount">返回页面总数</param>
    /// <returns></returns>
    public static DataTable ReturnDataTable(int index, string sql, int pageSize, out int totalCount,out int pageCount)
    {
        DataTable dt = new DataTable();
        try
        {
            OracleParameter[] param = new OracleParameter[] 
            { 
                new OracleParameter("Pindex", OracleType.Number),
                new OracleParameter("Psql", OracleType.VarChar), 
                new OracleParameter("Psize", OracleType.Number), 
                new OracleParameter("Pcount", OracleType.Number), 
                new OracleParameter("Prowcount", OracleType.Number),
                new OracleParameter("v_cur", OracleType.Cursor) 
            };
            param[0].Value = index;
            param[1].Value = sql;
            param[2].Value = pageSize;
            param[0].Direction = ParameterDirection.Input;
            param[1].Direction = ParameterDirection.Input;
            param[2].Direction = ParameterDirection.Input;
            param[3].Direction = ParameterDirection.Output;
            param[4].Direction = ParameterDirection.Output;
            param[5].Direction = ParameterDirection.Output;
            dt = Maticsoft.DBUtility.DbHelperOra.RunProcedure("JT_P_page.Pagination", param, "queryTable").Tables["queryTable"];
            pageCount = int.Parse(param[3].Value.ToString());
            totalCount = int.Parse(param[4].Value.ToString());
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        return dt;
    }
    protected void AspNetPager1_PageChanged(object sender, EventArgs e)
    {
        BindGridView(AspNetPager1.CurrentPageIndex - 1, "DD_Demo", AspNetPager1.PageSize);
    }