.NET+Oracle 分页

mac2022-06-30 108

http://www.cnblogs.com/Jusoc/archive/2011/08/28/2156530.html#commentform 1、在oracle的sqlplus或其他工具中运行一下pl/sql块建立存储过程 ------------------------------------------------------------ --分页存储过程 ------------------------------------------------------------ --创建包 create or replace package testpackage as type test_cursor is ref cursor; end testpackage; / --创建过程 create or replace procedure fenye ( tableName in varchar2, --表名 fields in varchar2, --查询解果显示字段 wherecase in varchar2, --查询条件 pageSize in number, --一页显示记录数 pageNow in number, --当前页 orderField varchar2, --排序字段，为空表示不排序 orderFlag number, --排序标识 0：正序 1：倒序 myrows out number, --总记录数 myPageCount out number, --总分页 p_cursor out testpackage.test_cursor --返回的记录集 ) is --定义部分 --定义sql语句字符串 v_sql varchar2(1000); --定义两个整数 v_begin number:=(pageNow-1)*pagesize+1; --开始记录 v_end number:=pageNow*pageSize; --结束记录 --排序sql v_orderSql varchar2(100):=''; v_wherecase varchar2(100):=''; begin --执行部分 --如果orderField不为空，则进行排序，如果orderFlag=0为升序，1为降序 if orderField is not null then if orderFlag=0 then v_orderSql:=' order by '||orderField; elsif orderFlag=1 then v_orderSql:=' order by '||orderField||' desc'; else null; end if; end if; --条件判断语句 if wherecase is not null then v_wherecase:=' where '||wherecase; end if; v_sql:='select * from (select t1.* ,rownum rn from(select '|| fields ||' from '|| tableName|| v_wherecase ||' '||v_orderSql ||') t1 where rownum<='|| v_end ||') where rn>='|| v_begin; --把游标和sql关联 open p_cursor for v_sql; --计算myrows和myPageCount --组织一个sql v_sql:='select count(*) from '|| tableName || v_wherecase || ' ' ||v_orderSql; --执行sql,并把返回的值赋给myrows; execute immediate v_sql into myrows; --计算myPageCount if mod(myrows,Pagesize)=0 then myPageCount:=myrows/Pagesize; else myPageCount:=myrows/pagesize+1; end if; --关闭游标 --close p_cursor; end; / 2、.net中的oracle过程调用类 /// <summary> /// c#调用oracle的过程进行分页/// </summary> public class Paging { private string _connectionString; private string _tableName; private string _fields = "*"; private string _whercase=""; private int _pageSize=10; private int _pageNow=1; private string _orderField=""; private int _orderFlag = 0; private int _myRows; private int _myPageCount; private DataTable _result; /// <summary> /// 数据库连接字符串 /// </summary> public string ConnectionString { get { return _connectionString; } set { _connectionString = value; } } /// <summary> /// 表名 /// </summary> public string TableName { get { return _tableName; } set { _tableName = value; } } /// <summary> /// 查询结果显示字段 /// </summary> public string Fields { get { return _fields; } set { _fields = value; } } /// <summary> /// 查询条件 /// </summary> public string Whercase { get { return _whercase; } set { _whercase = value; } } /// <summary> /// 页显示记录数 /// </summary> public int PageSize { get { return _pageSize; } set { _pageSize = value; } } /// <summary> /// 当前页 /// </summary> public int PageNow { get { return _pageNow; } set { _pageNow = value; } } /// <summary> /// 排序字段，为空""表示不排序 /// </summary> public string OrderField { get { return _orderField; } set { _orderField = value; } } /// <summary> /// 排序标识 0：正序 1：倒序 /// </summary> public int OrderFlag { get { return _orderFlag; } set { _orderFlag = value; } } /// <summary> /// 总记录数 /// </summary> public int MyRows { get { return _myRows; } } /// <summary> /// 总分页 /// </summary> public int MyPageCount { get { return _myPageCount; } } /// <summary> /// 返回的记录集 /// </summary> public DataTable Result { get { return _result; } } public Paging() { fenye(); } public Paging(string connectionString,string tableName, string fields, string wherecase, int pageSize, int pageNow, string orderField, int orderFlag, out int myRows, out int myPageCount, out DataTable result) { _connectionString = connectionString; _tableName = tableName; _fields = fields; _whercase = wherecase; _pageSize = pageSize; _pageNow = pageNow; _orderField = orderField; _orderFlag = orderFlag; fenye(); myRows = _myRows; myPageCount = _myPageCount; result = _result; } private void fenye() { OracleConnection conn = new OracleConnection(_connectionString); conn.Open(); try { OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; //调用存储过程查询数据 cmd.CommandText = "fenye"; OracleParameter[] parameters = new OracleParameter[10]; //注意这里的参数名和类型号与存储过程里面的一样 parameters[0] = new OracleParameter("tableName", OracleType.VarChar); parameters[1] = new OracleParameter("fields", OracleType.VarChar); parameters[2] = new OracleParameter("wherecase", OracleType.VarChar); parameters[3] = new OracleParameter("pageSize", OracleType.Int32); parameters[4] = new OracleParameter("pageNow", OracleType.Int32); parameters[5] = new OracleParameter("orderField", OracleType.VarChar); parameters[6] = new OracleParameter("orderFlag", OracleType.Int32); parameters[7] = new OracleParameter("myrows", OracleType.Int32); parameters[8] = new OracleParameter("myPageCount", OracleType.Int32); parameters[9] = new OracleParameter("p_cursor", OracleType.Cursor); parameters[0].Value = _tableName; parameters[1].Value = _fields; parameters[2].Value = _whercase; parameters[3].Value = _pageSize; parameters[4].Value = _pageNow; parameters[5].Value = _orderField; parameters[6].Value = _orderFlag; parameters[7].Direction = ParameterDirection.Output; parameters[8].Direction = ParameterDirection.Output; parameters[9].Direction = ParameterDirection.Output; foreach (OracleParameter parameter in parameters) { cmd.Parameters.Add(parameter); } //执行命令 OracleDataAdapter oda = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); oda.Fill(ds); //得到查询结果表 _result = ds.Tables[0]; //取出总行数 _myRows = Convert.ToInt32(parameters[7].Value); //取出总页数 _myPageCount = Convert.ToInt32(parameters[8].Value.ToString()); } catch (Exception ex) { throw; } finally { conn.Close(); } } }

转载于:https://www.cnblogs.com/siyunianhua/p/3640379.html