C #, MySQL, ADO.NET, delimiter causing syntax error

Question

I have C # code that cycles through .sql files and does what creates a database inside them.

A single .sql file basically looks like this:

 DROP PROCEDURE IF EXISTS myProc; DELIMITER $$ CREATE PROCEDURE myProc() BEGIN -- procedure stuff goes here END $$ DELIMITER ; CALL myProc(); 

When I enter this into the MySQL Query Browser script window, it works fine ... again and again, as I would like.

However, if I put the line in my IDbCommand and executed it ...

 connection.Open(); // An IDbConnection IDbTransaction transaction = connection.BeginTransaction(); using (IDbCommand cmd = connection.CreateCommand()) { cmd.Connection = connection; cmd.Transaction = transaction; cmd.CommandText = line; cmd.CommandType = CommandType.Text; try { cmd.ExecuteNonQuery(); } catch (SqlException ex) { transaction.Rollback(); return false; } } transaction.Commit(); connection.Close(); 

... I get the terrible exception 1064 ...

You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use near 'DELIMITER $$ CREATION PROCEDURE myProc () START ...

So the question is, why does MySQL allow me to do this without problems, but when I try to start it with C # it fails? And, of course, the second question is how I should fix it.

Answer 1

I think you are looking for: "Error # 46429: use the DELIMITER command in MySql.Data.MySqlClient.MySqlScript"

Answer 2

For those looking for a quick snippet ...

 var connectionString = @"server=ChangeMe;user=ChangeMe;pwd=ChangeMe;database=ChangeMe;"; var scriptText = File.ReadAllText(@"C:\script.sql"); using (var connection = new MySqlConnection(connectionString)) { var script = new MySqlScript(connection, scriptText); connection.Open(); script.Execute(); } 

Answer 3

Some of the commands in your script are interpreted as a mysqlk client, which can be released before sending SQL to the server.

See mysql commands

In fact, all delimiters

To interpret a script from C #, you have to write code that knows what a command is, you cannot just pass commands line by line to the server.

eg. you have 3 commands in this file and therefore want to call ExecuteNonQuery only 3 times

and from paracyclic code you need to use the MySqlScript class