SqlBulkCopy – Quick and Efficient Bulk Data Insert

SqlBulkCopy is a very useful component in the .NET framework used mainly for efficient bulk-inserting large amounts of data into SQL Server databases.

Compared to traditional row-by-row insert operations SqlBulkCopy allows high-performance bulk insert insert by minimizing the network round trips and database interaction.

SqlBulkCopy is very adaptable and works with DataReader, DataTable, and other custom data structures for different scenarios.

Synchronous SqlBulkCopy C# for Bulk insert

Let’s see the below example to demonstrate the SqlBulkCopy usage.

In the below code, we have two source tables i. Source Table and Destination Table placeholder which need to be inserted into SQL.

We are establishing a SqlConnection to SQL Server,

 using SqlConnection connection = new SqlConnection(conString);
 connection.Open();

Using SQL transactions for each DataTable, you create parameterized SQL queries and use SqlBulkCopy to insert the data.

using SqlTransaction transaction = connection.BeginTransaction();
///your logic
transaction.Commit();

For each DataTable, you create parameterized SQL queries and use SqlBulkCopy to insert the data.

         using SqlBulkCopy bulkCopy = 
         new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction);

 
         bulkCopy.DestinationTableName = destTableName;
         await bulkCopy.WriteToServerAsync(sourceTable);

Since we are using transactions, please commit the changes to make the update final.

Please Close the SqlConnection post all processing.

Ex.

         using SqlConnection connection = new SqlConnection(conString);
         connection.Open();

          /// your logicc here 

         connection.Close();

Here is the complete code,

Asynchronous c# SqlBulkCopy for Bulk insert

Let’s see the below example to demonstrate the SqlBulkCopy usage,

The below reusable method can be called asynchronously from the Calle side and can take advantage of multithreading.

Please note that there are no major differences between synchronous and asynchronous calls except use of the await keyword and method return type as Task.

Best Practices of Using to Use SqlBulkCopy

Below are a few best practices for using SqlBulkCopy,

• Ensure Proper Data Validation:
  • Validate data integrity before performing bulk inserts to avoid errors.
  • This includes data type compatibility and constraints.

• Use Appropriate Column Mapping:
  • Ensure that the source data columns are correctly mapped to the destination table
  • Mismatched columns can lead to data corruption.

• Consider Using Transactions:
  • Wrap your bulk copy operation in a transaction (SqlTransaction).
  • This allows you to commit or roll back the entire operation as a single transaction, ensuring data consistency.

• Handle Exceptions Gracefully:
  • Implement exception handling to capture any errors that occur during the bulk copy operation. This will help you identify and address issues promptly.

• Optimize Destination Table:
  • Disable non-clustered indexes, triggers, and constraints on the destination table before performing the bulk copy operation.
  • Rebuild or re-enable them after the operation to improve performance.

• Consider Using Temporary Tables:
  • In some cases, using temporary tables for bulk inserts and then transferring the data to the final table can improve performance.
  • This is especially useful when dealing with large datasets in a production environment.

• Asynchronous and Parallelism:
  • Depending on the hardware and the nature of your data, consider asynchronous and parallelizing your bulk copy operations to take full advantage of multi-core processors.

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

---

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.

---