C# .NET 8 SQL Bulk Insert Dapper vs BulkCopy vs Table-Value Params

Introduction

Handling large datasets in a .NET application often requires efficient data insertion methods into SQL Server. This article explores three popular techniques for bulk inserting data using C# and .NET 8: Dapper, SqlBulkCopy, and Table-Value Parameters (TVPs). We will compare their performance, ease of use, and suitability for different scenarios.

1. Dapper Bulk Insert

Dapper is a lightweight ORM (Object-Relational Mapper) that provides a balance between raw SQL and full-featured ORMs like Entity Framework. While Dapper does not natively support bulk inserts, it can be extended to perform them efficiently.

Implementation

To use Dapper for bulk inserts, you typically execute multiple insert statements within a single transaction. Here's an example.

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

public async Task BulkInsertDapperAsync(IEnumerable<MyData> data, string connectionString)
{
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        using (var transaction = connection.BeginTransaction())
        {
            string sql = "INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, @Column2)";
            await connection.ExecuteAsync(sql, data, transaction: transaction);
            transaction.Commit();
        }
    }
}

2. SqlBulkCopy

SqlBulkCopy is a built-in .NET class specifically designed for bulk data transfers to SQL Server. It provides the fastest performance among the three methods discussed here, as it directly writes to the database.

Implementation

Here's how to use SqlBulkCopy.

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

public async Task BulkInsertSqlBulkCopyAsync(DataTable dataTable, string connectionString)
{
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        
        using (var bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "MyTable";
            await bulkCopy.WriteToServerAsync(dataTable);
        }
    }
}

To use SqlBulkCopy, you'll need to convert your data to a DataTable or use IDataReader.

3. Table-Value Parameters (TVPs)

TVPs allow you to pass a table as a parameter to a stored procedure or SQL command. They provide a flexible and efficient way to perform bulk inserts, especially when you need to validate or manipulate the data on the server side.

Implementation

First, define a user-defined table type in your SQL Server.

CREATE TYPE MyDataType AS TABLE 
(
    Column1 INT,
    Column2 NVARCHAR(50)
);

Next, create a stored procedure to handle the insert.

CREATE PROCEDURE InsertMyData
    @MyData MyDataType READONLY
AS
BEGIN
    INSERT INTO MyTable (Column1, Column2)
    SELECT Column1, Column2 FROM @MyData;
END

Finally, implement the C# code to use TVPs.

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

public async Task BulkInsertTVPAsync(IEnumerable<MyData> data, string connectionString)
{
    var dataTable = new DataTable();
    dataTable.Columns.Add("Column1", typeof(int));
    dataTable.Columns.Add("Column2", typeof(string));

    foreach (var item in data)
    {
        dataTable.Rows.Add(item.Column1, item.Column2);
    }

    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        using (var command = new SqlCommand("InsertMyData", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            var tvpParameter = command.Parameters.AddWithValue("@MyData", dataTable);
            tvpParameter.SqlDbType = SqlDbType.Structured;
            await command.ExecuteNonQueryAsync();
        }
    }
}

Performance Comparison

The performance of each method can vary based on the dataset size and the specific requirements of the application. Generally.

• SqlBulkCopy is the fastest and most efficient method for raw bulk data insertion.
• TVPs offer flexibility and are efficient for scenarios requiring server-side data validation or manipulation.
• Dapper provides ease of use and integration with existing Dapper-based projects, though it might not be as fast as SqlBulkCopy or TVPs for very large datasets.

Conclusion

Choosing the right method for bulk inserting data into SQL Server depends on your specific needs.

• Use SqlBulkCopy for the highest performance in raw data transfers.
• Use Table-Value Parameters when you need flexibility and server-side processing.
• Use Dapper for simplicity and when working within a Dapper-centric codebase.

By understanding the strengths and use cases of each method, you can make an informed decision to optimize the performance and maintainability of your .NET applications.