C# 在单个事务中发送多个 SQL 命令

Sending several SQL commands in a single transaction

提问by Bick

I have a huge list of INSERT INTO ...strings. Currently I run them with:

我有一个很大的INSERT INTO ...字符串列表。目前我运行它们：

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    foreach (var commandString in sqlCommandList)
    {
        SqlCommand command = new SqlCommand(commandString, connection);
        command.ExecuteNonQuery();
    }
}

I see that each ExecuteNonQuery()also executes commit.

我看到每个人ExecuteNonQuery()也执行提交。

1. Is there a way to insert all rows in a single transaction (commit in the end)?
2. The reason I want a single transaction is to make my "inserts" process faster. Will a single transaction also make it quicker?

1. 有没有办法在单个事务中插入所有行（最后提交）？
2. 我想要单笔交易的原因是让我的“插入”过程更快。单笔交易也会使其更快吗？

回答by Shyam sundar shah

You can use Parallel for each

您可以为每个使用 Parallel

   using (SqlConnection connection = new SqlConnection(connectionString))
    {
        List<string> sqlCommandList = new List<string>();
        connection.Open();
        Parallel.ForEach(sqlCommandList, commandString =>
        {
            SqlCommand command = new SqlCommand(commandString, connection);
            command.ExecuteNonQuery();
        });
    }

回答by Mohammad abumazen

Its recommended to use SQL transaction in case you are executing Multiple queries in one thread , you can have it like this :

如果您在一个线程中执行多个查询，建议使用 SQL 事务，您可以这样使用：

    SqlTransaction trans; 

    try
    {
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();

        trans = connection.BeginTransaction(); 

        foreach (var commandString in sqlCommandList)
        {
            SqlCommand command = new SqlCommand(commandString, connection,trans);
            command.ExecuteNonQuery();
        }

        trans.Commit(); 
    }
    catch (Exception ex) //error occurred
    {
        trans.Rollback();
        //Handel error
    }

回答by Gerardo H

You might probably gain some performance by using just one single transaction and command, as follows:

您可能只使用一个事务和命令就可以获得一些性能，如下所示：

using (SqlConnection connection = new SqlConnection(connectionString))
{
   try
   {
      connection.Open();

      using (SqlTransaction trans = connection.BeginTransaction())
      {
          using (SqlCommand command = new SqlCommand("", connection,trans))
          {
             command.CommandType = System.Data.CommandType.Text;

             foreach (var commandString in sqlCommandList)
             {
                command.CommandText = commandString;
                command.ExecuteNonQuery();
             }
          }

          trans.Commit();
       }        
    }
    catch (Exception ex) //error occurred
   {
       //Handel error
   }
}

回答by Arkitec

A little late, but if you are inserting all of the values into the same table, code the SQL insert as "insert into tablex (f1, f2, f3,...) values (@F1,@F2,@F3...)". Create the command and add the parameters @F1..., and then set the Prepare flag on the command. Now as you loop through your list of values to insert, you can set them into the appropriate parameters and then do the ExecuteNonQuery. SQL will pre-parse the command string once, and then use the new parameters each time. This is a bit faster.

有点晚了，但是如果您将所有值插入同一个表中，请将 SQL 插入编码为“插入 tablex (f1, f2, f3,...) 值 (@F1,@F2,@F3.. .)”。创建命令并添加参数@F1...，然后在命令上设置准备标志。现在，当您遍历要插入的值列表时，您可以将它们设置为适当的参数，然后执行 ExecuteNonQuery。SQL 将预解析命令字符串一次，然后每次使用新参数。这有点快。

Finally, you can execute multiple SQL statements in a single command by appending ';' to each statement, if you must execute the entire string. You can bunch a number of these commands together and make one request to SQL server to execute them.

最后，您可以通过附加“;”在单个命令中执行多个 SQL 语句 到每个语句，如果您必须执行整个字符串。您可以将许多这些命令组合在一起，并向 SQL 服务器发出一个请求以执行它们。

回答by Arjan

You can just concatenate the sql and let the server handle it:

您可以连接 sql 并让服务器处理它：

using (SqlConnection connection = new SqlConnection(connectionString))
{
    string lsSql = string.Empty;
    foreach (var commandString in sqlCommandList)
    {
        lsSql = lsSql + commandString + " ; " + Environment.NewLine;
    }

    connection.Open();
    SqlCommand command = new SqlCommand(lsSql, connection);
    command.ExecuteNonQuery();
}