如何执行* .SQL mysql的文件在我的C＃应用​​程序 [英] How to execute *.sql mysql file in my c# application

问题描述

我在，我需要能够从我的C＃应用​​程序执行文件的MySQL脚本。这是一个什么样的脚本包含一个样本：

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

这将引发异常，有我的SQL语法错误。

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

当我在dbForge工作室手动运行整个脚本执行它完美。我怎样才能得到这个脚本来正确地从我的C＃应用​​程序执行

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

推荐答案

看看的此处。您应该指定一个分隔符MySqlScript（因为你已经存储在那里的​​过程）。和您的查询应该是这样的：

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
$$

和那么你的code：

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

这篇关于如何执行* .SQL mysql的文件在我的C＃应用​​程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！