OracleHelper操作类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using System.IO;
using System.Configuration;


namespace xxx.DBHelper
{
    public static class OracleTools
    {
        /// <summary>
        /// 依据连接串名字connectionName返回数据连接字符串  
        /// </summary>
        /// <param name="connectionName">key值</param>
        /// <returns></returns>
        public static string GetConnectionStringsConfig(string connectionName)
        {
            //指定config文件读取
            //获取Configuration对象
            //winform的
            Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            //web的
            //Configuration config = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~");
            //根据Key读取<add>元素的Value
            return config.AppSettings.Settings[connectionName].Value;


        }

        #region [ 连接对象 ]
        /// <summary>  
        /// 连接对象 字段  
        /// </summary>  
        private static OracleConnection conn = null;
        /// <summary>  
        /// 连接串 字段  
        /// </summary>  

        //private static string connstr = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));User Id=leaf;Password=leaf;";  

        public static string connstr = ConfigurationManager.AppSettings["OracleConStr"].ToString();
        //public static string connstr = GetConnectionStringsConfig("OracleConStr");

        /// <summary>  
        /// 取得连接串  
        /// </summary>  
        public static string GetConnectionString
        {
            get
            {
                return connstr;
            }
        }

        /// <summary>  
        /// 取得连接对象, 没有打开  
        /// </summary>  
        public static OracleConnection GetOracleConnection
        {
            get
            {
                //return new OracleConnection(GetConnectionString);
                string conn = ConfigurationManager.AppSettings["OracleConStr"].ToString();// GetConnectionStringsConfig("OracleConStr");
                return new OracleConnection(conn);
            }
        }

        /// <summary>  
        /// 取得连接对象， 并打开  
        /// </summary>  
        public static OracleConnection GetOracleConnectionAndOpen
        {
            get
            {
                OracleConnection conn = GetOracleConnection;
                conn.Open();
                return conn;
            }
        }

        /// <summary>  
        /// 彻底关闭并释放 OracleConnection 对象，再置为null.  
        /// </summary>  
        /// <param name="conn">OracleConnection</param>  
        public static void CloseOracleConnection(OracleConnection conn)
        {
            if (conn == null)
                return;
            conn.Close();
            conn.Dispose();
            conn = null;
        }
        #endregion

        #region [ ExecuteNonQuery ]
        /// <summary>  
        /// 普通SQL语句执行增删改  
        /// </summary>  
        /// <param name="cmdText">SQL语句</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns>受影响行数</returns>  
        public static int ExecuteNonQuery(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteNonQuery(cmdText, CommandType.Text, commandParameters);
        }
        /// <summary>  
        /// 存储过程执行增删改  
        /// </summary>  
        /// <param name="cmdText">存储过程</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns>受影响行数</returns>  
        public static int ExecuteNonQueryByProc(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteNonQuery(cmdText, CommandType.StoredProcedure, commandParameters);
        }
        /// <summary>  
        /// 执行增删改  
        /// </summary>  
        /// <param name="cmdText">命令字符串</param>  
        /// <param name="cmdType">命令类型</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns>受影响行数</returns>  
        public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
        {
            int result = 0;
            OracleConnection conn = null;

            try
            {
                conn = GetOracleConnectionAndOpen;
                OracleCommand command = new OracleCommand();
                PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
                result = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                result = -1;
            }
            finally
            {
                if (conn != null)
                    CloseOracleConnection(conn);
            }

            return result;
        }
        #endregion

