c# 执行mysql文件,如何执行* .SQL mysql的文件在我的C＃应用​​程序

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

解决方案

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();