OracleHelper类

using System;
using System.Data;
using System.Collections;
using System.Configuration;
using System.Data.OracleClient;

namespace DBhepler
{
    /// <summary>
    /// A helper class used to execute queries against an Oracle database||一个用于Oracle DB查询的帮助类
    /// </summary>
    public abstract class OracleHelper
    {
        //Database connection strings
        private static readonly string connectionString =ConfigurationManager.AppSettings["OrcConnStr"];

        //Create a hashtable for the parameter cached||为缓存中的参数创建一张hashtable
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// Internal function to prepare a command for execution by the database||关于command的数据库执行的内部函数
        /// </summary>
        /// <param name="cmd">Existing command object||现有的command(命令)对象</param>
        /// <param name="conn">Database connection object数据库链接对象</param>
        /// <param name="trans">Optional transaction object||可选事务对象</param>
        /// <param name="cmdType">Command type,e.g. stored procedure||命令类型：例如：存储结构</param>
        /// <param name="cmdText">Command text||命令文本</param>
        /// <param name="commandParameters">Parameters for the command||命令参数</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
        {
            //Open the connection if required
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //Set up the command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //Bind it to the transaction if it exists
            if (trans != null)
                cmd.Transaction = trans;

            // Bind the parameters passed in
            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                    cmd.Parameters.Add(parm);
            }
        }
 
