How should I multiple insert multiple records?

Question

I have a class named Entry declared like this:

class Entry{     string Id {get;set;}     string Name {get;set;} }   

and then a method that will accept multiple such Entry objects for insertion into the database using ADO.NET:

static void InsertEntries(IEnumerable<Entry> entries){     //build a SqlCommand object     using(SqlCommand cmd = new SqlCommand()){         ...         const string refcmdText = "INSERT INTO Entries (id, name) VALUES (@id{0},@name{0});";         int count = 0;         string query = string.Empty;         //build a large query         foreach(var entry in entries){             query += string.Format(refcmdText, count);             cmd.Parameters.AddWithValue(string.Format("@id{0}",count), entry.Id);             cmd.Parameters.AddWithValue(string.Format("@name{0}",count), entry.Name);             count++;         }         cmd.CommandText=query;         //and then execute the command         ...     } }   

And my question is this: should I keep using the above way of sending multiple insert statements (build a giant string of insert statements and their parameters and send it over the network), or should I keep an open connection and send a single insert statement for each Entry like this:

using(SqlCommand cmd = new SqlCommand(){     using(SqlConnection conn = new SqlConnection(){         //assign connection string and open connection         ...         cmd.Connection = conn;         foreach(var entry in entries){             cmd.CommandText= "INSERT INTO Entries (id, name) VALUES (@id,@name);";             cmd.Parameters.AddWithValue("@id", entry.Id);             cmd.Parameters.AddWithValue("@name", entry.Name);             cmd.ExecuteNonQuery();         }     }  }   

What do you think? Will there be a performance difference in the Sql Server between the two? Are there any other consequences I should be aware of?

Answer 1

static void InsertSettings(IEnumerable<Entry> settings) {     using (SqlConnection oConnection = new SqlConnection("Data Source=(local);Initial Catalog=Wip;Integrated Security=True")) {         oConnection.Open();         using (SqlTransaction oTransaction = oConnection.BeginTransaction()) {             using (SqlCommand oCommand = oConnection.CreateCommand()) {                 oCommand.Transaction = oTransaction;                 oCommand.CommandType = CommandType.Text;                 oCommand.CommandText = "INSERT INTO [Setting] ([Key], [Value]) VALUES (@key, @value);";                 oCommand.Parameters.Add(new SqlParameter("@key", SqlDbType.NChar));                 oCommand.Parameters.Add(new SqlParameter("@value", SqlDbType.NChar));                 try {                     foreach (var oSetting in settings) {                         oCommand.Parameters[0].Value = oSetting.Key;                         oCommand.Parameters[1].Value = oSetting.Value;                         if (oCommand.ExecuteNonQuery() != 1) {                             //'handled as needed,                              //' but this snippet will throw an exception to force a rollback                             throw new InvalidProgramException();                         }                     }                     oTransaction.Commit();                 } catch (Exception) {                     oTransaction.Rollback();                     throw;                 }             }         }     } }