Execute MySQL’s SQL files using .NET

Whenever I make an update to my database (changes in stored procs, changes in tables, new tables ,etc.), I create a new sql file which I run on client’s computer via TeamViewer.

I wanted my application to update its own database. So I decided to make it download sql files via ftp then updates itself using ExecuteNonQuery or something.

Unfortunately I’m stuck at Delimiters pushing errors. If I remove Delimiters, running stored proc update queries are impossible (or am I missing something?).

Here is a sample content of an sql file I usually run:

USE 'iceberg';
DROP procedure IF EXISTS 'get_unpaidandduetickets';

DELIMITER $$
USE 'iceberg'$$
CREATE DEFINER='root'@'localhost' PROCEDURE 'get_unpaidandduetickets'()
BEGIN

SELECT
    (SELECT IFNULL(COUNT(ticketid),0) FROM tickets WHERE balance > 0 AND status = 1) AS unpaidtickets
    ,(SELECT IFNULL(COUNT(ticketid),0) FROM tickets WHERE balance > 0 AND status = 1 AND DATE(duedate) = DATE(NOW())) AS dueticketstoday
    ,(SELECT IFNULL(COUNT(ticketid),0) FROM tickets WHERE balance > 0 AND status = 1 AND DATE(duedate) < DATE(NOW())) AS pastduetickets;

END$$

DELIMITER ;    

CALL set_databaseversion('001020181119');

Is there any way I could pull this off? I’m open to other means.

Thanks in advance!

Answer

Assumed that you have connection string which contains the database name, it is unnecessary to append USE 'iceberg'; and DELIMITER $$ in the script because MySqlScript instance can control the delimiter for you. Therefore, adjust your script contents as in example below:

DROP procedure IF EXISTS 'get_unpaidandduetickets'$$

CREATE DEFINER='root'@'localhost' PROCEDURE 'get_unpaidandduetickets'()
BEGIN
    -- procedure contents here
END$$

CALL set_databaseversion('001020181119')$$

Then, use ReadAllText() to read entire file contents as string and pass it into Query property inside MySqlScript instance:

' connection string example
' see connectionstrings.com/mysql-connector-net-mysqlconnection/ for details
Dim connectionString As String = "Server=localhost;Port=3306;Database=iceberg;Uid=UserID;Pwd=Password"

Using con As New MySqlConnection(connectionString)
    Dim content As String = System.IO.File.ReadAllText("scriptfilename.sql")

    Dim sqlScript As New MySqlScript(con)

    sqlScript.Query = content   ' query string
    sqlScript.Delimiter = "$$"  ' delimiter setting
    sqlScript.Execute()

    ' other stuff
End Using

Note: You can create custom extension method which removes unnecessary parts such like DELIMITER and USE keywords.

Reference: Using Delimiters with MySqlScript