C# MySqlConnection.ChangeDatabase方法代码示例

            private void init(string connString) {
                connection = new MySqlConnection(connString);
                connection.Open();
                connection.ChangeDatabase(Server.MySQLDatabaseName);

                transaction = connection.BeginTransaction();
            }

        public static MySqlConnection GetConnection()
        {
            MySqlConnection connection;

            try
            {
                Dictionary<string, string> defaults = new Dictionary<string, string>();
                defaults.Add("connectionString", "datasource=localhost;port=3306;username=talky;password=talky;");
                defaults.Add("database", "talky");

                ConfigurationFile config = new ConfigurationFile("database");
                if (!config.Exists())
                {
                    config.Write(defaults);
                }

                string connectionString;
                string db;
                config.Values(defaults).TryGetValue("connectionString", out connectionString);
                config.Values(defaults).TryGetValue("database", out db);

                connection = new MySqlConnection(connectionString);
                connection.Open();
                connection.ChangeDatabase(db);

                return connection;
            } catch (MySqlException e)
            {
                Program.Instance.OHGODNO("Terminate!! Could not connect to MySQL!! Danger!! Danger!!", e);
                return null;
            }
        }

 public override IDbConnection GetConnWithoutDatabaseSpecified()
 {
     var systemConnection = new MySqlConnection(connectionString);
     systemConnection.Open();
     systemConnection.ChangeDatabase("mysql");
     return systemConnection;
 }

        public void CanCreateDBScriptWithDateTimePrecision()
        {
            if (Version < new Version(5, 6, 5)) return;

             MySqlConnection c = new MySqlConnection(conn.ConnectionString);
             c.Open();

             var script = new MySqlScript(c);
             using (var ctx = new datesTypesEntities())
             {
               MySqlCommand query = new MySqlCommand("Create database test_types", c);
               query.Connection = c;
               query.ExecuteNonQuery();
               c.ChangeDatabase("test_types");

               script.Query = ctx.CreateDatabaseScript();
               script.Execute();

               query = new MySqlCommand("Select Column_name, Is_Nullable, Data_Type, DateTime_Precision from information_schema.Columns where table_schema ='" + c.Database + "' and table_name = 'Products' and column_name ='DateTimeWithPrecision'", c);
               query.Connection = c;
               MySqlDataReader reader = query.ExecuteReader();
               while (reader.Read())
               {
              Assert.AreEqual("DateTimeWithPrecision", reader[0].ToString());
              Assert.AreEqual("NO", reader[1].ToString());
              Assert.AreEqual("datetime", reader[2].ToString());
              Assert.AreEqual("3", reader[3].ToString());
               }
               reader.Close();
               ctx.DeleteDatabase();
               c.Close();
             }
        }

 public static void fill(string queryString, DataTable toReturn) {
     using (var conn = new MySqlConnection(connString)) {
         conn.Open();
         conn.ChangeDatabase(Server.MySQLDatabaseName);
         using (MySqlDataAdapter da = new MySqlDataAdapter(queryString, conn)) {
             da.Fill(toReturn);
         }
         conn.Close();
     }
 }

 public static void execute(string queryString, bool createDB) {
     using (var conn = new MySqlConnection(connString)) {
         conn.Open();
         if (!createDB) {
             conn.ChangeDatabase(Server.MySQLDatabaseName);
         }
         using (MySqlCommand cmd = new MySqlCommand(queryString, conn)) {
             cmd.ExecuteNonQuery();
             conn.Close();
         }
     }
 }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="host"></param>
        /// <param name="username"></param>
        /// <param name="passwd"></param>
        /// <param name="dbname"></param>
        /// <param name="port"></param>
        /// <param name="socket"></param>
        public MySQLi(
            string host = TAKE_FROM_INI,
            string username = TAKE_FROM_INI,
            string passwd = TAKE_FROM_INI,
            string dbname = "",
            int port = default_port,
            string socket = TAKE_FROM_INI)
        {
            if (host == TAKE_FROM_INI)
                PhpIni.TryGetValue("mysqli.default_host", out host);
            if (username == TAKE_FROM_INI)
                PhpIni.TryGetValue("mysqli.default_user", out username);
            if (passwd == TAKE_FROM_INI)
                PhpIni.TryGetValue("mysqli.default_pw", out passwd);
            if (port == default_port)
            {
                string portStr;
                if (PhpIni.TryGetValue("mysqli.default_port", out portStr))
                    port = int.Parse(portStr);
                else
                    port = PhpIni.MYSQL_DEFAULT_PORT;
            }
            if (socket == TAKE_FROM_INI)
                PhpIni.TryGetValue("mysqli.default_socket", out socket);
            //  MySQL a = new MySQL();
            MySqlConnectionStringBuilder b = new MySqlConnectionStringBuilder();
            b.Server = host;
            b.UserID = username;
            b.Port = (uint)port;
            if (!string.IsNullOrEmpty(passwd))
                b.Password = passwd;
            _connection = new MySqlConnection(b.ToString());
            try
            {
                _connection.Open();
                if (!string.IsNullOrEmpty(dbname))
                    _connection.ChangeDatabase(dbname);
            }
            catch (MySqlException e)
            {
                connectError = e.Message;
                connectErrno = e.Number;
            }
            catch (Exception e)
            {
                connectError = e.Message;
            }

        }

 public override void executeQuery(string queryString)
 {
     try {
         using (var conn = new MySqlConnection(connString))
         {
             conn.Open();
             if (queryString.IndexOf("CREATE DATABASE") != -1)
                 conn.ChangeDatabase(ServerSettings.GetSetting("MySQL-DBName"));
             MySqlCommand cmd = new MySqlCommand(queryString, conn);
             cmd.ExecuteNonQuery();
             conn.Clone();
             conn.Dispose();
         }
     }
     catch (Exception e)
     {
         Server.Log(e);
     }
 }

 public static void executeQuery(string queryString, bool createDB = false)
 {
     int totalCount = 0;
     if (!Server.useMySQL)
         return;
     retry:  try
     {
         using (var conn = new MySqlConnection(connString))
         {
             conn.Open();
             if (!createDB)
             {
                 conn.ChangeDatabase(Server.MySQLDatabaseName);
             }
             using (MySqlCommand cmd = new MySqlCommand(queryString, conn))
             {
                 cmd.ExecuteNonQuery();
                 conn.Close();
             }
         }
     }
     catch (Exception e)
     {
         if (!createDB)
         {
             totalCount++;
             if (totalCount > 10)
             {
                 File.AppendAllText("MySQL_error.log", DateTime.Now + " " + queryString + "\r\n");
                 Server.ErrorLog(e);
             }
             else
             {
                 goto retry;
             }
         }
         else
         {
             throw e;
         }
     }
 }

 public static void executeQuery(string queryString, bool createDB = false)
 {
     int totalCount = 0;
     retry: try
     {
         using (var conn = new MySqlConnection(connString))
         {
             conn.Open();
             if (!createDB)
             {
                 conn.ChangeDatabase(Program.Config.DatabaseName);
             }
             MySqlCommand cmd = new MySqlCommand(queryString, conn);
             cmd.ExecuteNonQuery();
             conn.Close();
         }
     }
     catch (Exception e)
     {
         if (!createDB)
         {
             totalCount++;
             if (totalCount > 10)
             {
                 File.WriteAllLines("LastMySQLError.txt", new string[] {
                     "Query string: " + queryString,
                     "Error Log: " + e.ToString()
                 });
                 Console.WriteLine(e.ToString());
             }
             else
             {
                 goto retry;
             }
         }
         else
         {
             throw e;
         }
     }
 }

        public static DataTable fillData(string queryString, bool skipError = false)
        {
            DataTable toReturn = new DataTable("toReturn");
            if (!Server.useMySQL) return toReturn;

            int totalCount = 0;
            retry:  try
            {
                using (var conn = new MySqlConnection(connString))
                {
                    conn.Open();
                    conn.ChangeDatabase(Server.MySQLDatabaseName);
                    using (MySqlDataAdapter da = new MySqlDataAdapter(queryString, conn))
                    {
                        da.Fill(toReturn);
                    }
                    conn.Close();
                }
            }
            catch (Exception e)
            {
                totalCount++;
                if (totalCount > 10)
                {
                    if (!skipError)
                    {
                        File.WriteAllText("MySQL_error.log", queryString);
                        Server.ErrorLog(e);
                    }
                }
                else
                    goto retry;
            }

            return toReturn;
        }

        MySqlConnection GetMySqlConnection(string server, string dbname, string pwd, string uid, bool sspi, string port)
        {
            MySqlConnection dbcon = null;
            try
            {
                dbcon = new MySqlConnection(getConnectionString(server, dbname, pwd, uid, sspi, port));
                dbcon.Open();
                
                if (!string.IsNullOrEmpty(dbname))
                {
                    using (MySqlCommand cmd = dbcon.CreateCommand())
                    {
                        object o;

                        //cmd.CommandText = string.Format("use {0}", dbname);
                        //object o = cmd.ExecuteScalar();
                        dbcon.ChangeDatabase(dbname);

                        cmd.CommandText = "select database()";
                        o = cmd.ExecuteScalar();
                        if (string.IsNullOrEmpty(o.ToString()) || o.ToString()!=dbname)
                        {
                            dbcon.Close();
                            dbcon = null;
                        }
                        else
                        {
                            long minpksize = 10 * 1024 * 1024;
                            long pksize = 10 * 1024 * 1024;
                            cmd.CommandText = "SHOW VARIABLES like 'max_allowed_packet%'";
                            using (MySqlDataReader dr = cmd.ExecuteReader())
                            {
                                if (dr.Read())
                                {
                                    pksize = dr.GetInt64(1);
                                }
                            }

                            if (pksize < minpksize)
                            {
                                cmd.CommandText = string.Format("set global max_allowed_packet = {0}", minpksize);
                                cmd.ExecuteNonQuery();

                                dbcon.Close();
                                dbcon = null;

                                System.Threading.Thread.Sleep(2000);

                                dbcon = new MySqlConnection(getConnectionString(server, dbname, pwd, uid, sspi, port));
                                dbcon.Open();
                                dbcon.ChangeDatabase(dbname);

                                using (MySqlCommand cmd2 = dbcon.CreateCommand())
                                {
                                    cmd2.CommandText = "SHOW VARIABLES like 'max_allowed_packet%'";
                                    using (MySqlDataReader dr = cmd2.ExecuteReader())
                                    {
                                        if (dr.Read())
                                        {
                                            pksize = dr.GetInt64(1);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            catch
            {
                dbcon = null;
            }
            return dbcon;
        }

        private void create(String project)
        {
            String mysqlDB = "mysql";
            String connectionString = String.Format("server={0}; port={1}; user id={2}; password={3}; database={4}; pooling=false", server, port, username, password, mysqlDB);
            DataSet dataset = new DataSet(database);
            DataTable table;
            MySqlCommand command;
            MySqlDataReader datareader;

            try
            {
                //Console.WriteLine("Opening connection to the '{0}' database.", mysqlDB);
                connection = new MySqlConnection(connectionString);
                connection.Open();

                //Console.WriteLine("Deleting the '{0}' database!", database);
                String dropDBQuery = String.Format("DROP DATABASE IF EXISTS {0}", database);
                command = new MySqlCommand(dropDBQuery, connection);
                command.ExecuteNonQuery();

                //Console.WriteLine("Creating the '{0}' database.", database);
                String createDBQuery = String.Format("CREATE DATABASE IF NOT EXISTS {0}", database);
                command = new MySqlCommand(createDBQuery, connection);
                command.ExecuteNonQuery();

                //Console.WriteLine("Switching the connection to the '{0}' database.", database);
                connection.ChangeDatabase(database);

                // Create Tables and schema file.
                String createTableQuery = String.Empty;
                String tableName = String.Empty;
                String sql = String.Empty;

                // ComponentTable
                tableName = "ComponentTable";
                //Console.WriteLine("Creating the '{0}' table.", tableName);
                createTableQuery = String.Format("CREATE TABLE {0} (id INT NOT NULL AUTO_INCREMENT, type VARCHAR(80), name VARCHAR(80), description VARCHAR(255), etype VARCHAR(80), PRIMARY KEY (id)) ENGINE = MYISAM", tableName);
                command = new MySqlCommand(createTableQuery, connection);
                command.ExecuteNonQuery();
                // For schema file.
                sql = String.Format("SELECT * FROM {0}", tableName);
                command = new MySqlCommand(sql, connection);
                datareader = command.ExecuteReader();
                table = new DataTable(tableName);
                table.Load(datareader);
                dataset.Tables.Add(table);
                datareader.Close();

                // ParameterTable
                tableName = "ParameterTable";
                //Console.WriteLine("Creating the '{0}' table.", tableName);
                createTableQuery = String.Format("CREATE TABLE {0} (id INT NOT NULL AUTO_INCREMENT, parentId INT NOT NULL, parentType VARCHAR(80), name VARCHAR(80), value VARCHAR(512), description VARCHAR(256), PRIMARY KEY (id), UNIQUE (parentId, parentType, name)) ENGINE = MYISAM", tableName);
                command = new MySqlCommand(createTableQuery, connection);
                command.ExecuteNonQuery();
                // For schema file.
                sql = String.Format("SELECT * FROM {0}", tableName);
                command = new MySqlCommand(sql, connection);
                datareader = command.ExecuteReader();
                table = new DataTable(tableName);
                table.Load(datareader);
                dataset.Tables.Add(table);
                datareader.Close();

                // LinkTable
                tableName = "LinkTable";
                //Console.WriteLine("Creating the '{0}' table.", tableName);
                createTableQuery = String.Format("CREATE TABLE {0} (id INT NOT NULL AUTO_INCREMENT, fromComponentId INT, toComponentId INT, type VARCHAR(80), description VARCHAR(255), PRIMARY KEY (id)) ENGINE = MYISAM", tableName);
                command = new MySqlCommand(createTableQuery, connection);
                command.ExecuteNonQuery();
                // For schema file.
                sql = String.Format("SELECT * FROM {0}", tableName);
                command = new MySqlCommand(sql, connection);
                datareader = command.ExecuteReader();
                table = new DataTable(tableName);
                table.Load(datareader);
                dataset.Tables.Add(table);
                datareader.Close();

                //RegistryKey rk = Registry.CurrentUser.CreateSubKey(@"Software\Aptima\MOST");
                //String path = System.Convert.ToString(rk.GetValue("ConfigPath", ""));
                //if (!path.Equals(String.Empty))
                // {
                //    dataset.WriteXmlSchema(path + @"\" + "db.xsd");
                //}               
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error connecting to the server: " + ex.Message + '\n');
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message);
            }
            finally
            {
                //Console.WriteLine(String.Format("Closing connection to the '{0}' database.", DATABASE_NAME));
                connection.Close();
                //Console.WriteLine();
            }
        }

        /// <summary>
        /// Connect to the server
        /// </summary>
        /// <param name="server">IP or domain</param>
        /// <param name="userName">Username</param>
        /// <param name="password">Password</param>
        /// <param name="database">Optional initial database</param>
        /// <returns></returns>
        public bool Connect(string server, string userName, string password, string database = "")
        {
            try
            {
                var connection = new MySqlConnection($"server={server};uid={userName}");
                if (password != "")
                {
                    connection.ConnectionString += $";password={password}";
                }
                Debug.WriteLine($"Connecting to {server} as {userName}");
                connection.Open();

                CurrentConnection = connection;
            }
            catch (Exception exception)
            {
                Debug.WriteLine("Unable to connect...");
                var myException = exception as MySqlException;
                if (myException != null)
                    HandleException(myException);
                else
                    MessageBox.Show("Unknown exception:" + exception.Message);
                return false;
            }

            // ensure that the database exists
            if (database != "")
            {
                var databaseExists = new MySqlCommand($"SHOW DATABASES LIKE '{database}'") {Connection = CurrentConnection};

                if (databaseExists.ExecuteScalar() != null)
                {
                    Debug.WriteLine($"Database {database} already exists");
                }
                else
                {
                    Debug.WriteLine($"Trying to create database {database}");

                    try
                    {
                        ExecuteCommand(Properties.Resources.create_my_sound_lib);
                    }
                    catch (DatabaseAccessDeniedExcpetion)
                    {
                        MessageBox.Show($"{userName} is not allowed to create databases");
                        return false;
                    }
                    catch (Exception e) {
                        MessageBox.Show("Unable to create database: " + e.Message);
                    }

                    Debug.WriteLine($"Successfully created {database}");
                    MessageBox.Show("Created initial database");
                }
                CurrentConnection.ChangeDatabase(database);
            }

            CurrentConnection.StateChange += CurrentConnectionOnStateChange;

            return CurrentConnection.State == ConnectionState.Open;
        }

    public void ReclaimBrokenConnection()
    {
      // now create a new connection string only allowing 1 connection in the pool
      string connStr = GetPoolingConnectionString() + ";connect timeout=2;max pool size=1";

      // now use up that connection
      MySqlConnection c = new MySqlConnection(connStr);
      c.Open();

      // now attempting to open a connection should fail
      try
      {
        MySqlConnection c2 = new MySqlConnection(connStr);
        c2.Open();
        Assert.Fail("Open after using up pool should fail");
      }
      catch (Exception) { }

      // we now kill the first connection to simulate a server stoppage
      base.KillConnection(c);

      // now we do something on the first connection
      try
      {
        c.ChangeDatabase("mysql");
        Assert.Fail("This change database should not work");
      }
      catch (Exception) { }

      // Opening a connection now should work
      MySqlConnection connection = new MySqlConnection(connStr);
      connection.Open();
      KillConnection(connection);
      connection.Close();
    }