我应该如何多次插入多条记录? [英] How should I multiple insert multiple records?

问题描述

我有一个名为 Entry 的类，声明如下:

I have a class named Entry declared like this:

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

然后是一个方法，该方法将接受多个这样的 Entry 对象以使用 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
        ...
    }
}  

我的问题是:我应该继续使用上述发送多个插入语句的方式(构建一大串插入语句及其参数并通过网络发送)，还是应该保持开放连接并发送一个每个 Entry 的单个插入语句，如下所示:

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 添加参数)在循环中更改它们的值并执行命令.这样，语句将只准备一次.您还应该在开始循环之前打开连接并在循环之后关闭它.

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屋！