Dapper - 批量插入新项目并获取新ID [英] Dapper - Bulk insert of new items and get back new IDs

问题描述

我使用dapper使用此方法在一个db命中添加多个新学生：

I am using dapper to add multiple new students in one db hit using this method:

db.ExecuteAsync(@"INSERT Student(Name,Age) values (@Name,@Age)", 
  students.Select(s => new { Name = s.Name, Age = s.Age })
);

但问题我没有新的ID。

But the problem I don't have the new ids.

我可以创建一个数据库命中，还有一些如何获取新的ID？

如果没有，什么是最有效的方式执行批量插入？

Can I make one db hit and still some how get the new ids ?
And if not, what is the most efficient way of performing such bulk insert ?

推荐答案

这是它基本上只是缩写循环;虽然有趣（可能）是可以请求它管道的顺序如果MARS在您的连接上启用。没有管道，这是基本上的缩写：

That is not a bulk insert; it is basically just shorthand that unrolls the loop; although interestingly (perhaps) it is possible to ask it to pipeline that sequence if "MARS" is enabled on your connection. Without the pipeline, it is basically shorthand for:

foreach(var obj in students.Select(s => new { Name = s.Name, Age = s.Age }))
{
    await db.ExecuteAsync(@"INSERT Student(Name,Age) values (@Name,@Age)", obj);
}

在这种情况下，您可以使用 Query 或或 ExecuteScalar 以获取 SCOPE_IDENTITY（）。

in which case, you might as well use Query or ExecuteScalar to fetch the SCOPE_IDENTITY().

使用管道，它更加微妙;它做同样的事情，但有一个时间多个未完成的命令（它只有 await 时，当积压已满，或所有的命令已发出）。

With the pipeline, it is more subtle; it does the same thing, but with multiple outstanding commands at a time (it only awaits when the backlog is full, or when all of the commands have been issued).

批量插入不返回ids。您可以考虑使用表值参数，并使用 OUTPUT 子句插入 INSERT 一个整个 DataTable （获取进程中的身份），但在负面：它涉及使用 DataTable ; p

Bulk insert does not return ids. You could consider using a table valued parameter and using INSERT with the OUTPUT clause to insert an entire DataTable of data at a time (getting the identities in the process), but on the negative side: it does involve using DataTable ;p

这篇关于Dapper - 批量插入新项目并获取新ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！