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);
}