找了好久,在MySQL官网找到了,连接如下:
22.2.4.8. Tutorial: Using MySqlScript
22.2.4.8.1. Using Delimiters with MySqlScript
第一步:引用 程序集 MySql.Data.dll
第二步:应用类MySqlScript :
这是官网的代码:
1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5
6 usingMySql.Data;7 usingMySql.Data.MySqlClient;8
9 namespaceConsoleApplication810 {11 classProgram12 {13 static void Main(string[] args)14 {15 string connStr = "server=localhost;user=root;database=TestDB;port=3306;password=******;";16 MySqlConnection conn = newMySqlConnection(connStr);17
18 try
19 {20 Console.WriteLine("Connecting to MySQL...");21 conn.Open();22
23 string sql = "DROP PROCEDURE IF EXISTS test_routine??" +
24 "CREATE PROCEDURE test_routine()" +
25 "BEGIN" +
26 "SELECT name FROM TestTable ORDER BY name;" +
27 "SELECT COUNT(name) FROM TestTable;" +
28 "END??" +
29 "CALL test_routine()";30
31 MySqlScript script = newMySqlScript(conn);32
33 script.Query =sql;34 script.Delimiter = "??";35 int count =script.Execute();36 Console.WriteLine("Executed" + count + "statement(s)");37 script.Delimiter = ";";38 Console.WriteLine("Delimiter:" +script.Delimiter);39 Console.WriteLine("Query:" +script.Query);40 }41 catch(Exception ex)42 {43 Console.WriteLine(ex.ToString());44 }45
46 conn.Close();47 Console.WriteLine("Done.");48 }49 }50 }
看我在官网代码基础上注释掉和修改string sql的内容(以下代码第23行,添加了:Delimiter??,
并且注释掉了第34行和第37行//script.Delimiter = "??";我想说明的是MySqlScript类能自己
处理一些特殊情况(如:特殊字符,注释,关键字Delimiter等等),MySqlScript自己会处理
好的,不用你管。不要被官网的那几个英文绕晕了,还要自己script.Delimiter = "??";去处理特殊情况 ,看看:
1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5
6 usingMySql.Data;7 usingMySql.Data.MySqlClient;8
9 namespaceConsoleApplication810 {11 classProgram12 {13 static void Main(string[] args)14 {15 string connStr = "server=localhost;user=root;database=TestDB;port=3306;password=******;";16 MySqlConnection conn = newMySqlConnection(connStr);17
18 try
19 {20 Console.WriteLine("Connecting to MySQL...");21 conn.Open();22
23 string sql = "Delimiter ??DROP PROCEDURE IF EXISTS test_routine??" +
24 "CREATE PROCEDURE test_routine()" +
25 "BEGIN" +
26 "SELECT name FROM TestTable ORDER BY name;" +
27 "SELECT COUNT(name) FROM TestTable;" +
28 "END??" +
29 "CALL test_routine()";30
31 MySqlScript script = newMySqlScript(conn);32
33 script.Query =sql;34 //script.Delimiter = "??";35 int count =script.Execute();36 Console.WriteLine("Executed" + count + "statement(s)");37 //script.Delimiter = ";";38 Console.WriteLine("Delimiter:" +script.Delimiter);39 Console.WriteLine("Query:" +script.Query);40 }41 catch(Exception ex)42 {43 Console.WriteLine(ex.ToString());44 }45
46 conn.Close();47 Console.WriteLine("Done.");48 }49 }50 }
最后,string sql可以从sql脚本文件中读取;
1 FileInfo file = new FileInfo(filename); //filename是sql脚本文件路径。
2 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 databaseif not exists `TestDB`;
连接字符串中不能有数据库的名字,否则执行脚本失败,道理很显然易见,在数据库没创建之前,根本无法通过该连接执行。
在数据库尚未存在,需要创建数据时,创建的连接应该是:
15 string connStr = "server=localhost;user=root;database=;port=3306;password=******;";16 MySqlConnection conn = new MySqlConnection(connStr);
是
database=;
(即:没有数据库名)
而不是:
database=TestDB;