C#工具类OracleHelper,基于Oracle.ManagedDataAccess.Client封装
程序员文章站
2022-05-27 09:20:04
基于Oracle.ManagedDataAccess.Client封装的Oracle工具类OracleHelper,代码如下: ......
基于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; } } }
推荐阅读
-
C#编写了一个基于Lucene.Net的搜索引擎查询通用工具类:SearchEngineUtil
-
基于C#实现XML文件读取工具类
-
基于C#实现XML文件读取工具类
-
【C#】工具类-FTP操作封装类FTPHelper
-
C#编写了一个基于Lucene.Net的搜索引擎查询通用工具类:SearchEngineUtil
-
C#工具类OracleHelper,基于Oracle.ManagedDataAccess.Client封装
-
PHP基于MySQLI函数封装的数据库连接工具类【定义与用法】
-
封装一个基于NLog+NLog.Mongo的日志记录工具类LogUtil
-
分享基于MemoryCache(内存缓存)的缓存工具类,C# B/S 、C/S项目均可以使用!
-
基于MongoDb官方C#驱动封装MongoDbCsharpHelper类(CRUD类)