SQL Bulk Update - C# (BulkCopy)

Introduction:

Bulk update is a topic that many .net developers are struggling to figure out. There is a way to get it working as Microsoft provides a SqlBulkCopy.

For bulk insertion you can check my previous article Bulk Insertion

To work with SqlBulkCopy class, the only requirement is to convert your list to a DataTable instance.

If you are coming from my previous Bulk Insertion then this process will be familiar to you.

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

Steps:

So there is no magic method to do a bulk update on the database using c#. But we can have some workaround on it. There are some steps to achieve that.

Create a temp table.
Insert all the data into the temp table.
Update from that temp table to destination table.
Drop temp table.

And That should do it. 😁

So let's go into the code.

But at first, create SqlConnection first.

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

I have created an SQL command txt to create the temp table. You can do however you feel comfortable.

string GetTempTableCreateCmd(DataTable dataTable, string tempTable)
{
  StringBuilder columnTxt = new StringBuilder();
  columnTxt.Append($"CREATE TABLE {tempTable}(");
  int columnCount = dataTable.Columns.Count;
  for (int i = 0; i < columnCount; i++)
  {
    string dataType = dataTable.Columns[i].DataType == Type.GetType("System.String") ? "VARCHAR(100) " : dataTable.Columns[i].DataType.ToString();
    string colum = $"{dataTable.Columns[i]} {dataType}";
    columnTxt.Append($"{colum}");
    if (i != columnCount - 1)
      columnTxt.Append(", ");
  }
  columnTxt.Append(");");
  return columnTxt.ToString();
}

Now execute this.

string tempTableTxtCmd = GetTempTableCreateCmd(datatable, tempTableName);
ExecuteCmd(tempTableTxtCmd, conn);

private void ExecuteCmd(string cmdTxt, SqlConnection connection)
{
  using (SqlCommand cmd = new SqlCommand(cmdTxt, connection))
  {
    cmd.ExecuteNonQuery();
  }
}

Now it's time to insert to the temp table. For this checkout Bulk Insert C#

So temp table creation is complete. Now coming to our third step is to update from the temp table to our destination/main table. Let's write some SQL commands to do that.


string GetOriginalTblToTempTableUpdateCmd(DataTable dataTable, string originalTable, string tempTable)
{
  StringBuilder updateTblCmd = new StringBuilder();
  updateTblCmd.Append("UPDATE ORGI SET ");

  for (int i = 1; i < dataTable.Columns.Count; i++)
  {
    updateTblCmd.Append($"ORGI.{dataTable.Columns[i]} = TEMP.{dataTable.Columns[i]}");

    if (i != dataTable.Columns.Count - 1)
      updateTblCmd.Append(", ");
  }

  updateTblCmd.Append($" FROM {tempTable} TEMP INNER JOIN {originalTable} ORGI ON ORGI.{dataTable.Columns[0]} = TEMP.{dataTable.Columns[0]}");

  return updateTblCmd.ToString();
}

Now it's time to execute it also. ExecuteCmd(updateFromTempTableCmd, conn);

Finally, it's time for the last step that is to drop the temp table. This should do the job.

var dropTempTableCmd = $"DROP TABLE {tempTableName}";
ExecuteCmd(dropTempTableCmd, conn);

And that's it ✅

Repo

For full code visit Github Repo

SQL Bulk Update - C# (BulkCopy)

Using C# (SQL BulkCopy)

Table of contents

Introduction:

Code

Steps:

Repo