存储过程可以在Mysql工作台中使用，但不能在C#代码中使用 [英] Stored Procedure works in Mysql workbench but not in C# code

问题描述

我有一个要删除的存储过程，然后想重新创建它.它可以在Mysql工作台上运行，但不能从c#代码上运行.

I have a stored procedure which I want to delete and then want to re-create it. It is working from Mysql workbench but not working from c# code.

我正在使用MySql Workbench 6.3 CE

I am using MySql Workbench 6.3 CE

这是存储过程的试运行:

This is the dry run of the stored procedure:

 DROP PROCEDURE IF EXISTS AccountGetAccountOpeningClosingStock;
 DELIMITER $$
 CREATE DEFINER=`root`@`%` PROCEDURE 
 `AccountGetAccountOpeningClosingStock`(IN _endDate datetime)
  BEGIN
  Select * From openingclosingstock Where CreateDate <= _endDate order by 
  CreateDate asc;
  END$$
  DELIMITER ;

这是来自C#代码的调用:

This is the call from the C# code:

string myConnectionString = FetchDbConnectionString(environment, item);
MySqlConnection mySqlConnection = new MySqlConnection(myConnectionString);

MySqlCommand mySqlCommand = new MySqlCommand(storedProcedureSqlQuery, mySqlConnection);

await mySqlConnection.OpenAsync();

var reader = await mySqlCommand.ExecuteReaderAsync();

while (await reader.ReadAsync())
{
     // do something
}

reader.Close();
await mySqlConnection.CloseAsync();

来自C#代码的错误:

您的SQL语法有错误；查看与您的MySQL服务器版本相对应的手册，以在'DELIMITER $$附近使用正确的语法 在第1行的CREATE DEFINER = root @ %步骤`AccountGetAccountOpeningClosi'

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE DEFINER=root@% PROCEDURE `AccountGetAccountOpeningClosi' at line 1

我为一个示例创建了一个应用程序: 演示存储过程

I created an app for an example : Demo stored procedure

推荐答案

我找到了一个解决方案，它运行良好.

I found a solution and it is working perfectly.

C#代码:

string myConnectionString = FetchDbConnectionString(environment, item);
MySqlConnection mySqlConnection = new MySqlConnection(myConnectionString);

const string sqlQuery ="DELIMITER $$ DELIMITER $$ DROP PROCEDURE IF EXISTS AccountGetAccountOpeningClosingStock; $$ CREATE PROCEDURE AccountGetAccountOpeningClosingStock(IN _endDate datetime) BEGIN Select * From tutorials_tbl Where CreateDate <= _endDate order by CreateDate asc; END$$ $$  DELIMITER; DELIMITER;";
MySqlScript script = new MySqlScript();
script.Connection = mySqlConnection;
script.Query = sqlQuery;
script.Execute();
mySqlConnection.Close();

这篇关于存储过程可以在Mysql工作台中使用，但不能在C#代码中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！