SqlBulkCopy — Bulk insert using SqlBulkCopy with C# & ASP.NET

Yasser Shaikh
Nov 4, 2013 · 2 min read

So there was this requirement I was working on recently, where I had to make inserts of around 5,000 to 10,000 into a table all at one go. As I am quite used to using ORMs — Entity framework 5 : I initially implemented this bulk insert using Entity Framework 5. When testing my code with around 5,000 records, it took around 5–6 minutes for my code to insert all the 5,000 records.

6 minutes !!! NOT ACCEPTABLE. Enter SqlBulkCopy…

SqlBulkCopy lets you efficiently bulk load a SQL Server table with data from another source. The code is pretty self-explanatory, questions if any can be asked below in the comment section. Cheers !

FYI : With SqlBulkCopy now it took only 1 to 2 seconds for the same 5,000 inserts :D

Table-BulkCopy
Table-BulkCopy
public class Employee 
{
public int Id { get; set; }
public string Name { get; set; }
public string Email { get; set; }
}
public int BulkInsertIntoEmployees(List<Employee> employees)
{
using (var scope = new TransactionScope())
{
string connectionString = "your-connection-string-here";
var sqlConnection = new SqlConnection(connectionString);
var sqlBulkCopy = new SqlBulkCopy(sqlConnection)
{
DestinationTableName = "Tbl_Employees",
BulkCopyTimeout = 6000
};
var dataTable = GetDataTableForEmployees(employees);
sqlConnection.Open();
sqlBulkCopy.WriteToServer(dataTable);scope.Complete();
sqlBulkCopy.Close();
sqlConnection.Close();
sqlConnection.Dispose();
}
return employees.Count;
}
private DataTable GetDataTableForEmployees(List<Employee> employees)
{
var table = new DataTable();
table.Columns.Add("Id", typeof(int));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Email", typeof(string));
// note : the order of the field is very important
// and should be same as the defined in table structure.
employees.ForEach(data => table.Rows.Add(
data.Id
, data.Name
, data.Email
));
return table;
}

Yasser Shaikh's blog

It's supposed to be automatic, but actually you have to push this button.

Yasser Shaikh

Written by

Yasser Shaikh's blog

It's supposed to be automatic, but actually you have to push this button.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade