Sending several SQL commands in a single transaction

Question

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();
    }
}

I see that each ExecuteNonQuery() also executes commit.

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?

Answer 1

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
    }

Answer 2

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
   }
}

Answer 3

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.

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.

Answer 4

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

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();
}

Answer 5

Here is what I use on my daily work, before it a use a foreach for any non-query that I need to run on database. You can see that I'm using the OracleCommand, but if you need you can change to SQL statement

    public static void ExecuteDatabaseNonQuery(string command)
    {
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;

        OracleTransaction transaction;
        transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
        cmd.Transaction = transaction;
        try
        {
            cmd.CommandText = command;
            var update = cmd.ExecuteNonQuery();
            transaction.Commit();

            Console.WriteLine("{0} rows updated", update);
        }
        catch (Exception e)
        {
            transaction.Rollback();
            throw new Exception("Error: " + e);
        }
    }

Note: If theres any uncommited changes on database this method will wait indefinitely

Answer 6

You can use Parallel for each

   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();
        });
    }