泛型OraHelper

将SqlHelper转化为OraHelper,发现代码冗余量太多了，故修改之。

 

调用示例如下：

 

            string spName = "foo.fmonthsignstatdata";//"Usefreqmonthstat";
            string spName2 = "foo.test2";//无参数
            string connStr = "Data Source=ip3;user id=foo;password=foo";

            Dictionary<string, object> opc = new Dictionary<string, object>();
            opc["P_STATMODE"]       = "1";
            opc["P_MANAGCODE"]      = "3505";
            opc["P_YEARMONTH"]      = "200804";
            opc["P_LIMITOCCUPERNT"] = "1";

            //正常方式下执行
            DataSet ds = OraHelper.Execute4SP<DataSet>(connStr, spName, opc);

            int count = OraHelper.Execute4SP<int>(connStr, spName2, null);

            //事务方式下:
            //开始
             OraHelper.BeginTran(connStr);

            //事务中调用
             OracleDataReader dr = OraHelper.Execute4SPByTran<OracleDataReader>(spName, opc);

            //事务中调用
             int count4Tran =  OraHelper.Execute4SPByTran<int>(spName2, null);

            // 提交事务。
            OraHelper.Commit();

 

代码如下：

 

    /// <summary>
    /// Oracle 命令执行助手类。泛型方法，目常只支持int,DataSet,OracleDataReader,object。
    /// 要执行带参数的存储过程，使用Execute4SP,事务方式使用Execute4SPByTran
    /// 要执行无参数的存储过程，使用Execute4SP和Execute,事务方式使用Execute4SPByTran或ExecuteByTran. (包括没有输入参数的情况）
    /// 要执行SQL，使用Execute,事务方式使用ExecuteByTran
    /// </summary>
    public sealed class OraHelper
    {
        #region 私有办法和构造器

        // 因为本类为密封类，所以构造函数为私有的
        private OraHelper() { }

        /// <summary>
        /// Oracle事务
        /// </summary>
        private static OracleTransaction _Tran = null;

        /// <summary>
        /// 是否使用事务，避免第一次设置事务开始，执行事务时，事务为空。
        /// </summary>
        private static bool _UsingTran = false;

        /// <summary>
        /// 是否正在使用事务
        /// </summary>
        public static bool UsingTran
        {
            get
            {
                return _UsingTran;
            }
        }

        /// <summary>
        /// 当前连接
        /// </summary>
        public static OracleConnection OracleConnection
        {
            get
            {
                return _conn;
            }
            set
            {
                _conn = value;
            }
        }
        /// <summary>
        /// 当前数据连接
        /// </summary>
        private static OracleConnection _conn = null;

        /// <summary>
        /// 是否要关闭连接
        /// </summary>
        private static bool _mustColseConnection = false;

        ///// <summary>
        ///// 是否必须要关闭连接
        ///// </summary>
        //private static bool MustColseConnection
        //{
        //    get
        //    {
        //        return _mustColseConnection;
        //    }
        //    set
        //    {
        //        _mustColseConnection = value;
        //    }
        //}

        /// <summary>
        /// 将参数数组添加至命令对象
        ///
        /// 如果参数为null,将自动设置为DBNull.Value
        ///
        /// </summary>
        /// <param name="command">要将参数组添加至命令对象</param>
        /// <param name="commandParameters">要添加的参数数组</param>
        private static void AttachParameters(OracleCommand command, OracleParameter[] commandParameters)
        {
            bool flag4DBNULL = false;//什么情况下可以将参数值设置为DBNull.Value

            //参数检验
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }
            if (commandParameters == null)
            {
                return;
            }

            //添加参数至命令对象
            foreach (OracleParameter p in commandParameters)
            {
                if (p == null) continue;

                //如果参数为null,将自动设置为DBNull.Value
                flag4DBNULL = p.Value == null;

                if (flag4DBNULL == true)
                {
                    p.Value = DBNull.Value;
                }
                command.Parameters.Add(p);
            }
        }

        /// <summary>
        /// 参数数组赋值：将数据行中的值赋给相近的参数名的参数,参数名需要包含于数据元素对应的列名中
        /// </summary>
        /// <param name="commandParameters">需要赋值的参数数组</param>
        /// <param name="dataRow">赋值来源数据行</param>
        private static void AssignParameterValues(OracleParameter[] commandParameters, DataRow dataRow)
        {
            // 数据列集合比较参数名的开始位置,SQL server必须从1开始，因为前面有一个@符号
            int offset = 0;

            //参数索引
            int i = 0;

            //临时参数名
            string tmPara = string.Empty;

            if ((commandParameters == null) || (dataRow == null))
            {
                return;
            }

            // 设置参数值
            foreach (OracleParameter commandParameter in commandParameters)
            {
                // 检查赋值两个对象
                if (string.IsNullOrEmpty(commandParameter.ParameterName) || commandParameter.ParameterName.Length <= offset)
                {
                    throw new Exception(string.Format("请提供一个有效的参数名, 位置：第#{0}个；参数名为： '{1}'.", i, commandParameter.ParameterName));
                }

                //设置临时参数名
                tmPara = commandParameter.ParameterName.Substring(offset);

                if (dataRow.Table.Columns.IndexOf(tmPara) != -1)
                {
                    commandParameter.Value = dataRow[tmPara];
                }
                i++;
            }
        }

        /// <summary>
        /// 参数数组赋值：对象数组赋给参数数组，次序对应
        /// </summary>
        /// <param name="commandParameters">参数数组</param>
        /// <param name="parameterValues">对象数组</param>
        private static void AssignParameterValues(OracleParameter[] commandParameters, object[] parameterValues)
        {
            //参数检验
            if ((commandParameters == null) || (parameterValues == null))
            {
                return;
            }

            // 赋值双方的元素量必须一致
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("参数数组与对象数组的大小不匹配.");
            }

            // 对象数组赋给参数数组，次序对应
            for (int i = 0, j = commandParameters.Length; i < j; i++)
            {
                // If the current array value derives from IDbDataParameter, then assign its Value property
                if (parameterValues[i] is IDbDataParameter)
                {
                    IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
                    if (paramInstance.Value == null)
                    {
                        commandParameters[i].Value = DBNull.Value;
                    }
                    else
                    {
                        commandParameters[i].Value = paramInstance.Value;
                    }
                }
                else if (parameterValues[i] == null)
                {
                    commandParameters[i].Value = DBNull.Value;
                }
                else
                {
                    commandParameters[i].Value = parameterValues[i];
                }
            }
        }

        /// <summary>
        /// 对命令对象进行设置连接、事务，类型等,连接未打开将会自动打开
        /// </summary>
        /// <param name="command">需要设置的命令对象</param>
        /// <param name="connection">连接对象</param>
        /// <param name="transaction">事务对象，有效则使用事务，无效则不使用事务</param>
        /// <param name="commandType">命令的类型</param>
        /// <param name="commandText">存储过程名或SQL</param>
        /// <param name="commandParameters">命令参数</param>
        /// <param name="mustCloseConnection"><c>true</c> 是否要关闭连接</param>
        private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters, out bool mustCloseConnection)
        {
            //检验参数
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }
            if (commandText == null || commandText.Length == 0)
            {
                throw new ArgumentNullException("commandText");
            }

            // 连接未打开将会自动打开，mustCloseConnection设置为true，调用者必须要关闭连接
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }

            // 保存当前连接对象
            _conn = connection;

            // 以下为将命令对象与其它对象关联起来
            // 关联连接
            command.Connection = connection;

            // 设置SQL命令或存储名
            command.CommandText = commandText;

            // 协调命令与事务关联
            PreStartTran();
            //if (transaction != null)
            //{
            //    if (transaction.Connection == null)
            //    {
            //        throw new ArgumentException("事务会被提交或回滚, 请提供一个打开的事务.", "事务");
            //    }

            //    command.Transaction = transaction;
            //}

            // 设置命令类型
            command.CommandType = commandType;

            // 设置命令参数
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }

        #endregion 私有办法和构造器

        #region 事务

        /// <summary>
        /// 开始事务根据连接串
        /// </summary>
        /// <param name="connstr">连接串</param>
        /// <returns></returns>
        public static void BeginTran( string connstr)
        {
            // 检验参数
            if (string.IsNullOrEmpty(connstr) == true)
            {
                throw new Exception("oracle connection is not open");
            }

            // 调用重载函数
            BeginTran(new OracleConnection(connstr));
        }

        /// <summary>
        /// 开始事务根据连接
        /// </summary>
        /// <param name="connection">数据连接</param>       
        public static void BeginTran(OracleConnection connection)
        {
            // 检验参数
            if (connection == null)
            {
                throw new ArgumentException("connection is null");
            }

            // 打开连接
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            // 保存连接
            _conn = connection;

            // 设置事务标志
            _UsingTran = true;
        }

        /// <summary>
        /// 协调命令与事务关联
        /// </summary>
        public static void PreStartTran()
        {
            // 未使用BeginTran,忽略使用事务
            if( _UsingTran == false )
            {
                return;
            }

            // 事务进行中，不可以重新开始
            if( _Tran != null )
            {
                return;
            }

            //开启事务
            _Tran = _conn.BeginTransaction();
        }

        /// <summary>
        /// 结束事务
        /// </summary>
        /// <returns></returns>
        public static void Commit()
        {
            // 提交事务
            _Tran.Commit();

            // 关闭连接
            _conn.Close();

            // 连接和事务置空
            _conn = null;
            _Tran = null;
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        /// <returns></returns>
        public static void Rollback()
        {
            // 提交事务
            _Tran.Rollback();

            // 关闭连接
            _conn.Close();

            // 连接和事务置空
            _conn = null;
            _Tran = null;
        }
        #endregion

        #region ExecuteNonQuery

        #region My custom
      
        #region 事务
        /// <summary>
        /// 执行存储过程，事务方式
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterNameValues">传入的参数值对,参数名一定要大写</param>
        /// <returns></returns>
        public static T Execute4SPByTran<T>(string spName, Dictionary<string, object> parameterNameValues)
        {
            // 声明一个替代者
            OracleParameterCollection tmpParas;

            // 调用重载函数
            return Execute4SP<T>(_conn, spName, parameterNameValues, out tmpParas);
        }

        /// <summary>
        /// 执行存储过程，事务方式
        /// </summary>       
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterNameValues">传入的参数值对,参数名一定要大写</param>
        /// <param name="returnParameters">返回执行参数集合</param>
        /// <returns></returns>
        public static T Execute4SPByTran<T>(string spName, Dictionary<string, object> parameterNameValues, out OracleParameterCollection returnParameters)
        {
            // 调用重载函数
            return Execute4SP<T>(_conn, spName, parameterNameValues, out returnParameters);
        }
        #endregion

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="connectionString">连接串</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterNameValues">传入的参数值对,参数名一定要大写</param>
        /// <returns></returns>
        public static T Execute4SP<T>(string connectionString, string spName, Dictionary<string, object> parameterNameValues)
        {
            // 调用重载函数
            return Execute4SP<T>(new OracleConnection(connectionString), spName, parameterNameValues);
        }       

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterNameValues">传入的参数值对,参数名一定要大写</param>
        /// <returns></returns>
        public static T Execute4SP<T>(OracleConnection connection, string spName, Dictionary<string, object> parameterNameValues)
        {
            // 声明一个替代者
            OracleParameterCollection tmpParas;

            // 调用重载函数
            return Execute4SP<T>(connection, spName, parameterNameValues, out tmpParas);
        }
               
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="connectionString">连接串</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterNameValues">传入的参数值对,参数名一定要大写</param>
        /// <param name="returnParameters">返回执行参数集合</param>
        /// <returns></returns>
        public static T Execute4SP<T>(string connectionString, string spName, Dictionary<string, object> parameterNameValues, out OracleParameterCollection returnParameters)
        {
            // 调用重载函数
            return Execute4SP<T>(new OracleConnection(connectionString), spName, parameterNameValues, out returnParameters);
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="connectionString">数据库连接</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterNameValues">传入的参数值对,参数名一定要大写</param>
        /// <param name="returnParameters">返回执行参数集合</param>
        /// <returns></returns>
        public static T Execute4SP<T>(OracleConnection connection, string spName, Dictionary<string, object> parameterNameValues, out OracleParameterCollection returnParameters)
        {
            OracleParameter[] paras = OracleHelperParameterCache.GetSpParameterSet(connection, spName);

            if (parameterNameValues != null)
            {
                foreach (OracleParameter item in paras)
                {
                    string paraName = item.ParameterName;

                    if (parameterNameValues.ContainsKey(paraName) == false)
                    {
                        //item.Value = DBNull.Value;从数据库查找参数列表时已经赋为DBNull.Value
                        continue;
                    }

                    // 给命令参数赋值
                    item.Value = parameterNameValues[paraName];
                }
            }
           
            // 调用重载函数
            T retVal = OraHelper.Execute<T>(connection, CommandType.StoredProcedure, spName, paras);

            // 输出执行参数集合
            returnParameters = new OracleParameterCollection();
            returnParameters.AddRange(paras);

            // 返回值
            return retVal;
        }
        #endregion
       
        #region Stored Procedure

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterNameValues">传入的参数值对,参数名一定要大写</param>
        /// <returns></returns>
        internal static T Execute<T>(OracleConnection connection, string spName, OracleParameter[] parameterNameValues)
        {
            // 调用重载函数
            return Execute<T>(connection, CommandType.StoredProcedure, spName, parameterNameValues);
        }
      
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="connectionString">连接串</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterNameValues">传入的参数值对,参数名一定要大写</param>
        /// <returns></returns>
        internal static T Execute<T>(string connectionString, string spName, OracleParameter[] parameterNameValues)
        {
            // 调用重载函数
            return Execute<T>(new OracleConnection(connectionString), CommandType.StoredProcedure, spName, parameterNameValues);
        }
        #endregion Stored Procedure

        #region base
        /// <summary>
        /// 执行命令或存储过程
        /// </summary>
        /// <param name="cmd">命令对象</param>
        /// <returns></returns>
        public static T Execute<T>(OracleCommand cmd)
        {
            object retval = default(T);// 返回值
            Type type = typeof(T);// 返回值类型

            try
            {
                // 关联事务，若为空，则不使用事务操作
                cmd.Transaction = _Tran;
                // 根据返回值类型来判断调用哪个方法
                if (type == typeof(int))
                {
                    // 只返回影响行数
                    retval = cmd.ExecuteNonQuery();

                    // retval = cmd.ExecuteOracleNonQuery(
                }
                else if (type == typeof(DataSet))
                {
                    // 返回DataSet
                    DataSet ds = new DataSet();

                    using (OracleDataAdapter da = new OracleDataAdapter(cmd))
                    {
                        da.Fill(ds);
                    }
                    retval = ds;
                }
                else if (type == typeof(OracleDataReader))
                {
                    // 返回OracleDataReader
                    retval = cmd.ExecuteReader();
                }
                else if (type == typeof(object))
                {
                    // 返回第一个值
                    retval = cmd.ExecuteScalar();
                }
                else
                {
                    throw new Exception(string.Format("this type({0}) is not support", type));
                }

                // 清除命令
                cmd.Parameters.Clear();
            }
            finally
            {
                // 是否要关闭连接
                if (_mustColseConnection == true)
                {
                    _conn.Close();

                    _conn = null;
                }
            }

            // 返回值
            return (T)retval;
        }

        /// <summary>
        /// 执行命令或存储过程
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        internal static T Execute<T>(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
        {
            // 检查是否存在事务
            if (connection != _conn && _UsingTran == true)
            {
                throw new ArgumentException("事务进行中，不允许更改连接");
            }

            OracleCommand cmd = new OracleCommand();

            // 准备命令对象
            PrepareCommand(cmd, connection, null, commandType, commandText, commandParameters, out _mustColseConnection);

            // 调用重载函数
            return Execute<T>(cmd);
        }

        /// <summary>
        /// 执行命令或存储过程
        /// </summary>
        /// <param name="connectionString">连接串</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">存储过程名或SQL</param>
        /// <param name="commandParameters">参数名值对，参数名必须大写</param>
        /// <returns></returns>
        internal static T Execute<T>(string connectionString, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
        {
            // 调用重载函数
            return Execute<T>(new OracleConnection(connectionString), commandType, commandText, commandParameters);
        }

        /// <summary>
        /// 执行无参数的存储过程或命令
        /// </summary>     
        /// <param name="connectionString">连接串</param>
        /// <param name="commandType">类型</param>
        /// <param name="commandText">存储名或SQL语句</param>
        /// <returns></returns>
        public static T Execute<T>(string connectionString, CommandType commandType, string commandText)
        {
            // 调用重载函数
            return Execute<T>(connectionString, commandType, commandText, (OracleParameter[])null);
        }

        /// <summary>
        /// 执行命令或存储过程，使用事务。如果是带参数的存储过程，请使用Execute4SPByTran
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">存储过程名或SQL</param>
        /// <returns></returns>
        public static T ExecuteByTran<T>(CommandType commandType, string commandText )
        {
            // 执行
            return Execute<T>(_conn, commandType, commandText, (OracleParameter[])null);
        }

        #endregion base

        #endregion ExecuteNonQuery
    }   

    /// <summary>
    /// OracleHelperParameterCache 提供存储过程参数缓存功能，以及根据存储过程名得到其参数列表
    ///
    /// </summary>
    public sealed class OracleHelperParameterCache
    {
        #region 私有方法，变量及构造器

        //静态构造函数
        private OracleHelperParameterCache() { }

        /// <summary>
        /// 缓存，缓存名为 连接串 : 存储过程名或Sql:include ReturnValue Parameter，其中:include ReturnValue Parameter为包含返回参数才有的
        /// </summary>
        private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

        ///// <summary>
        ///// 是否要强制关闭连接
        ///// </summary>
        //private static bool mustCloseConnection = false;

        /// <summary>
        /// 返回一个存储过程的参数数组
        /// </summary>
        /// <param name="connection">有效的数据连接</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="includeReturnValueParameter">是否包含返回值参数</param>
        /// <returns>返回找到的参数数组.</returns>
        private static OracleParameter[] DiscoverSpParameterSet(OracleConnection connection, string spName, bool includeReturnValueParameter)
        {
            // 检验参数
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }

            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }

            // 定义一个模拟命令
            OracleCommand cmd = new OracleCommand(spName, connection);
            cmd.CommandType = CommandType.StoredProcedure;

            // 打开连接
            connection.Open();

            // 查找存储过程的参数
            OracleCommandBuilder.DeriveParameters(cmd);
            //// 是否要关闭连接
            //if (mustCloseConnection == true)
            //{
            //    connection.Close();
            //}

            connection.Close();

            // 如果包含返回参数，则删除第一个参数，该情况存在于SQL SERVER中
            if (!includeReturnValueParameter)
            {
                cmd.Parameters.RemoveAt(0);
            }

            // 返回找到的参数的副本
            OracleParameter[] discoveredParameters = new OracleParameter[cmd.Parameters.Count];

            cmd.Parameters.CopyTo(discoveredParameters, 0);

            // 初始化各参数为DBNull
            foreach (OracleParameter discoveredParameter in discoveredParameters)
            {
                discoveredParameter.Value = DBNull.Value;
            }
            return discoveredParameters;
        }

        /// <summary>
        /// 深拷贝参数数组
        /// </summary>
        /// <param name="originalParameters">源参数数组</param>
        /// <returns>返回参数数组的副本</returns>
        private static OracleParameter[] CloneParameters(OracleParameter[] originalParameters)
        {
            OracleParameter[] clonedParameters = new OracleParameter[originalParameters.Length];

            for (int i = 0, j = originalParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (OracleParameter)((ICloneable)originalParameters[i]).Clone();
            }

            return clonedParameters;
        }

        #endregion 私有方法，变量及构造器

        #region 缓存

        /// <summary>
        /// 将参数保存至缓存，手动缓存
        /// </summary>
        /// <param name="connectionString">有效的数据连接</param>
        /// <param name="commandText">存储过程或SQL</param>
        /// <param name="commandParameters">An array of SqlParamters to be cached</param>
        public static void CacheParameterSet(string connectionString, string commandText, params OracleParameter[] commandParameters)
        {
            // 检验参数
            if (connectionString == null || connectionString.Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }
            if (commandText == null || commandText.Length == 0)
            {
                throw new ArgumentNullException("commandText");
            }

            //保存至缓存
            string hashKey = connectionString + ":" + commandText;

            paramCache[hashKey] = commandParameters;
        }

        /// <summary>
        /// 从缓存中得到参数数组，手动缓存
        /// </summary>
        /// <param name="connectionString">一个有效的参数数组</param>
        /// <param name="commandText">存储过程名或SQL</param>
        /// <returns>返回参数数组</returns>
        public static OracleParameter[] GetCachedParameterSet(string connectionString, string commandText)
        {
            // 检验参数
            if (connectionString == null || connectionString.Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }
            if (commandText == null || commandText.Length == 0)
            {
                throw new ArgumentNullException("commandText");
            }

            // 如果缓存中存在，则深拷贝一个参数数组返回
            string hashKey = connectionString + ":" + commandText;

            OracleParameter[] cachedParameters = paramCache[hashKey] as OracleParameter[];
            if (cachedParameters == null)
            {
                return null;
            }
            else
            {
                return CloneParameters(cachedParameters);
            }
        }

        #endregion 缓存

        #region 查找存储过程参数

        /// <summary>
        /// 得到存储过程对应的参数数组，自动缓存
        /// </summary>
        /// <remarks>
        /// 查找存储过程的参数数组，并且将该参数数组保存至缓存中
        /// </remarks>
        /// <param name="connectionString">有效的数据连接</param>
        /// <param name="spName">存储过程名</param>
        /// <returns>参数数组</returns>
        public static OracleParameter[] GetSpParameterSet(string connectionString, string spName)
        {
            // 是否包含返回值
            // 在SQL SERVER中查找存储过程的参数数组，第一个参数值为返回值，无用，所以置为false,将其丢弃
            // 在ORACLE中查找存储过程的参数数组时，第一个参数值是有效的，所以置为true
            bool includeReturnValueParameter = true;

            return GetSpParameterSet(connectionString, spName, includeReturnValueParameter);
        }

        /// <summary>
        /// 得到存储过程对应的参数数组，自动缓存
        /// </summary>
        /// <remarks>
        /// 查找存储过程的参数数组，并且将该参数数组保存至缓存中
        /// </remarks>
        /// <param name="connectionString">有效的数据连接</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="includeReturnValueParameter">是否包含返回值参数</param>
        /// <returns>参数数组</returns>
        public static OracleParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {
            // 检验参数
            if (connectionString == null || connectionString.Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }

            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }

            //调用内部方法，返回参数数组
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
            }
        }

        /// <summary>
        /// 得到存储过程对应的参数数组，自动缓存
        /// </summary>
        /// <remarks>
        /// 查找存储过程的参数数组，并且将该参数数组保存至缓存中
        /// </remarks>
        /// <param name="connection">有效的数据连接</param>
        /// <param name="spName">存储过程名</param>
        /// <returns>参数数组</returns>
        internal static OracleParameter[] GetSpParameterSet(OracleConnection connection, string spName)
        {
            return GetSpParameterSet(connection, spName, true);
        }

        /// <summary>
        /// 得到存储过程对应的参数数组，自动缓存
        /// </summary>
        /// <remarks>
        /// 查找存储过程的参数数组，并且将该参数数组保存至缓存中
        /// </remarks>
        /// <param name="connection">有效的数据连接</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="includeReturnValueParameter">是否包含返回值参数</param>
        /// <returns>参数数组</returns>
        internal static OracleParameter[] GetSpParameterSet(OracleConnection connection, string spName, bool includeReturnValueParameter)
        {
            // 检验参数
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }

            //
            using (OracleConnection clonedConnection = (OracleConnection)((ICloneable)connection).Clone())
            {
                return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
            }
        }

        /// <summary>
        /// 得到存储过程对应的参数数组，自动缓存
        /// </summary>
        /// <param name="connection">有效的数据连接</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="includeReturnValueParameter">是否包含返回值参数</param>
        /// <returns>参数数组</returns>
        private static OracleParameter[] GetSpParameterSetInternal(OracleConnection connection, string spName, bool includeReturnValueParameter)
        {
            // 检验参数
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }

            ////执行完毕后要关闭连接
            //mustCloseConnection = ( connection.State != ConnectionState.Open );
           
                       
            // 缓存规则
            string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");

            // 从缓存中取参数数组根据缓存规则
            // 如果找不到，则从数据库中查找，并将找到的参数数组保存至缓存           
            OracleParameter[] cachedParameters;

            cachedParameters = paramCache[hashKey] as OracleParameter[];
            if (cachedParameters == null)
            {
                OracleParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
                paramCache[hashKey] = spParameters;
                cachedParameters = spParameters;
            }

            // 深拷贝一个参数数组返回，不管是缓存中，还是从数据库返回的
            return CloneParameters(cachedParameters);
        }

        #endregion 查找存储过程参数
    }
}