none
Perform a Set of Update Commands RRS feed

  • Question

  • I need to execute a set of update commands in C# using ExecuteNonQuery

    There are two ways to implement that:

    • Either I loop through the set of commands and execute them one by one, which involves opening/closing the connection on each iteration.
    • Or build a string that holds all the update commands, open a connection, and execute the big string command once, and then close the connection. Which involves a big string being transferred through the connection to SQL Server.

    Is one approach more preferable than the other? Is there any side effect from executing a big string command (let's say it contains about 100 UPDATE commands). Can the string 'SqlCommand.CommandText' be limited to a certain number of characters? Can the second approach (which I assume it faster) cause any security issues when the command string is big? Finally, what's the best approach?



    • Edited by yazanpro Friday, February 7, 2014 7:17 AM
    Friday, February 7, 2014 7:16 AM

Answers

  • Since you are using command strings in both scenarios, the security issues would be the same. I don't believe you will encounter any issues with respect to command length. There was no indication as to whether the updates were dependent upon one another (e.g. transaction).

    Given the number of commands being executed I would opt for executing each one at a time. It will be easier to troubleshoot (identify) any problems when using this approach. Opening and closing a connection each time should not result in a performance issue because of connection pooling.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by yazanpro Monday, February 10, 2014 7:43 AM
    Friday, February 7, 2014 4:27 PM

All replies

  • Since you are using command strings in both scenarios, the security issues would be the same. I don't believe you will encounter any issues with respect to command length. There was no indication as to whether the updates were dependent upon one another (e.g. transaction).

    Given the number of commands being executed I would opt for executing each one at a time. It will be easier to troubleshoot (identify) any problems when using this approach. Opening and closing a connection each time should not result in a performance issue because of connection pooling.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by yazanpro Monday, February 10, 2014 7:43 AM
    Friday, February 7, 2014 4:27 PM
  • Also, even without connection pooling, you don't have to close the connection after each command. eg:

    using (var con = new SqlConnection(...))
    {
      con.Open();
      foreach (var sql in commandList)
      {
        var cmd = new SqlCommand(sql,con);
        cmd.ExecuteNonQuery();
      }
      con.Close();
    }
    

    Also if you are perfoming DDL, there are certain commands that cannot appear in a large batch, and so you will have to issue them as multiple batches.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, February 7, 2014 4:35 PM