SQL Bulk Upload for Inserts or Updates

Using SqlBulkCopy() instead of Entity Frameworks for fast bulk insert or update operations.

 

Create a datatable and a temporary table on the database.
Entity Frameworks 6 is great for most things, but for bulk inserts or updates it is very slow (this has been improved in Entity Frameworks Core). There are some good workarounds available for deleting and recreating the data context every 100 updates, and that greatly improves the performance, but for hundreds of thousands of rows, it is still too slow. SqlBulkCopy() is very fast for these large insert or update batch operations.

First convert a list of objects to a datatable by adding a row in the datatable for each object in the list and adding a column in the datatable for each of the object's properties, then copy the data from the list to the datatable.

Then, open a connection to the database and create a temporary table to hold the datatable's data.
 
var dt = ConvertToDatatable(list);

using (var conn = new SqlConnection("DatabaseConnectionString"))
{
  using (var command = new SqlCommand("", conn))
  {
    try
    {
      conn.Open();

      // Creating temp table on database
      command.CommandText = "CREATE TABLE #TmpTable([Primary_ID] [int] NOT NULL, [Some_TX] [varchar](10) NULL)";
      command.ExecuteNonQuery();
 
Upload the data to the temporary table, then perform the SqlBulkCopy update.
Using SqlBulkCopy(), upload the datatable's data to the temporary table.

Then execute a SQL command to update the main table's data from the temporary table.

Finally drop the temporary table.
 
      // Bulk insert into temp table
      using (var bulkcopy = new SqlBulkCopy(conn))
      {
         bulkcopy.BulkCopyTimeout = 660;
         bulkcopy.DestinationTableName = "#TmpTable";
         bulkcopy.WriteToServer(dt);
         bulkcopy.Close();
       }

       // Updating destination table, and dropping temp table
       command.CommandTimeout = 300;
       command.CommandText = "UPDATE MainTbl SET Prim.Some_TX = Temp.Some_TX "+
                             "FROM Primary_TB Prim "+
                             "INNER JOIN #TmpTable Temp ON (Temp.Primary_ID = Prim.Primary_ID) "+
                             "DROP TABLE #TmpTable";
       command.ExecuteNonQuery();
     }
     catch (Exception ex)
     {
        // Handle exception
        throw;
     }
     finally
     {
       conn.Close();
     }
  }
}
 
Convert a List Collection to a DataTable
Method to create a datatable from a list.
 
private DataTable ConvertToDatatable(List<some_class> list)
{
  var dt = new DataTable();

  try
  {
    dt.Columns.Add("Key_Col");
    dt.Columns.Add("Data_Col");

    foreach (var item in list)
    {
      var row = dt.NewRow();

      row["Key_Col"] = item.Key_Col;
      row["Data_Col"] = item.Data_Col;

      dt.Rows.Add(row);
    }
  }
  catch (Exception e)
  {
    // Handle
    throw;
  }
  return dt;
}