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

2016-12-30 09:54:53来源:oschina作者:深圳大道人点击

第七城市
--包头部分
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);

}
}
///
/// 数据绑定
///

/// 当前页码
/// 表名或者视图名
/// 每页显示记录数
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 = "共" + AspNetPager1.RecordCount.ToString() + "条记录";
AspNetPager1.CustomInfoHTML += " 当前第" + AspNetPager1.CurrentPageIndex.ToString() + "页";
AspNetPager1.CustomInfoHTML += "/共" + AspNetPager1.PageCount.ToString() + "页";
}
///
/// 执行存储过程返回分页数据
///

/// 当前页码
/// 表名或者视图名
/// 每页显示记录数
/// 返回记录总数
/// 返回页面总数
///
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);
}
第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台