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

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

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;
}