欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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