How to insert a C# List to database using Dapper.NET
You'd have to do it a little differently. In Dapper, it matches on convention AKA property or field names being identical to SQL parameters. So, assuming you had a MyObject
:
public class MyObject
{
public int A { get; set; }
public string B { get; set; }
}
And assuming processList = List<MyObject>
, You'd want to do this
foreach (var item in processList)
{
string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@A, @B)";
connection.Execute(processQuery, item);
}
Note that the MyObject
property names A and B match the SQL parameter names @A and @B.
If you don't want to rename objects, you can use anonymous types to do the mappings instead of concrete types:
foreach (var item in processList)
{
string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@A, @B)";
connection.Execute(processQuery, new { A = item.A, B = item.B });
}
EDIT:
Per Marc Gravell's comment, you can also have Dapper do the loop for you:
string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@A, @B)";
connection.Execute(processQuery, processList);
I believe the bulk insert is better than iterate the list and insert one by one.
SqlTransaction trans = connection.BeginTransaction();
connection.Execute(@"
insert PROCESS_LOGS(Id, st_Time, ed_Time, td_Time)
values(@Id, @st_Time, @ed_Time, @td_Time)", processList, transaction: trans);
trans.Commit();
Reference: https://stackoverflow.com/a/12609410/1136277