在.NET中将多个非SELECT MySQL语句作为单个命令文本执行 [英] Executing Multiple Non-SELECT MySQL Statements as a Single Command Text in .NET

问题描述

我一直在从一个线程跳到另一个线程，但是我似乎找不到我需要的答案.

I've been jumping from one thread to another but I can't seem to find the answer that I need.

我想使用.NET在单个ExecuteNonQuery函数中执行非SELECT MySQL语句.

I wanted to execute non-SELECT MySQL statements in a single ExecuteNonQuery function using .NET.

这是我要运行的一组SQL语句示例:

Here is a sample group of SQL statements I wanted to run:

DROP procedure IF EXISTS `SubProcA`;
DROP procedure IF EXISTS `SubProcB`;
DROP procedure IF EXISTS `SubProcC`;


CREATE PROCEDURE 'SubProcD'()
BEGIN
SELECT
     (<Subselect Statement>) AS A
     ,(<Subselect Statement>) AS B
     ,(<Subselect Statement>) AS C;
END;


CREATE PROCEDURE 'SubProcE'(
     VarA TEXT,
     VarB VARCHAR(255),
     VarC VARCHAR(255),
     VarD tinyint(1)
)
BEGIN
     SET @Query = "";
     SET @Query = <Select Statement>;              
     PREPARE Statement FROM @Query;
     EXECUTE Statement;
     DEALLOCATE PREPARE Statement; 
END;

如您所见，其中有五个语句(3个DROP和2个CREATE).我在.NET中所做的是:

As you can see, there are five statements in there (3 DROPs and 2 CREATEs). What I do in .NET is:

    CommandText = <The SQL Statement above stored in a String variable>
    Connection.Open()
    Command.CommandType = CommandType.Text
    Command.ExecuteNonQuery()
    Connection.Close()

假设sql语句没有语法错误，运行代码时我仍然会收到错误消息.

Assuming that the sql statements have no syntax error whatsoever, I still get an error when running the code.

我尝试砍掉(运行一条语句)的sql语句，然后它通过了.

I tried chopping the sql statements of (running one statement) and it pushes through.

我想在其中放一些特殊字符吗?

Am I suppose to put some special character in there or something?

推荐答案

在.net中，这些多个串联的查询称为 batch .

These multiple concatenated queries are known as a batch in .net parlance.

您必须在连接字符串中告诉MySQL连接器(MySQL的.net驱动程序)要使用批处理.使用 AllowBatch 选项.

You must tell the MySQL connector (the .net driver for MySQL) you want to use batches in your connection string. Use the AllowBatch option.

类似这样的东西:

var connectionString = Server=host;Database=db;Uid=user;Pwd=pass;AllowBatch=True;

您不能在批处理中包括存储过程调用.它们必须与 ... CommandType = CommandType.StoredProcedure 一起独立存在.

You cannot include stored procedure invocations within a batch. They must stand alone with ...CommandType=CommandType.StoredProcedure .

但是，这可能不起作用，具体取决于服务器和连接器的版本.如果不只是使用您的切掉" SQL语句的解决方法.它可以正常工作，并且不会以可怕的方式破坏性能.

But, this may not work depending on server and connector versions. If it doesn't just use your workaround of "chopping" the SQL statements. It works fine and doesn't mess up performance in a horrible way.

这篇关于在.NET中将多个非SELECT MySQL语句作为单个命令文本执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！