How to use SqlBulkCopy in C#

It provides functionality similar to the bcp (bulk copy) utility, enabling fast data transfers from .NET applications into a SQL Server database. This class is commonly used when dealing with large datasets, offering better performance than inserting data row-by-row.

There are several methods for loading batches of data into SQL Server from .NET, including:

1. Using an ORM (Object-Relational Mapper), adding items to the context, and submitting changes.
2. Using a SqlDataAdapter to send a DataTable of items to SQL Server via the Update method.
3. Passing XML to a stored procedure, which shreds the data and inserts it into the table.
4. Passing a Table-Valued Parameter (TVP) to a stored procedure for insertion into the table.

Each approach offers different benefits depending on the specific requirements and complexity of the data.

How to Use SqlBulkCopy in C# for Efficient Data Import

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

public BulkCopy(DataTable dt, string targetTable)
{
    using (SqlConnection cn = new SqlConnection(connectionString))
    {
        if (cn.State == ConnectionState.Closed)
            cn.Open();
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cn))
        {
            bulkCopy.DestinationTableName = targetTable;
            bulkCopy.BatchSize = 1000;  // Bulk copy 1000 rows at a time
            bulkCopy.BulkCopyTimeout = 600;  // Timeout in 600 seconds (10 minutes)

            // Optional: Notify progress every 500 rows
            bulkCopy.NotifyAfter = 500;
            bulkCopy.SqlRowsCopied += (sender, e) =>
            {
                Console.WriteLine($"{e.RowsCopied} rows copied.");
            };

            // Perform the bulk copy
            bulkCopy.WriteToServer(dt);
        }
    }
}

You can configure SqlBulkCopy with several options to fine-tune performance and behavior:

• BatchSize: The number of rows to send to SQL Server in one batch.
• BulkCopyTimeout: The maximum time in seconds to wait before throwing an error.
• NotifyAfter: Used to specify when to receive notifications during the bulk copy operation (e.g., after every 1000 rows).
• EnableStreaming: For large data loads, this allows rows to be streamed directly from memory.

The SqlBulkCopy is an extremely powerful and efficient tool for bulk data import into SQL Server. By using it, you can significantly reduce the time needed to insert large amounts of data.

Key tips for high-throughput loading include:

1. Use a heap table: Load data into a new heap table without primary keys or indexes, as adding indexes after the load minimizes work and fragmentation. If loading into an existing table, consider disabling or removing indexes during the load. A common approach is to load into a heap staging table first and then migrate the data using T-SQL.

2. Use SqlBulkCopyOptions.TableLock: This option applies a bulk update lock to the table, improving throughput, especially when loading into a heap table. Avoid it if loading into a table with a clustered index that cannot be removed, as parallel operations might be blocked.

3. Use parallel threads: Create multiple SqlBulkCopy instances, each on separate threads or machines, to load data into the same table. Splitting the data across these threads and using multiple bulk update locks increases throughput.

By following these steps, you can easily integrate SqlBulkCopy into your C# applications for fast and reliable data insertion into SQL Server.