Bulk Inserting Data With Dapper

Dapper doesn't proved a formal API for performing bulk inserts along the lines of SqlBulkCopy provided by ADO.NET SqlClient provider, for example, but it is possible to bulk load data by passing a collection of parameters to the Execute method.

In this example, the parameters are passed as an array of anonymous types.

var sql = "insert into categories (CategoryName, Description) values (@CategoryName, @Description)";
using (var connection = new SQLiteConnection(connString))
{
    var newCategories = new[]{
        new {CategoryName = "New Category 1", Description = "Description 1"},
        new {CategoryName = "New Category 2", Description = "Description 2"},
        new {CategoryName = "New Category 3", Description = "Description 3"},
        new {CategoryName = "New Category 4", Description = "Description 4"},
    };
    var affectedRows =  connection.Execute(sql, newCategories);
    Console.WriteLine($"Affected Rows: {affectedRows}");
}

Image

Note that this approach is not very efficient and should only be used for a small number of inserts. For larger and regular operations, you should use the formal bulk insert API where your provider offers one. Note that if you are using SQLite, there isn't one.

You can use the same approach (passing collections of parameters) to execute multiple UPDATE and DELETE statements too.

Last updated: 21/05/2019 13:52:13