Sending Several Sql Commands In A Single Transaction

March 31, 2024 Post a Comment

I have a huge list of INSERT INTO ... strings. Currently I run them with: using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); forea

Solution 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
    {
        SqlConnectionconnection=newSqlConnection(connectionString);
        connection.Open();

        trans = connection.BeginTransaction(); 

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

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

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

Solution 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.

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

Solution 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

publicstaticvoidExecuteDatabaseNonQuery(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();
            thrownew Exception("Error: " + e);
        }
    }

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

Lab Sql