MySQL Connector/NET Release Notes
This tutorial teaches you how to use the
MySqlScript class. This class enables you to
execute a series of statements. Depending on the circumstances, this
can be more convenient than using the
MySqlCommand approach.
Further details of the MySqlScript class can
be found in the reference documentation supplied with MySQL Connector/NET.
To run the example programs in this tutorial, set up a simple test database and table using the mysql Command-Line Client or MySQL Workbench. Commands for the mysql Command-Line Client are given here:
CREATE DATABASE TestDB;
USE TestDB;
CREATE TABLE TestTable (id INT NOT NULL PRIMARY KEY
AUTO_INCREMENT, name VARCHAR(100));
The main method of the MySqlScript class is
the Execute method. This method causes the
script (sequence of statements) assigned to the
Query property of the MySqlScript object to be
executed. The Query property can be set through
the MySqlScript constructor or by using the
Query property.
Execute returns the number of statements
executed.
The MySqlScript object will execute the
specified script on the connection set using the
Connection property. Again, this property can
be set directly or through the MySqlScript
constructor. The following code snippets illustrate this:
string sql = "SELECT * FROM TestTable";
...
MySqlScript script = new MySqlScript(conn, sql);
...
MySqlScript script = new MySqlScript();
script.Query = sql;
script.Connection = conn;
...
script.Execute();The MySqlScript class has several events associated with it. There are:
Error - generated if an error occurs.
ScriptCompleted - generated when the script successfully completes execution.
StatementExecuted - generated after each statement is executed.
It is possible to assign event handlers to each of these events. These user-provided routines are called back when the connected event occurs. The following code shows how the event handlers are set up.
script.Error += new MySqlScriptErrorEventHandler(script_Error);
script.ScriptCompleted += new EventHandler(script_ScriptCompleted);
script.StatementExecuted += new MySqlStatementExecutedEventHandler(script_StatementExecuted);In VisualStudio, you can save typing by using tab completion to fill out stub routines. Start by typing, for example, “script.Error +=”. Then press TAB, and then press TAB again. The assignment is completed, and a stub event handler created. A complete working example is shown below:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace MySqlScriptTest
{
class Program
{
static void Main(string[] args)
{
string connStr = "server=localhost;user=root;database=TestDB;port=3306;password=******";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
Console.WriteLine("Connecting to MySQL...");
conn.Open();
string sql = "INSERT INTO TestTable(name) VALUES ('Superman');" +
"INSERT INTO TestTable(name) VALUES ('Batman');" +
"INSERT INTO TestTable(name) VALUES ('Wolverine');" +
"INSERT INTO TestTable(name) VALUES ('Storm');";
MySqlScript script = new MySqlScript(conn, sql);
script.Error += new MySqlScriptErrorEventHandler(script_Error);
script.ScriptCompleted += new EventHandler(script_ScriptCompleted);
script.StatementExecuted += new MySqlStatementExecutedEventHandler(script_StatementExecuted);
int count = script.Execute();
Console.WriteLine("Executed " + count + " statement(s).");
Console.WriteLine("Delimiter: " + script.Delimiter);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
Console.WriteLine("Done.");
}
static void script_StatementExecuted(object sender, MySqlScriptEventArgs args)
{
Console.WriteLine("script_StatementExecuted");
}
static void script_ScriptCompleted(object sender, EventArgs e)
{
/// EventArgs e will be EventArgs.Empty for this method
Console.WriteLine("script_ScriptCompleted!");
}
static void script_Error(Object sender, MySqlScriptErrorEventArgs args)
{
Console.WriteLine("script_Error: " + args.Exception.ToString());
}
}
}
In the script_ScriptCompleted event
handler, the EventArgs parameter
e will be EventArgs.Empty. In
the case of the ScriptCompleted event there is no
additional data to be obtained, which is why the event object is
EventArgs.Empty.
Depending on the nature of the script, you may need control of the delimiter used to separate the statements that will make up a script. The most common example of this is where you have a multi-statement stored routine as part of your script. In this case if the default delimiter of “;” is used you will get an error when you attempt to execute the script. For example, consider the following stored routine:
CREATE PROCEDURE test_routine()
BEGIN
SELECT name FROM TestTable ORDER BY name;
SELECT COUNT(name) FROM TestTable;
END
This routine actually needs to be executed on the MySQL Server as
a single statement. However, with the default delimiter of
“;”, the MySqlScript class
would interpret the above as two statements, the first being:
CREATE PROCEDURE test_routine()
BEGIN
SELECT name FROM TestTable ORDER BY name;
Executing this as a statement would generate an error. To solve
this problem MySqlScript supports the
ability to set a different delimiter. This is achieved through the
Delimiter property. For example, you could
set the delimiter to “??”, in which case the above
stored routine would no longer generate an error when executed.
Multiple statements can be delimited in the script, so for
example, you could have a three statement script such as:
string sql = "DROP PROCEDURE IF EXISTS test_routine??" +
"CREATE PROCEDURE test_routine() " +
"BEGIN " +
"SELECT name FROM TestTable ORDER BY name;" +
"SELECT COUNT(name) FROM TestTable;" +
"END??" +
"CALL test_routine()";You can change the delimiter back at any point by setting the Delimiter property. The following code shows a complete working example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace ConsoleApplication8
{
class Program
{
static void Main(string[] args)
{
string connStr = "server=localhost;user=root;database=TestDB;port=3306;password=******";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
Console.WriteLine("Connecting to MySQL...");
conn.Open();
string sql = "DROP PROCEDURE IF EXISTS test_routine??" +
"CREATE PROCEDURE test_routine() " +
"BEGIN " +
"SELECT name FROM TestTable ORDER BY name;" +
"SELECT COUNT(name) FROM TestTable;" +
"END??" +
"CALL test_routine()";
MySqlScript script = new MySqlScript(conn);
script.Query = sql;
script.Delimiter = "??";
int count = script.Execute();
Console.WriteLine("Executed " + count + " statement(s)");
script.Delimiter = ";";
Console.WriteLine("Delimiter: " + script.Delimiter);
Console.WriteLine("Query: " + script.Query);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
Console.WriteLine("Done.");
}
}
}