5.10.1 Using Delimiters with MySqlScript

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.");
        }
    }
}