CODEWITHSUNDEEP
c#mysql
AS7K
AS7K

Reputation: 457

How can I create a MySql function in C#?

I'm trying to create a mysql function in C#. The reason I'm doing this is because I want to loop through multiple mysql databases and create the function in each one.

The problem is that the MySqlCommand does not like the DELIMITER syntax required to create the function. The script below executes fine in MySql clients, but does not work in this context:

DELIMITER //
DROP FUNCTION IF EXISTS cleandate //
CREATE FUNCTION cleandate(mydate DATE) 
RETURNS DATE
READS SQL DATA

BEGIN
RETURN  (CASE WHEN mydate <= '1900-01-01' THEN '1900-01-01' WHEN mydate = '0000-00-00' THEN '1900-01-01' 
        WHEN DATE_FORMAT(mydate, '%Y') = '0000' THEN '1900-01-01'
        WHEN DATE_FORMAT(mydate, '%m') = '00' THEN '1900-01-01'
        WHEN DATE_FORMAT(mydate, '%d') = '00' THEN '1900-01-01'
        ELSE mydate 
        END);
END//

The c# code trying to implement this(assuming connstrings is a List of strings):

foreach(var connstring in connstrings)
{
    // Create connection to database
    MySql.Data.MySqlClient.MySqlConnection dbConn = new MySql.Data.MySqlClient.MySqlConnection(connstring);

    MySqlCommand cmd = dbConn.CreateCommand();
    cmd.CommandText = @"[same script here]";

    try
    {
        dbConn.Open();
        cmd.ExecuteNonQuery();
    }
    catch (Exception erro)
    {
        Console.WriteLine(erro.ToString());                    
    }
}

I get the "syntax error near DELIMITER //" when I run this though. Any ideas how I can achieve this?

Upvotes: 2

Views: 796

Answers (1)

Lauren Riddick
Lauren Riddick

Reputation: 56

The MySQL Connector offers a MySqlScript class that can be used in place of MySqlCommand when dealing with executing multiple statements and setting delimiters.

Adapted to your example: 

foreach(var connstring in connstrings)
{
    MySqlConnection dbConn = new MySqlConnection(connstring);

    try
    {
        dbConn.Open();

        MySqlScript script = new MySqlScript(dbConn);

        script.Query = @"[same script here]";
        script.Delimiter = @"//";
        script.Execute();
    }
    catch (Exception error)
    {
        Console.WriteLine(error.ToString());                    
    }
}

https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-mysqlscript.html

Upvotes: 4

Related Questions