C#调用Oracle存储过程分页

直接调用Oracle中的存储过程完成对表的分页,对代码封闭程度高,执行高效.以下为C#分页类代码,包括Oracle数据库端存储过程PLSQL代码.

 

//    程序调用示例:

//　int totalrows = 0;     //总记录数
//    int totalpages = 0;   //总页数

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

//　int pagesize =5;  //每页记录数

//　int indexnowpage =2;  //当前页码

//    String strConnection = "DataSource =db1;User ID =LuGang; Password="GIS""; //连接字符串

//    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);

 

//         Author: Lu Gang 
//　　Email: giser@139.com
//　　Date: 2009.10.29
#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();
        }
    }

}