bottlenecked bottlenecked - 2 years ago 133
C# Question

How should I multiple insert multiple records?

I have a class named

declared like this:

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

and then a method that will accept multiple such
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);
//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
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);

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 Source

If I were you I would not use either of them.

The disadvantage of the first one is that the parameter names might collide if there are same values in the list.

The disadvantage of the second one is that you are creating command and parameters for each entity.

The best way is to have the command text and parameters constructed once (use Parameters.Add to add the parameters) change their values in the loop and execute the command. That way the statement will be prepared only once. You should also open the connection before you start the loop and close it after it.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download