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

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