Efficient Data Transfer with SQL Server BulkCopy and C# using System.Data.SqlClient

In the world of data-driven applications, efficient data transfer between various sources and databases is crucial for maintaining optimal performance. When dealing with large datasets, traditional methods of inserting records one by one can be time-consuming and resource-intensive. This is where SQL Server BulkCopy comes to the rescue. In this blog post, we’ll explore how to utilize SQL Server BulkCopy using C# to achieve blazing-fast data transfer.

What is SQL Server BulkCopy?

SQL Server BulkCopy, commonly known as “BCP,” is a command-line utility and API (Application Programming Interface) provided by Microsoft SQL Server for efficiently copying large volumes of data between SQL Server databases or between SQL Server and external data files. It is designed to perform high-speed data transfers with minimal impact on server resources and processing time.

BCP allows you to transfer data in and out of SQL Server tables using text files (CSV, TSV, etc.) or binary files. It’s particularly useful for scenarios where you need to import or export large amounts of data quickly, such as loading data into a data warehouse, archiving data, or migrating data between databases.

Some key features and aspects of SQL Server Bulk Copy (BCP) include:

1. Speed: BCP is optimized for high-speed data transfers, making it suitable for moving large datasets efficiently.
2. Command-line utility: BCP is invoked through the command line, allowing you to specify parameters such as source and destination data, authentication, format files, and more. However, in this post, we’ll look at how we can tap into this feature from within your C# applications.
3. Format files: BCP supports the use of format files, which define the structure of the data being transferred, including column mappings, data types, and more. This can be especially helpful when the source and destination data formats differ.
4. Batch processing: BCP supports batch processing, allowing you to transfer data in chunks, reducing memory and resource consumption.
5. Minimal logging: BCP operations can be performed with minimal logging, reducing the impact on the transaction log and improving performance.
6. Parallel processing: BCP supports parallel data transfers, which can further improve performance by utilizing multiple threads.

Using SQL Server BulkCopy in C#

The most elegant and pain-free method of utilizing the SQL Server BulkCopy feature from within your C# applications is to use the System.Data.SqlClient NuGet package, provided by Microsoft. You may already be familiar with this package as it is the de facto library for interacting with SQL Server databases via .NET. While most users utilize this package for transactional processing of data in SQL Server, it provides a rich API for bulk-processing, as well. Search for it and install the latest version from NuGet.

The Code

Setting up the Connection: Let’s explore a simple example. If you have not used this library before, the first step is establishing a connection with the SQL Server database. You can use the SqlConnection class to do this.

using System.Data.SqlClient;

string connectionString = "Data Source=your_server;Initial Catalog=your_database;" + 
    "Integrated Security=True";
using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

Creating a DataTable: Next, you’ll want to create a data source that the SqlBulkCopy class can understand which will house or serve as a conduit of your data that you are bulk inserting into SQL Server. The library can take a DbDataReader, an array of DataRow objects, any object that implements the IDataReader interface or a DataTable object as the source for the bulk copy process. These data structures have long existed in the .NET world and they’re all closely related but, if you’re unfamiliar with them, you can check out the links that I provided to get to associated documentation. As an example, I’m creating a DataTable and populating it with a couple of rows of dummy data, below.

using DataTable dataTable = new DataTable();
// Add columns to the DataTable
dataTable.Columns.Add("Column1", typeof(int));
dataTable.Columns.Add("Column2", typeof(string));
// Add rows to the DataTable
dataTable.Rows.Add(1, "Data1");
dataTable.Rows.Add(2, "Data2");
// Add more rows as needed

Configuring BulkCopy: Now comes the core part—configuring and executing the BulkCopy operation.

using SqlBulkCopy bulkCopy = new SqlBulkCopy(connection);
// The name of your destination table in the database
bulkCopy.DestinationTableName = "YourDestinationTable"; 
bulkCopy.WriteToServer(dataTable);

Closing Resources: After the bulk copy operation is complete, make sure to close the connection.

connection.Close();

Additional Configuration

SQL Server BulkCopy provides various options for configuring the behavior of the data transfer. Some important options include:

• BulkCopyOptions: Allows you to control options like table locking, identity insert, and more.
• BatchSize: Determines how many rows are sent to the database in a single batch. Experiment with this value to get the best transfer speeds without running into timeouts or overloading your systems.
• NotifyAfter: Enables progress monitoring by specifying the number of rows to be processed before raising the SqlRowsCopied event.

Conclusion

Today we looked at an example where we bulk inserted some data that we generated, on the fly, to a SQL server database. But that data may come from another SQL Server instance or from another database or external system. As long as you are able to package that data as a DataTable, or a DataReader, you can use this library to bulk insert it into SQL Server.

Another common use-case is pumping large volumes of data into SQL Server from flat files — CSVs or TXTs or other such text-based formats. Stay tuned for the next episode where we’ll do just that – work on an example where we’ll bulk insert data from a CSV file.