我应该怎样多个插入多条记录？ [英] How should I multiple insert multiple records?

问题描述

我有一类名为输入声明是这样的：

I have a class named Entry declared like this:

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

然后，将接受多个这样的输入对象插入到数据库使用ADO.NET的方法：

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

和我的问题是：我应该继续使用发送多个insert语句（建立insert语句的一个巨大的字符串和参数，并通过网络发送）以上的方式，或者我应该保持开放的连接，并发送每个单个插入语句输入是这样的：

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

你怎么看？会不会有这两者之间在SQL Server的性能差异？是否有任何其他后果，我应该知道的？

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?

推荐答案

如果我是你，我不会使用其中任一。

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.

最好的办法是让命令文本和建造一旦参数（使用 Parameters.Add 添加参数）改变环路他们的价值观和执行命令。这样的声明将是ppared只有一次$ P $。你也应该打开连接启动循环之前和之后关闭它。

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.

这篇关于我应该怎样多个插入多条记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！