In this tutorial you will learn how to use the
MySqlScript
class. This class allows 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.
If you wish to run the example programs in this tutorial, you will need to set up a simple test database and table. This can be achived 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. Note the Query property can be set
through the MySqlScript
constructor or via
the Query property.
Execute
returns the number of statements
executed.
The MySqlScript
object will execute the
specified script on the connection set via the
Connection property. Again, this property can
be set directly or through the MySqlScript
constructor. The following code snipets 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 in 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 routies will be called back should the connected event occur. 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 use tab completion to fill out stub routines for you, to save typing. To do this start by typing, for example, “script.Error +=”. Then press TAB, and then press TAB again. The assignment will be 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()); } } }
Note that 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 made 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."); } } }
User Comments
Add your own comment.