Introduction:

Bulk insertion is a topic that many .net developers are struggling to figure out. Thankfully Microsoft provides a SQLBULKCOPY class that is under System.Data.SqlClient namespace. You can go through the documentation to understand what is it or even how to do it (they already provide some dummy code)

There are a lot of ways to do an insertion in .net. The most common approach (if you are going to use c#) is to use an ORM (EF or Dapper). But for high performance, some companies use SqlDataAdapter.

So in this tutorial, I am going to show my way of implementing SqlBulkCopy (you can follow along to test the speed of it 😁).

To use SqlBulkCopy, the only requirement is that the datalist has to be loaded into a DataTable instance or read from IDataReader instance.

For that, we can create our generic DataTableConvertion method to convert any List to DataTable instance.

Code

private DataTable ToDataTable(List<T> items)
{
  DataTable dataTable = new DataTable(typeof(T).Name);
  PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
  foreach (PropertyInfo prop in Props)
  {
    dataTable.Columns.Add(prop.Name);
  }
  foreach (T item in items)
  {
    var values = new object[Props.Length];
    for (int i = 0; i < Props.Length; i++)
    {
      values[i] = Props[i].GetValue(item, null);
    }
    dataTable.Rows.Add(values);
  }
  return dataTable;
}

Once we Convert our list to DataTable, we need to create SqlConnection.

Now its time for to initiate SQL Connection

using (SqlConnection conn = new SqlConnection(_connectionString))
{
  conn.Open();
}

Then we can call SqlBulkCopy. It has 4 overloads. It's safe to use transactions while using any SQL operation.

var transaction = conn.BeginTransaction();
using (var bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
{
  bulkCopy.DestinationTableName = entryTableName;
  bulkCopy.WriteToServer(datatables);
}
transaction.Commit();

That's it! We have done It. ✅

For bulk update, you can check this Bulk Update

In this way, we can use this helper class to quickly insert a bulk amount of records in the database.

Repo

For full code visit Github Repo

Happy Learning. 🕺👊🤾‍♂️