/// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">ArrayList</param>
        public static void ExecuteSqlTran(ArrayList sqlList)
        {
            bool mustCloseConnection = false;
            string ConString = System.Configuration.ConfigurationSettings.AppSettings["conInsertDB2"].ToString();
            using (DB2Connection conn = new DB2Connection(ConString))
            {
                conn.Open();
                using (DB2Transaction trans = conn.BeginTransaction())
                {
                    DB2Command cmd = new DB2Command();
                    try
                    {
                        for (int i = 0; i < sqlList.Count; i++)
                        {
                            string cmdText = sqlList[i].ToString();
                            PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, null, out mustCloseConnection);
                            int val = cmd.ExecuteNonQuery();

                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                        cmd.Dispose();
                    }
                }
            }
        }

 internal List<Right> GetBexisRights(string dataBase,  Dictionary<int, int> dataSetsMapping)
 {
     List<Right> bexisRights = new List<Right>();
     string datasetQuery = "";
     foreach (var dataSetMapping in dataSetsMapping)
     {
         datasetQuery += "DATASETID = "+ dataSetMapping.Key;
         if (dataSetsMapping.Last().Key != dataSetMapping.Key)
             datasetQuery += " or ";
     }
     if (dataSetsMapping.Any())
     {
         datasetQuery = "where " + datasetQuery + "";
     }
     // DB query
     string mySelectQuery = "SELECT ROLENAME, DATASETID, FOREDIT, APPLICATIONNAME FROM \"PROVIDER\".\"RIGHTS\"   "+ datasetQuery;
     DB2Connection connect = new DB2Connection(dataBase);
     DB2Command myCommand = new DB2Command(mySelectQuery, connect);
     connect.Open();
     DB2DataReader myReader = myCommand.ExecuteReader();
     while (myReader.Read())
     {
         bexisRights.Add(new Right()
         {
             RoleName = myReader.GetString(0),
             DataSetId = (int)(myReader.GetValue(1)),
             CanEdit = myReader.GetString(2)=="N"?false:true
         });
     }
     myReader.Close();
     connect.Close();
     return bexisRights;
 }

        /// <summary>
        /// 创建DB2Command命令,指定数据库连接对象,存储过程名和参数.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DB2Command command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="sourceColumns">源表的列名称数组</param>
        /// <returns>返回DB2Command命令</returns>
        public static DB2Command CreateCommand(DB2Connection connection, string spName, params string[] sourceColumns)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 创建命令
            DB2Command cmd = new DB2Command(spName, connection);
            cmd.CommandType = CommandType.StoredProcedure;

            // 如果有参数值
            if ((sourceColumns != null) && (sourceColumns.Length > 0))
            {
                // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()
                DB2Parameter[] commandParameters = DB2HelperParameterCache.GetSpParameterSet(connection, spName);

                // 将源表的列到映射到DataSet命令中.
                for (int index = 0; index < sourceColumns.Length; index++)
                    commandParameters[index].SourceColumn = sourceColumns[index];

                // Attach the discovered parameters to the DB2Command object
                AttachParameters(cmd, commandParameters);
            }

            return cmd;
        }

        public static IDbConnection GetDbConn(string dbname, MDataBaseType type)
        {
            IDbConnection conn = null;
            string connstr = GetBDConnString(dbname);
            switch (type)
            {
                case MDataBaseType.MYSQL:
                    conn = new MySqlConnection(connstr);
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }

                    break;
                case MDataBaseType.SQLSERVER:
                    conn = new SqlConnection(connstr);
                    break;
                case MDataBaseType.DB2:
                    conn = new DB2Connection(connstr);
                    break;
                case MDataBaseType.UNKNOW:
                    throw new Exception("未知数据库类型，创建数据库链接失败");
            }

            return conn;
        }

		protected override IDbConnection CreateConnection()
		{
			DB2Connection conn = new DB2Connection(ConnectionString);

			conn.Open();

			return conn;
		}

		public DB2Command(string commandStr, DB2Connection con) : this()
		{
			db2Conn = con;
			commandText = commandStr;
			if(con != null)
			{
				con.AddCommand(this);
			}
		}

		public DB2OpenConnection GetRealOpenConnection(DB2Connection connection)
		{
			if(pool != null)
			{
				return pool.GetOpenConnection(connection);
			}
			else
			{
				return new DB2OpenConnection(this, connection);
			}
		}

		public DB2OpenConnection GetOpenConnection(DB2Connection db2Conn)
		{
			DB2OpenConnection connection = null;
			lock(openFreeConnections.SyncRoot)
			{
				if((connectionSettings.ConnectionPoolSizeMax > 0) &&
					(connectionsOpen >= connectionSettings.ConnectionPoolSizeMax))
				{
					throw new ArgumentException("Maximum connections reached for connectionstring");
				}

				while(connectionsOpen > connectionsInUse)
				{
					connection = (DB2OpenConnection)openFreeConnections[openFreeConnections.Count - 1];
					openFreeConnections.RemoveAt(openFreeConnections.Count - 1);

					// check if connection is dead
					int isDead;
					short sqlRet = DB2CLIWrapper.SQLGetConnectAttr(connection.DBHandle, DB2Constants.SQL_ATTR_CONNECTION_DEAD, out isDead, 0, IntPtr.Zero);
					if(((sqlRet == DB2Constants.SQL_SUCCESS_WITH_INFO) || (sqlRet == DB2Constants.SQL_SUCCESS)) &&
						(isDead == DB2Constants.SQL_CD_FALSE))
					{
						connectionsInUse++;
						break;
					}
					else
					{
						connectionsOpen--;
						connection.Dispose();
						connection = null;
					}

				}
				if(connectionsOpen == connectionsInUse)
				{
					if(timer != null)
					{
						timer.Dispose();
						timer = null;
					}
				}
			}
			if(connection == null)
			{
				openFreeConnections.Clear();
				connectionsUsableOffset = 0;

				connection = new DB2OpenConnection(connectionSettings, db2Conn);
				connectionsOpen++;
				connectionsInUse++;
			}

			return connection;
		}

        public SitioCentral(String id, String cc,List<SuperGridControl> sgc)
            : base(id, cc)
        {
            conexion = new DB2Connection(cadena_conexion);

            SPG = new SitioPG("CONTROL_INTERNO", dame_cc_de("CONTROL_INTERNO"));
            SO = new SitioOracle("FARMACIA", dame_cc_de("FARMACIA"));
            SM1 = new SitioMySQL("CONSULTORIO1", dame_cc_de("CONSULTORIO1"));
            SM2 = new SitioMySQL("CONSULTORIO2", dame_cc_de("CONSULTORIO2"));
            SM3 = new SitioMySQL("CONSULTORIO3", dame_cc_de("CONSULTORIO3"));
            grids = sgc;
        }

        public void IBMBuldCopy()
        {
            string sql_bruv_1 = "Server=10.24.1.202:446;Database=BRUVDB4V;UID=ATWO;PWD=24rete31;Max Pool Size=100;Min Pool Size=10;";
            string sql_frymek = "Server=10.27.5.197:50000;Database=BRUV;UID=db2admin;[email protected]#$;Max Pool Size=100;Min Pool Size=10;";

            try
            {
                using (DB2Connection myConn = new DB2Connection(sql_bruv_1))
                {
                    using (DB2Connection conn = new DB2Connection(sql_frymek))
                    {
                        myConn.Open();
                        conn.Open();

                        log.InfoFormat("{0}: Time elapsed: {1}", Table, DateTime.Now);
                        string myInsertQuery = String.Format("SELECT * FROM RREV.{0}", Table);

                        using (DB2Command myDB2Command = new DB2Command(myInsertQuery, myConn))
                        {
                            using (DB2DataReader reader = myDB2Command.ExecuteReader())
                            {
                                using (DB2BulkCopy salesCopy = new DB2BulkCopy(conn))
                                {
                                    salesCopy.DestinationTableName = String.Format("ATWO.{0}", Table);
                                    salesCopy.WriteToServer(reader);
                                    var errors = salesCopy.Errors;
                                    if (errors.Count > 0)
                                    {
                                        log.ErrorFormat("table:{0}, errors:{1}", Table, errors.Count);
                                        foreach (var er in errors)
                                        {
                                            log.ErrorFormat("table:{0}, msg:{1}", Table, er.ToString());
                                        }
                                    }
                                    salesCopy.Close();

                                    myConn.Close();
                                    conn.Close();
                                }
                            }
                        }
                    }
                }
                log.InfoFormat("{0}: Time elapsed: {1}", Table, DateTime.Now);
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
                Console.WriteLine(String.Format("error: {0} with exception: {1}", Table, ex.Message));
            }
        }

        public List<MColumn> GetColumnList(string tableName)
        {
            string connstr = ConnectionFactory.TRSDbConnString;
            List<MColumn> ret = new List<MColumn>();
            using (DB2Connection conn = new DB2Connection(connstr))
            {
                conn.Open();
                var dao = DALFactory.GetSchemaDAO(MDataBaseType.DB2, MDBAccessType.WRITE);

                ret = dao.GetColumnList(conn, tableName);
            }

            return ret;
        }

		/// <summary>
		/// 
		/// </summary>
		/// <param name="con"></Connection object to Db2>
		/// <param name="com"></Command object>
		internal DB2DataReader(DB2Connection con, DB2Command com, CommandBehavior behavior)
		{
			db2Conn = con;
			db2Comm = com;
			this.behavior = behavior;
			hwndStmt = com.statementHandle;    //We have access to the results through the statement handle
			
			_resultSet = null;
			
			GetFieldCountAndAffectedRows();
			internalBuffer = Marshal.AllocHGlobal(internalBufferSize);

			isClosed = false;
		}

 //Boolean result = false;
 public Boolean ConnectDatabase()
 {
     try
     {
         //ConnPath = "";
         Conn = new DB2Connection("Server=localhost; UserID=db2admin; Password=admin...; Database=HRESS;");
         Conn.Open();
         return true;
     }
     catch (Exception e)
     {
         return false;
     }
 }

        // query bexis1 user from provider.users and generate a random password
        public List<UserProperties> GetFromBExIS(string DataBase)
        {
            List<UserProperties> transferUsers = new List<UserProperties>();

            // DB query
            string mySelectQuery = "select username, email, firstname, lastname, " +
                                   "organization, projectname, projectleader, " +
                                   "url, phone, mobile, fax, original, street, zipcode, city";
            mySelectQuery += " from provider.users;";
            DB2Connection connect = new DB2Connection(DataBase);
            DB2Command myCommand = new DB2Command(mySelectQuery, connect);
            connect.Open();
            DB2DataReader myReader = myCommand.ExecuteReader();
            // random password
            Random gen = new Random();
            while (myReader.Read())
            {
                UserProperties transferUser = new UserProperties();

                // bexis1 DB user data
                transferUser.username = myReader.GetValue(0).ToString();
                transferUser.email = myReader.GetValue(1).ToString();
                transferUser.firstname = myReader.GetValue(2).ToString();
                transferUser.lastname = myReader.GetValue(3).ToString();
                transferUser.organization = myReader.GetValue(4).ToString();
                transferUser.projectname = myReader.GetValue(5).ToString();
                transferUser.projectleader = myReader.GetValue(6).ToString();
                transferUser.url = myReader.GetValue(7).ToString();
                transferUser.phone = myReader.GetValue(8).ToString();
                transferUser.mobile = myReader.GetValue(9).ToString();
                transferUser.fax = myReader.GetValue(10).ToString();
                transferUser.original = myReader.GetValue(11).ToString();
                transferUser.street = myReader.GetValue(12).ToString();
                transferUser.zipcode = myReader.GetValue(13).ToString();
                transferUser.city = myReader.GetValue(14).ToString();
                // bexis2 required security data
                transferUser.password = randomPassword(ref gen); // random password
                transferUser.securityQuestionId = 1;
                transferUser.securityAnswer = "1";
                transferUser.authenticatorId = 1;

                // add to list; username required
                if (transferUser.username != "")
                    transferUsers.Add(transferUser);
            }
            myReader.Close();
            connect.Close();

            return transferUsers;
        }

        public List<MTableDesc> GetTableList(string creator)
        {
            string connstr = ConnectionFactory.TRSDbConnString;

            List<MTableDesc> ret = null;

            using (DB2Connection conn = new DB2Connection(connstr))
            {
                conn.Open();
                var dao = DALFactory.GetSchemaDAO(MDataBaseType.DB2, MDBAccessType.WRITE);

                ret = dao.GetTableList(conn, creator);
            }

            return ret;
        }

 internal List<string> GetBexisRoles(string DataBase)
 {
     List<string> bexisRoles = new List<string>();
     // DB query
     string mySelectQuery = "SELECT ROLENAME, APPLICATIONNAME FROM \"PROVIDER\".\"ROLES\"";
     DB2Connection connect = new DB2Connection(DataBase);
     DB2Command myCommand = new DB2Command(mySelectQuery, connect);
     connect.Open();
     DB2DataReader myReader = myCommand.ExecuteReader();
     while (myReader.Read())
     {
         bexisRoles.Add(myReader.GetValue(0).ToString());
     }
     myReader.Close();
     connect.Close();
     return bexisRoles;
 }

 internal List<string> GetBexisUsersInRole(string dataBase, string roleName)
 {
     List<string> bexisUsersInRole = new List<string>();
     // DB query
     string mySelectQuery = "SELECT USERNAME FROM \"PROVIDER\".\"USERSINROLES\" where ROLENAME='" + roleName + "'";
     DB2Connection connect = new DB2Connection(dataBase);
     DB2Command myCommand = new DB2Command(mySelectQuery, connect);
     connect.Open();
     DB2DataReader myReader = myCommand.ExecuteReader();
     while (myReader.Read())
     {
         bexisUsersInRole.Add(myReader.GetValue(0).ToString());
     }
     myReader.Close();
     connect.Close();
     return bexisUsersInRole;
 }

        public XmlDocument getMetadataXml(string dataSetID, string DataBase)
        {
            XmlDocument doc = new XmlDocument();

            string mySelectQuery = "select datasetid, metadata";
            mySelectQuery += " from explorer.datasets where datasetid = " + dataSetID + ";";
            DB2Connection connect = new DB2Connection(DataBase);
            DB2Command myCommand = new DB2Command(mySelectQuery, connect);
            connect.Open();
            DB2DataReader myReader = myCommand.ExecuteReader();
            while (myReader.Read())
            {
                doc.LoadXml(myReader.GetString(1));
            }
            myReader.Close();
            connect.Close();

            return doc;
        }

		/// <summary>
		/// Please refer to the documentation of <see cref="GentleProvider"/> and the
		/// <see cref="IGentleProvider"/> interface it implements for details. 
		/// </summary>
		public override IDbConnection GetConnection()
		{
			try
			{
				IDbConnection dbc = new DB2Connection( ConnectionString );
				dbc.Open();
				Check.VerifyEquals( dbc.State, ConnectionState.Open, Error.NoNewConnection );
				return dbc;
			}
			catch( GentleException )
			{
				throw; // expose the errors raised by ourselves (i.e. the data framework) in the try block
			}
			catch( Exception e )
			{
				Check.Fail( e, Error.DatabaseUnavailable, Name, ConnectionString );
				throw new GentleException( Error.Unspecified, "Unreachable code!" );
			}
		}

		public static void DB2CheckReturn(short sqlRet, short handleType, IntPtr handle, string message, DB2Connection connection)
		{
			switch (sqlRet) 
			{
				case DB2Constants.SQL_SUCCESS_WITH_INFO:
					if(connection != null)
					{
						connection.OnInfoMessage(handleType, handle);
					}
					goto case DB2Constants.SQL_SUCCESS;
				case DB2Constants.SQL_SUCCESS:
				case DB2Constants.SQL_NO_DATA:
					return;

				case DB2Constants.SQL_INVALID_HANDLE:
					throw new ArgumentException("Invalid handle");

				default:
				case DB2Constants.SQL_ERROR:
					throw new DB2Exception(handleType, handle, message);
			}
		}