C#、MySQL、ADO.NET、分隔符导致语法错误

发布于 2024-08-02 13:19:53 字数 1126 浏览 15 评论 0原文

我有 C# 代码，它循环遍历 .sql 文件并执行其中的内容来设置数据库。

一个 .sql 文件基本上如下：

DROP PROCEDURE IF EXISTS myProc;

DELIMITER $$

CREATE PROCEDURE myProc()
BEGIN
-- procedure stuff goes here
END $$

DELIMITER ;

CALL myProc();

当我将其输入 MySQL 查询浏览器的脚本窗口时，它完美地运行......一遍又一遍，正如人们所希望的那样。

但是，如果我将字符串放入 IDbCommand 中并执行它...

connection.Open(); // An IDbConnection
IDbTransaction transaction = connection.BeginTransaction();
using (IDbCommand cmd = connection.CreateCommand())
{
    cmd.Connection = connection;
    cmd.Transaction = transaction;
    cmd.CommandText = line;
    cmd.CommandType = CommandType.Text;

    try
    {
        cmd.ExecuteNonQuery();
    }
    catch (SqlException ex)
    {
        transaction.Rollback();
        return false;
    }
}

transaction.Commit();
connection.Close();

...我会得到可怕的异常 1064...

您的 SQL 语法有错误；检查手册与您的 MySQL 服务器版本相对应，以便使用正确的语法靠近 'DELIMITER $$ CREATE PROCEDURE myProc() BEGIN...

所以，问题是... 为什么 MySQL 让我毫无问题地执行此操作，但当我尝试从 C# 运行它时，它失败了？当然，第二个问题是我应该如何解决它。

原文

I have C# code that cycles through .sql files and executes what's inside them to set up a database.

One .sql file is basically as follows:

DROP PROCEDURE IF EXISTS myProc;

DELIMITER $

CREATE PROCEDURE myProc()
BEGIN
-- procedure stuff goes here
END $

DELIMITER ;

CALL myProc();

When I input this into the MySQL Query Browser's script window, it runs perfectly... over and over again, just as one would want it to.

However, if I put the string into my IDbCommand and execute it...

connection.Open(); // An IDbConnection
IDbTransaction transaction = connection.BeginTransaction();
using (IDbCommand cmd = connection.CreateCommand())
{
    cmd.Connection = connection;
    cmd.Transaction = transaction;
    cmd.CommandText = line;
    cmd.CommandType = CommandType.Text;

    try
    {
        cmd.ExecuteNonQuery();
    }
    catch (SqlException ex)
    {
        transaction.Rollback();
        return false;
    }
}

transaction.Commit();
connection.Close();

... I get the dreaded exception 1064...

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 PROCEDURE myProc() BEGIN...

So, the question is... why does MySQL let me do this with no problems, but when I try to run it from C#, it fails? And of course the second question is how I'm supposed to fix it.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

围归者 2024-08-09 13:19:53

对于那些寻找快速片段的人...

var connectionString = @"server=ChangeMe;user=ChangeMe;pwd=ChangeMe;database=ChangeMe;"; 

var scriptText = File.ReadAllText(@"C:\script.sql");

using (var connection = new MySqlConnection(connectionString))
{
    var script = new MySqlScript(connection, scriptText);
    connection.Open();
    script.Execute();
}

For those looking for a quick snippet...

var connectionString = @"server=ChangeMe;user=ChangeMe;pwd=ChangeMe;database=ChangeMe;"; 

var scriptText = File.ReadAllText(@"C:\script.sql");

using (var connection = new MySqlConnection(connectionString))
{
    var script = new MySqlScript(connection, scriptText);
    connection.Open();
    script.Execute();
}

回复收藏 0 原文