How To Perform Bulk Update Using SqlBulkCopy, C#.NET And SQL Server

Good afternoon!

Before I go into details, this post is solely based on the solution provided in this link Bulk Update In C#. The purpose of this tutorial is to provide step by step approach on how bulk update can be achieved using SqlBulkCopy, C#.NET And SQL Server. First is to create a simple table in your database that holds some fictitious records.

USE [testdatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Person](
 [ID] [int] NOT NULL,
 [Name] [varchar](50) NULL,
 [Address] [varchar](50) NULL
) ON [PRIMARY]
GO

Database table with temp records

Next is to create a console app that will perform the bulk update of records. The key to solving performance issues in updating records is to use a temporary table approach.

class Program
{
    
  static void Main(string[] args)
  {
  var listPerson = new List<Person>
  {
  new Person() {Id = 1001, Name = "James A.", Address = "US"},
  new Person() {Id = 1002, Name = "Troy B.", Address = "UK"},
  new Person() {Id = 1003, Name = "Mike C.", Address = "Philippines"},
  new Person() {Id = 1004, Name = "Angel D.", Address = "Japan"}
  };

  UpdateData(listPerson, "dbo.Person");
     Console.WriteLine("Finished Updating Records!");
  Console.ReadLine();
  }
  
  public static void UpdateData<T>(List<T> list, string TableName)
  {
  DataTable dt = new DataTable("PersonTable");
  dt = list.AsDataTable(); 

  using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["dbConnection"]))
  {
  using (SqlCommand command = new SqlCommand("", conn))
  {
     try
     {
     conn.Open();

     //Creating temp table on database
     command.CommandText = "CREATE TABLE #tblTmpPerson([ID] [int] NOT NULL, [Name][varchar](50) NULL, [Address] [varchar] (50) NULL);";
     command.ExecuteNonQuery();

     //Bulk insert into temp table
     using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
     {
     bulkcopy.BulkCopyTimeout = 660;
     bulkcopy.DestinationTableName = "#tblTmpPerson";
     bulkcopy.WriteToServer(dt);
     bulkcopy.Close();
     }

     // Updating destination table, and dropping temp table
     command.CommandTimeout = 300;
     command.CommandText = "UPDATE T SET Name=Temp.Name, Address=Temp.Address FROM " + TableName + " T INNER JOIN #tblTmpPerson Temp ON T.ID = Temp.ID; DROP TABLE #tblTmpPerson;";
     command.ExecuteNonQuery();
     }
     catch (Exception ex)
     {
     // Handle exception properly
     }
     finally
     {
     conn.Close();
     }
  }
 }
}

After the application is executed, the address of each person is updated.

Source Code: Bulk Update Using C# And SQLServer

Cheers!