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查询(就像您提到的)效果很好.在交易中包装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. 绕过Dapper；使用ADO.NET.
2. 将存储过程与用户定义的表"参数一起使用.
3. 通过Dapper传递表值参数
4. 使用其他工具，例如 SqlBulkCopy ， Dapper Plus ， MicroOrm.Dapper.Repositories 等.
   我从来没有使用过那些工具.所以我不知道它们的性能或其他缺点.

1. Bypass Dapper; use ADO.NET.
2. Use stored procedure with User Defined Table parameter.
3. Passing Table Valued Parameters with Dapper
4. Use some other tool like SqlBulkCopy, Dapper Plus, MicroOrm.Dapper.Repositories etc.
   I have never used those tools; so I am not aware about their performance or other drawbacks.

这篇关于Dapper是否支持在单个查询中插入多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！