Oracle通用分页存储过程和AspNetPage结合代码,Asp.Net代码库,德仔网

代码语言
代码分类
【Asp.Net】 Oracle通用分页存储过程和AspNetPage结合
作者:Dezai.CN / 发布于2013/5/13/ 798

	--包头部分

	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);
	 }
试试其它关键字
　分页存储过程　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)