public async Task ExecuteScriptWithProceduresAsync()
{
if (_fixture.Version < new Version(5, 0))
{
return;
}
string spTpl = @"CREATE PROCEDURE `SEScriptWithProceduresAsyncSpTest{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 = _fixture.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 'SEScriptWithProceduresAsyncSpTest%'", _fixture.database0), _fixture.conn);
Assert.Equal(10, Convert.ToInt32(cmd.ExecuteScalar()));
}
public async Task ExecuteScriptWithInsertsAsync()
{
_fixture.execSQL("CREATE TABLE SEScriptWithInsertsAsyncTest (id int, name varchar(50))");
string queryTpl = @"INSERT INTO SEScriptWithInsertsAsyncTest (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 = _fixture.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 SEScriptWithInsertsAsyncTest", _fixture.conn);
Assert.Equal(10, Convert.ToInt32(cmd.ExecuteScalar()));
}
public void ExecuteScriptWithProceduresAsync()
{
if (st.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 = 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 void ExecSql(string query)
{
if (!this.CheckConnetion())
{
Log.WriteError("Can't execute a query:{0}.\nNot connected to the server", query);
return;
}
lock (lockMysql) {
var script = new MySqlScript(this.conn, query);
this.Try(() => script.ExecuteAsync().Wait());
}
}
public void ExecuteScriptWithInsertsAsync()
{
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 = st.conn;
script.StatementExecuted += new MySqlStatementExecutedEventHandler(ExecuteScriptWithInserts_StatementExecuted);
int count = script.ExecuteAsync().Result;
Assert.Equal(10, count);
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM Test", st.conn);
Assert.Equal(10, Convert.ToInt32(cmd.ExecuteScalar()));
}
public async Task ExecuteScriptWithInsertsAsync()
{
st.execSQL("CREATE TABLE test (id int, name varchar(50))");
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 = st.conn;
script.StatementExecuted += new MySqlStatementExecutedEventHandler(ExecuteScriptWithInserts_StatementExecuted);
int count = await script.ExecuteAsync();
Assert.Equal(10, count);
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM Test", st.conn);
Assert.Equal(10, Convert.ToInt32(cmd.ExecuteScalar()));
}
private static void ExecuteScript(string file)
{
MySqlConnection connection = new MySqlConnection(connectionString);
string fileName = "";
try
{
connection.Open();
Stopwatch timer = new Stopwatch();
timer.Start();
FileInfo dataFile = new FileInfo(file);
fileName = dataFile.Name;
string messageExecuteFile = "Execute file " + fileName;
//int cursorTop = Console.CursorTop;
//Console.SetCursorPosition(0, cursorTop + 1);
Console.WriteLine(messageExecuteFile);
string text = dataFile.OpenText().ReadToEnd();
if (!Regex.IsMatch(text, @"\b(?i)USE\b"))
{
Console.WriteLine("Error when execute : " + fileName);
Console.WriteLine("No database selected! ");
}
string cleanText = Regex.Replace(text, @"(DEFINER=)(\S+)", "");
MySqlScript script = new MySqlScript(connection, cleanText);
//script.StatementExecuted += new MySqlStatementExecutedEventHandler(script_StatementExecuted);
var execute = script.ExecuteAsync();
timer.Stop();
if (execute.Exception != null)
{
Console.WriteLine("Error when execute : " + fileName);
Console.WriteLine(execute.Exception.ToString());
}
else
{
double spendTime = Convert.ToDouble(timer.ElapsedMilliseconds) / 1000;
string spendUnit = "seconds";
if (spendTime >= 60)
{
spendTime = spendTime / 60;
spendUnit = "minutes";
}
Console.WriteLine("Success execute file " + fileName + " on " + spendTime.ToString() + " " + spendUnit);
//Console.CursorLeft = 2;
//Console.WriteLine(messageExecuteFile + " [Done] Elapsed Time " + spendTime.ToString() + " " + spendUnit);
}
script.Query = "USE information_schema;";
script.Execute();
}
catch (Exception e)
{
Console.WriteLine("Error when execute : " + fileName);
Console.WriteLine(e.ToString());
}
finally
{
connection.Close();
}
}