我应该如何多次插入多条记录？
我有一个名为 Entry 的类，如下声明：
class Entry{
string Id {get;set;}
string Name {get;set;}
}
然后是一个方法，该方法将接受多个此类 Entry 对象，以便使用 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
...
}
}
我的问题是这样的：我应该继续使用上述发送多个插入语句的方式（构建一个巨大的插入语句及其参数字符串并通过网络发送它），还是应该保持开放连接并为每个 发送一个插入语句像这样的条目：
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性能会有差异吗？我还应该注意任何其他后果吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（11）
如果我是你，我就不会使用它们中的任何一个。
第一个的缺点是，如果列表中存在相同的值，参数名称可能会发生冲突。
第二种方法的缺点是您要为每个实体创建命令和参数。
最好的方法是构造一次命令文本和参数（使用
Parameters.Add添加参数）在循环中更改它们的值并执行命令。这样，该声明将只准备一次。您还应该在开始循环之前打开连接并在循环之后关闭连接。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.Addto 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.真正可怕的方法是将每个
INSERT语句作为自己的批处理执行：批处理1：
批处理2：
批处理3：
批处理4：
批处理5：
注意：出于说明目的，参数化、错误检查和任何其他挑剔都被省略。
这是真正的、可怕的、可怕的做事方式。它确实提供了糟糕性能，因为您每次都会受到网络往返时间的影响。
一个更好的解决方案是将所有
INSERT语句分批放入一个批次中：第 1 批：
这样您只需经历一次往返。该版本具有巨大性能优势；速度快 5 倍左右。
更好的是使用
VALUES子句：与 5 个单独的
INSERT版本相比，这为您带来了一些性能改进；它让服务器可以做它擅长的事情：在集合上操作：一次、 SQL Sever喜欢在<上操作 时才需要操作数据集；这是维京人的地方！
参数限制
为了清楚起见，上面的 T-SQL 示例删除了所有参数化内容。但实际上你想要参数化查询
QuotedString(firstName)的优秀开发人员，但是 Bruno 有一个重要的观点； SQL Server 的驱动程序仅允许您在批处理中包含 2,100 个参数。上面的查询有两个值：
如果您在单个批次中导入 1,051 行，即 2,102 个参数 - 您将收到错误：
这就是为什么我通常一次插入 5 或 10 行。每批添加更多行并不会提高性能；收益递减。
它保持较低的参数数量，因此不会接近 T-SQL 批处理大小限制。还有一个事实是
VALUES子句无论如何都限制为 1000 个元组。实现它
您的第一种方法很好，但确实存在以下问题：
因此，目标是生成一个字符串，例如：
我将通过以下方式更改您的代码我凭感觉
The truly terrible way to do it is to execute each
INSERTstatement as its own batch:Batch 1:
Batch 2:
Batch 3:
Batch 4:
Batch 5:
Note: Parameterization, error checking, and any other nit-picks elided for expoistory purposes.
This is truly, horrible, terrible way to do things. It gives truely awful performance, because you suffer the network round-trip-time every time.
A much better solution is to batch all the
INSERTstatements into one batch:Batch 1:
This way you only suffer one-round trip. This version has huge performance wins; on the order of 5x faster.
Even better is to use the
VALUESclause:This gives you some performance improvements over the 5 separate
INSERTs version; it lets the server do what it's good at: operating on sets:SQL Sever loves to operate on sets of data; it's where it's a viking!
Parameter limit
The above T-SQL examples have all the parameteriztion stuff removed for clarity. But in reality you want to parameterize queries
QuotedString(firstName)But Bruno has an important point; SQL Server's driver only lets you include 2,100 parameters in a batch. The above query has two values:
If you import 1,051 rows in a single batch, that's 2,102 parameters - you'll get the error:
That is why i generally insert 5 or 10 rows at a time. Adding more rows per batch doesn't improve performance; there's diminishing returns.
It keeps the number of parameters low, so it doesn't get anywhere near the T-SQL batch size limit. There's also the fact that a
VALUESclause is limited to 1000 tuples anyway.Implementing it
Your first approach is good, but you do have the issues of:
So the goal is to generate a string such as:
I'll change your code by the seat of my pants
跟进@Tim Mahy - 有两种可能的方法来提供 SqlBulkCopy：DataReader 或通过 DataTable。这里是DataTable的代码：
Following up @Tim Mahy - There's two possible ways to feed SqlBulkCopy: a DataReader or via DataTable. Here the code for DataTable:
您应该在每个循环上执行该命令，而不是构建一个巨大的命令 Text(btw,StringBuilder 就是为此而制作的）
底层连接不会为每个循环关闭并重新打开，让连接池管理器处理这个问题。请查看此链接以获取更多信息：在 ASP.NET 应用程序中调整 ADO.NET 连接池
如果您想确保每个命令都成功执行，您可以使用 事务 和回滚（如果需要），
You should execute the command on every loop instead of building a huge command Text(btw,StringBuilder is made for this)
The underlying Connection will not close and re-open for each loop, let the connection pool manager handle this. Have a look at this link for further informations: Tuning Up ADO.NET Connection Pooling in ASP.NET Applications
If you want to ensure that every command is executed successfully you can use a Transaction and Rollback if needed,
当条目很多时，请考虑使用 SqlBulkCopy。性能比一系列单个插入要快得多。
When it are a lot of entries consider to use SqlBulkCopy. The performance is much faster than a series of single inserts.
如果
DataTable创建正确，您可以直接插入它。首先确保访问表列具有相同的列名和相似的类型。然后你就可以使用这个功能，我相信它非常快速和优雅。
You can directly insert a
DataTableif it is created correctly.First make sure that the access table columns have the same column names and similar types. Then you can use this function which I believe is very fast and elegant.
只需格式化查询字符串即可添加所有要插入的值集。
像这样的东西 -
Just format the query string to add all set of values to be inserted.
Something like this -
考虑使用 TransactionScope。作用域内的任何连接和查询执行都将自动包装到事务中，您所需要做的就是在最后调用scope.Complete()。如果出现问题，里面的所有执行都会回滚。更简单、更好的代码。阅读 MS 文档并查看图示示例。
https://learn.microsoft。 com/en-us/dotnet/api/system.transactions.transactionscope?view=net-8.0
Consider using TransactionScope. Any connection and query execution inside the scope will automatically be wrapped into the transaction and all you need to do is call scope.Complete() at the end. If something goes wrong all the executions inside will be rolled back. Much simpler and nicer code. Read the MS doc and see the illustrated example.
https://learn.microsoft.com/en-us/dotnet/api/system.transactions.transactionscope?view=net-8.0
使用单次插入插入多条记录的存储过程：
代码隐藏：
Stored procedure to insert multiple records using single insertion:
Code behind: