C#調用Oracle預存程序分頁

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

端預存程序PLSQL代碼.

 

//    程式調用樣本:

//　int totalrows = 0;     //總記錄數
//    int totalpages = 0;   //總頁數

//　string tableName ="temp"; //Oracle表名

//　int pagesize =5;  //每頁記錄數

//　int indexnowpage =2;  //當前頁碼

//    String strConnection = "DataSource =temp;User ID =system; Password=admin"; //連接字串

//    OracleConnection pOrclConnection = new OracleConnection(strConnection);
//    Pagination pPagintion = new Pagination("sp_cur2", pOrclConnection);   //sp_cur2為儲存過程名稱
//    DataTable datatable = pPagintion.Paging(tablename, pagesize, indexnowpage, ref totalrows, ref

totalpages);

#region -------------------------------------------------------Oracle端分頁預存程序--------------------

--------------------------------

#region 包p_pak1
//CREATE OR REPLACE PACKAGE p_pak1 AS TYPE p_cur1 IS REF CURSOR;
//END p_pak1;
#endregion

#region 預存程序sp_pro2
//CREATE OR REPLACE PROCEDURE sp_cur2
//                                 (rowcountPerPage NUMBER,  --每頁面記錄條數
//                                  indexNowPage NUMBER,       --當前頁碼
//                                  tabName VARCHAR2,             --分頁表名
//                                  totalRows OUT NUMBER,        --總記錄數
//                                  totalPages OUT NUMBER,        --總頁數
//                                  p_cursor OUT p_pak1.p_cur1     --遊標,用於返回結果集
//                                  ) IS

//     --分頁起始記錄編號
//    v_startRowNum NUMBER:= (indexNowPage-1)*rowcountPerPage+1;
//     --分頁結束記錄編號
//    v_endRowNum NUMBER:= indexNowPage*rowcountPerpage;

//    v_sql VARCHAR2(1000);

//    BEGIN
//         --分頁查詢核心Sql
//         v_sql:='SELECT * FROM (SELECT t1.*,ROWNUM rn FROM
//                     (SELECT * FROM '||tabName||')t1 WHERE ROWNUM<='||v_endRowNum||') WHERE

rn>='||v_startrowNum;

//         --開啟遊標,關聯Sql語句
//         OPEN p_cursor FOR v_sql;

//         --查詢記錄總數
//         v_sql:='SELECT COUNT(*) FROM '||tabName;
//         EXECUTE IMMEDIATE v_sql INTO  totalRows;

//         --計算總頁數
//         IF MOD(totalRows,rowcountPerPage)=0
//         THEN
//         totalPages:=totalRows/rowcountperPage;
//         ELSE
//         totalpages:=totalRows/rowcountperPage+1;
//         END IF;

//         --關閉遊標,執行時報溢出錯誤
//         --CLOSE p_cursor;

//         END;

#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();
        }
    }

}