SQL Bulk Insert with SqlBulkCopy in C#

To perform a Bulk Insert, the simplest approach is to use SqlBulkCopy (from System.Data.SqlClient) in conjunction with a DataTable.

1. Create a DataTable with columns/types that match the target database table.
2. Add rows of data into the DataTable.
3. Set up the SqlBulkCopy with the connection string and specify the destination table.
4. Use SqlBulkCopy.WriteToServer() to insert the data from the DataTable.

For example:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

void BulkInsertOrders(List<Order> orders)
{
    DataTable table = new DataTable();
    table.TableName = "Orders";

    table.Columns.Add(nameof(Order.OrderId), typeof(int));
    table.Columns.Add(nameof(Order.CustomerName), typeof(string));
    table.Columns.Add(nameof(Order.OrderDate), typeof(DateTime));
    table.Columns.Add(nameof(Order.TotalAmount), typeof(decimal));

    foreach (var order in orders)
    {
        var row = table.NewRow();
        row[nameof(Order.OrderId)] = order.OrderId;
        row[nameof(Order.CustomerName)] = order.CustomerName;
        row[nameof(Order.OrderDate)] = order.OrderDate;
        row[nameof(Order.TotalAmount)] = order.TotalAmount;
        table.Rows.Add(row);
    }

    using (var bulkInsert = new SqlBulkCopy(ConnectionString))
    {
        bulkInsert.DestinationTableName = table.TableName;
        bulkInsert.WriteToServer(table);
    }
}

public static object GetDBValue(object o)
{
    return o ?? DBNull.Value;
}

Performance Comparison: Multiple Inserts vs. Bulk Insert

Now, let’s compare inserting 100,000 records into the Orders table using both multiple individual inserts and the BulkInsert method.

Orders Table Definition:

CREATE TABLE [dbo].[Orders](
    [OrderId] [int] NOT NULL,
      NOT NULL,
    [OrderDate] [datetime] NULL,
    [TotalAmount] [decimal](18, 2) NULL,
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ([OrderId])
) ON [PRIMARY];

Generating Test Data:

We use the following method to generate a list of Order objects:

List<Order> GenerateOrders()
{
    var orders = new List<Order>();

    for (int i = 0; i < 100000; i++)
    {
        orders.Add(new Order()
        {
            OrderId = i + 1,
            CustomerName = $"Customer{i}",
            OrderDate = DateTime.Now.AddDays(-i),
            TotalAmount = (i % 10 == 0) ? (decimal?)null : (decimal)(i * 1.23)
        });
    }

    return orders;
}

Performance Test - Multiple Inserts (Using Dapper):

List<Order> orders = GenerateOrders();

Stopwatch sw = new Stopwatch();
sw.Start();

MultipleInserts(orders);

sw.Stop();

Console.WriteLine($"Elapsed={sw.Elapsed}. TotalMS={sw.Elapsed.TotalMilliseconds}");

void MultipleInserts(List<Order> orders)
{
    using (var con = new SqlConnection(ConnectionString))
    {
        con.Execute(@"INSERT INTO [dbo].[Orders]
                       ([OrderId], [CustomerName], [OrderDate], [TotalAmount])
                     VALUES
                       (@OrderId, @CustomerName, @OrderDate, @TotalAmount)", 
                     orders);
    }
}

This method performs 100,000 individual inserts. You can observe the execution by profiling SQL using SQL Profiler.

Performance Test - Bulk Insert:

Now let’s use SqlBulkCopy to insert all 100,000 records in one operation:

List<Order> orders = GenerateOrders();

Stopwatch sw = new Stopwatch();
sw.Start();

BulkInsertOrders(orders);

sw.Stop();

Console.WriteLine($"Elapsed={sw.Elapsed}. TotalMS={sw.Elapsed.TotalMilliseconds}");

Results from Stopwatch:

• Multiple Inserts: Elapsed: 00:00:45.789 (45.789 seconds)

• Bulk Insert: Elapsed: 00:00:02.123 (2.123 seconds)

Conclusion

The Bulk Insert method significantly outperforms the traditional multiple inserts.

Bulk Insert is 20-30x faster, uses significantly less CPU, and performs fewer IO operations than multiple individual inserts.

When inserting large amounts of data, Bulk Insert is the clear winner for performance. If you're handling large datasets, optimizing for this technique will save both time and system resources.