Oracle與C#預存程序分頁

--分頁
select * from(select t1.ename ,rownum rn from (select * from emp) t1 where rownum<=10) where rn>=6;

 

--開發一個包
create or replace package tespackage as
type test_cursor is ref cursor;
end tespackage;

--分頁預存程序
create or replace procedure sp_fengye
(tableName in varchar2,--參數1 表名字
pagesize in number,
pageNow in number,
myrows out number,--總記錄數
myPageCount out number,--總頁數
p_cursor out tespackage.test_cursor --返回記錄集
)is
--定義部分
 --定義sql語句 字串
 v_sql varchar2(1000);
--定義兩個整數
v_begin number:=(pageNow-1)*pagesize+1;  --從哪裡開始取
v_end number:=pageNow*pagesize;
begin
--執行部分
v_sql:='select * from(select t1.ename ,rownum rn from
 (select * from '||tableName||' order by sal) 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;
--執行sql，並把傳回值賦給myrows；
execute immediate  v_sql into myrows;
--計算myPagweCount
if mod(myrows,pagesize)=0 then
myPageCount:=myrows/pagesize;
else
myPageCount:=myrows/pagesize+1;
end if;
--關閉遊標
close p_cursor;
end;

 

　　直接調用Oracle中的預存程序完成對錶的分頁,對代碼封閉程度高,執行高效.以下為C#分頁類代碼,包括Oracle資料庫端預存程序PLSQL代碼.

 

#endregion

#endregion
using System;
using System.Collections.Generic;
using System.Web;
using System.Data.OracleClient;
using System.Data;

/// <summary>
///調用Oracle預存程序對錶進行分頁
/// </summary>
public class Pagination
{
   
    string m_procedureName;//要調用的預存程序名稱
    OracleConnection m_oracleConnection; //Oracle連線物件

    /// <summary>
    /// 建構函式,傳入預存程序名稱與連線物件
    /// </summary>
    /// <param name="procedureName">預存程序名稱</param>
    /// <param name="orclConnection">初始化後連接字串的Oracle連線物件</param>
 public Pagination(string procedureName,OracleConnection orclConnection)
 {
        m_procedureName = procedureName;
        m_oracleConnection = orclConnection;
 }
   
    /// <summary>
    /// 執行分頁
    /// </summary>
    /// <param name="tableName">需分頁表名</param>
    /// <param name="paeSize">每頁記錄數</param>
    /// <param name="indexNowPage">當前頁碼</param>
    /// <param name="totalRows">引用參數,總記錄數</param>
    /// <param name="totalPages">引用參數,總頁數</param>
    /// <returns>分頁結果集</returns>
    public DataTable Paging(string tableName, int paeSize, int indexNowPage, ref int totalRows, ref int totalPages)
    {
        try
        {
            //開啟串連
            OpenOracleConnection();

            //定義OracleCommand對象,設定命令類型為預存程序
            OracleCommand pOracleCMD = new OracleCommand(m_procedureName, m_oracleConnection);
            pOracleCMD.CommandType = CommandType.StoredProcedure;

            //根據預存程序的參數個數及類型產生參數對象
            OracleParameter p1 = new OracleParameter("rowCountPerPage", OracleType.Number, 10);
            OracleParameter p2 = new OracleParameter("indexNowPage", OracleType.Number, 10);
            OracleParameter p3 = new OracleParameter("tabName", OracleType.VarChar, 50);
            OracleParameter p4 = new OracleParameter("totalRows", OracleType.Number, 10);
            OracleParameter p5 = new OracleParameter("totalPages", OracleType.Int16, 10);
            OracleParameter p6 = new OracleParameter("p_cursor", OracleType.Cursor);

            //設定參數的輸入輸出類型,預設為輸入
            p1.Direction = ParameterDirection.Input;
            p2.Direction = ParameterDirection.Input;
            p3.Direction = ParameterDirection.Input;
            p4.Direction = ParameterDirection.Output;
            p5.Direction = ParameterDirection.Output;
            p6.Direction = ParameterDirection.Output;

            //對輸入參數定義初值,輸出參數不必賦值.
            p1.Value = paeSize;
            p2.Value = indexNowPage;
            p3.Value = tableName;

            //按照預存程序參數順序把參數依次加入到OracleCommand對象參數集合中
            pOracleCMD.Parameters.Add(p1);
            pOracleCMD.Parameters.Add(p2);
            pOracleCMD.Parameters.Add(p3);
            pOracleCMD.Parameters.Add(p4);
            pOracleCMD.Parameters.Add(p5);
            pOracleCMD.Parameters.Add(p6);

            //執行,把分頁結果集填入datatable中
            OracleDataAdapter pOracleDataAdapter = new OracleDataAdapter(pOracleCMD);
            DataTable datatable = new DataTable();
            pOracleDataAdapter.Fill(datatable);

            //在執行結束後,從預存程序輸出參數中取得相應的值放入引用參數中以供程式調用
            totalRows = int.Parse(p4.Value.ToString());
            totalPages = int.Parse(p5.Value.ToString());

            //關閉串連
            CloseOracleConnection();

            return datatable;
        }
        catch(Exception ex)
        {
            return null;
        }

    }

    /// <summary>
    /// 關閉串連
    /// </summary>
    private void CloseOracleConnection()
    {
        if (m_oracleConnection.State == ConnectionState.Open)
        {
            m_oracleConnection.Close();
        }
    }

    /// <summary>
    /// 開啟串連
    /// </summary>
    private void OpenOracleConnection()
    {
        if (m_oracleConnection.State== ConnectionState.Closed)
        {
            m_oracleConnection.Open();
        }
    }

}