Execute MySQL's SQL files using .NET

Question

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!

Tetsuya Yamamoto · Accepted Answer · 2018-11-26 02:37:11Z

1

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

answered Nov 26, 2018 at 2:37

Tetsuya Yamamoto

25.1k8 gold badges43 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Coffeecat Over a year ago

Thank you very much! I was on the impression that I still need to use Delimiters when executing command texts through .net when Deilimiters are exclusively used by Workbench. Your answer has been very helpful!

Evry1falls Over a year ago

Could you please update the Link in your answer !!!?

Collectives™ on Stack Overflow

Execute MySQL's SQL files using .NET

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related