private void ExecCommandWithTransaction(string sSql)
{
if (sSql.Length > 0)
{
//MySql 不支持在存贮过程外使用IF语句，创建一个临时的存贮过程
string sProcName = "temp_Proc_" + Guid.NewGuid().ToString().Replace('-', '_');
sSql = "DELIMITER $$ \n"
+ "DROP PROCEDURE IF EXISTS " + sProcName + " $$ \n"
+ "CREATE PROCEDURE " + sProcName + " () \n"
+ "BEGIN \n\n"
+ sSql
+ "\n END $$\n"
+ "DELIMITER ;\n"
+ "CALL " + sProcName + "();\n"
+ "DROP PROCEDURE IF EXISTS " + sProcName + "; \n";
Debug.WriteLine(sSql);
DbTransaction objTrans = BeginTransaction();
MySqlScript script = new MySqlScript(objTrans.Connection as MySqlConnection, sSql);
script.Execute();
//ExecuteNonQuery(objTrans, sSql);
objTrans.Commit();//使用事务提交，保证数据完整性
}
}
public void ExecuteScriptWithProcedures()
{
if (Version < new Version(5, 0))
{
return;
}
statementCount = 0;
string scriptText = String.Empty;
for (int i = 0; i < 10; i++)
{
scriptText += String.Format(statementTemplate1, i, "$$");
}
MySqlScript script = new MySqlScript(scriptText);
script.StatementExecuted += new MySqlStatementExecutedEventHandler(ExecuteScriptWithProcedures_QueryExecuted);
script.Connection = conn;
script.Delimiter = "$$";
int count = script.Execute();
Assert.AreEqual(10, count);
MySqlCommand cmd = new MySqlCommand(
String.Format(@"SELECT COUNT(*) FROM information_schema.routines WHERE
routine_schema = '{0}' AND routine_name LIKE 'spTest%'",
database0), conn);
Assert.AreEqual(10, cmd.ExecuteScalar());
}
public static void Initialize(bool drop = false)
{
if (!Initialized)
{
if (Connection.State != System.Data.ConnectionState.Open)
{
Connection.Open();
}
if (drop)
{
new MySqlCommand($"DROP DATABASE IF EXISTS {database}", Connection).ExecuteNonQuery();
}
// Set up the database
new MySqlCommand($"CREATE DATABASE IF NOT EXISTS {database}; USE bank_system;", Connection).ExecuteNonQuery();
if (drop)
{
MySqlScript script = new MySqlScript(Connection, File.ReadAllText(databaseScript));
script.Execute();
// call the create_Defaults procedure
new MySqlCommand("call create_defaults()", Connection).ExecuteNonQuery();
}
Initialized = true;
}
}
private void MultipleThreadsWorkerScript(object ev)
{
MySqlScript script = new MySqlScript();
script.Query = "show variables like '%ssl%'";
(ev as ManualResetEvent).WaitOne();
string strConn = GetConnectionString(true) + ";Pooling=true;";
MySqlConnection c = new MySqlConnection(strConn);
int result;
script.Connection = c;
try
{
for (int i = 0; i < 10; i++)
{
c.Open();
result = script.Execute();
c.Close();
}
}
catch (InvalidOperationException ioe)
{
//Assert.AreEqual("Collection was modified; enumeration operation may not execute.", ioe.Message );
//Assert.Fail();
lastException = ioe;
}
catch (Exception e)
{
lastException = e;
}
}
public SetUpEntityTests()
: base()
{
// 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(SetUpEntityTests).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 ImportButton_Click(object sender, RoutedEventArgs e)
{
MessageBox.Show("A sikeres importálás után újra fog indulni az alkalmazás!");
try
{
Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
dlg.DefaultExt = ".sql";
dlg.Filter = "SQL Files (*.sql)|*.sql";
Nullable <bool> result = dlg.ShowDialog();
if (result == true)
{
// Open document
string file = dlg.FileName;
using (conn = new MySqlConnection(File.ReadAllText("config.txt")))
{
conn.Open();
var sc = new MySqlScript(conn, File.ReadAllText(file));
sc.Execute();
}
}
else
{
return;
}
}
catch (Exception ex)
{
MessageBox.Show("Sikertelen importálás: " + ex.ToString());
return;
}
MessageBox.Show("Sikeres importálás!");
MessageBox.Show("Az alkalmazás újraindul!");
System.Diagnostics.Process.Start(Application.ResourceAssembly.Location);
Application.Current.Shutdown();
}
//Asynchronous worker is used to read and execute sql script since the file is too large
private void sqlScript_DoWork(object sender, DoWorkEventArgs e)
{
string sqlConnectionString = "";
connectionStr = new MySqlConnectionStringBuilder();
connectionStr.UserID = username;
connectionStr.Password = password;
connectionStr.Server = "localhost";
sqlConnectionString = connectionStr.GetConnectionString(true);
using (MySqlConnection connDatabase = new MySqlConnection(sqlConnectionString))
{
try
{
connDatabase.Open();
string query = File.ReadAllText("vx.sql");
MySqlScript script = new MySqlScript(connDatabase, query);
script.Delimiter = ";";
script.Execute();
connDatabase.Close();
}
catch (Exception ex) {
MessageBox.Show("vx.sql File not found! Please include it in the application directory and run again.");
}
}
}
public static void CreateDatabase(string databaseName, bool deleteifExists = false, string script = null)
{
if (script != null)
{
if (deleteifExists)
{
script = "Drop database if exists [database0];" + script;
}
script = script.Replace("[database0]", databaseName);
//execute
using (var cnn = new MySqlConnection(rootConnectionString))
{
cnn.Open();
MySqlScript s = new MySqlScript(cnn, script);
s.Execute();
}
}
else
{
using (var cnn = new MySqlConnection(rootConnectionString))
{
cnn.Open();
var cmd = new MySqlCommand(string.Format("Drop database {0}; Create Database {0};", databaseName), cnn);
cmd.ExecuteNonQuery();
}
}
}
public SetUp()
{
MySqlConnection con = new MySqlConnection(TestUtils.CONNECTION_STRING_WITHOUT_DB);
con.Open();
Assembly executingAssembly = Assembly.GetExecutingAssembly();
Stream stream = executingAssembly.GetManifestResourceStream("MySql.Debugger.Tests.Properties.Setup.sql");
StreamReader sr = new StreamReader(stream);
string sql = sr.ReadToEnd();
sr.Close();
MySqlCommand cmd = new MySqlCommand("drop database if exists test", con);
MySqlScript s = new MySqlScript(con, sql);
try
{
cmd.ExecuteNonQuery();
cmd.CommandText = "create database test;";
cmd.ExecuteNonQuery();
s.Execute();
}
finally
{
con.Close();
}
}
protected override void ExecuteCreateProcedureScript(string script)
{
var withDelim = new MySqlScript(this.Connection, script);
withDelim.Delimiter = "//";
withDelim.Execute();
}
public void ExecuteScriptWithProceduresAsync()
{
if (st.Version < new Version(5, 0))
{
return;
}
statementCount = 0;
string scriptText = String.Empty;
for (int i = 0; i < 10; i++)
{
st.execSQL(String.Format("DROP PROCEDURE IF EXISTS spTest{0}", i));
scriptText += String.Format(statementTemplate1, i, "$$");
}
MySqlScript script = new MySqlScript(scriptText);
script.StatementExecuted += new MySqlStatementExecutedEventHandler(ExecuteScriptWithProcedures_QueryExecuted);
script.Connection = st.conn;
script.Delimiter = "$$";
int count = script.ExecuteAsync().Result;
Assert.Equal(10, count);
MySqlCommand cmd = new MySqlCommand(
String.Format(@"SELECT COUNT(*) FROM information_schema.routines WHERE
routine_schema = '{0}' AND routine_name LIKE 'spTest%'",
st.database0), st.conn);
Assert.Equal(10, Convert.ToInt32(cmd.ExecuteScalar()));
}
public static void ExecuteScriptFromFile(string filename)
{
StreamReader sr = new StreamReader(filename);
MySqlConnection conn = new MySqlConnection();
conn.ConnectionString = "Database=addr;Data Source=192.168.0.90;Port=3306;User Id=root;Password=Ijhrby22";
conn.Open();
MySqlTransaction tran = conn.BeginTransaction();
string curr_script = "";
while (!sr.EndOfStream)
{
string curr_str = sr.ReadLine();
if (!curr_str.Contains(";"))
{
curr_script += curr_str + Environment.NewLine;
}
else
{
curr_script += curr_str;
MySqlScript script = new MySqlScript(conn, curr_script);
script.Execute();
curr_script = "";
}
}
}
//Сделал максимально просто. По хорошему объединить с настоящим обновлением.
//Но это усложнит и так не простой код, может здесь вручную выполнять обновления даже лучше.
void RunOneUpdate(MySqlConnection connection, UpdateHop updateScript)
{
if (updateScript.ExecuteBefore != null)
{
updateScript.ExecuteBefore(connection);
}
string sql;
using (Stream stream = updateScript.Assembly.GetManifestResourceStream(updateScript.Resource)) {
if (stream == null)
{
throw new InvalidOperationException(String.Format("Ресурс {0} указанный в обновлениях не найден.", updateScript.Resource));
}
StreamReader reader = new StreamReader(stream);
sql = reader.ReadToEnd();
}
var script = new MySqlScript(connection, sql);
//script.StatementExecuted += Script_StatementExecuted;
script.Execute();
var command = connection.CreateCommand();
command.CommandText = "UPDATE base_parameters SET str_value = @version WHERE name = 'version'";
command.Parameters.AddWithValue("version", updateScript.Destination.VersionToShortString());
command.ExecuteNonQuery();
}
public void Restore(BitnamiRedmineStack stack, DatabaseConfiguration configuration, string path)
{
if (configuration == null)
{
throw new ArgumentNullException(nameof(configuration));
}
try
{
if (!File.Exists(path))
{
throw new FileNotFoundException("インポートする sql ファイルが存在しません。", path);
}
var connectionString = CreateConnectionString(configuration);
this._LogService.Info("Create MySqlConnection");
using (var con = new MySqlConnection(connectionString))
{
var text = File.ReadAllText(path);
var script = new MySqlScript(con, text);
this._LogService.Info("Execute MySqlScript");
var result = script.Execute();
this._LogService.Info($"MySqlScript,Execute returns {result}");
}
}
catch (Exception e)
{
this._LogService.Error(e.Message);
throw;
}
}
/// <summary>
/// Drops and creates the database, as to start off with a clean sheet
/// </summary>
private new void RefreshDatabaseSchema()
{
var watch = System.Diagnostics.Stopwatch.StartNew();
MySqlScript script = new MySqlScript(connector.Connection, File.ReadAllText(Properties.Resources.mysql_generate_schema_ct));
try
{
Logger.Debug("Executing refresh schema script..");
script.Execute();
}
catch (MySqlException e)
{
Logger.Error("An error ocurred executing the refresh schema script: " + e);
}
finally
{
watch.Stop();
}
Logger.Info("Refreshed schema in " + watch.ElapsedMilliseconds + "ms " + "(" + (watch.ElapsedMilliseconds / 1000) + "s) ");
// set autocommit to off
string statement = "START TRANSACTION;";
MySqlCommand com = new MySqlCommand(statement, connector.Connection);
com.ExecuteNonQuery();
}
private void button_tesztadatok_Click(object sender, EventArgs e)
{
try
{
string file = @"..\..\..\latvanyossagokdb(8).sql";
using (StreamReader sr = new StreamReader(file, Encoding.UTF8))
{
Program.conn.Open();
MySqlScript script = new MySqlScript(Program.conn, sr.ReadToEnd());
script.Execute();
}
}
catch (IOException ex)
{
MessageBox.Show(ex.Message);
}
catch (MySqlException myex)
{
MessageBox.Show(myex.Message);
}
finally
{
Program.conn.Close();
}
//VaroslistaUpdate();
}
public void NonCompliant_MySqlData(MySqlConnection connection, MySqlTransaction transaction, string query, string param)
{
var command = new MySqlCommand($"SELECT * FROM mytable WHERE mycol={param}"); // Noncompliant
command = new MySqlCommand($"SELECT * FROM mytable WHERE mycol={param}", connection); // Noncompliant
command = new MySqlCommand($"SELECT * FROM mytable WHERE mycol={param}", connection, transaction); // Noncompliant
command.CommandText = string.Format("INSERT INTO Users (name) VALUES (\"{0}\")", param); // Noncompliant
var adapter = new MySqlDataAdapter($"SELECT * FROM mytable WHERE mycol=" + param, connection); // Noncompliant
MySqlHelper.ExecuteDataRow("connectionString", $"SELECT * FROM mytable WHERE mycol={param}"); // Noncompliant
MySqlHelper.ExecuteDataRowAsync("connectionString", $"SELECT * FROM mytable WHERE mycol={param}"); // Noncompliant
MySqlHelper.ExecuteDataRowAsync("connectionString", $"SELECT * FROM mytable WHERE mycol={param}", new System.Threading.CancellationToken()); // Noncompliant
MySqlHelper.ExecuteDataset("connectionString", $"SELECT * FROM mytable WHERE mycol={param}"); // Noncompliant
MySqlHelper.ExecuteDatasetAsync("connectionString", $"SELECT * FROM mytable WHERE mycol={param}"); // Noncompliant
MySqlHelper.ExecuteNonQuery("connectionString", $"SELECT * FROM mytable WHERE mycol={param}"); // Noncompliant
MySqlHelper.ExecuteNonQueryAsync(connection, $"SELECT * FROM mytable WHERE mycol={param}"); // Noncompliant
MySqlHelper.ExecuteReader(connection, $"SELECT * FROM mytable WHERE mycol={param}"); // Noncompliant
MySqlHelper.ExecuteReaderAsync(connection, $"SELECT * FROM mytable WHERE mycol={param}"); // Noncompliant
MySqlHelper.ExecuteScalar(connection, $"SELECT * FROM mytable WHERE mycol={param}"); // Noncompliant
MySqlHelper.ExecuteScalarAsync(connection, $"SELECT * FROM mytable WHERE mycol={param}"); // Noncompliant
MySqlHelper.UpdateDataSet("connectionString", $"SELECT * FROM mytable WHERE mycol={param}", new DataSet(), "tableName"); // Noncompliant
MySqlHelper.UpdateDataSetAsync("connectionString", $"SELECT * FROM mytable WHERE mycol={param}", new DataSet(), "tableName"); // Noncompliant
var script = new MySqlScript($"SELECT * FROM mytable WHERE mycol={param}"); // Noncompliant
script = new MySqlScript(connection, $"SELECT * FROM mytable WHERE mycol={param}"); // Noncompliant
}
public ResultMessage InData(DBHost host, DBInfo tmodel, SCHEMA source)
{
ResultMessage resultMessage = new ResultMessage();
this.SetProgress("获取目标数据结构");
SCHEMA sCHEMA = MySqlCore.GetSCHEMA(Data.Instance.InformationConnString(host), tmodel.DBName);
MySqlScript mySqlScript = new MySqlScript(source, sCHEMA);
TableMySQL tableMySQL = new TableMySQL(Data.Instance.DBConnString(host, tmodel.DBName));
this.SetProgress("生成需要更新的脚本，并更新");
try
{
foreach (string current in mySqlScript.MakeScript())
{
this.SetProgress(current);
tableMySQL.ExecuteNonQuery(current);
}
this.SetProgress("更新成功");
resultMessage.Result = true;
resultMessage.Message = "更新成功";
}
catch (Exception ex)
{
this.SetProgress("失败" + ex.Message);
resultMessage.Result = false;
resultMessage.Message = ex.Message;
}
return(resultMessage);
}
/// <summary>
/// Créer et configure la base de donnée <code>dbName</code>
/// </summary>
/// <param name="dbconn">Objet de connexion à la base de données</param>
/// <param name="dbName">Nom de la base de données à créer</param>
/// <returns></returns>
public static void SetupDB(MySqlConnection dbconn, string dbName)
{
// On essaie de créer la base de données
try
{
// Créer la base de données vide
MySqlCommand cmd = new MySqlCommand("CREATE DATABASE " + dbName, dbconn);
dbconn.Open();
cmd.ExecuteNonQuery();
// Définie la connexion sur la nouvelle base de données créée précédemment
dbconn.ChangeDatabase(dbName);
// Lecture du script en ressources
Stream propertiesFile = Assembly.GetExecutingAssembly().GetManifestResourceStream("ProjectManagerCoreLib.data.projectmanager.sql");
StreamReader reader = new StreamReader(propertiesFile);
string content = reader.ReadToEnd();
// Préparation et execution du script
MySqlScript script = new MySqlScript(dbconn, content);
script.Error += new MySqlScriptErrorEventHandler(script_Error);
script.ScriptCompleted += new EventHandler(script_ScriptCompleted);
script.StatementExecuted += new MySqlStatementExecutedEventHandler(script_StatementExecuted);
script.Execute();
dbconn.Close();
connectionString = string.Format("server={0};user={1};pwd={2};database={3}", dbSettings.GetDatabaseServer(), dbSettings.GetUser(), dbSettings.GetPassword(), dbSettings.GetDatabaseName());
dbConnection = new MySqlConnection(connectionString);
}
// On capture les éventuelle exceptions/erreurs
catch (Exception ex)
{
}
}
private void RefreshList()
{
int prodId = 0;
ProductListBox.Items.Clear();
MySqlCommand mysql_query = DbConnector.conn.CreateCommand();
mysql_query.CommandText = $"SELECT ID,Name,Quantity FROM Warehouse";
MySqlDataReader mysql_result;
mysql_result = mysql_query.ExecuteReader();
while (mysql_result.Read())
{
int tmpInt = int.Parse(mysql_result.GetString(2).ToString());
if (tmpInt != 0)
{
ProductListBox.Items.Add(mysql_result.GetString(0).ToString() + " " + " " + mysql_result.GetString(1).ToString() + " - " + " " + mysql_result.GetString(2).ToString());
}
else
{
prodId = int.Parse(mysql_result.GetString(0).ToString());
}
}
mysql_result.Close();
if (prodId != 0)
{
string query = $"DELETE FROM Warehouse WHERE ID = '{prodId}'";
MySqlScript script = new MySqlScript(DbConnector.conn, query);
int count = script.Execute();
}
}
internal protected void LoadSchema(int version)
{
if (version < 1)
{
return;
}
MySQLMembershipProvider provider = new MySQLMembershipProvider();
string schema = LoadResource($"MySql.Web.Properties.schema{version}.sql");
MySqlScript script = new MySqlScript(Connection);
script.Query = schema.ToString();
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();
}
}
}
/// <summary>
/// 手机魔域更新脚本专用
/// </summary>
/// <param name="sql"></param>
/// <returns>成功或者失败</returns>
public static bool intoSql(string sql)
{
var conn = new MySqlConnection(connstr);
try
{
conn.Open();
var script = new MySqlScript(conn)
{
Query = sql, Delimiter = "//"
};
script.Execute();
//script.Delimiter = "\r\n";
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return(false);
}
finally
{
conn.Close();
}
}
internal 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 static void CreateDatabase()
{
string fileLines = File.ReadAllText(Paths.SOLUTION_DIR + "/MapleServer2/Database/SQL/Database.sql");
MySqlScript script = new MySqlScript(new MySqlConnection($"SERVER={Server};PORT={Port};USER={User};PASSWORD={Password};"), fileLines.Replace("DATABASE_NAME", Database));
script.Execute();
}
public async Task ExecuteScriptWithProceduresAsync()
{
if (st.Version < new Version(5, 0))
{
return;
}
string spTpl = @"CREATE PROCEDURE `ScriptWithProceduresAsyncSpTest{0}`() NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN SELECT 1,2,3; END{1}";
statementCount = 0;
string scriptText = String.Empty;
for (int i = 0; i < 10; i++)
{
scriptText += String.Format(spTpl, i, "$$");
}
MySqlScript script = new MySqlScript(scriptText);
script.StatementExecuted += new MySqlStatementExecutedEventHandler(delegate(object sender, MySqlScriptEventArgs e)
{
string stmt = String.Format(spTpl, statementCount++, null);
Assert.Equal(stmt, e.StatementText);
});
script.Connection = st.conn;
script.Delimiter = "$$";
int count = await script.ExecuteAsync();
Assert.Equal(10, count);
MySqlCommand cmd = new MySqlCommand(String.Format(@"SELECT COUNT(*) FROM information_schema.routines WHERE routine_schema = '{0}' AND routine_name LIKE 'ScriptWithProceduresAsyncSpTest%'", st.database0), st.conn);
Assert.Equal(10, Convert.ToInt32(cmd.ExecuteScalar()));
}
public ImportResultSet executeScript()
{
ImportResultSet result = new ImportResultSet();
if (string.IsNullOrWhiteSpace(this.script))
{
result.wasSuccessful = false;
result.errorMessage = "Script not set";
return(result);
}
try
{
MySqlConnection con = new MySqlConnection(connectionString);
con.Open();
MySqlScript script = new MySqlScript(con, this.script);
script.Delimiter = config.scriptDelimeter;
script.StatementExecuted += scriptStatementExecuted;
script.Execute();
result.wasSuccessful = true;
}
catch (Exception ex)
{
result.wasSuccessful = false;
result.errorMessage = ex.Message;
}
return(result);
}
public async Task ExecuteScriptWithInsertsAsync()
{
st.execSQL("CREATE TABLE ScriptWithInsertsAsyncTest (id int, name varchar(50))");
string queryTpl = @"INSERT INTO ScriptWithInsertsAsyncTest (id, name) VALUES ({0}, 'a "" na;me'){1}";
statementCount = 0;
string scriptText = String.Empty;
for (int i = 0; i < 10; i++)
{
scriptText += String.Format(queryTpl, i, ";");
}
MySqlScript script = new MySqlScript(scriptText);
script.Connection = st.conn;
script.StatementExecuted += new MySqlStatementExecutedEventHandler(delegate(object sender, MySqlScriptEventArgs e)
{
string stmt = String.Format(queryTpl, statementCount++, null);
Assert.Equal(stmt, e.StatementText);
});
int count = await script.ExecuteAsync();
Assert.Equal(10, count);
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM ScriptWithInsertsAsyncTest", st.conn);
Assert.Equal(10, Convert.ToInt32(cmd.ExecuteScalar()));
}
public void ExecuteScriptNotContinueOnError()
{
statementCount = 0;
string scriptText = String.Empty;
for (int i = 0; i < 5; i++)
{
scriptText += String.Format(statementTemplate2, i, ";");
}
scriptText += "bogus statement;";
for (int i = 5; i < 10; i++)
{
scriptText += String.Format(statementTemplate2, i, ";");
}
MySqlScript script = new MySqlScript(scriptText);
script.Connection = conn;
script.Error += new MySqlScriptErrorEventHandler(ExecuteScript_NotContinueOnError);
int count = script.Execute();
Assert.AreEqual(5, count);
Assert.AreEqual(1, statementCount);
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM Test", conn);
Assert.AreEqual(5, cmd.ExecuteScalar());
}
private void Deployment(string path, PublishDeploymentType publishDeploymentType)
{
if (string.IsNullOrEmpty(path))
{
return;
}
InputManager.Instance.WriteInfo($">>>{publishDeploymentType.ToString()}");
InputManager.Instance.Write($"Read File : {path}");
var sqlCommand = File.ReadAllText(path);
if (string.IsNullOrEmpty(path))
{
throw new Exception($"File : { path } 내용이 없습니다.");
}
using (var connection = new MySqlConnection(_config.SqlConnect))
{
connection.Open();
var command = new MySqlScript(connection, sqlCommand);
command.Execute();
connection.Close();
}
}
public bool ExecuteScript(string script)
{
MySqlScript scriptPrimary = null;
MySqlScript scriptSecondary = null;
try
{
if (ConnectionPrimary != null)
{
scriptPrimary = new MySqlScript(ConnectionPrimary, script);
}
if (ConnectionSecondary != null)
{
scriptSecondary = new MySqlScript(ConnectionSecondary, script);
}
if ((scriptPrimary == null || scriptPrimary.Execute() > 0) &&
(scriptSecondary == null || scriptSecondary.Execute() > 0) &&
(scriptSecondary != null || scriptPrimary != null))
{
return(true);
}
}
catch { }
return(false);
}
public void EmptyLastLineWithScriptExecute()
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("DROP FUNCTION IF EXISTS `BlaBla`;");
sb.AppendLine("DELIMITER ;;");
MySqlScript script = new MySqlScript(conn, sb.ToString());
// InvalidOperationException : The CommandText property has not been properly initialized.
script.Execute();
}
public void ExecuteScriptWithInserts()
{
statementCount = 0;
string scriptText = String.Empty;
for (int i = 0; i < 10; i++)
{
scriptText += String.Format(statementTemplate2, i, ";");
}
MySqlScript script = new MySqlScript(scriptText);
script.Connection = conn;
script.StatementExecuted += new MySqlStatementExecutedEventHandler(ExecuteScriptWithInserts_StatementExecuted);
int count = script.Execute();
Assert.AreEqual(10, count);
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM Test", conn);
Assert.AreEqual(10, cmd.ExecuteScalar());
}
public void DelimiterInScriptV2()
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("DELIMITER MySuperDelimiter");
sql.AppendLine("CREATE PROCEDURE TestProcedure1()");
sql.AppendLine("BEGIN");
sql.AppendLine(" SELECT * FROM mysql.proc;");
sql.AppendLine("END MySuperDelimiter");
sql.AppendLine("CREATE PROCEDURE TestProcedure2()");
sql.AppendLine("BEGIN");
sql.AppendLine(" SELECT * FROM mysql.proc;");
sql.AppendLine("END mysuperdelimiter");
sql.AppendLine("DELIMITER ;");
MySqlScript script = new MySqlScript(conn, sql.ToString());
script.Execute();
}
public void ExecuteScriptWithProcedures()
{
if (version < new Version(5, 0)) return;
statementCount = 0;
string scriptText = String.Empty;
for (int i=0; i < 10; i++)
{
scriptText += String.Format(statementTemplate1, i, "$$");
}
MySqlScript script = new MySqlScript(scriptText);
script.StatementExecuted += new MySqlStatementExecutedEventHandler(ExecuteScriptWithProcedures_QueryExecuted);
script.Connection = conn;
script.Delimiter = "$$";
int count = script.Execute();
Assert.AreEqual(10, count);
MySqlCommand cmd = new MySqlCommand(
String.Format(@"SELECT COUNT(*) FROM information_schema.routines WHERE
routine_schema = '{0}' AND routine_name LIKE 'spTest%'",
database0), conn);
Assert.AreEqual(10, cmd.ExecuteScalar());
}
private void MultipleThreadsWorkerScript(object ev)
{
MySqlScript script = new MySqlScript();
script.Query = "show variables like '%ssl%'";
(ev as ManualResetEvent).WaitOne();
string strConn = GetConnectionString(true) + ";Pooling=true;";
MySqlConnection c = new MySqlConnection(strConn);
int result;
script.Connection = c;
try
{
for (int i = 0; i < 10; i++)
{
c.Open();
result = script.Execute();
c.Close();
}
}
catch (InvalidOperationException ioe)
{
//Assert.AreEqual("Collection was modified; enumeration operation may not execute.", ioe.Message );
//Assert.Fail();
lastException = ioe;
}
catch (Exception e)
{
lastException = e;
}
}
public void ExecuteScriptWithUserVariables()
{
string connStr = conn.ConnectionString.ToLowerInvariant();
connStr = connStr.Replace("allow user variables=true",
"allow user variables=false");
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
string scriptText = "SET @myvar = 1";
MySqlScript script = new MySqlScript(scriptText);
script.Connection = c;
int count = script.Execute();
Assert.AreEqual(1, count);
}
}
public void ExecuteScriptNotContinueOnError()
{
statementCount = 0;
string scriptText = String.Empty;
for (int i = 0; i < 5; i++)
scriptText += String.Format(statementTemplate2, i, ";");
scriptText += "bogus statement;";
for (int i = 5; i < 10; i++)
scriptText += String.Format(statementTemplate2, i, ";");
MySqlScript script = new MySqlScript(scriptText);
script.Connection = conn;
script.Error += new MySqlScriptErrorEventHandler(ExecuteScript_NotContinueOnError);
int count = script.Execute();
Assert.AreEqual(5, count);
Assert.AreEqual(1, statementCount);
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM Test", conn);
Assert.AreEqual(5, cmd.ExecuteScalar());
}
protected void ExecuteSQLAsRoot(string sql)
{
MySqlScript s = new MySqlScript(rootConn, sql);
s.Execute();
}
public void DelimiterCommandDoesNotThrow()
{
MySqlScript script = new MySqlScript(conn, "DELIMITER ;");
Assert.DoesNotThrow(delegate { script.Execute(); });
}
public void ScriptWithDelimiterStatements()
{
if (Version < new Version(5, 0)) return;
StringBuilder sql = new StringBuilder();
sql.AppendFormat("{0}DELIMITER $${0}", Environment.NewLine);
sql.AppendFormat(statementTemplate1, 1, "$$");
sql.AppendFormat("{0}DELIMITER //{0}", Environment.NewLine);
sql.AppendFormat(statementTemplate1, 2, "//");
MySqlScript s = new MySqlScript();
s.Query = sql.ToString();
s.Delimiter = "XX";
s.Connection = conn;
int count = s.Execute();
}