        /// <summary>
        /// Execute a database query which does not include a select||执行数据库查询，其中不包括一个选择
        /// </summary>
        /// <remark>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(conn, CommandType.Text, sql, new OracleParameter(":prodid", 24));
        /// </remark>
        /// <param name="connString">Connection string to database||数据库链接字符串</param>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)||命令类型（SQL语句等）</param>
        /// <param name="cmdText">the stored procedure name or PL/SQL command||存储过程或SQL语句</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command||用于执行命令oracleparamters数组</param>
        /// <returns>an int representing the number of rows affected by the command||返回该命令影响的行数（int类型）</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
        {
            OracleCommand cmd = new OracleCommand();
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                conn.Open();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute an OracleCommand (that returns no resultset) against an existing database transaction||对现有的数据库事务执行OracleCommand（不返回结果集） 
        /// using the provided parameters.||使用提供的参数
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="trans">an existing database transaction||现有的数据库事务</param>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)||命令类型（存储过程等）</param>
        /// <param name="cmdText">the stored procedure name or PL/SQL command||存储过程或SQL语句</param>
        /// <param name="cmdParameters">an array of OracleParamters used to execute the command||用于执行命令oracleparamters数组</param>
        /// <returns>an int representing the number of rows affected by the command||返回该命令影响的行数（int类型）</returns>
        public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;

        }
        
        /// <summary>
        /// Execute an OracleCommand (that returns no resultset) against an existing database connection||对现有的数据库链接执行OracleCommand（不返回结果集）
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.TableDirect,, "TableName", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="connectionString">an existing database connection||现有的数据库链接</param>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)||命令类型（表等）</param>
        /// <param name="cmdText">the stored procedure name or PL/SQL command||存储过程或SQL语句</param>
        /// <param name="cmdParameters">an array of OracleParamters used to execute the command||用于执行命令oracleparamters数组</param>
        /// <returns>an int representing the number of rows affected by the command||返回该命令影响的行数（int类型）</returns>
        public static int ExecuteNonQuery(OracleConnection connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, connectionString, null, cmdType, cmdText, cmdParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;

        }

        /// <summary>
        /// Batch execution about ExecuteNonQuery||ExecuteNonQuery的批量执行
        /// </summary>
        /// <remarks>
        /// Usage according to ExecuteNonQuery||用法参照ExecuteNonQuery
        /// </remarks>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParameters"></param>
        /// <returns></returns>
        public static int ExecuteBatchNonQuery(string connectionString, CommandType cmdType, string[] cmdText, params OracleParameter[] cmdParameters)
        {
            int val = 0;
            OracleCommand cmd = new OracleCommand();
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                conn.Open();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try 
                {
                    for(int i=0;i<cmdText.Length;i++)
                    {
                        cmd.CommandText = cmdText[i];
                        cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                    val = 1;
                }
                catch(System.Data.OracleClient.OracleException e)
                {
                    tx.Rollback();
                    throw new Exception(e.Message);
                }
                finally
                {
                    conn.Close();
                }
                return val;
            }
        }

        /// <summary>
        /// Execute a select query that will return a result set||执行一个查询，将返回结果集
        /// </summary>
        /// <param name="connString">Connection string</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns>return a result set(OracleDataReader)</returns>
        public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
        {
            OracleCommand cmd = new OracleCommand();
            OracleConnection conn = new OracleConnection(connectionString);
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParameters);

                OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
            finally
            { 
            }
        }

        /// <summary>
        /// Execute a select query that will return a DataSet||执行一个查询，将返回数据集
        /// </summary>
        /// <param name="connString">Connection string</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns>return a result set(OracleDataReader)</returns>
        public static DataSet ExecuteReadAdapter(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
        {
            OracleCommand cmd = new OracleCommand();
            OracleConnection conn = new OracleConnection(connectionString);
            DataSet ds = new DataSet();
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParameters);

                OracleDataAdapter OrcAdapter = new OracleDataAdapter(cmd);
                OrcAdapter.Fill(ds);
                cmd.Parameters.Clear();
                return ds;
            }
            catch
            {
                conn.Close();
                throw;
            }
            finally
            {
            }
        }

        /// <summary>
        /// Execute an OracleCommand that returns the first column of the first record against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>

        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
        {
            OracleCommand cmd = new OracleCommand();

            using(OracleConnection conn=new OracleConnection(connectionString))
            {
                PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        ///<summary>
        ///Execute a OracleCommand (that returns a 1x1 resultset) against the specified SqlTransaction
        ///using the provided parameters.
        ///</summary>
        ///<param name="transaction">A valid SqlTransaction</param>
        ///<param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        ///<param name="commandText">The stored procedure name    or PL/SQL command</param>
        ///<param name="commandParameters">An array of    OracleParamters used to execute the command</param>
        ///<returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalar(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
        {
            if (trans == null)
                throw new ArgumentNullException("trans");
            if (trans != null && trans.Connection == null)
                throw new ArgumentException("The transaction was rollbacked or commited,please provide an open transacton.","trans");

            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;

        }

        /// <summary>
        /// Execute an OracleCommand that returns the first column of the first record against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(OracleConnection connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
        {
            OracleCommand cmd = new OracleCommand();

            PrepareCommand(cmd, connectionString, null, cmdType, cmdText, cmdParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }
        
        /// <summary>
        /// Add a set of parameters to the cached||缓存中添加一个参数设置
        /// </summary>
        /// <param name="cacheKey">Key value to look up the parameters||查找参数的关键值</param>
        /// <param name="cmdParameters">Actual parameters to cached||实际参数缓存</param>
        public static void CacheParameters(string cacheKey,params OracleParameter[] cmdParameters)
        {
            parmCache[cacheKey] = cmdParameters;
        }
        
        /// <summary>
        /// Fetch parameters form the cache||从缓存中取出参数
        /// </summary>
        /// <param name="cacheKey"> Key to look up the parameters||查找参数的关键字</param>
        /// <returns></returns>
        public static OracleParameter[] GetCachedParameters(string cacheKey)
        {
            OracleParameter[] cacheParms = (OracleParameter[])parmCache[cacheKey];
            if (cacheParms == null)
                return null;
            OracleParameter[] clonedParms = new OracleParameter[cacheParms.Length];
            for (int i = 0, j = cacheParms.Length; i < j; i++)
                clonedParms[i] = (OracleParameter)((ICloneable)cacheParms[i]).Clone();
            return clonedParms;
        }
        
        /// <summary>
        /// Converter to use boolean data type with Oracle||Oracle中bool类型值转换成string类型
        /// </summary>
        /// <param name="value">Value to convert||转换的值</param>
        /// <returns></returns>
        public static string OraBit(bool value)
        {
            if (value)
                return "Y";
            else
                return "N";
        }

        /// <summary>
        /// Converter to use boolean data type with Oracle||Oracle中string类型值转换成bool类型
        /// </summary>
        /// <param name="value">Value to convert||转换的值</param>
        /// <returns></returns>
        public static bool OraBool(string value)
        {
            if (value.Equals("Y"))
                return true;
            else
                return false;
        }
    }
}