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