IMEX.mes.wcf/DK.Basics/DataAccess/OracleHelper.cs

/*******************************************************************************
 * Copyright(c) 2014 dongke All rights reserved. / Confidential
 * 类的信息：
 *      1.程序名称：OracleHelper.cs
 *      2.功能描述：C#访问Oracle数据库共通类
 * 编辑履历：
 *      作者            日期                版本            修改内容
 *      欧阳涛          2010-10-13          1.00            新建
 *      陈冰            2014-07-22          1.00            修改
 *******************************************************************************/

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
//using System.Data.OracleClient;
using Oracle.DataAccess.Client;

namespace Dongke.IBOSS.PRD.Basics.DataAccess
{
    /// <summary>
    /// C#访问Oracle数据库共通类
    /// </summary>
    internal sealed class OracleHelper
    {
        // 装载参数缓存的哈希表
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="connectionString">Oracle数据库连接串</param>
        /// <param name="SQLString">查询SQL语句</param>
        /// <returns>查询结果集：DataSet</returns>
        public static DataSet Query(string connectionString, string SQLString)
        {
            using (OracleConnection oracleConnection =
                new OracleConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                try
                {
                    oracleConnection.Open();

                    OracleDataAdapter command =
                        new OracleDataAdapter(SQLString, oracleConnection);

                    command.Fill(dataSet, "dataSet");
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    if (oracleConnection.State != ConnectionState.Closed)
                    {
                        oracleConnection.Close();
                    }
                }
                return dataSet;
            }
        }

        /// <summary>
        /// 执行带参数的查询语句，返回DataSet
        /// </summary>
        /// <param name="connectionString">Oracle数据库连接串</param>
        /// <param name="SQLString">查询SQL语句</param>
        /// <param name="cmdParms">查询参数</param>
        /// <returns>查询结果集：DataSet</returns>
        public static DataSet Query(string connectionString, string SQLString,
            params OracleParameter[] cmdParms)
        {
            using (OracleConnection oracleConnection =
                new OracleConnection(connectionString))
            {
                OracleCommand oracleCommand = new OracleCommand();

                // 对SQL语句执行对象进行赋值
                PrepareCommand(oracleCommand, oracleConnection, null, SQLString, cmdParms);

                using (OracleDataAdapter oracleDataAdapter =
                    new OracleDataAdapter(oracleCommand))
                {
                    DataSet dataSet = new DataSet();
                    try
                    {
                        // 填充dataset
                        oracleDataAdapter.Fill(dataSet, "dataSet");
                        oracleCommand.Parameters.Clear();
                    }
                    catch (OracleException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        if (oracleConnection.State != ConnectionState.Closed)
                        {
                            oracleConnection.Close();
                        }
                    }
                    return dataSet;
                }
            }
        }

