Use single Insert instead of multiple with big IEnumerable<T> as DataTable in parameters

[t-smirnov]

For now, when we use DataTable as parameter and insert a lot of values into it - Dapper generates smth like this:

insert into @someVar values(@x,@y,@z)
insert into @someVar values(@x1,@y1,@z1)

There is insert statement on every element in collection, but it's very inefficient when collection is too big (e.g. 400-500 elements). Query becomes too slow.
The solution will be to generate one insert statement such as:
insert into @someVar values(1,2,3), (4,5,6)... (n,n,n)

[NickCraver]

Can you provide the C# code you're calling with? I just want to be clear on the codepath here. This would vary by provider, so it's not as trivial as it sounds - though it is a reasonable request.

[alexeyzimarev]

@t-smirnov no code? Would be nice to see some, if we even want this solved...

[phillijw]

I'm thinking using some form of invented template language might be the best way to achieve this and would allow it to be extensible as well. Something like this perhaps?

var sqlTemplate = @"INSERT INTO order (type, price, userId, status) VALUES {template-repeater:(@type, @price, @userId, @status)};";
var queryParameters = new List<DynamicParameters>();

foreach (var order in orders)
{
	var p = new DynamicParameters();
	p.Add("@type", order.Type);
	p.Add("@price", order.Price);
	p.Add("@userId", order.UserId);
	p.Add("@status", order.Status);
	queryParameters.Add(p);
}

await connection.ExecuteTemplateRepeater(sqlTemplate, separator: ",", queryParameters);

This is a fairly important feature and I'd hate to have to keep concatenating strings forever for stuff like this.

[NickCraver]

There are a few ways to do this with IEnumerable or TVPs, but there was never enough info added to help here. I do recommend not concatenating statements into one big one...that's generally not the best approach :)