ADO.NET Command.CommandText 属性中是否可以有多个 SQL 指令？

Question

摘要

我目前正在编写一个应用程序，我已将 SQL 指令放入项目的参数中。

在代码中，我获取查询的值，该值返回查询本身。比方说，我的 SQL 查询如下：

select col1, col2, col3 from my_table

此外，col1、col2 和 col3 来自不同的表，并作为外键迁移进入 my_table。因此，在插入时，我必须执行多个 INSERT 语句才能从其他表中获取上述列的值。假设如下：

BEGIN TRANSACTION

insert into first_table (col_x, col_y) values ('col_x', 'col_y')
insert into second_table (col_z, col_a) values ('col_z', 'col_a')
insert into third_table (col_b, col_c) values ('col_b', 'col_c')

最后：

insert into my_table (col1, col2, col3, col_v) values (@col1, @col2, @col3, 'col_v')

COMMIT

假设这些 col1、col2、col3 列是表 first 的自增整数、第二和第三。

问题

1. 我能否将复杂的 SQL 语句写入 IDbCommand.CommandText 属性，同时每条指令用分号 (;) 分隔？

2. 是否可以在此 CommandText 属性中包含 BEGIN TRANSACTION...COMMIT/ROLLBACK？

3. 简而言之，我可以写这样的东西吗？

   使用 cnx = New SqlConnection(connString)
       使用 cmd = cnx.CreateCommand()
           cmd.CommandText = "开始交易" _
                     & “插入first_table（col_x，col_y）值（'col_x'，'col_y'）；” _ 
                     & “插入第二表（col_z，col_a）值（'col_z'，'col_a'）;” _
                     & “插入third_table（col_b，col_c）值（'col_b'，'col_c'）；” _
                     & “插入my_table（col1，col2，col3，col_v）值（@col1，@col2，@col3，'col_v'）;” _
                     & “犯罪”
           cmd.ExecuterNonQuery()
       结束使用
   结束使用
   

编辑#1

我应该之前提到过...麦克的答案是我想要走的路，但我不能这样做，因为我的客户的 IT 部门内部有奇怪的政策，除非我使用他们的自定义组件，为了简单起见，我宁愿避免使用它们。请注意，无论如何我都赞成麦克的答案，因为无论如何它都是一个可行的解决方案。

预先感谢您宝贵的帮助和时间！这对我来说至关重要！

Answer 1

如果您不能使用存储过程，那么这段代码可能会满足您的要求：

SqlConnection cnx = new SqlConnection(connString);
SqlCommand cmd = cnx.CreateCommand();
cnx.Open();
string complexCommand = string.Concat(
"DECLARE @first_table AS TABLE(col1 int IDENTITY, col_x varchar(20), col_y varchar(20))"
, " DECLARE @second_table AS TABLE(col2 int IDENTITY, col_z varchar(20), col_a varchar(20))"
, " DECLARE @third_table AS TABLE(col3 int IDENTITY, col_b varchar(20), col_c varchar(20))"
, " DECLARE @my_table AS TABLE(col1 int, col2 int, col3 int, col_v varchar(20))"
, " DECLARE @col1 int"
, " DECLARE @col2 int"
, " DECLARE @col3 int"
, " BEGIN TRAN"
, " BEGIN TRY"
, "   insert into @first_table (col_x, col_y) values ('col_x', 'col_y')"
, "   SET @col1=@@IDENTITY"
, "   insert into @second_table (col_z, col_a) values ('col_z', 'col_a')"
, "   SET @col2=@@IDENTITY"
, "   insert into @third_table (col_b, col_c) values ('col_b', 'col_c')"
, "   SET @col3=@@IDENTITY"
, "   insert into @my_table(col1, col2, col3, col_v) values (@col1, @col2, @col3, 'col_v')"
, "   COMMIT"
, " END TRY"
, " BEGIN CATCH"
, "   ROLLBACK"
, " END CATCH");

cmd.CommandText = complexCommand;
cmd.ExecuteNonReader();

我已根据需要添加了表变量以使示例代码运行，显然您可以利用永久表。

Answer 2

尝试使用交易...

using (SqlConnection cnx = new SqlConnection(connString))

{
    cnx.Open();

    // Start a local transaction.
    SqlTransaction sqlTran = cnx.BeginTransaction();

    // Enlist a command in the current transaction.
    SqlCommand command = cnx.CreateCommand();
    command.Transaction = sqlTran;

    try
    {
        // Execute two separate commands.
        command.CommandText = "insert into first_table (col_x, col_y) values ('col_x', 'col_y')";
        command.ExecuteNonQuery();
        command.CommandText = "insert into second_table (col_z, col_a) values ('col_z', 'col_a')";
        command.ExecuteNonQuery();
        command.CommandText = "insert into third_table (col_b, col_c) values ('col_b', 'col_c')";
        command.ExecuteNonQuery();
        command.CommandText = "insert into my_table (col1, col2, col3, col_v) values (@col1, @col2, @col3, 'col_v')";
        command.ExecuteNonQuery();

        // Commit the transaction.
        sqlTran.Commit();
    }
    catch (Exception ex)
    {
        // Handle the exception if the transaction fails to commit.
        // do something with this (ex.Message);
        try
        {
            // Attempt to roll back the transaction.
            sqlTran.Rollback();
        }
        catch (Exception exRollback)
        {
            // Throws an InvalidOperationException if the cnx 
            // is closed or the transaction has already been rolled 
            // back on the server.
            // do something with this (exRollback.Message);
        }
    }
}