        /// <summary>
        /// 对执行SQL语句或者存储过程的对象进行赋值
        /// </summary>
        /// <param name="oracleCommand">执行SQL语句或者存储过程的对象</param>
        /// <param name="oracleConnection">Oracle数据库连接</param>
        /// <param name="oracleTransaction">Oracle事务</param>
        /// <param name="oracleCommandText">SQL语句或者存储过程</param>
        /// <param name="oracleCommandParms">SQL语句的参数</param>
        private static void PrepareCommand(OracleCommand oracleCommand,
            OracleConnection oracleConnection, OracleTransaction oracleTransaction,
            string oracleCommandText, OracleParameter[] oracleCommandParms)
        {
            // 验证数据库连接是否打开，如果未打开则打开数据库连接
            if (oracleConnection.State != ConnectionState.Open)
            {
                oracleConnection.Open();
            }

            // 对执行对象进行的各个属性赋值
            oracleCommand.Connection = oracleConnection;
            oracleCommand.CommandText = oracleCommandText;
            if (oracleTransaction != null)
            {
                oracleCommand.Transaction = oracleTransaction;
            }
            oracleCommand.CommandType = CommandType.Text;

            // 对执行对象的参数进行赋值
            if (oracleCommandParms != null)
            {
                foreach (OracleParameter parameter in oracleCommandParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput ||
                        parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    oracleCommand.Parameters.Add(parameter);
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="connectionString">Oracle数据库连接串</param>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        private static object GetSingle(string connectionString, string SQLString)
        {
            using (OracleConnection oracleConnection =
                new OracleConnection(connectionString))
            {
                using (OracleCommand oracleCommand =
                    new OracleCommand(SQLString, oracleConnection))
                {
                    try
                    {
                        // 打开Oracle数据库连接
                        oracleConnection.Open();
                        object obj = oracleCommand.ExecuteScalar();
                        // 结果为空返回NULL,否则返回obj
                        if ((Object.Equals(obj, null))
                            || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (OracleException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        if (oracleConnection.State != ConnectionState.Closed)
                        {
                            oracleConnection.Close();
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 判断某一SQL查询语句是否存在记录
        /// </summary>
        /// <param name="connectionString">Oracle数据库连接串</param>
        /// <param name="strOracle">查询SQL语句</param>
        /// <returns>False:不存在查询记录  True:存在查询记录</returns>
        private static bool Exists(string connectionString, string strOracle)
        {
            object obj = OracleHelper.GetSingle(connectionString, strOracle);

            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 执行一条带有参数的数据库操作语句（不包括SELECT）
        /// </summary>
        /// <param name="connectionString">Oracle数据库连接串</param>
        /// <param name="commandType">执行操作的方式</param>
        /// <param name="commandText">SQL语句文本</param>
        /// <param name="commandParameters">SQL语句的参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString,
            CommandType commandType, string commandText,
            params OracleParameter[] commandParameters)
        {
            // 创建一个执行SQL语句对象
            OracleCommand oracleCommand = new OracleCommand();
            OracleConnection oracleConnection = null;
            try
            {
                // 创建一个Oracle数据库连接
                using (oracleConnection = new OracleConnection(connectionString))
                {
                    // 对SQL语句执行对象进行赋值
                    PrepareCommand(oracleCommand, oracleConnection, null,
                        commandType, commandText, commandParameters);

                    // 执行一个SQL语句对象
                    int intReturnVal = oracleCommand.ExecuteNonQuery();

                    // 关闭数据库连接
                    oracleConnection.Close();
                    // 清除执行SQL语句对象的参数
                    oracleCommand.Parameters.Clear();

                    return intReturnVal;
                }
            }
            catch (Exception ex)
            {
                oracleConnection.Close();
                throw ex;
            }
            finally
            {
                if (oracleConnection != null &&
                    oracleConnection.State != ConnectionState.Closed)
                {
                    oracleConnection.Close();
                }
            }
        }

        /// <summary>
        /// 执行一个带有事务处理的SQL语句执行对象或者存储过程，带有参数
        /// 例如：int result = ExecuteNonQuery(trans, CommandType.StoredProcedure,
        ///                     "PublishOrders", new OracleParameter(":prodid", 24));
        /// </summary>
        /// <param name="oracleTransaction">一个存在的数据库事务</param>
        /// <param name="commandType">执行命令类型(存储过程、SQL语句等)</param>
        /// <param name="commandText">存储过程名称或者SQL语句</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>执行命令对数据库影响的行数</returns>
        public static int ExecuteNonQuery(OracleTransaction oracleTransaction,
            CommandType commandType, string commandText,
            params OracleParameter[] commandParameters)
        {
            try
            {
                if (oracleTransaction == null)
                {
                    throw new ArgumentNullException("事务处理");
                }
                if (oracleTransaction != null && oracleTransaction.Connection == null)
                {
                    throw new ArgumentException(
                        "事务已经回滚或者提交，请提供一个打开的事务。", "事务处理");
                }

                OracleCommand oracleCommand = new OracleCommand();

                // 给SQL执行对象赋值
                PrepareCommand(oracleCommand, oracleTransaction.Connection,
                    oracleTransaction, commandType, commandText, commandParameters);
                // 执行SQL语句并返回受影响的行数
                int intReturnVal = oracleCommand.ExecuteNonQuery();
                // 清除SQL执行对象的属性值
                oracleCommand.Parameters.Clear();

                return intReturnVal;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 用一个已经存在的数据库连接执行一个SQL命令
        /// 例如：int result = ExecuteNonQuery(oracleConnection,
        ///                     CommandType.StoredProcedure,
        ///                     "PublishOrders",
        ///                     new OracleParameter(":prodid", 24));
        /// </summary>
        /// <param name="oracleConnection">一个存在的数据库连接</param>
        /// <param name="commandType">执行命令类型(存储过程、SQL语句等)</param>
        /// <param name="commandText">存储过程名称或者SQL语句</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>执行命令对数据库影响的行数</returns>
        private static int ExecuteNonQuery(OracleConnection oracleConnection,
            CommandType commandType, string commandText,
            params OracleParameter[] commandParameters)
        {
            try
            {
                OracleCommand oracleCommand = new OracleCommand();
                // 给SQL执行对象赋值
                PrepareCommand(oracleCommand, oracleConnection, null,
                    commandType, commandText, commandParameters);
                // 执行SQL语句并返回受影响的行数
                int intReturnVal = oracleCommand.ExecuteNonQuery();
                // 清除SQL执行对象的属性值
                oracleCommand.Parameters.Clear();
                return intReturnVal;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 用一个数据库连接串执行一个SQL语句
        /// 例如：int result = ExecuteNonQuery(connectionString, "PublishOrders");
        /// </summary>
        /// <param name="connectionString">数据库连接串</param>
        /// <param name="commandText">SQL语句</param>
        /// <returns>执行命令对数据库影响的行数</returns>
        private static int ExecuteNonQuery(string connectionString, string commandText)
        {
            // 创建一个新的数据库连接
            OracleCommand oracleCommand = new OracleCommand();
            OracleConnection oracleConnection = new OracleConnection(connectionString);
            try
            {
                // 给SQL执行对象赋值
                PrepareCommand(oracleCommand, oracleConnection, null,
                    CommandType.Text, commandText, null);

                // 执行SQL语句并返回受影响的行数
                int intReturnVal = oracleCommand.ExecuteNonQuery();

                // 关闭数据库连接
                oracleConnection.Close();

                // 清除SQL执行对象的属性值
                oracleCommand.Parameters.Clear();
                return intReturnVal;
            }
            catch (Exception ex)
            {
                oracleConnection.Close();
                throw ex;
            }
            finally
            {
                if (oracleConnection != null &&
                    oracleConnection.State != ConnectionState.Closed)
                {
                    oracleConnection.Close();
                }
            }
        }

        /// <summary>
        /// 执行一个查询语句，返回一个结果集--调用后一定要关闭OracleDataReader
        /// </summary>
        /// <param name="connectionString">数据库连接串</param>
        /// <param name="commandType">执行命令类型(存储过程、SQL语句等)</param>
        /// <param name="commandText">存储过程名称或者SQL语句</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>结果集</returns>
        public static OracleDataReader ExecuteReader(string connectionString,
            CommandType commandType, string commandText,
            params OracleParameter[] commandParameters)
        {
            OracleCommand oracleCommand = new OracleCommand();
            OracleConnection oracleConnection = new OracleConnection(connectionString);
            OracleDataReader oracleDataReader = null;
            try
            {
                // 给SQL执行对象赋值
                PrepareCommand(oracleCommand, oracleConnection, null,
                    commandType, commandText, commandParameters);
                oracleDataReader =
                    oracleCommand.ExecuteReader(CommandBehavior.CloseConnection);

                // 清除SQL执行对象的属性值
                oracleCommand.Parameters.Clear();
                return oracleDataReader;
            }
            catch (Exception ex)
            {
                oracleConnection.Close();
                throw ex;
            }
        }

        /// <summary>
        /// 执行一个查询语句，返回一个结果集--调用后一定要关闭OracleDataReader
        /// </summary>
        /// <param name="oracleTransaction">一个存在的数据库事务</param>
        /// <param name="commandType">执行命令类型(存储过程、SQL语句等)</param>
        /// <param name="commandText">存储过程名称或者SQL语句</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>结果集</returns>
        public static OracleDataReader ExecuteReader(OracleTransaction oracleTransaction,
            CommandType commandType, string commandText,
            params OracleParameter[] commandParameters)
        {
            OracleDataReader oracleDataReader = null;
            try
            {
                if (oracleTransaction == null)
                {
                    throw new ArgumentNullException("事务处理");
                }
                if (oracleTransaction != null && oracleTransaction.Connection == null)
                {
                    throw new ArgumentException(
                        "事务已经回滚或者提交，请提供一个打开的事务。", "事务处理");
                }

                OracleCommand oracleCommand = new OracleCommand();

                // 给SQL执行对象赋值
                PrepareCommand(oracleCommand, oracleTransaction.Connection,
                    oracleTransaction, commandType, commandText, commandParameters);
                oracleDataReader =
                    oracleCommand.ExecuteReader(CommandBehavior.CloseConnection);

                // 清除SQL执行对象的属性值
                oracleCommand.Parameters.Clear();
                return oracleDataReader;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 用一个数据库连接串执行一个带有参数的SQL语句或者存储过程等，
        ///     返回结果集的第一行第一列数据对象
        /// 例如：Object obj = ExecuteScalar(connString, CommandType.StoredProcedure,
        ///                     "PublishOrders", new OracleParameter(":prodid", 24));
        /// </summary>
        /// <param name="connectionString">一个有效的连接字符串</param>
        /// <param name="commandType">执行命令类型(存储过程、SQL语句等)</param>
        /// <param name="commandText">存储过程名称或者SQL语句</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>一个能转换为预期的类型的对象</returns>
        private static object ExecuteScalar(string connectionString, CommandType commandType,
            string commandText, params OracleParameter[] commandParameters)
        {
            OracleCommand oracleCommand = new OracleCommand();
            OracleConnection oracleConnection = null;

            try
            {
                // 创建一个Oracle数据库连接
                using (oracleConnection = new OracleConnection(connectionString))
                {
                    // 对SQL语句执行对象进行赋值
                    PrepareCommand(oracleCommand, oracleConnection, null,
                        commandType, commandText, commandParameters);

                    // 执行查询
                    object objReturnVal = oracleCommand.ExecuteScalar();

                    // 关闭数据库连接
                    oracleConnection.Close();
                    // 清除执行SQL语句对象的参数
                    oracleCommand.Parameters.Clear();
                    return objReturnVal;
                }
            }
            catch (Exception ex)
            {
                oracleConnection.Close();
                throw ex;
            }
            finally
            {
                if (oracleConnection != null &&
                    oracleConnection.State != ConnectionState.Closed)
                {
                    oracleConnection.Close();
                }
            }
        }

        /// <summary>
        /// 用一个事务执行一个带有参数的SQL语句或者存储过程等，
        ///     返回结果集的第一行第一列数据对象
        /// 例如：Object obj = ExecuteScalar(oracleTransaction, CommandType.StoredProcedure,
        ///                     "PublishOrders", new OracleParameter(":prodid", 24));
        /// </summary>
        /// <param name="oracleTransaction">一个存在的事务</param>
        /// <param name="commandType">执行命令类型(存储过程、SQL语句等)</param>
        /// <param name="commandText">存储过程名称或者SQL语句</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>一个能转换为预期的类型的对象</returns>
        private static object ExecuteScalar(OracleTransaction oracleTransaction,
            CommandType commandType, string commandText,
            params OracleParameter[] commandParameters)
        {
            try
            {
                if (oracleTransaction == null)
                {
                    throw new ArgumentNullException("事务处理");
                }
                if (oracleTransaction != null && oracleTransaction.Connection == null)
                {
                    throw new ArgumentException(
                        "事务已经回滚或者提交，请提供一个打开的事务。", "事务处理");
                }

                // 创建一个SQL执行命令对象
                OracleCommand oracleCommand = new OracleCommand();
                // 给SQL执行命令对象赋值
                PrepareCommand(oracleCommand, oracleTransaction.Connection,
                    oracleTransaction, commandType, commandText, commandParameters);

                // 执行查询
                object objReturnVal = oracleCommand.ExecuteScalar();

                // 清除SQL执行命令对象的参数
                oracleCommand.Parameters.Clear();
                return objReturnVal;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 用一个数据库连接串执行一个带有参数的SQL语句或者存储过程等，
        ///     返回结果集的第一行第一列数据对象
        /// 例如：Object obj = ExecuteScalar(connectionString, CommandType.StoredProcedure,
        ///                     "PublishOrders", new OracleParameter(":prodid", 24));
        /// </summary>
        /// <param name="oracleConnection">一个存在的数据库连接</param>
        /// <param name="commandType">执行命令类型(存储过程、SQL语句等)</param>
        /// <param name="commandText">存储过程名称或者SQL语句<</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>一个能转换为预期的类型的对象</returns>
        private static object ExecuteScalar(OracleConnection oracleConnection,
            CommandType commandType, string commandText,
            params OracleParameter[] commandParameters)
        {
            try
            {
                // 创建一个SQL执行命令对象
                OracleCommand oracleCommand = new OracleCommand();
                // 给SQL执行命令对象赋值
                PrepareCommand(oracleCommand, oracleConnection, null,
                    commandType, commandText, commandParameters);
                object objReturnVal = oracleCommand.ExecuteScalar();
                oracleCommand.Parameters.Clear();
                return objReturnVal;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 添加一个参数数组到缓存
        /// </summary>
        /// <param name="cacheKey">参数的关键字</param>
        /// <param name="commandParameters">输送到缓存的参数</param>
        private static void CacheParameters(string cacheKey,
            params OracleParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>
        /// 从缓存中取得参数
        /// </summary>
        /// <param name="cacheKey">参数的关键字</param>
        /// <returns>SQL执行命令对象参数</returns>
        private static OracleParameter[] GetCachedParameters(string cacheKey)
        {
            OracleParameter[] cachedParms = (OracleParameter[])parmCache[cacheKey];

            if (cachedParms == null)
            {
                return null;
            }

            // 缓存中有参数
            OracleParameter[] clonedParms = new OracleParameter[cachedParms.Length];

            // 返回一个参数的拷贝
            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (OracleParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        /// <summary>
        /// 为数据库执行准备一个执行对象（内部方法）
        /// </summary>
        /// <param name="oracleCommand">执行命令对象</param>
        /// <param name="oracleConnection">一个存在的数据库连接</param>
        /// <param name="oracleTransaction">事务</param>
        /// <param name="commandType">执行命令类型(存储过程、SQL语句等)</param>
        /// <param name="commandText">存储过程名称或者SQL语句</param>
        /// <param name="commandParameters">参数数组</param>
        private static void PrepareCommand(OracleCommand oracleCommand,
            OracleConnection oracleConnection, OracleTransaction oracleTransaction,
            CommandType commandType, string commandText, OracleParameter[] commandParameters)
        {
            try
            {
                // 打开连接
                if (oracleConnection.State != ConnectionState.Open)
                {
                    oracleConnection.Open();
                }
                // 设置执行命令对象的属性值
                oracleCommand.Connection = oracleConnection;
                oracleCommand.CommandText = commandText;
                oracleCommand.CommandType = commandType;

                // 如果事务存在，则将事务绑定到执行命令对象上
                if (oracleTransaction != null)
                {
                    oracleCommand.Transaction = oracleTransaction;
                }
                // 将参数绑定到执行命令对象上
                if (commandParameters != null)
                {
                    foreach (OracleParameter parm in commandParameters)
                    {
                        oracleCommand.Parameters.Add(parm);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 转换成Oracle的布尔数据类型
        /// </summary>
        /// <param name="value">转换值</param>
        /// <returns></returns>
        private static string OraBit(bool value)
        {
            if (value)
            {
                return "Y";
            }
            else
            {
                return "N";
            }
        }

        /// <summary>
        /// 转换成Oracle的布尔数据类型
        /// </summary>
        /// <param name="value">转换值</param>
        /// <returns></returns>
        private static bool OraBool(string value)
        {
            if (value.Equals("Y"))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="SQLStringList">多条SQL语句</param>        
        private static bool ExecuteSqlTran(string connectionString,
            List<CommandInfo> commandInfoList)
        {
            using (OracleConnection oracleConnection =
                new OracleConnection(connectionString))
            {
                // 打开连接
                oracleConnection.Open();
                // 给执行命令对象赋值
                OracleCommand oracleCommand = new OracleCommand();
                oracleCommand.Connection = oracleConnection;
                OracleTransaction oracleTransaction = oracleConnection.BeginTransaction();
                oracleCommand.Transaction = oracleTransaction;
                try
                {
                    foreach (CommandInfo commandInfo in commandInfoList)
                    {
                        if (!string.IsNullOrEmpty(commandInfo.CommandText))
                        {
                            // 为执行命令对象赋值
                            PrepareCommand(oracleCommand, oracleConnection,
                                oracleTransaction, CommandType.Text, commandInfo.CommandText,
                                (OracleParameter[])commandInfo.Parameters);
                            // 检查SQL语句是否符合要求，不符合则回滚事务，抛出异常
                            if (commandInfo.EffentNextType ==
                                EffentNextType.WhenHaveContine ||
                                commandInfo.EffentNextType ==
                                EffentNextType.WhenNoHaveContine)
                            {
                                if (commandInfo.CommandText.ToLower().IndexOf("count(") == -1)
                                {
                                    oracleTransaction.Rollback();
                                    throw new Exception("Oracle:违背要求" +
                                        commandInfo.CommandText +
                                        "必须符合select count(..的格式");
                                }

                                // 检查影响行是否大于0，否则回滚事务并抛出异常
                                object obj = oracleCommand.ExecuteScalar();
                                bool isHave = false;
                                if (obj == null && obj == DBNull.Value)
                                {
                                    isHave = false;
                                }
                                isHave = Convert.ToInt32(obj) > 0;
                                if (commandInfo.EffentNextType ==
                                    EffentNextType.WhenHaveContine && !isHave)
                                {
                                    oracleTransaction.Rollback();
                                    throw new Exception("Oracle:违背要求" +
                                        commandInfo.CommandText + "返回值必须大于0");
                                }
                                if (commandInfo.EffentNextType ==
                                    EffentNextType.WhenNoHaveContine && isHave)
                                {
                                    oracleTransaction.Rollback();
                                    throw new Exception("Oracle:违背要求" +
                                        commandInfo.CommandText + "返回值必须等于0");
                                }
                                continue;
                            }
                            // 检查是否符合影响行数不为0的规则，否则回滚事务并抛出异常
                            int res = oracleCommand.ExecuteNonQuery();
                            if (commandInfo.EffentNextType ==
                                EffentNextType.ExcuteEffectRows && res == 0)
                            {
                                oracleTransaction.Rollback();
                                throw new Exception("Oracle:违背要求" +
                                    commandInfo.CommandText + "必须有影像行");
                            }
                        }
                    }
                    oracleTransaction.Commit();
                    return true;
                }
                catch (OracleException ex)
                {
                    oracleTransaction.Rollback();
                    throw ex;
                }
                finally
                {
                    // 关闭数据库连接
                    if (oracleConnection.State != ConnectionState.Closed)
                    {
                        oracleConnection.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="SQLStringList">多条SQL语句</param>
        public static void ExecuteSqlTranByStrs(string connectionString,
            List<String> SQLStringList)
        {
            using (OracleConnection oracleConnection =
                new OracleConnection(connectionString))
            {
                // 打开数据库连接
                oracleConnection.Open();
                // 为执行命令对象赋值
                OracleCommand oracleCommand = new OracleCommand();
                oracleCommand.Connection = oracleConnection;
                OracleTransaction oracleTransaction = oracleConnection.BeginTransaction();
                oracleCommand.Transaction = oracleTransaction;
                try
                {
                    foreach (string sql in SQLStringList)
                    {
                        if (!string.IsNullOrEmpty(sql))
                        {
                            oracleCommand.CommandText = sql;
                            oracleCommand.ExecuteNonQuery();
                        }
                    }
                    oracleTransaction.Commit();
                }
                catch (OracleException ex)
                {
                    oracleTransaction.Rollback();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    if (oracleConnection.State != ConnectionState.Closed)
                    {
                        oracleConnection.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 获得该SQL查询返回DataTable，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="sql">查询语句</param>
        /// <param name="cmdParms">SQL语句参数</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTable(string connectionString,
            string SQLString, params OracleParameter[] cmdParms)
        {
            try
            {
                DataTable dataTable = new DataTable();
                DataSet dataSet = Query(connectionString, SQLString, cmdParms);
                if (dataSet != null)
                {
                    dataTable = dataSet.Tables["dataSet"];
                }
                return dataTable;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}