A lot of times we come across a requirement to bulk insert data in SQL server. Now there are several ways to bulk insert data in SQL server table but in this example I’ll use SqlBulkCopy.

Why use SqlBulkCopy

  • SqlBulkCopy is super fast as compared to other data inserts.
  • You can specify different strategies for data insert & you can use multiple instances of SqlBulkCopy at the same time to work more efficiently.
  • you can use the Table lock option in SqlBulkCopy so that while updating it lock table instead of row (which is the default). This will massive performance gain while inserting a lot of records or loading data in a heap table. (Don’t use this option with clustered indexes)

In this example i have created a data table. I have added payload in datatable and SqlBulkCopy uses Writetoserver to insert this datatable in sqlserver

 DataTable dt = new DataTable("Products_Temp");
 dt = ConvertToDataTable(list); //object list is my dto
using (SqlConnection conn = new SqlConnection("your-connection-string"))
            {
                using (SqlCommand command = new SqlCommand("", conn))
                {
                    try
                    {
                        conn.Open();
                        //Bulk insert into temp table
                        using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                        {
                            bulkcopy.BulkCopyTimeout = 660;
                            bulkcopy.DestinationTableName = "Products_Temp";
                            bulkcopy.WriteToServer(dt);
                            bulkcopy.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.ToString());
                    }
                    finally
                    {
                        conn.Close();
                        Console.WriteLine(list.Count + " Records updated");
                    }
                }
            }

Conclusion

I’m using SqlBulkCoy for a long time and this class is one of my favorite classes. Although SqlBulkCopy normally uses to insert data, you can use it to do bulk updates as well. For example load data in the temp or staging table and after that run update statement to update the target table.

Categorized in:

.NET

Tagged in:

,