c# - 如何在我的 C# 应用程序中执行 *.sql mysql 文件

Question

我的文件中有一个 mysql 脚本，我需要能够从我的 C# 应用程序中执行该脚本。以下是脚本包含的示例:

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 ;

如您所见，它混合了调用存储过程的行和一些用于更新其他存储过程的 drop 和 create 语句。

我尝试了两种不同的方法来执行脚本，但都失败了:

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

这会引发异常:索引超出数组范围。

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

这会抛出一个异常，表明我的 sql 语法有错误。

当我在 dbForge Studio 中手动运行整个脚本时，它执行得很好。如何让此脚本从我的 C# 应用程序正确执行

Answer 1

看看here .您应该为 MySqlScript 指定一个分隔符(因为您在其中存储了过程)。您的查询应如下所示:

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

然后是你的代码:

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