One of the things that really annoys me is that bulk inserts in .NET Core are such a pain in the ass. It seems like every database has a completely different way to perform bulk inserts, if they support it at all.

And after I finally get it working, inevitably some requirement changes and I need to get back the list of newly inserted keys. That means switching to batch inserts, with all the annoying SQL generation and parameter handling.

So I decided to fix it. One of the nice things about running my own ORM project is that I can bolt on things to fix what annoys me. And now I can do batch and bulk inserts into SQL Server, PostgreSQL, SQLite, and MySQL with only 2 lines of code.

Bulk Inserts

var dataSource = new XxxDataSource(connectionString);

dataSource.InsertBulk(employeeList).Execute();

If you want async, simply modify the last link.

await dataSource.InsertBulk(employeeList).ExecuteAsync();

If the table name doesn’t match the class name, either use a Table attribute or explicitly pass in the table name.

[Table(“Employee”, Schema = “HR”)] public class Employee

dataSource.InsertBulk(“HR.Employee”, employeeList).Execute();

If you need to modify the batch sizes for performance (SQL Server) or database limitations (MySQL), add the `WithBatchSize` link.

dataSource.InsertBulk(employeeList).WithBatchSize(250).Execute();

If you want to get back the row count, add the `AsCount` link.

dataSource.InsertBulk(employeeList).AsCount().Execute();

If you want to track progress, include the WithNotifications link.

dataSource.InsertBulk(employeeList).WithNotifications(eventHandler, rowsPerEvent).Execute();

Note: Currently WithNotifications is only for SQL Server. It is still in the planning phase for PostgreSQL and MySQL.

If you need to override the primary keys in SQL Server, also known as identity insert, the normal bulk copy options are available.

dataSource.InsertBulk(employeeList, SqlBulkCopyOptions.KeepIdentity).Execute();

This illustrates one of the design principals of Chain. While it does try to offer equivalent functionality across all databases, it also exposes database-specific features when you need more control.

Batch Inserts

Switching from bulk (i.e. database specific) to batch (i.e. SQL) inserts is as easy as changing one link in the chain.

dataSource.InsertBatch(employeeList).Execute();

Once you are in batch mode, you can do things like return all of the newly generated primary keys. Simply add a “ToXxx” link with the correct data type for your primary key.

dataSource.InsertBatch(employeeList).ToInt32().Execute();

If you would prefer to get back the whole record rather than just the PKs, you can do that too.

dataSource.InsertBatch(employeeList).ToCollection<Employee>().Execute();

For some databases, the maximum number of parameters may become a problem. In this case you’ll need to use the “InsertMultipleBatch” command instead. This is more limited than “InsertBatch” in that it should be used in a transaction and can only return the number of inserted rows.

using (var trans = dataSource.BeginTransaction())

{

trans.InsertMultipleBatch(employeeList).ToCollection<Employee>().Execute();

trans.Commit();

}

All of these examples use Tortuga Chain 3.1. When installing from NuGet.org, look for the database specific package.