Is it possible to insert many rows in a single query using Dapper?

dapper mariadb mysql

Question

Can I use the following SQL syntax with StackExchange.Dapper?

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

Although there are several cases where you may provide a List to be added, the descriptions I've heard imply that it is just looping and inserting many times.

I want to know whether Dapper enables making a single query with numerous rows in it or not since my study indicates that doing so is quicker.

1
3
4/17/2018 2:07:40 PM

Popular Answer

Not at all, no.

Actually, one of the most talked-about issues is bulk insert. I've never found Dapper to be the answer you're searching for.

One trick I can think of (but haven't tested) is to passDynamicParameters the values you really have (1, 2, 3....). Your question then takes the form of:

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

And you enter.DynamicParameters similar to the following

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 it succeeds, this won't be a good solution since it will be expensive to generate the string and challenging to manage too many arguments. There are also restrictions on the maximum number of parameters you can send from the RDBMS side. Therefore, I do not advise doing this.

If the quantity of data is reasonable OR if the performance is not crucial (essential, I agree), passing in theList to INSERT It works wonderfully, as you mentioned in inquiry. WrappingExecute It could be helpful to dial zz-52 zz.

Otherwise, the following options are often advised:

  1. Use ADO.NET rather than Dapper.
  2. Use a user-defined table parameter with a stored method.
  3. Stylishly Passing Table Valued Parameters
  4. Use a different tool, such as SqlBulkCopy, Stylish Plus, or MicroOrm.Dapper.Repositories.
    Since I have never used such tools, I am unaware of their functionality or any potential limitations.
2
4/18/2018 2:09:08 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow