I want insert var list = new List<string>{"Name 1", "Name 2"};
into a database table.
I make script for using in dapper
var sqlScript = "USE [LetoSnipment] INSERT INTO Card(Name, Id) VALUES(@Key, 1);"
And I'm Executing script to insert data into a table
db.Execute(sqlScript, list);
How should I write SQL(dapper)script to insert a string collection into a table?
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