Insert an List using dapper

Question

Insert an List using dapper

c# dapper sql sql-server

Question

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?

ÐÐ½Ð´Ñ€ÐµÐ¹ ÐœÐ¾Ñ€ÑÐºÐ¾Ð²

Popular Answer

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

Thalles Noce

View more on Stack Overflow

Haney · Accepted Answer · 7/26/2017 10:23:59 AM

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);

Insert an List using dapper

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator