C# 代码执行MySQL脚本文件--Using MySqlScript

找了好久，在MySQL官网找到了，连接如下：

http://dev.mysql.com/doc/refman/5.5/en/connector-net-tutorials-mysqlscript.html

22.2.4.8. Tutorial: Using MySqlScript

22.2.4.8.1. Using Delimiters with MySqlScript

       第一步：引用 程序集 MySql.Data.dll

       第二步：应用类MySqlScript ：

这是官网的代码：

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

 

看我在官网代码基础上注释掉和修改string sql的内容（以下代码第23行，添加了：Delimiter??，

并且注释掉了第34行和第37行//script.Delimiter = "??";我想说明的是MySqlScript类能自己

处理一些特殊情况（如：特殊字符，注释，关键字Delimiter等等），MySqlScript自己会处理

好的，不用你管。不要被官网的那几个英文绕晕了，还要自己script.Delimiter = "??";去处理特殊情况 ，看看：

 

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

            最后，string sql可以从sql脚本文件中读取；            

                                    FileInfo file = new FileInfo(filename);  //filename是sql脚本文件路径。
                                    string sql = file.OpenText().ReadToEnd();

        这是脚本文件：

Delimiter ??  
DROP PROCEDURE IF EXISTS test_routine??
CREATE PROCEDURE test_routine() 
BEGIN 
   SELECT name FROM TestTable ORDER BY name;
   SELECT COUNT(name) FROM TestTable;
END??

Delimiter ;
CALL test_routine();

 

OK啦！

后记：

如果执行的脚本是创建数据库的脚本,例如：

createDataBase.sql

-- ----------------------------
-- CREATE SCHEMA `TestDB`

-- ----------------------------

 create database if not exists `TestDB`;

连接字符串中不能有数据库的名字，否则执行脚本失败，道理很显然易见，在数据库没创建之前，根本无法通过该连接执行。

在数据库尚未存在，需要创建数据时，创建的连接应该是：

15             string connStr = "server=localhost;user=root;database=;port=3306;password=******;";
16             MySqlConnection conn = new MySqlConnection(connStr);

是

database=;

         （即：没有数据库名）

而不是：

database=TestDB;