Dapper 是否支持在单个查询中插入多行?

2022-01-15 00:00:00 mariadb mysql dapper

StackExchange.Dapper 是否支持以下 SQL 语法?

Does StackExchange.Dapper support the following SQL syntax?

INSERT INTO MyTable (a, b, c)
VALUES
  (1, 2, 3),
  (4, 5, 6),
  (7, 8, 9);

我看过一些示例，您可以传入要插入的列表，但我看到的描述表明它只是循环并执行多次插入.

I've seen some examples where you can pass in a List to be inserted but the descriptions I've seen suggest it is just looping and doing multiple inserts.

我的研究表明，在其中执行包含多行的单个查询会更快，所以我很想知道 Dapper 是否支持列表.

My research suggests doing a single query with multiple rows in it is faster so I'm curious to know if Dapper supports this with a list or not.

推荐答案

不.

实际上，批量插入是讨论最多的问题之一.我从来没有遇到过您正在使用 Dapper 寻找的解决方案.

Actually, bulk insert is one of the most discussed issue. I never came across the solution you are looking for with Dapper.

我可以想象的一个技巧(不确定；从未尝试过)是通过 DynamicParameters 替换您的实际值(1、2、3....).所以你的查询变成了下面这样:

One hack I can imagine (not sure; never tried) is to pass DynamicParameters replacing your actual values (1, 2, 3....). So your query becomes something like below:

INSERT INTO MyTable (a, b, c)
VALUES
  (@1, @2, @3),
  (@4, @5, @6),
  (@7, @8, @9);

并且，您传入 DynamicParameters 如下所示:

And, you pass in DynamicParameters something like below:

var param = new DynamicParameters();
param.Add("@1", ...);
param.Add("@2", ...);
param.Add("@3", ...);
param.Add("@4", ...);
....

正如我上面所说，这是我想象的；我还没有尝试过.即使这样可行，这也不是一个好的解决方案，因为字符串构建成本会很高，并且管理太多参数会很棘手.此外，在 RDBMS 方面，您可以传递多少个最大参数是有限制的.所以，我不推荐这个.

As I said above, this is what I imagine; I have not tried it. Even if this works, this will not be a good solution as string building cost will be high and managing too many parameters will be tricky. Also, there is limitation on RDBMS side how many maximum parameters you can pass. So, I am not recommending this.

如果记录数不是太多或者如果性能不是那么关键(仍然很重要；我同意)，则将 List 传递给 INSERT 查询(如您提到有问题)效果很好.在 Transaction 中包装 Execute 调用可能会有所帮助.

If number of records are not too much OR if performance is not that critical (still important; I agree), passing in the List to INSERT query (as you mentioned in question) works great. Wrapping Execute call in Transaction may help.

否则，一般推荐以下解决方案:

Otherwise, following solutions are generally recommended:

  1. 绕过小巧玲珑；使用 ADO.NET.
  2. 使用带有用户定义表参数的存储过程.
  3. 使用 Dapper 传递表值参数
  4. 使用其他工具，例如 SqlBulkCopy, Dapper Plus, MicroOrm.Dapper.Repositories 等
    我从未使用过这些工具；所以我不知道它们的性能或其他缺点.

相关文章