CODEWITHSUNDEEP
mysqlsql.netvb.net
Coffeecat
Coffeecat

Reputation: 25

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!

Upvotes: 0

Views: 671

Answers (1)

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

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

Upvotes: 1

Related Questions