How to execute *.sql mysql file in my c# application

Question

I have a mysql script in a file that I need to be able to execute from my c# application. Here is a sample of what the script contains:

USE osae;

-- Set DB version 
CALL osae_sp_object_property_set('SYSTEM', 'DB Version', '0.3.5', '', '');
CALL osae_sp_object_property_set('SYSTEM', 'Debug', 'FALSE', '', '');
CALL osae_sp_object_type_property_add ('Prune Logs','Boolean','TRUE','SYSTEM',0);
CALL osae_sp_object_property_set ('SYSTEM','Prune Logs','TRUE','','');

DELIMITER $$

DROP PROCEDURE IF EXISTS osae_sp_object_event_script_update$$
CREATE DEFINER = 'root'@'localhost'
PROCEDURE osae_sp_object_event_script_update(IN pobject varchar(200), IN pevent varchar(200), IN ptext text)
BEGIN
DECLARE vObjectCount INT;
DECLARE vObjectID INT;
DECLARE vObjectTypeID INT;
DECLARE vEventCount INT;
DECLARE vEventID INT;
    SELECT COUNT(object_id) INTO vObjectCount FROM osae_object WHERE UPPER(object_name)=UPPER(pobject);
    IF vObjectCount > 0 THEN
              SELECT object_id,object_type_id INTO vObjectID,vObjectTypeID FROM osae_object WHERE UPPER(object_name)=UPPER(pobject);
        SELECT COUNT(event_id) INTO vEventCount FROM osae_object_type_event WHERE object_type_id=vObjectTypeID AND (UPPER(event_name)=UPPER(pevent) OR UPPER(event_label)=UPPER(pevent));
        IF vEventCount = 1 THEN     
            SELECT event_id INTO vEventID FROM osae_object_type_event WHERE object_type_id=vObjectTypeID AND (UPPER(event_name)=UPPER(pevent) OR UPPER(event_label)=UPPER(pevent));
            UPDATE osae_object_event_script SET event_script=ptext WHERE object_id=vObjectID AND event_id=vEventID;
         -- CALL osae_sp_debug_log_add(CONCAT('Updated ',vObjectID,' - ',vEventID,ptext),'');  
        END IF;
    END IF; 
END
$$

DELIMITER ;

As you can see it has a mixture of lines that call stored procedures and some drop and create statements to update other stored procedures.

I have tried two different methods to execute the script and both have failed:

MySqlScript script = new MySqlScript(connection, File.ReadAllText("script.sql"));
script.Execute();

This throws the exception: Index was outside the bounds of the array.

MySqlCommand upgCommand = new MySqlCommand();
upgCommand.Connection = connection;
upgCommand.CommandText = File.ReadAllText("script.sql");
upgCommand.ExecuteNonQuery();

This throws an exception that there is an error in my sql syntax.

When I run the entire script manually in dbForge Studio it executes perfectly. How can I get this script to execute correctly from my C# app

Will this script ever be changed? If so, it's probably best to have your program execute mysql at the command line with the script as the parameter. Otherwise, consume each step in the script into your program; that way the chain is less brittle, i.e. you'll be relying entirely on your own process and not some script that may or may not be there and may or may not work. — Michael Todd
– Michael Todd, Commented Jan 13, 2012 at 18:18
unfortunately I can't use the mysql exe to execute the script because the mysql server may be on a different machine — Brian
– Brian, Commented Jan 13, 2012 at 19:11

Jared Phelps · Accepted Answer · 2020-08-12 18:23:32Z

10

Take a look here. You should specify a delimiter for MySqlScript (since you have stored procedure in there). And your query should look like:

-- Set DB version 
CALL osae_sp_object_property_set('SYSTEM', 'DB Version', '0.3.5', '', '')$$
CALL osae_sp_object_property_set('SYSTEM', 'Debug', 'FALSE', '', '')$$
CALL osae_sp_object_type_property_add ('Prune Logs','Boolean','TRUE','SYSTEM',0)$$
CALL osae_sp_object_property_set ('SYSTEM','Prune Logs','TRUE','','')$$