        #region [ ExecuteReader ]
        /// <summary>  
        /// SQL语句得到 OracleDataReader 对象  
        /// </summary>  
        /// <param name="cmdText">命令字符串</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns>OracleDataReader 对象</returns>  
        public static OracleDataReader ExecuteReader(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteReader(cmdText, CommandType.Text, commandParameters);
        }
        /// <summary>  
        /// 存储过程得到 OracleDataReader 对象  
        /// </summary>  
        /// <param name="cmdText">命令字符串</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns>OracleDataReader 对象</returns>  
        public static OracleDataReader ExecuteReaderByProc(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteReader(cmdText, CommandType.StoredProcedure, commandParameters);
        }
        /// <summary>  
        /// 得到 OracleDataReader 对象  
        /// </summary>  
        /// <param name="cmdText">命令字符串</param>  
        /// <param name="cmdType">命令类型</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns>OracleDataReader 对象</returns>  
        public static OracleDataReader ExecuteReader(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
        {
            OracleDataReader result = null;
            OracleConnection conn = null;

            try
            {
                conn = GetOracleConnectionAndOpen;
                OracleCommand command = new OracleCommand();
                PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
                result = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                result = null;
            }
            finally
            {
                if (conn != null)
                    CloseOracleConnection(conn);
            }

            return result;
        }
        #endregion

        #region [ ExecuteScalar ]
        /// <summary>  
        /// 执行SQL语句, 返回Object  
        /// </summary>  
        /// <param name="cmdText">命令字符串</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns> Object </returns>  
        public static Object ExecuteScalar(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteScalar(cmdText, CommandType.Text, commandParameters);
        }

        /// <summary>  
        /// 执行存储过程, 返回Object  
        /// </summary>  
        /// <param name="cmdText">命令字符串</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns> Object </returns>  
        public static Object ExecuteScalarByProc(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteScalar(cmdText, CommandType.StoredProcedure, commandParameters);
        }

        /// <summary>  
        /// 返回Object  
        /// </summary>  
        /// <param name="cmdText">命令字符串</param>  
        /// <param name="cmdType">命令类型</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns> Object </returns>  
        public static Object ExecuteScalar(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
        {
            Object result = null;
            OracleConnection conn = null;

            try
            {
                conn = GetOracleConnectionAndOpen;
                OracleCommand command = new OracleCommand();

                PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
                result = command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                result = null;
            }
            finally
            {
                if (conn != null)
                    CloseOracleConnection(conn);
            }

            return result;
        }
        #endregion

        #region [ ExecuteDataSet ]
        /// <summary>  
        /// 执行SQL语句, 返回DataSet  
        /// </summary>  
        /// <param name="cmdText">命令字符串</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns> DataSet </returns>  
        public static DataSet ExecuteDataSet(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteDataSet(cmdText, CommandType.Text, commandParameters);
        }

        /// <summary>  
        /// 执行存储过程, 返回DataSet  
        /// </summary>  
        /// <param name="cmdText">命令字符串</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns> DataSet </returns>  
        public static DataSet ExecuteDataSetByProc(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteDataSet(cmdText, CommandType.StoredProcedure, commandParameters);
        }

        /// <summary>  
        /// 返回DataSet  
        /// </summary>  
        /// <param name="cmdText">命令字符串</param>  
        /// <param name="cmdType">命令类型</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns> DataSet </returns>  
        public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
        {
            DataSet result = null;
            OracleConnection conn = null;

            try
            {
                conn = GetOracleConnectionAndOpen;
                OracleCommand command = new OracleCommand();

                PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
                OracleDataAdapter adapter = new OracleDataAdapter();
                adapter.SelectCommand = command;
                result = new DataSet();
                adapter.Fill(result);
            }
            catch (Exception ex)
            {
                result = null;
                //LogHelper.WriteLog(typeof(OracleTools), string.Format("连接oracle数据库出错：sql={2},Message={0},StackTrace={1}", ex.Message, ex.StackTrace, cmdText));
                throw new Exception(string.Format("连接oracle数据库出错：sql={2},Message={0},StackTrace={1}", ex.Message, ex.StackTrace, cmdText));                      
            }
            finally
            {
                if (conn != null)
                    CloseOracleConnection(conn);
            }

            return result;
        }

        public static string GetYMD()
        {
            string day = DateTime.Now.Day.ToString();
            return DateTime.Now.Year.ToString().Substring(2, 2) + (DateTime.Now.Month.ToString().Length == 1 ? "0" + DateTime.Now.Month.ToString() : DateTime.Now.Month.ToString()) + (day.Length == 1 ? "0" + day : day);
        }


        public static object ExecuteDataSet_self(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
        {
            string messge = string.Empty;
            object result = null;
            DataSet ds = new DataSet();
            OracleConnection conn = null;

            try
            {
                conn = GetOracleConnectionAndOpen;
                OracleCommand command = new OracleCommand();

                PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
                OracleDataAdapter adapter = new OracleDataAdapter();
                adapter.SelectCommand = command;
                //result = new DataSet();
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                messge = ex.Message;
            }
            finally
            {
                if (conn != null)
                    CloseOracleConnection(conn);
            }
            if (messge.Length > 0)
                result = messge;
            else
                result = ds;
            return result;
        }
        #endregion

        #region [ ExecuteDataTable ]
        /// <summary>  
        /// 执行SQL语句, 返回DataTable  
        /// </summary>  
        /// <param name="cmdText">命令字符串</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns> DataTable </returns>  
        public static DataTable ExecuteDataTable(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteDataTable(cmdText, CommandType.Text, commandParameters);
        }

        /// <summary>  
        /// 执行存储过程, 返回DataTable  
        /// </summary>  
        /// <param name="cmdText">命令字符串</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns> DataTable </returns>  
        public static DataTable ExecuteDataTableByProc(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteDataTable(cmdText, CommandType.StoredProcedure, commandParameters);
        }

        /// <summary>  
        /// 返回DataTable  
        /// </summary>  
        /// <param name="cmdText">命令字符串</param>  
        /// <param name="cmdType">命令类型</param>  
        /// <param name="commandParameters">可变参数</param>  
        /// <returns> DataTable </returns>  
        public static DataTable ExecuteDataTable(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
        {
            DataTable dtResult = null;
            DataSet ds = ExecuteDataSet(cmdText, cmdType, commandParameters);

            if (ds != null && ds.Tables.Count > 0)
            {
                dtResult = ds.Tables[0];
            }
            return dtResult;
        }
        #endregion

        #region [ PrepareCommand ]
        /// <summary>  
        /// Command对象执行前预处理  
        /// </summary>  
        /// <param name="command"></param>  
        /// <param name="connection"></param>  
        /// <param name="trans"></param>  
        /// <param name="cmdType"></param>  
        /// <param name="cmdText"></param>  
        /// <param name="commandParameters"></param>  
        private static void PrepareCommand(OracleCommand command, OracleConnection connection, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
        {
            if (connection.State != ConnectionState.Open) connection.Open();

            command.Connection = connection;
            command.CommandText = cmdText;
            command.CommandType = cmdType;

            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                    command.Parameters.Add(parm);
            }
        }
        #endregion

        /// <summary>
        /// 事务执行，返回字符串空时表示成功
        /// </summary>
        /// <param name="comList"></param>
        /// <returns></returns>
        public static string ExecuteSqlWithTrans(List<OracleCommandInfo> comList)
        {
            using (OracleConnection conn = GetOracleConnectionAndOpen)
            {
                OracleTransaction trans = conn.BeginTransaction();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.Transaction = trans;
                try
                {
                    int count = 0;
                    for (int i = 0; i < comList.Count; i++)
                    {
                        cmd.CommandText = comList[i].CommandText;

                        //存储过程 or Sql语句
                        if (comList[i].IsProc)
                            cmd.CommandType = CommandType.StoredProcedure;
                        else
                            cmd.CommandType = CommandType.Text;

                        if (comList[i].Parameters.Length > 0)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddRange(comList[i].Parameters);
                        }
                        count += cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    trans.Commit();

                    return string.Empty;
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    return string.Format("Oracle执行事务出现异常，exception={0}", ex.ToString());
                }
            }
        }
    }

    public class OracleCommandInfo
    {
        public string CommandText { get; set; }
        public bool IsProc { get; set; }
        public OracleParameter[] Parameters { get; set; }

        public OracleCommandInfo(string commandText, bool isProc)
        {
            CommandText = commandText;
            IsProc = isProc;
        }
    }
}

<!--oracle数据库连接字符串-->
<add key="OracleConStr" value="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.1.2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));Persist Security Info=True;User ID=xxx;Password=xxx;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5; Decr Pool Size=1;"/>

使用示例：

public ResultModel AddOutBound(List<b_DataSync_OutBoundByTJDX> list)
        {
            var lstOracleCommandInfo = new List<OracleCommandInfo>();
            var sql = @"insert into TPB_GWO_DX(
                         TGD_ID
                        ,TGD_PI_CODE
                        ,TGD_PD_NAME
                        ,TGD_PI_SPEC
                        ,TGD_PI_SPEC1
                        ,TGD_DATE
                        ,TGD_LOT
                        ,TGD_EXP_DATE
                        ,TGD_PRD_DATE
                        ,TGD_UNIT
                        ,TGD_QUAN
                        ,TGD_PRICE
                        ,TGD_AMOUNT
                        ,TGD_DEPT_ID
                        ,TGD_DEPT_NAME) 
                    values(
                         :TGD_ID
                        ,:TGD_PI_CODE
                        ,:TGD_PD_NAME
                        ,:TGD_PI_SPEC
                        ,:TGD_PI_SPEC1
                        ,:TGD_DATE
                        ,:TGD_LOT
                        ,:TGD_EXP_DATE
                        ,:TGD_PRD_DATE
                        ,:TGD_UNIT
                        ,:TGD_QUAN
                        ,:TGD_PRICE
                        ,:TGD_AMOUNT
                        ,:TGD_DEPT_ID
                        ,:TGD_DEPT_NAME
                    )";

            foreach (var item in list)
            {
                var lstParameter = new List<OracleParameter>();
                lstParameter.Add(new OracleParameter(":TGD_ID", item.TGD_ID));
                lstParameter.Add(new OracleParameter(":TGD_PI_CODE", item.TGD_PI_CODE));
                lstParameter.Add(new OracleParameter(":TGD_PD_NAME", item.TGD_PD_NAME));
                lstParameter.Add(new OracleParameter(":TGD_PI_SPEC", item.TGD_PI_SPEC));
                lstParameter.Add(new OracleParameter(":TGD_PI_SPEC1", item.TGD_PI_SPEC1));
                lstParameter.Add(new OracleParameter(":TGD_DATE", item.TGD_DATE));
                lstParameter.Add(new OracleParameter(":TGD_LOT", item.TGD_LOT));
                lstParameter.Add(new OracleParameter(":TGD_EXP_DATE", item.TGD_EXP_DATE));
                lstParameter.Add(new OracleParameter(":TGD_PRD_DATE", item.TGD_PRD_DATE));
                lstParameter.Add(new OracleParameter(":TGD_UNIT", item.TGD_UNIT));
                lstParameter.Add(new OracleParameter(":TGD_QUAN", item.TGD_QUAN));
                lstParameter.Add(new OracleParameter(":TGD_PRICE", item.TGD_PRICE));
                lstParameter.Add(new OracleParameter(":TGD_AMOUNT", item.TGD_AMOUNT));
                lstParameter.Add(new OracleParameter(":TGD_DEPT_ID", item.TGD_DEPT_ID));
                lstParameter.Add(new OracleParameter(":TGD_DEPT_NAME", item.TGD_DEPT_NAME));

                var oracleCommandInfo = new OracleCommandInfo(sql, false);
                oracleCommandInfo.Parameters = lstParameter.ToArray();
                lstOracleCommandInfo.Add(oracleCommandInfo);
            }

            var message = OracleTools.ExecuteSqlWithTrans(lstOracleCommandInfo);
            return string.IsNullOrWhiteSpace(message) ? new ResultModel(1, "操作成功！") : new ResultModel(-1, message);
        }

        public DataTable GetPurchaseApplyData(DateTime startDate, DateTime? endDate)
        {
            string sql = @"select * from yxzb.vw_order
                           where ORDERDATE > :StartDate ";

            if (endDate.HasValue)
                sql += " and ORDERDATE <= :EndDate ";

            List<OracleParameter> lstPt = new List<OracleParameter>();
            lstPt.Add(new OracleParameter(":StartDate", startDate));
            if (endDate.HasValue)
                lstPt.Add(new OracleParameter(":EndDate", endDate));
            OracleParameter[] parameters = lstPt.ToArray();
            DataTable dt = OracleTools.ExecuteDataTable(sql, parameters);
            return dt;
        }