I am using C# Dapper with MYSQL.
I have a list of classes that i want to insert into MySQL table.
I used to do it using TVP in MS SQL, how do we do it in MySQL.
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
INSERT query (as you mentioned in question) works great. Wrapping
Execute call in Transaction may help.
Otherwise, following solutions are generally recommended: