C#工具类OracleHelper，基于Oracle.ManagedDataAccess.Client封装

基于oracle.manageddataaccess.client封装的oracle工具类oraclehelper，代码如下：

using system;
using system.data;
using system.collections.generic;
using system.configuration;
using system.text;
using system.io;
using oracle.manageddataaccess.client;
//using system.data.oracleclient;    system.data.oracleclient类已经不推荐使用
namespace fly.util.database
{
/// <summary>
/// oracle数据库操作类
/// </summary>
public static class oraclehelper
{
/// <summary>  
/// 执行数据库非查询操作,返回受影响的行数  
/// </summary>  
/// <param name="connectionstring">数据库连接字符串</param>
/// <param name="cmdtype">命令的类型</param>
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>  
/// <param name="cmdparms">命令参数集合</param>  
/// <returns>当前操作影响的数据行数</returns>  
public static int executenonquery(string connectionstring, commandtype cmdtype, string cmdtext, params oracleparameter[] cmdparms)
{
oraclecommand cmd = new oraclecommand();
using (oracleconnection conn = new oracleconnection(connectionstring))
{
preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
int val = cmd.executenonquery();
cmd.parameters.clear();
return val;
}
}
/// <summary>  
/// 执行数据库事务非查询操作,返回受影响的行数  
/// </summary>  
/// <param name="transaction">数据库事务对象</param>  
/// <param name="cmdtype">command类型</param>  
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>  
/// <param name="cmdparms">命令参数集合</param>  
/// <returns>当前事务操作影响的数据行数</returns>  
public static int executenonquery(oracletransaction trans, commandtype cmdtype, string cmdtext, params oracleparameter[] cmdparms)
{
oraclecommand cmd = new oraclecommand();
preparecommand(cmd, trans.connection, trans, cmdtype, cmdtext, cmdparms);
int val = cmd.executenonquery();
cmd.parameters.clear();
return val;
}
/// <summary>  
/// 执行数据库非查询操作,返回受影响的行数  
/// </summary>  
/// <param name="connection">oracle数据库连接对象</param>  
/// <param name="cmdtype">command类型</param>  
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>  
/// <param name="cmdparms">命令参数集合</param>  
/// <returns>当前操作影响的数据行数</returns>  
public static int executenonquery(oracleconnection connection, commandtype cmdtype, string cmdtext, params oracleparameter[] cmdparms)
{
if (connection == null)
throw new argumentnullexception("当前数据库连接不存在");
oraclecommand cmd = new oraclecommand();
preparecommand(cmd, connection, null, cmdtype, cmdtext, cmdparms);
int val = cmd.executenonquery();
cmd.parameters.clear();
return val;
}
/// <summary>  
/// 执行数据库查询操作,返回oracledatareader类型的内存结果集  
/// </summary>  
/// <param name="connectionstring">数据库连接字符串</param>
/// <param name="cmdtype">命令的类型</param>
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>  
/// <param name="cmdparms">命令参数集合</param>  
/// <returns>当前查询操作返回的oracledatareader类型的内存结果集</returns>  
public static oracledatareader executereader(string connectionstring, commandtype cmdtype, string cmdtext, params oracleparameter[] cmdparms)
{
oraclecommand cmd = new oraclecommand();
oracleconnection conn = new oracleconnection(connectionstring);
try
{
preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
oracledatareader reader = cmd.executereader(commandbehavior.closeconnection);
cmd.parameters.clear();
return reader;
}
catch
{
cmd.dispose();
conn.close();
throw;
}
}
/// <summary>  
/// 执行数据库查询操作,返回dataset类型的结果集  
/// </summary>  
/// <param name="connectionstring">数据库连接字符串</param>
/// <param name="cmdtype">命令的类型</param>
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>  
/// <param name="cmdparms">命令参数集合</param>  
/// <returns>当前查询操作返回的dataset类型的结果集</returns>  
public static dataset executedataset(string connectionstring, commandtype cmdtype, string cmdtext, params oracleparameter[] cmdparms)
{
oraclecommand cmd = new oraclecommand();
oracleconnection conn = new oracleconnection(connectionstring);
dataset ds = null;
try
{
preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
oracledataadapter adapter = new oracledataadapter();
adapter.selectcommand = cmd;
ds = new dataset();
adapter.fill(ds);
cmd.parameters.clear();
}
catch
{
throw;
}
finally
{
cmd.dispose();
conn.close();
conn.dispose();
}
return ds;
}
/// <summary>  
/// 执行数据库查询操作,返回datatable类型的结果集  
/// </summary>  
/// <param name="connectionstring">数据库连接字符串</param>
/// <param name="cmdtype">命令的类型</param>
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>  
/// <param name="cmdparms">命令参数集合</param>  
/// <returns>当前查询操作返回的datatable类型的结果集</returns>  
public static datatable executedatatable(string connectionstring, commandtype cmdtype, string cmdtext, params oracleparameter[] cmdparms)
{
oraclecommand cmd = new oraclecommand();
oracleconnection conn = new oracleconnection(connectionstring);
datatable dt = null;
try
{
preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
oracledataadapter adapter = new oracledataadapter();
adapter.selectcommand = cmd;
dt = new datatable();
adapter.fill(dt);
cmd.parameters.clear();
}
catch
{
throw;
}
finally
{
cmd.dispose();
conn.close();
conn.dispose();
}
return dt;
}
/// <summary>  
/// 执行数据库查询操作,返回结果集中位于第一行第一列的object类型的值  
/// </summary>  
/// <param name="connectionstring">数据库连接字符串</param>
/// <param name="cmdtype">命令的类型</param>
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>  
/// <param name="cmdparms">命令参数集合</param>  
/// <returns>当前查询操作返回的结果集中位于第一行第一列的object类型的值</returns>  
public static object executescalar(string connectionstring, commandtype cmdtype, string cmdtext, params oracleparameter[] cmdparms)
{
oraclecommand cmd = new oraclecommand();
oracleconnection conn = new oracleconnection(connectionstring);
object result = null;
try
{
preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
result = cmd.executescalar();
cmd.parameters.clear();
}
catch
{
throw;
}
finally
{
cmd.dispose();
conn.close();
conn.dispose();
}
return result;
}
/// <summary>  
/// 执行数据库事务查询操作,返回结果集中位于第一行第一列的object类型的值  
/// </summary>  
/// <param name="trans">一个已存在的数据库事务对象</param>  
/// <param name="commandtype">命令类型</param>  
/// <param name="commandtext">oracle存储过程名称或pl/sql命令</param>  
/// <param name="cmdparms">命令参数集合</param>  
/// <returns>当前事务查询操作返回的结果集中位于第一行第一列的object类型的值</returns>  
public static object executescalar(oracletransaction trans, commandtype cmdtype, string cmdtext, params oracleparameter[] cmdparms)
{
if (trans == null)
throw new argumentnullexception("当前数据库事务不存在");
oracleconnection conn = trans.connection;
if (conn == null)
throw new argumentexception("当前事务所在的数据库连接不存在");
oraclecommand cmd = new oraclecommand();
object result = null;
try
{
preparecommand(cmd, conn, trans, cmdtype, cmdtext, cmdparms);
result = cmd.executescalar();
cmd.parameters.clear();
}
catch
{
throw;
}
finally
{
trans.dispose();
cmd.dispose();
conn.close();
conn.dispose();
}
return result;
}
/// <summary>  
/// 执行数据库查询操作,返回结果集中位于第一行第一列的object类型的值  
/// </summary>  
/// <param name="conn">数据库连接对象</param>  
/// <param name="cmdtype">command类型</param>  
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>  
/// <param name="cmdparms">命令参数集合</param>  
/// <returns>当前查询操作返回的结果集中位于第一行第一列的object类型的值</returns>  
public static object executescalar(oracleconnection conn, commandtype cmdtype, string cmdtext, params oracleparameter[] cmdparms)
{
if (conn == null) throw new argumentexception("当前数据库连接不存在");
oraclecommand cmd = new oraclecommand();
object result = null;
try
{
preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
result = cmd.executescalar();
cmd.parameters.clear();
}
catch
{
throw;
}
finally
{
cmd.dispose();
conn.close();
conn.dispose();
}
return result;
}
/// <summary>  
/// 执行数据库命令前的准备工作  
/// </summary>  
/// <param name="cmd">command对象</param>  
/// <param name="conn">数据库连接对象</param>  
/// <param name="trans">事务对象</param>  
/// <param name="cmdtype">command类型</param>  
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>  
/// <param name="cmdparms">命令参数集合</param>  
private static void preparecommand(oraclecommand cmd, oracleconnection conn, oracletransaction trans, commandtype cmdtype, string cmdtext, oracleparameter[] cmdparms)
{
if (conn.state != connectionstate.open)
conn.open();
cmd.connection = conn;
cmd.commandtext = cmdtext;
if (trans != null)
cmd.transaction = trans;
cmd.commandtype = cmdtype;
if (cmdparms != null)
{
foreach (oracleparameter parm in cmdparms)
cmd.parameters.add(parm);
}
}
/// <summary>  
/// 将.net日期时间类型转化为oracle兼容的日期时间格式字符串  
/// </summary>  
/// <param name="date">.net日期时间类型对象</param>  
/// <returns>oracle兼容的日期时间格式字符串（如该字符串：to_date('2007-12-1','yyyy-mm-dd')）</returns>  
public static string getoracledateformat(datetime date)
{
return "to_date('" + date.tostring("yyyy-m-dd") + "','yyyy-mm-dd')";
}
/// <summary>  
/// 将.net日期时间类型转化为oracle兼容的日期格式字符串  
/// </summary>  
/// <param name="date">.net日期时间类型对象</param>  
/// <param name="format">oracle日期时间类型格式化限定符</param>  
/// <returns>oracle兼容的日期时间格式字符串（如该字符串：to_date('2007-12-1','yyyy-mm-dd')）</returns>  
public static string getoracledateformat(datetime date, string format)
{
if (format == null || format.trim() == "") format = "yyyy-mm-dd";
return "to_date('" + date.tostring("yyyy-m-dd") + "','" + format + "')";
}
/// <summary>  
/// 将指定的关键字处理为模糊查询时的合法参数值  
/// </summary>  
/// <param name="source">待处理的查询关键字</param>  
/// <returns>过滤后的查询关键字</returns>  
public static string handlelikekey(string source)
{
if (source == null || source.trim() == "") return null;
source = source.replace("[", "[]]");
source = source.replace("_", "[_]");
source = source.replace("%", "[%]");
return ("%" + source + "%");
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="connection">sqlserver数据库连接对象</param>  
/// <param name="storedprocname">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>sqldatareader对象</returns>
public static oracledatareader runstoredprocedure(oracleconnection connection, string storedprocname, idataparameter[] parameters)
{
oracledatareader returnreader = null;
connection.open();
oraclecommand command = buildsqlcommand(connection, storedprocname, parameters);
returnreader = command.executereader(commandbehavior.closeconnection);
return returnreader;
}
/// <summary>
/// 构建sqlcommand对象
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedprocname">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>sqlcommand</returns>
private static oraclecommand buildsqlcommand(oracleconnection connection, string storedprocname, idataparameter[] parameters)
{
oraclecommand command = new oraclecommand(storedprocname, connection);
command.commandtype = commandtype.storedprocedure;
foreach (oracleparameter parameter in parameters)
{
command.parameters.add(parameter);
}
return command;
}
}
}