SqlBulkCopy Class in C#

The SqlBulkCopy class in C# allows you to efficiently bulk insert large amounts of data into a SQL Server database. This is much faster than inserting rows individually using INSERT statements.

1. Why Use SqlBulkCopy?

High Performance – Optimized for bulk data insertion.
Reduces Database Load – Inserts multiple rows in one batch.
Avoids Transaction Overhead – Compared to multiple INSERT statements.
Directly Writes to Database – No intermediate staging table required.

2. Basic Usage

Here's a basic example of how to use SqlBulkCopy to insert data from a DataTable into SQL Server.

Example: Bulk Insert from DataTable

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string";

        // Create a DataTable with sample data
        DataTable dt = new DataTable();
        dt.Columns.Add("Id", typeof(int));
        dt.Columns.Add("Name", typeof(string));

        dt.Rows.Add(1, "John");
        dt.Rows.Add(2, "Jane");

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
            {
                bulkCopy.DestinationTableName = "Users"; // Target table name

                // Map columns if the DataTable column names are different from the DB column names
                bulkCopy.ColumnMappings.Add("Id", "Id");
                bulkCopy.ColumnMappings.Add("Name", "Name");

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

Efficiently inserts multiple rows in one batch.
Uses ColumnMappings to match DataTable columns with SQL table columns.

3. Data Sources for SqlBulkCopy

DataTable
IDataReader (like SqlDataReader)
DataRow[] (array of DataRows)

Example: Bulk Insert from SqlDataReader

using (SqlConnection sourceConn = new SqlConnection(sourceConnectionString))
using (SqlConnection destConn = new SqlConnection(destConnectionString))
{
    sourceConn.Open();
    destConn.Open();

    using (SqlCommand cmd = new SqlCommand("SELECT Id, Name FROM SourceTable", sourceConn))
    using (SqlDataReader reader = cmd.ExecuteReader())
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConn))
    {
        bulkCopy.DestinationTableName = "DestinationTable";
        bulkCopy.WriteToServer(reader);
    }
}

Efficient for transferring data between databases.

4. Performance Optimization

4.1. BatchSize

Set BatchSize to control how many rows are inserted per batch.

bulkCopy.BatchSize = 1000;

Prevents memory overload for large datasets.

4.2. Enable Table Locking

Use SqlBulkCopyOptions.TableLock for faster inserts.

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, null))

Improves performance in high-volume inserts.

4.3. Set Timeouts for Large Data

Avoid timeout errors for large inserts.

bulkCopy.BulkCopyTimeout = 300; // Timeout in seconds

5. Error Handling

SQL Server constraints (e.g., unique constraints) may cause errors. Use SqlBulkCopy with try-catch.

try
{
    bulkCopy.WriteToServer(dt);
}
catch (SqlException ex)
{
    Console.WriteLine("SQL Bulk Copy Error: " + ex.Message);
}

6. Summary

Best for inserting large datasets
Works with DataTable, IDataReader
Use ColumnMappings if column names differ
Optimize with BatchSize, TableLock, and BulkCopyTimeout

interview