Bulk insert or update within same function with Dapper

Question

I want to create a function in my Data Access Layer that will loop through a list of contacts and either INSERT or UPDATE based on whether the contact is new or existing.

I know with Dapper you can do a bulk insert by simply creating an INSERT Statement and passing it an object list. Is it possible to do the same with a BULK Update? Or even better yet a BULK InsertOrUpdate.

Here is my solution, is there a better way? I really didn't want to have to call the execute function once for each contact in the list.

StringBuilder insertSql = new StringBuilder();
insertSql.AppendLine("INSERT INTO Contact (FirstName, LastName, Email ");
insertSql.AppendLine("VALUES(@FirstName, @LastName, @Email);");

StringBuilder updateSql = new StringBuilder();
updateSql.AppendLine("UPDATE Contact ");
updateSql.AppendLine("SET FirstName = @FirstName, ");
updateSql.AppendLine("    LastName = @LastName, ");
updateSql.AppendLine("    Email = @Email ");
updateSql.AppendLine("WHERE Id = @Id");

foreach (Contact contact in contacts)
{
    if (contact.Id == 0)
    {
        context.Database.Connection.Execute(insertSql.ToString(), contact);
    }
    else
    {
        context.Database.Connection.Execute(updateSql.ToString(), contact);
    }
}

Answer 1

I can't speak to Dapper, I took a quick look at it and it seems Dapper would need to support MERGE functionality first, which it may not. But, given what you said above, this is what I would do.

First, use Dapper to bulk insert the contacts to some temp table, then build your MERGE statement and execute it.

Let's call the table temp1. In temp1, row with id=2 will replace row with id=2 in contacts, and row with id=3 will be inserted into contacts.

create table contacts (id int, alias varchar(10))
create table temp1 (id int, alias varchar(10))
go

insert into temp1 values (2, 'nameX'), (3, 'name3')
insert into contacts values (1, 'name1'), (2, 'name2')
go

MERGE contacts c
USING temp1 t
ON (c.id = t.id) 
WHEN NOT MATCHED BY TARGET
    THEN INSERT(id, alias) VALUES(t.id, t.alias)
WHEN MATCHED 
    THEN UPDATE SET c.alias = t.alias
;
go

select * from contacts
go