SQL Bulk Copy

I am re-posting this from an article I posted on December 3rd, 2015 on Geekswithblogs.net

Omnitech has a weekly lunch and learn for developers, and devoting this hour together is important for many reasons. We are all working on different projects for multiple clients, although sometimes small teams from our company work together on projects. During our lunches, we share tips we’ve gained from experiences and technologies that will better the team.

The topic that came up as we were looking at SQL 2016 Always On encryption (which looks very useful and slick) was SQL Bulk Copy. I asked Chad if I could share his example code he emailed out later that day. Thanks Chad for giving permission to share this!

Yesterday at lunch I briefly mentioned that the use of the .RemoveRange() and .AddRange() LINQ extensions creates individual delete or insert (respectively) SQL statements for each item in the collection. As we also briefly discussed, a way to avoid that is to use bulk operations – context.database.ExecuteSqlCommand for the delete and SqlBulkCopy for the insert.

Here is a code snippet with both items included:

using (var context = new GisContext())
{
    context.Database.ExecuteSqlCommand("DELETE FROM CountyParcel WHERE County = @County", new SqlParameter("@County", county));
}

var bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["GisContext"].ConnectionString)
{
    DestinationTableName = "CountyParcel"
};

bulkCopy.WriteToServer(data.ToDataTable());
bulkCopy.Close();

public static DataTable ToDataTable<T>(this List<T> list)
{
    var dataTable = new DataTable(typeof(T).Name);
    var properties = typeof (T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (var property in properties)
    {
        dataTable.Columns.Add(property.Name);
    }
    foreach (var item in list)
    {
        var values = new object[properties.Length];

        for (var i = 0; i < properties.Length; i++)
        {
            values[i] = properties[i].GetValue(item, null);
        }

        dataTable.Rows.Add(values);
    }

    return dataTable;
}

There was some also some interesting email back and forth on when SQL Bulk Copy should be used.

“What size of data are we talking here? I wouldn’t be using SqlBulkCopy as a standard practice and should probably be used sparingly. If you have tons of data to go through I would probably look at other options like SSIS or other ETL practices.”

“The scenario in which I am using it (console application as a scheduled job), one of the .csv files being imported is upwards of 71000 rows. I’m sure SSIS could be a viable alternative but I was not looking to fully re-write what was already in place…just fix it and make it a little easier to maintain.Smile”

“Another thing i just thought of if you cant do SSIS is to create a stored proc that uses the BULK INSERT in TSQL and then just have .Net call the proc. You could pass of params if needed. This off loads the work to Sql. Let Sql do what its good at. :)”

Lessons learned

Share information with the people you work with and you’ll come up with a better solution than you would on your own and learn something new. Others will learn from your interactions. Spend time together to build up your team, a lunch and learn is one way (who doesn’t like company provided food?) SQL Bulk Copy is better than RemoveRange() and .AddRange() LINQ extensions creates individual delete or insert for a lot of records (seconds instead of minutes or even hours) Posted on Thursday, December 3, 2015 3:41 PM | Back to top