Yet another geek blog about software development

Main menu

Tag Archives: SqlBulkCopy

This is the first of a 2 series articles about how to perform bulk inserts in your .NET applications, using a SQL Server database.

In this article I’ll show how to create a wrapper object for SqlBulkCopy that can do a bulk insert for a collection of objects. In the second article I’ll show how that wrapper can be easily integrated with Entity Framework (creating extension methods for DbContext and ObjectContext).

The problem

I am working in an application that parses Excel files and creates an object for each line. After some validations and properties mapping the objects are then being inserted in a database table, one by one. At the beggining this wasn’t a big problem because the number of objects to insert in the database was small, but now there can be files with thousands of rows, so using Entity Framework isn’t the best way to do it (currently Entity Framework has no support for bulk insert operations).

The solution

I decided to use the object SqlBulkCopy because it seemed the best and easiest option for performing a bulk insert in a SQL Server database. I created a wrapper for SqlBulkCopy – BulkCopy.cs. This class works the same way as SqlBulkCopy but has some extra features.

The following properties are available:

DestinationTableName: Name of the destination table on the server

BatchSize (optional): Number of rows in each batch. At the end of each batch, the rows in the batch are sent to the server