DbProviderFactory 多数据库访问



public class DBHandler
    {
        enum DBType
        {
            SqlServer2000,
            SqlServer,
            Oracle,
            SQLite
        }

        private static string FLastError = string.Empty;   

        private static string FEngine = "";
        private DBType dbType;//数据库类型     
        private static ConnectionStringSettings FConSettings;
        private static DbProviderFactory FProvider;
        private static DbConnection FConn;
        private static int Timeout = 240;

        #region Get Property
        public string LastError
        {
            get { return FLastError; }
        }

        public string ConnectionString
        {
            get { return GetConnectionString(); }
        }

        public DbProviderFactory ProviderFactory
        {
            get { return FProvider; }
        }

        public string ProviderType
        {
            get { return GetProviderType(); }
        }
        #endregion

        public DBHandler(string AEngine="")
        {
            string CurPC = string.Empty;
            string DevPC = string.Empty;

            try
            {
                FEngine = string.IsNullOrEmpty(AEngine) ? ConfigurationManager.AppSettings["DBType"].ToString() : AEngine;

                CurPC = System.Environment.MachineName;
                DevPC = ConfigurationManager.AppSettings["DevelopmentPC"].ToString();

                if (CurPC.ToUpper().Equals(DevPC.ToUpper()))
                {
                    #if (DEBUG)
                        FEngine += "DV";
                    #else
                        FEngine += "QA";
                    #endif
                }
                else
                {
                    #if (DEBUG)
                        FEngine += "QA";
                    #endif
                }

                if (string.IsNullOrEmpty(FEngine))
                {
                    throw new Exception("Have not set up or designate the  driven for database connection!");
                }
                FConSettings = ConfigurationManager.ConnectionStrings[FEngine];
                FProvider = DbProviderFactories.GetFactory(FConSettings.ProviderName);
                GetDatabaseType();
            }
            catch(Exception ex)
            {
                HandleException(ex);
            }
        }

        #region 数据库连接
        private static string GetConnectionString()
        {       
            try
            {      
                        
                if (string.IsNullOrEmpty(FConSettings.ConnectionString))
                {
                    throw new Exception(string.Format("Please add connection string {0}!", FEngine));
                }
                return FConSettings.ConnectionString;
            }
            catch(Exception ex)
            {
                HandleException(ex) ;
                return "";
            }
        }

        private static void OpenConnection()
        {
            try
            {
                if (FConn == null)
                {
                    FConn = FProvider.CreateConnection();
                    //switch (FConSettings.ProviderName)
                    //{
                    //    case "System.Data.SqlClient":
                    //        FConn = new SqlConnection();
                    //        break;
                    //    case "Oracle.ManagedDataAccess.Client":
                    //    case "Oracle.DataAccess.Client":
                    //    case "System.Data.OracleClient":
                    //        FConn = new OracleConnection();// FProvider.CreateConnection();
                    //        break;
                    //    default:
                    //        FConn = FProvider.CreateConnection();
                    //        break;
                    //}
                }

                if(FConn.State != ConnectionState.Open)
                {
                    FConn.ConnectionString = GetConnectionString();// FConSettings.ConnectionString;
                    FConn.Close();
                    FConn.Open();
                }                                       
            }
            catch(Exception ex)
            {
                HandleException( ex);
            }      
        }

        public void CloseConnection()
        {
            if (FConn .State !=ConnectionState.Closed )
            {               
               // FConn.Dispose();
                FConn.Close();
                FConn = null;
            }
        }
      
        private static string GetProviderType()
        {
            return FProvider.ToString();
        }

        private void GetDatabaseType()
        {
            string FactoryName = FProvider.GetType().Name;

            if (FactoryName.StartsWith("Oracle")) dbType = DBType.Oracle;
            else if (FactoryName.StartsWith("SQLite")) dbType = DBType.SQLite;
            else if (FactoryName.StartsWith("System.Data.SqlClient")) dbType = DBType.SqlServer;
            // else try with provider name
            else if (FactoryName.IndexOf("Oracle", StringComparison.InvariantCultureIgnoreCase) >= 0) dbType = DBType.Oracle;
            else if (FactoryName.IndexOf("SQLite", StringComparison.InvariantCultureIgnoreCase) >= 0) dbType = DBType.SQLite;
        }

        private DbParameter refCursorPara(string AParaName = "")
        {
            string ParaName = string.IsNullOrEmpty(AParaName) ? "PCur" : AParaName;
            var refCursorType = Enum.Parse(((dynamic)FProvider.CreateParameter()).OracleDbType.GetType(), "RefCursor");
            var param = FProvider.CreateParameter();
            param.ParameterName = ParaName;
            param.Direction = ParameterDirection.Output;
            var piInstance = param.GetType().GetProperty("OracleDbType");
            piInstance.SetValue(param, refCursorType, null);
            return param;
        }
        #endregion

        #region 创建&删除表
        public Boolean DeleteTable(string ATable)
        {
            Boolean CreResult = false;
            string DelSql = string.Empty;
            DbDataReader dr=null;

            try
            {
                //switch (FProvider.ToString())
                //{
                //    case "Oracle.ManagedDataAccess.Client":
                //    case "Oracle.DataAccess.Client":
                //    case "System.Data.OracleClient":
                //    case "System.Data.OracleClient.OracleClientFactory":
                //        DelSql = string.Format("SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_NAME =upper('{0}')",ATable);
                //        break;
                //    default:
                //        DelSql = string.Format("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='{0}' ", ATable);
                //        break;
                //}

                if(FProvider.ToString().ToUpper().IndexOf("ORACLE")>-1)
                {
                    DelSql = string.Format("SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_NAME =upper('{0}')", ATable);
                }
                else
                {
                    DelSql = string.Format("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='{0}' ", ATable);
                }

                dr = GetDataReader(DelSql) ;
                if(dr.HasRows)
                {
                    if (!dr.IsClosed)
                    { dr.Close(); }
                    DelSql = string.Format("Drop table {0}", ATable);
                    ExecuteSql(DelSql);
                }

                CreResult = true;
            }
            catch(Exception ex)
            {
                HandleException(ex);
                CreResult = false;
            }
            finally
            {
                if(!dr.IsClosed )
                { dr.Close(); }               
            }
            return CreResult;
        }

        public Boolean CreateTmpTable(string ATmpTable,string ASource,string AFields, NameValueCollection AVCFields=null)
        {
            Boolean CreResult = false;
            string CreSql = string.Empty;
            string CreFields = string.Empty;

            if(AVCFields !=null)
            {
                foreach(string fs in AVCFields .Keys )
                {
                    CreFields += string.Format("{0},", fs);
                }
                CreFields = CreFields.Substring(0, CreFields.LastIndexOf(","));
            }
            else
            {
                CreFields = AFields;
            }

            try
            {
                DeleteTable(ATmpTable);             
                if(FProvider.ToString().ToUpper ().IndexOf("ORACLE")>-1)
                {
                    CreSql = string.Format("create table {0} as " + System.Environment.NewLine, ATmpTable);
                    CreSql += string.Format("select {0} from {1} where 1=0", CreFields, ASource);
                }
                else
                { CreSql = string.Format("select {0} into {1} from {2} where 1=0", CreFields, ATmpTable, ASource); }

                ExecuteSql(CreSql);
                CreResult = true;
            }
            catch (Exception ex)
            {
                HandleException(ex);
                CreResult = false;
            }
            return CreResult;
        }
        #endregion

        #region  执行简单SQL语句

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteSql(string SQLString )
        { 
            try
            {                    
                OpenConnection();                      
                using (DbCommand cmd = FConn.CreateCommand())
                {
                    cmd.CommandTimeout = Timeout;
                    cmd.Connection = FConn;
                    cmd.CommandText = SQLString;
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
            }
            catch(Exception ex)
            {
                FConn.Close();
               // FConn.Dispose();
                HandleException(ex);
                return -1;
            }           
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>       
        public void ExecuteMultiSql(ArrayList SQLStringList)
        {
            if (FConn == null)
            {
                OpenConnection();
            }
            if (FConn.State == ConnectionState.Closed)
            { FConn.Open(); }
         
            using (DbCommand cmd = FConn.CreateCommand())
            {
                cmd.Connection = FConn;
                cmd.CommandTimeout = Timeout;
                using (DbTransaction tx = FConn.BeginTransaction())
                {
                    cmd.Transaction = tx;
                    try
                    {
                        for (int n = 0; n < SQLStringList.Count; n++)
                        {
                            string strsql = SQLStringList[n].ToString();
                            if (strsql.Trim().Length > 1)
                            {
                                cmd.CommandText = strsql;
                                cmd.ExecuteNonQuery();
                            }
                        }
                        tx.Commit();
                    }
                    catch (DbException ex)
                    {
                        tx.Rollback();
                        FConn.Close();
                        //FConn.Dispose();
                        HandleException(ex);
                    }
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）第一行第一列。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public object GetSingle(string SQLString)
        {
            if (FConn == null)
            {
                OpenConnection();
            }
            if (FConn.State == ConnectionState.Closed)
            { FConn.Open(); }
         
            using (DbCommand cmd = FConn.CreateCommand())
            {
                cmd.CommandTimeout = Timeout;
                cmd.Connection = FConn;
                cmd.CommandText = SQLString;
                try
                {
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (DbException ex)
                {
                    FConn.Close();
                   // FConn.Dispose();
                    HandleException(ex);
                    return null;
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public  DbDataReader GetDataReader(string strSQL)
        {
            if (FConn == null)
            {
                OpenConnection();
            }
            if (FConn.State == ConnectionState.Closed)
            { FConn.Open(); }
            DbCommand cmd = FConn.CreateCommand();
            cmd.CommandTimeout = Timeout;
            cmd.Connection = FConn;
            cmd.CommandText = strSQL;
            try
            {
                DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch(Exception ex)
            {
                FConn.Close();
               // FConn.Dispose();
                HandleException(ex);
                return null;
            }

        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(string SQLString)
        {
            if (FConn == null)
            {
                OpenConnection();
            }
            if (FConn.State == ConnectionState.Closed)
            { FConn.Open(); }
               
            using (DbCommand cmd = FConn.CreateCommand())
            {
                cmd.CommandTimeout = Timeout;
                cmd.Connection = FConn;
                cmd.CommandText = SQLString;
                try
                {
                    DataSet ds = new DataSet();
                    DbDataAdapter adapter = FProvider.CreateDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.Fill(ds, "ds");
                    return ds;
                }
                catch (DbException ex)
                {
                    FConn.Close();
                 //   FConn.Dispose();
                    HandleException(ex);
                    return null;
                }
            }
        }

        /// <summary>
        /// 执行查询返回DataTable
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <returns>成功返回DataTable,失败则返回 null</returns>
        public DataTable GetDataTable(string sql)
        {
            try
            {
                DataTable dt = new DataTable();
                IDataReader reader = GetDataReader(sql);
                dt.Load(reader);
                return dt;
            }
            catch (Exception ex)
            {
                HandleException(ex);
                return null;
            }
        }

        #endregion

        #region 执行带参数的SQL语句

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteSql(string SQLString, DbParameter[] cmdParms)
        {
            OpenConnection();

            using (DbCommand cmd = FConn.CreateCommand())
            {
                cmd.CommandTimeout = Timeout;
                cmd.Connection = FConn;
                cmd.CommandText = SQLString;
                try
                {
                    PrepareCommand(cmd, FConn, null, SQLString, cmdParms);
                    int rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return rows;
                }
                catch (DbException ex)
                {
                    FConn.Close();
                   // FConn.Dispose();
                    HandleException(ex);
                    return -1;
                }
            }           
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public void ExecuteMultiSql(Hashtable SQLStringList)
        {
            OpenConnection();
            using (DbTransaction trans = FConn.BeginTransaction())
            {
                using (DbCommand cmd = FConn.CreateCommand())
                {
                    cmd.CommandTimeout = Timeout;
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            DbParameter[] cmdParms = (DbParameter[])myDE.Value;
                            PrepareCommand(cmd, FConn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch (DbException ex)
                    {
                        trans.Rollback();
                        FConn.Close();
                       // FConn.Dispose();
                        HandleException(ex);
                    }
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）,返回首行首列的值;
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public object GetSingle(string SQLString, DbParameter[] cmdParms)
        {
            //if (FConn == null)
            //{
            //    OpenConnection();
            //}
            //if (FConn.State == ConnectionState.Closed)
            //{ FConn.Open(); }
            OpenConnection();

            using (DbCommand cmd = FConn.CreateCommand())
            {
                cmd.CommandTimeout = Timeout;
                try
                {
                    PrepareCommand(cmd, FConn, null, SQLString, cmdParms);
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (DbException ex)
                {
                    FConn.Close();
                  //  FConn.Dispose();
                    HandleException(ex);
                    return null;
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public DbDataReader GetDataReader(string SQLString, DbParameter[] cmdParms)
        {
            //if (FConn == null)
            //{
            //    OpenConnection();
            //}
            //if (FConn.State == ConnectionState.Closed)
            //{ FConn.Open(); }
            OpenConnection();

            DbCommand cmd = FConn.CreateCommand();
            cmd.CommandTimeout = Timeout;
            try
            {
                PrepareCommand(cmd, FConn, null, SQLString, cmdParms);
                DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (DbException ex)
            {
                FConn.Close();
               // FConn.Dispose();
                HandleException(ex);
                return null;
            }

        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(string SQLString, DbParameter[] cmdParms)
        {        
            OpenConnection();

            using (DbCommand cmd = FConn.CreateCommand())
            {
                cmd.CommandTimeout = Timeout;
                using (DbDataAdapter da = FProvider.CreateDataAdapter())
                {
                    PrepareCommand(cmd, FConn, null, SQLString, cmdParms);
                    da.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                        return ds;
                    }
                    catch (DbException ex)
                    {
                        FConn.Close();
                       // FConn.Dispose();
                        HandleException(ex);
                        return null;
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql,DbParameter[] cmdParms)
        {
            try
            {
                DataTable dt = new DataTable();
                IDataReader reader = GetDataReader(sql, cmdParms);
                dt.Load(reader);
                return dt;
            }
            catch(Exception ex)
            {
                HandleException(ex);
                return null;
            }
        }

        private void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cmdParms)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                cmd.CommandType = CommandType.Text;//cmdType;
                if (cmdParms != null)
                {
                    foreach (DbParameter parm in cmdParms)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }
            }
            catch(Exception ex)
            {
                HandleException(ex);
            }
        }

        #endregion

        #region 存储过程操作
        /// <summary>
        /// 执行存储过程;
        /// </summary>
        /// <param name="storeProcName">存储过程名</param>
        /// <param name="parameters">所需要的参数</param>
        /// <returns>返回受影响的行数</returns>
        public int ProcedureExecuteSql(string storeProcName,  DbParameter[] parameters)
        {
            int rows = -1;             
            try
            {
                DbCommand cmd = BuildQueryCommand(FConn, storeProcName, parameters);
                OpenConnection();
                cmd.CommandTimeout = Timeout;
                rows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                FConn.Close();
               
            }
            catch(Exception ex)
            {
                FConn.Close();
             //   FConn.Dispose();
                HandleException(ex);
            }
            return rows;
        }

        /// <summary>
        /// 执行存储过程,返回首行首列的值
        /// </summary>
        /// <param name="storeProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>返回首行首列的值</returns>
        public Object ProcedureGetSingle(string storeProcName, DbParameter[] parameters)
        {
            try
            {
                DbCommand cmd = BuildQueryCommand(FConn, storeProcName, parameters);
                OpenConnection();
                cmd.CommandTimeout = Timeout;
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    return null;
                }
                else
                {
                    return obj;
                }
            }
            catch (DbException ex)
            {
                FConn.Close();
             //   FConn.Dispose();
                HandleException(ex);
                return null;
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public DbDataReader ProcedureGetDataReader(string storedProcName, DbParameter[] parameters)
        {
            //if (FConn == null)
            //{
            //    OpenConnection();
            //}
            //if (FConn.State == ConnectionState.Closed)
            //{ FConn.Open(); }
            DbCommand cmd = BuildQueryCommand(FConn, storedProcName, parameters);
            OpenConnection();

            try
            {
               
                cmd.CommandTimeout = Timeout;
                cmd.CommandType = CommandType.StoredProcedure;
                DbDataReader returnReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return returnReader;
            }
            catch(Exception ex)
            {
                FConn.Close();
                // FConn.Dispose();
                HandleException(ex);
                return null;
            }              
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>DataSet</returns>
        public DataSet ProcedureGetDataSet(string storedProcName, DbParameter[] parameters)
        {
            //if (FConn == null)
            //{
            //    OpenConnection();
            //}
            //if (FConn.State == ConnectionState.Closed)
            //{ FConn.Open(); }
            OpenConnection();

            try
            {
                DataSet dataSet = new DataSet();
                DbDataAdapter sqlDA = FProvider.CreateDataAdapter();               
                sqlDA.SelectCommand = BuildQueryCommand(FConn, storedProcName, parameters);
                sqlDA.Fill(dataSet);
                sqlDA.SelectCommand.Parameters.Clear();
                sqlDA.Dispose();
                return dataSet;
            }
            catch (Exception ex)
            {
                FConn.Close();
               // FConn.Dispose();
                HandleException(ex);
                return null;
            }
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public DataTable ProcedureGetDataTable(string storedProcName, DbParameter[] cmdParms)
        {
            try
            {
                DataTable dt = new DataTable();
                IDataReader reader = ProcedureGetDataReader(storedProcName, cmdParms);
                dt.Load(reader);
                return dt;
            }
            catch (Exception ex)
            {
                HandleException(ex);
                return null;
            }
        }

        /// <summary>
        /// 执行多个存储过程，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">存储过程的哈希表（value为存储过程语句，key是该语句的DbParameter[]）</param>
        public bool ExecuteMultiProcedure(Hashtable SQLStringList)
        {
            //if (FConn == null)
            //{
            //    OpenConnection();
            //}
            //if (FConn.State == ConnectionState.Closed)
            //{ FConn.Open(); }
            OpenConnection();

            using (DbTransaction trans = FConn.BeginTransaction())
            {
                using (DbCommand cmd = FConn.CreateCommand())
                {
                    cmd.CommandTimeout = Timeout;
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            cmd.Connection = FConn;
                            string storeName = myDE.Value.ToString();
                            DbParameter[] cmdParms = (DbParameter[])myDE.Key;

                            cmd.Transaction = trans;
                            cmd.CommandText = storeName;
                            cmd.CommandType = CommandType.StoredProcedure;
                            if (cmdParms != null)
                            {
                                foreach (DbParameter parameter in cmdParms)
                                {
                                    cmd.Parameters.Add(parameter);
                                }
                            }
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                        return true;
                    }
                    catch(Exception ex)
                    {
                        trans.Rollback();
                        FConn.Close();
                     //   FConn.Dispose();
                        HandleException(ex);
                        return false;
                    }
                }
            }
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="AConn">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private DbCommand BuildQueryCommand(DbConnection AConn, string storedProcName, DbParameter[] parameters)
        {
            try
            {
                if (AConn.State != ConnectionState.Open)
                {
                    AConn.Open();
                }
                DbCommand command = AConn.CreateCommand();
                command.CommandTimeout = Timeout;
                command.CommandText = storedProcName;
                command.Connection = AConn;
                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (DbParameter parameter in parameters)
                    {
                        if (parameter != null)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                }

                if(dbType==DBType.Oracle)
                {
                    string CurSql = string.Format("select CurName from cursorlist where upper(ProcName)=upper('{0}') order by seqnum", storedProcName);
                    DbDataReader DR = null;
                    DbParameter par = null;

                    //AProviderFactory.CreateParameter();
                    //parameter.ParameterName = AName;
                    //parameter.Value = AValue;
                    //if (ParaLength > 0)
                    //{
                    //    parameter.Size = ParaLength;
                    //}
                    //parameter.Direction = parameterDirection;

                    try
                    {
                        DR = GetDataReader(CurSql);
                        if (DR.HasRows)
                        {
                            while (DR.Read())
                            {
                                //par = FProvider.CreateParameter();// new OracleParameter(DR["CurName"].ToString().Trim(), OracleDbType.RefCursor, ParameterDirection.Output);
                                //par.ParameterName = DR["CurName"].ToString().Trim();
                                //par.DbType = DbType.Object;
                                //par.Direction = ParameterDirection.Output;

                                command.Parameters.Add(refCursorPara(DR["CurName"].ToString().Trim()));
                            }
                        }
                        else
                        {
                            //par = FProvider.CreateParameter(); // new OracleParameter("PCur", OracleDbType.RefCursor, ParameterDirection.Output);
                            //par.ParameterName = "PCur";
                            //par.DbType = DbType.Object;
                            //par.Direction = ParameterDirection.Output;

                            command.Parameters.Add(refCursorPara("PCur"));
                        }
                    }
                    finally
                    {
                        if (!DR.IsClosed)
                        {
                            DR.Close();
                        }
                    }
                    //OracleParameter par = new OracleParameter("PCur", OracleDbType.RefCursor, ParameterDirection.Output);
                    //command.Parameters.Add(par);
                    //   DbParameter[] dbPar = { new OracleParameter("PCur", OracleDbType.RefCursor, ParameterDirection.Output) };
                    //  command.Parameters.AddRange(dbPar);

                }           
                return command;
            }
            catch (Exception ex)
            {
                string str = ex.Message;
                HandleException(ex);
                return null;
            }
        }
        #endregion

        #region Bulk import data
        public Boolean BulkImport(DataTable Adt,string ASql,string AParName,string ATypeName)
        {
            Boolean RetBI = false;

            try
            {
               
                switch (FProvider.ToString())
                {
                    case "Oracle.ManagedDataAccess.Client":
                    case "Oracle.DataAccess.Client":
                    case "System.Data.OracleClient":
                    case "System.Data.OracleClient.OracleClientFactory":
                       // RetBI = new OracleParameter();
                        break;
                    default:
                        RetBI = BulkImportSql(Adt, ASql, AParName, ATypeName);
                        break;
                }              

                return true;
            }
            catch(Exception ex)
            {
                HandleException(ex);
                RetBI= false;
            }
            finally
            {
               
            }
            return RetBI;
        }

        private Boolean BulkImportSql(DataTable Adt, string ASql, string AParName, string ATypeName)
        {
            Boolean RetBISql = false;
            try
            {
                SqlParameter catParam = new SqlParameter();// cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt);
                catParam.ParameterName = AParName;
                catParam.Value = Adt;
                catParam.SqlDbType = SqlDbType.Structured;
                catParam.TypeName = ATypeName.StartsWith("dbo.")? ATypeName:string.Format("dbo.{0}", ATypeName);        

                DbParameter[] dbPar = { catParam };

                ExecuteSql(ASql, dbPar);
            }
            catch(Exception ex)
            {
                RetBISql = false;
                throw (ex);              
            }
            return RetBISql;
        }

        public Boolean BulkImport(DataTable ADataTable, string tableName, string Columns="")
        {
            string SqlStr = string.Empty;
            string ColStr = string.Empty;
            DataTable dsNew;

            try
            {
                if (ADataTable.Rows.Count <= 0)
                {
                    return false;
                }

                if (FConn == null)
                {
                    OpenConnection();
                }
                if (FConn.State == ConnectionState.Closed)
                { FConn.Open(); }
            

                //select sql
                if(string.IsNullOrEmpty(Columns))
                {
                    foreach(DataColumn dc in ADataTable.Columns)
                    {
                        ColStr += string.Format("{0},", dc.ColumnName.Trim());
                    }
                    ColStr = ColStr.Substring(0, ColStr.LastIndexOf(","));
                }
                else
                {
                    ColStr = Columns;                   
                }
                SqlStr = string.Format("select {0} from {1} where 1=0", ColStr, tableName);

                DbCommand cmd = null;
                DbDataAdapter ada = null;
                DbCommandBuilder cb = null;

                switch (FProvider.ToString())
                {
                    case "Oracle.ManagedDataAccess.Client":
                    case "Oracle.DataAccess.Client":
                    case "System.Data.OracleClient":
                    case "System.Data.OracleClient.OracleClientFactory":
                        cmd =new OracleCommand();
                        ada = new OracleDataAdapter();
                        cb = new OracleCommandBuilder();
                        break;
                    default:
                        cmd = new SqlCommand();
                        ada = new SqlDataAdapter();
                        cb = new SqlCommandBuilder();
                        break;
                }

                cmd.CommandText = SqlStr;
                cmd.Connection = FConn;

                ada.SelectCommand = cmd;
                cb.DataAdapter = ada;

                dsNew = new DataTable();
                ada.Fill(dsNew);

                foreach (DataRow dr in ADataTable.Rows)
                {
                    dsNew.Rows.Add(dr.ItemArray);
                }
                ada.UpdateBatchSize = 200;
                ada.Update(dsNew);

                  using (DbCommand cmd = new OracleCommand(SqlStr, FConn as OracleConnection))
                //using (DbCommand cmd = FProvider.CreateCommand())
                //{
                //    cmd.CommandText = SqlStr;
                //    cmd.Connection = FConn;

                //    // OracleDataAdapter adapter = new OracleDataAdapter();
                //    DbDataAdapter adapter = FProvider.CreateDataAdapter();
                //    adapter.SelectCommand = cmd;
                //    // OracleCommandBuilder cb = new OracleCommandBuilder(adapter);
                //    DbCommandBuilder cb = FProvider.CreateCommandBuilder();
                //    cb.DataAdapter = adapter;

                //    dsNew = new DataTable();
                //    adapter.Fill(dsNew);

                //    foreach (DataRow dr in ADataTable.Rows)
                //    {
                //        dsNew.Rows.Add(dr.ItemArray);
                //    }
                //    adapter.UpdateBatchSize = 200;
                //    adapter.Update(dsNew);
                //}               
                return true;
            }
            catch(Exception ex)
            {
                HandleException(ex);
                return false;
            }
        }

        public Boolean BulkCopy(DataTable Adt, string ATargetTb, NameValueCollection AMappingFields, int AStartCol = 0, NameValueCollection AFixFields = null, NameValueCollection AShiftyFields = null, int ALength = 0, int ABatchSize = 0)
        {
            Boolean RetBC = false;

            try
            {
                if (Adt.Rows.Count <= 0)
                {
                    return RetBC;
                }

                //if (FConn == null)
                //{
                //    OpenConnection();
                //}
                //if (FConn.State == ConnectionState.Closed)
                //{ FConn.Open(); }

                BulkCopyHandler bcH = new BulkCopyHandler(FConSettings, Adt, ATargetTb, AMappingFields, AStartCol, AFixFields,AShiftyFields , ALength , ABatchSize);
                return true;
            }
            catch(Exception ex)
            {
                RetBC = false;
                HandleException(ex);
            }
            return RetBC;
        }

        #endregion

        #region Meger into
        public Boolean MergeInto(string ASouTable, string ADataSource, string AFile, string APrefix, NameValueCollection AMappingFields, NameValueCollection AFixFields = null, NameValueCollection AShiftyFields=null)
        {
            Boolean miResult = false;
            string MerStr = string.Empty;
            ArrayList ArrSql = new ArrayList();
            string[] SepStr = new string[] { "!#!" };

            try
            {
                MergeHandler meH = new MergeHandler(FConSettings, ASouTable,ADataSource, AFile,APrefix, AMappingFields, AFixFields, AShiftyFields);
                MerStr = meH.MergeStr;

                ArrSql.Clear();
                ArrSql.AddRange(MerStr.Split(SepStr, StringSplitOptions.RemoveEmptyEntries));
             
                if(ArrSql.Count>0)
                {
                    ExecuteMultiSql(ArrSql);
                    miResult = true;
                }              
            }
            catch(Exception ex)
            {
                miResult = false;
                HandleException(ex);
            }
            return miResult;
        }
        #endregion

        private static void HandleException(Exception e)
        {
            string ErrStr = string.Empty;
            if (e is SqlException)
            {
                ErrStr = string.Format("when opening the database connection error occurred:{0}", e.Message);
            }
            else if(e is InvalidOperationException)
            {
                ErrStr = e.Message;
            }
            else if (e is DBConcurrencyException)
            {
                ErrStr = string.Format("Execute the insert、Update、Delete, but no records are affected:{0}", e.Message);
            }
            else
            {
                ErrStr = string.Format("Unknown error occurred to perform database operations:{0}", e.Message );
            }
            FLastError = ErrStr;
            if (! string.IsNullOrEmpty(ErrStr))
            {
              //  MessageBox.Show(ErrStr);
                throw new Exception(ErrStr);
            }
           
        }

    }