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

Stored procedure to insert multiple records using single insertion:

ALTER PROCEDURE [dbo].[Ins]
@i varchar(50),
@n varchar(50),
@a varchar(50),
@i1 varchar(50),
@n1 varchar(50),
@a1 varchar(50),
@i2 varchar(50),
@n2 varchar(50),
@a2 varchar(50) 
AS
INSERT INTO t1
SELECT     @i AS Expr1, @i1 AS Expr2, @i2 AS Expr3
UNION ALL
SELECT     @n AS Expr1, @n1 AS Expr2, @n2 AS Expr3
UNION ALL
SELECT     @a AS Expr1, @a1 AS Expr2, @a2 AS Expr3
RETURN

Code behind:

protected void Button1_Click(object sender, EventArgs e)
{
    cn.Open();
    SqlCommand cmd = new SqlCommand("Ins",cn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@i",TextBox1.Text);
    cmd.Parameters.AddWithValue("@n",TextBox2.Text);
    cmd.Parameters.AddWithValue("@a",TextBox3.Text);
    cmd.Parameters.AddWithValue("@i1",TextBox4.Text);
    cmd.Parameters.AddWithValue("@n1",TextBox5.Text);
    cmd.Parameters.AddWithValue("@a1",TextBox6.Text);
    cmd.Parameters.AddWithValue("@i2",TextBox7.Text);
    cmd.Parameters.AddWithValue("@n2",TextBox8.Text);
    cmd.Parameters.AddWithValue("@a2",TextBox9.Text);
    cmd.ExecuteNonQuery();
    cn.Close();
    Response.Write("inserted");
    clear();
}

Answer 2

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