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

--包头部分create or replace package JT_P_page istype 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 isprocedure 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);    }