3

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.

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.

Joe Phillips
  • 49,743
  • 32
  • 103
  • 159

2 Answers2

3

No it doesn't.

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

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);

And, you pass in DynamicParameters something like below:

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

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.

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. 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.
Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
  • This approach works like a charm. If you have SQL server on a remote machine, then string building costs are negligible to the communication overhead. If you have too many rows, just insert them by chunks. – xmedeko Nov 16 '18 at 15:57
  • 2
    This project already does it: https://faithlife.github.io/DapperUtility/BulkInsert.html – xmedeko Nov 16 '18 at 16:09
0

As Amit Joshi said Dapper doesn't support this syntax.

What I can recommend to you from my experience, the most efficient way is to work with a stored procedure and passing a Table Valued Parameters.
If you have a list, you can use this nuget to convert the list to DataTable.

Disclosure, the nuget I suggested is mine

Daniel Tshuva
  • 483
  • 4
  • 12