C# MySqlScript.Execute方法代码示例

    protected void LoadSchema(int version)
    {
      if (version < 1) return;

      MySQLMembershipProvider provider = new MySQLMembershipProvider();

      ResourceManager r = new ResourceManager("MySql.Web.Properties.Resources", typeof(MySQLMembershipProvider).Assembly);
      string schema = r.GetString(String.Format("schema{0}", version));
      MySqlScript script = new MySqlScript(conn);
      script.Query = schema;

      try
      {
        script.Execute();
      }
      catch (MySqlException ex)
      {
        if (ex.Number == 1050 && version == 7)
        {
          // Schema7 performs several renames of tables to their lowercase representation. 
          // If the current server OS does not support renaming to lowercase, then let's just continue.
          script.Query = "UPDATE my_aspnet_schemaversion SET version=7";
          script.Execute();
        }
      }
    }

        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 override void Setup()
        {
            base.Setup();

              // Replace existing listeners with listener for testing.
              Trace.Listeners.Clear();
              Trace.Listeners.Add(this.asertFailListener);

              ResourceManager r = new ResourceManager("MySql.Data.Entity.Tests.Properties.Resources", typeof(BaseEdmTest).Assembly);
              string schema = r.GetString("schema");
              MySqlScript script = new MySqlScript(conn);
              script.Query = schema;
              script.Execute();

              // now create our procs
              schema = r.GetString("procs");
              script = new MySqlScript(conn);
              script.Delimiter = "$$";
              script.Query = schema;
              script.Execute();

              //ModelFirstModel1
              schema = r.GetString("ModelFirstModel1");
              script = new MySqlScript(conn);
              script.Query = schema;
              script.Execute();

              MySqlCommand cmd = new MySqlCommand("DROP DATABASE IF EXISTS `modeldb`", rootConn);
              cmd.ExecuteNonQuery();
        }

 private void Button4_Click(object sender, EventArgs e)
 {
     if (
         MessageBox.Show(this,
             @"การสร้างฐานข้อมูลใหม่จะลบ ฐานข้อมูลเก่าออกทั้งหมด คุณต้องการจะดำเนินการต่อหรือไม่?",
             @"ลบฐานข้อมูล", MessageBoxButtons.YesNo) == DialogResult.Yes)
     {
         try
         {
             if (_conn.State != ConnectionState.Closed)
             {
                 _conn.Close();
             }
             _conn.Open();
             var sc = new MySqlScript(_conn, Settings.Default.sql);
             if (sc.Execute() >= 1)
             {
                 MessageBox.Show(@"สร้างฐานข้อมูลสำเร็จ");
             }
             else
             {
                 MessageBox.Show(@"สร้างฐานข้อมูลล้มเหลว");
             }
         }
         catch (MySqlException ex)
         {
             MessageBox.Show(ex.Message);
         }
     }
 }

        private void button1_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("此操作会清空软件中的所有数据,如果有数据,请做好数据备份(导出)!您是否继续?", "警告", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == System.Windows.Forms.DialogResult.Yes)
            {
                try
                {
                    using (MySqlConnection conn = DBA.MySqlDbAccess.GetMySqlConnection())
                    {

                        conn.Open();

                        string sql = File.ReadAllText(System.AppDomain.CurrentDomain.BaseDirectory + "xy.dll", Encoding.GetEncoding("utf-8"));
                        MySqlScript script = new MySqlScript(conn);

                        script.Query = sql;
                        int count = script.Execute();
                    }
                    MessageBox.Show("数据库已更新至最新版本！", "提示");
                    log.Info("执行数据库重置:" + DateTime.Now.ToString());
                    showversion();
                }
                catch (Exception ex)
                {
                    log.Error(ex.ToString());
                }
            }
        }

		// http://bugs.mysql.com/bug.php?id=46429
		public override void run_sql(string sql_to_run)
		{
			if (string.IsNullOrEmpty(sql_to_run)) return;

			// TODO Investigate how pass CommandTimeout into commands which will be during MySqlScript execution.
			var connection = server_connection.underlying_type().downcast_to<MySqlConnection>();
			var script = new MySqlScript(connection, sql_to_run);
			script.Execute();
		}

 public static void RecreateMysqlDatabase()
 {
     if (!Connection.State.ToString().Equals("Open"))
     {
         Connection.Open();
     }
     var runScript = new MySqlScript(Connection, Properties.Settings.Default.WipeDatabase);
     runScript.Execute();
     Connection.Close();
 }

        public override int ExecuteScript(DbConnection conn, string sql, string delimiter)
        {
            MySqlScript script = new MySqlScript((MySqlConnection) conn, sql);

            if (!string.IsNullOrEmpty(delimiter))
            {
                script.Delimiter = delimiter;
            }

            return script.Execute();
        }

        private bool  CreateDb()
        {
            bool bRs = false;

            string connStr = string.Format("server={0};user={1};database=;password={2};charset=utf8;Allow User Variables=True", txbDbName.Text.Trim(), txbUs.Text.Trim(), txbPw.Text.Trim());
            MySqlConnection conn = new MySqlConnection(connStr);

            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();

                string sql = File.ReadAllText("createDb.sql");
                MySqlScript script = new MySqlScript(conn);

                script.Query = sql;
                //script.Delimiter = "??";
                int count = script.Execute();
                
                Console.WriteLine("Executed " + count + " statement(s)");
                //script.Delimiter = ";";
                conn.Close();
                Console.WriteLine("Delimiter: " + script.Delimiter);
                Console.WriteLine("Query: " + script.Query);


                /////////////
                connStr = string.Format("server={0};user={1};database=ztst;password={2};charset=utf8;Allow User Variables=True", txbDbName.Text.Trim(), txbUs.Text.Trim(), txbPw.Text.Trim());
                conn = new MySqlConnection(connStr);
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();

                sql = File.ReadAllText("ztst.sql");
                script = new MySqlScript(conn);
                script.Query = sql;
                //script.Delimiter = "??";
                count = script.Execute();
                Console.WriteLine("Executed " + count + " statement(s)");
                //script.Delimiter = ";";
                conn.Close();
            }
            catch (Exception ex)
            {
                bRs = false;
                Console.WriteLine(ex.ToString());
            }

            //conn.Close();
            Console.WriteLine("Done.");
            return bRs;
        }

        public static bool CheckAndUpData()
        {
            DataTable dt = DBA.MySqlDbAccess.GetDataTable(CommandType.Text, "select * from cfg_dbversion");
            string[] files = Directory.GetFiles(System.AppDomain.CurrentDomain.BaseDirectory + "MySqlScript");
            List<int> versionhis = new List<int>();
            foreach (var file in files)
            {
                try
                {
                    versionhis.Add(Convert.ToInt32(Path.GetFileNameWithoutExtension(file)));
                }
                catch (Exception)
                {
                    continue;
                }
            }
            int newserion = versionhis.Max();
            int exeversion = Convert.ToInt32(dt.Rows[0]["dbversion"].ToString());
            string updatatime = dt.Rows[0]["updatetime"].ToString();
            if (newserion == exeversion)
            {
                return true;
            }
            else
            {
                using (MySqlConnection conn = DBA.MySqlDbAccess.GetMySqlConnection())
                {
                    conn.Open();
                    for (int i = exeversion + 1; i <= newserion; i++)
                    {
                        string sql = File.ReadAllText(System.AppDomain.CurrentDomain.BaseDirectory + "MySqlScript\\" + i, Encoding.GetEncoding("utf-8"));
                        MySqlScript script = new MySqlScript(conn);
                        script.Query = sql;
                        int count = script.Execute();
                        DBA.MySqlDbAccess.ExecNoQuery(CommandType.Text, string.Format("update cfg_dbversion set dbversion={0},updatetime='{1}'", i, DateTime.Now));

                        //try
                        //{
                        //}
                        //catch (Exception ex)
                        //{
                        //    throw ex;
                        //}

                    }
                }
                return true;
            }
        }

 private void ExecuteScript(string fileName)
 {
     MySqlConnection connection = (MySqlConnection)DbConnectionFactory.CreateAndOpenConnection();
     try
     {
         string fullPath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\Resources\" + fileName + ".sql";
         MySqlScript script = new MySqlScript(connection, File.ReadAllText(fullPath));
         script.Delimiter = ";";
         script.Execute();
     }
     finally
     {
         DbConnectionFactory.CloseDisposeConnection(connection);
     }
 }

    public SetUp()
    {
      Initialize();
      LoadBaseConfiguration();

      using (MySqlConnection connection = new MySqlConnection(ConnectionStringRootMaster))
      {
        connection.Open();
        MySqlScript script = new MySqlScript(connection);

        // Sets users
        script.Query = Properties.Resources._01_Startup_root_script;
        script.Execute();

        // Sets database objects
        script.Query = string.Format(Properties.Resources._02_Startup_script, databaseName);
        script.Execute();
      }
    }

        protected override void ExecuteScript(DbConnection conn, string[] script)
        {
            if (!(conn is MySqlConnection))
            {
                base.ExecuteScript(conn, script);
                return;
            }

            MySqlScript scr = new MySqlScript((MySqlConnection) conn);
            {
                foreach (string sql in script)
                {
                    scr.Query = sql;
                    string sql1 = sql;
                    scr.Error += delegate { throw new Exception(sql1); };
                    scr.Execute();
                }
            }
        }

        public void ExecuteScripts()
        {
            if (string.IsNullOrEmpty(ConnectionString))
                throw new ArgumentNullException("Connection string shouldn´t be null.");

            if (ScriptsFiles == null)
                throw new ArgumentNullException("You must add a script to execute.");

            Assembly thisAssembly = Assembly.GetCallingAssembly();

            MySqlConnection connMySql = null;
            try {
                connMySql = new MySqlConnection(ConnectionString);
                foreach (string file in ScriptsFiles) {
                    string result = string.Empty;
                    using (Stream stream = thisAssembly.GetManifestResourceStream(file)) {
                        using (StreamReader reader = new StreamReader(stream)) {
                            result = reader.ReadToEnd();
                        }
                    }

                    MySqlScript script = new MySqlScript(connMySql, result);
                    script.Delimiter = "$$";
                    script.Execute();
                }

            } catch (MySqlException ex) {
                throw new Exception("Problems with the database sorry...", ex);
            } catch (Exception ex) {
                throw;
            } finally {
                connMySql.Close();
            }

            //sql server
            //FileInfo file = new FileInfo("C:\\myscript.sql");
            //string script = file.OpenText().ReadToEnd();
            //SqlConnection conn = new SqlConnection(sqlConnectionString);
            //Server server = new Server(new ServerConnection(conn));
            //server.ConnectionContext.ExecuteNonQuery(script);
        }

 private void initiateDB()
 {
     try
     {
         mysql_connection = new MySqlConnection(connectionString);
         mysql_connection.Open();
         //use the database created and then execute sql scripts to create table and populate with some sample data
         string sqlStatement = "USE " + mysqldbname + ";";
         MySqlCommand cmd = new MySqlCommand(sqlStatement, mysql_connection);
         cmd.ExecuteNonQuery();
         MySqlScript script1 = new MySqlScript(mysql_connection, File.ReadAllText("sql-scripts/create-schema.sql"));
         script1.Delimiter = "$$";
         script1.Execute();
         MySqlScript script2 = new MySqlScript(mysql_connection, File.ReadAllText("sql-scripts/insert-towns.sql"));
         script2.Delimiter = "$$";
         script2.Execute();
     }
     catch (MySqlException ex)
     {
     }
 }