我在一个文件中有一个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-and-create语句来更新其他存储过程。
我尝试了两种不同的方法来执行脚本,但都失败了:
MySqlScript script = new MySqlScript(connection, File.ReadAllText("script.sql"));
script.Execute();
这引发了异常:Index在数组的边界之外。
MySqlCommand upgCommand = new MySqlCommand();
upgCommand.Connection = connection;
upgCommand.CommandText = File.ReadAllText("script.sql");
upgCommand.ExecuteNonQuery();
这引发了一个异常,即我的sql语法中存在错误。
当我在dbForge Studio中手动运行整个脚本时,它会完美地执行。如何从我的C#应用程序中正确执行此脚本
看看这里。您应该为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();
您可以运行一个单独的进程来执行脚本。例如:
Process.Start("mysql < script.sql");
根据您的环境,您可能需要稍微处理一下它,以包括mysql可执行文件或sql脚本的路径。
如果将sql脚本放在存储过程中如何?如果您仍然想使用这种方式,请将sql文件附加到应用程序,作为应用程序资源的文件(files.resx),并像这样执行:
MySql.Data.MySqlClient.MySqlCommand cmdMySQL = newMySqlConnection.CreateCommand();
cmdMySQL.CommandText = (System.String)globalResource.your_file_name;
我不能100%确定本机MySql语法是什么,但当我们为Sql Server执行类似功能时,我们必须根据仅由查询执行器(Sql Server管理控制台)使用的文字值(GO
)将Sql文件分为块。
我怀疑您的上述请求中可能包含类似的信息,例如DEFINE声明。
准确了解MySql抱怨的内容将有助于改进解决方案。
为我工作:
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();