How can I create a MySql function in C#?

Question

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?

Normally you don't create SQL functions in code. Define them by hand and USE them in your code — juergen d
– juergen d, Commented Jul 28, 2017 at 16:26
I've tried this years ago with an ODBC connection (in VBA) with a little success; but I don't recall the details. One thing that stands out with your example though is that you are trying to execute multiple statements in one call; most database APIs don't allow that without at least setting some additional options on the connection (if at all). (Edit: It was ADODB over ODBC, and didn't need a DELIMITER override.) — Uueerdo
– Uueerdo, Commented Jul 28, 2017 at 16:35

Lauren Riddick · Accepted Answer · 2017-07-28 22:22:56Z

4

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

answered Jul 28, 2017 at 22:22

Lauren Riddick

561 bronze badge

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

2 Comments

AS7K Over a year ago

Can't believe I didn't know about the MySqlScript class. This worked for me! Thanks!

AS7K Over a year ago

I did also figure out you can actually execute it without a delimiter...to me it wasn't obvious since I read that you had to use a delimiter when creating a function. Oh well, now we know you can do both if needed.

Collectives™ on Stack Overflow

How can I create a MySql function in C#?

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