DROP PROCEDURE IF EXISTS osae_sp_object_event_script_update$$
CREATE DEFINER = 'root'@'localhost'
PROCEDURE osae_sp_object_event_script_update(IN pobject varchar(200), IN pevent varchar(200), IN ptext text)
BEGIN
DECLARE vObjectCount INT;
DECLARE vObjectID INT;
DECLARE vObjectTypeID INT;
DECLARE vEventCount INT;
DECLARE vEventID INT;
    SELECT COUNT(object_id) INTO vObjectCount FROM osae_object WHERE UPPER(object_name)=UPPER(pobject);
    IF vObjectCount > 0 THEN
              SELECT object_id,object_type_id INTO vObjectID,vObjectTypeID FROM osae_object WHERE UPPER(object_name)=UPPER(pobject);
        SELECT COUNT(event_id) INTO vEventCount FROM osae_object_type_event WHERE object_type_id=vObjectTypeID AND (UPPER(event_name)=UPPER(pevent) OR UPPER(event_label)=UPPER(pevent));
        IF vEventCount = 1 THEN     
            SELECT event_id INTO vEventID FROM osae_object_type_event WHERE object_type_id=vObjectTypeID AND (UPPER(event_name)=UPPER(pevent) OR UPPER(event_label)=UPPER(pevent));
            UPDATE osae_object_event_script SET event_script=ptext WHERE object_id=vObjectID AND event_id=vEventID;
         -- CALL osae_sp_debug_log_add(CONCAT('Updated ',vObjectID,' - ',vEventID,ptext),'');  
        END IF;
    END IF; 
END
$$

And then your code:

MySqlScript script = new MySqlScript(connection, File.ReadAllText("script.sql"));
script.Delimiter = "$$";
script.Execute();

edited Aug 12, 2020 at 18:23

Jared Phelps

4735 silver badges14 bronze badges

answered Jan 13, 2012 at 18:34

Aleksandar Vucetic

15k9 gold badges56 silver badges56 bronze badges

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

4 Comments

Brian Over a year ago

Unfortunately this still throws the exception: Index was outside the bounds of the array.

Aleksandar Vucetic Over a year ago

Can you try without specifying the DELIMITER $$ and USE (your already have database name in connection string). Take a look at my modified answer. (I've also used wrong delimiter it my code example (##), it is fixed now ($$)

Aleksandar Vucetic Over a year ago

And also, check example code here: dev.mysql.com/doc/refman/5.5/en/… You can try with modifying your code to include just procedure definition. It might happen that your stored procedures calls (at the beginning of the script) are causing problems.

Brian Over a year ago

figured it out. It looks like that DELIMITER ; at the end was causing the problem. I see you took it out of our last edit. Thanks!

jzacharuk · Accepted Answer · 2012-01-13 18:19:46Z

1

You could run a separate Process to execute the script. For example:

Process.Start("mysql < script.sql");

You are probably going to need to play around with it a little depending on your environment to include paths to the mysql executable or the sql script.

answered Jan 13, 2012 at 18:19

jzacharuk

2,1271 gold badge16 silver badges24 bronze badges

2 Comments

Brian Over a year ago

Problem with this is the mySql server may not be on the same machine as the c# app

NiKiZe Over a year ago

MySQL client can connect over the network, just like the app (which is a client) just copy over mysql.exe and any needed dependencies.

DaveShaw · Accepted Answer · 2012-01-13 20:06:13Z

1

How if you put your sql script in a stored procedure? If you still want use this way attach the sql file to application as file to the application resources (files .resx) and execute it like that:

MySql.Data.MySqlClient.MySqlCommand cmdMySQL = newMySqlConnection.CreateCommand();
cmdMySQL.CommandText = (System.String)globalResource.your_file_name;

edited Jan 13, 2012 at 20:06

DaveShaw

52.9k18 gold badges118 silver badges142 bronze badges

answered Jan 13, 2012 at 19:19

Massimo

312 bronze badges

Comments

competent_tech · Accepted Answer · 2012-01-13 18:20:11Z

0

I am not 100% sure what native MySql syntax is, but when we perform similar functionality for Sql Server, we have to break the sql file into chunks based on the literal values (GO) that are only used by the query executor (Sql Server Management Console).

I suspect there may be similar information embedded in your request above, such as the DEFINE statement.

Knowing exactly what MySql complained about would help refine the solution.

answered Jan 13, 2012 at 18:20

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

1 Comment

Tony Hopkinson Over a year ago

That was my thought as well. GO or it's equivalent isn't SQL and the sql parser chokes on it. Ripping them out or using them to split a long script is teh way we go as well.

ranojan · Accepted Answer · 2022-10-20 10:03:58Z

0

Worked For Me:

connectOb.conn.Open();
MySqlScript script = new MySqlScript(connectOb.conn, 
"SET @num := 0; 
UPDATE serving_table SET Id = @num := (@num + 1);
ALTER TABLE serving_table AUTO_INCREMENT = 1;");
script.Execute();
connectOb.conn.Close();

answered Oct 20, 2022 at 10:03

ranojan

8579 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to execute *.sql mysql file in my c# application

5 Answers 5

4 Comments

2 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related