在单个事务中发送多个SQL命令 [英] Sending several SQL commands in a single transaction

问题描述

我有大量的 INSERT INTO ... 字符串列表.目前，我通过以下方式运行它们:

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

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

我看到每个 ExecuteNonQuery()都执行提交.

I see that each ExecuteNonQuery() also executes commit.

1. 有没有办法在单个事务中插入所有行(最后提交)?
2. 我希望进行单笔交易的原因是为了使我的插入"过程更快.单笔交易也会更快吗?

推荐答案

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

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

    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
    }

这篇关于在单个事务中发送多个SQL命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！