c#+oracle預存程序實現分頁

標籤：des   io   ar   os   sp   for   資料   on   問題   

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:=trunc(myrows/pagesize)+1;//liujun 修改一下否則會有小數點問題
  end if;
  --關閉遊標
  --close p_cursor;
end;
/

 2、.net中的oracle程序呼叫類

/// <summary>
    /// c#調用oracle的過程進行分頁
    /// author:sirc_ljp
    /// date:2011.11.16
    /// </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();
            }
            
        }

    }

c#+oracle預存程序實現分頁