Dapper Plus or SqlBulkCopy? Pros, cons, and hidden limitations for high-performance .NET data ingestion

Recently I talk to my ex college with whom I went on internship with about some optimization techniques. At that time we had one project where we need to find an optimal solution to insert a huge amount of data in database.
The solution SqlBulkCopy (obviously) since we were using SQL Server database, but we also had experience with Dapper Plus in different companies.

So I thought to put this two on the test and how is taking first place.

I will try to provide a deep architectural and performance comparison between these tow, and also explore their pros, cons, and the hidden traps that catch even experienced engineers.

The need for speed in .NET

Even with the performance leaps in .NET 10 and optimizations to the Entity Framework Core Change Tracker, standard ORM inserts or looping ExecuteAsync calls often fall short for massive datasets.
There are still overheads like:

• Generating individual SQL statements
• Tracked entities
• Network round-trips

And taking these approach with bulk operations is a resource killer.

So what is the right tool impacts execution time, memory allocation, garbage collection pressure, and long-term codebase maintainability? You will learn shorty.

Deep Dive: SqlBulkCopy

In standard Entity Framework or Dapper, INSERT is sent as text-based statements. SQL Server receives that text, parse it, generate an execution plan, and execute it row-by-row.
SqlBulkCopy completely bypasses the standard relational engine's query parser.

The key is in Tabular Data Stream (TDS) protocol. It packages data into binary streams and writes it directly into the database pages. The perfect example of SqlBulkCopy power is when you use IDataReader wrapper in code which allows the TDS engine to stream the data directly from your POCOs to the network socket, keeping memory allocation nearly flat regardless of the dataset size.

But, it's not ideal. Let's say that one of the row(in a million) fails("String or binary data would be truncated") then the entire batch aborted. And diving into debugging to find can be hard since you need to binary tree-ed through whole batch.

Deep Dive: Dapper Plus

Dapper Plus acts as an orchestration engine. It uses two feature: #TempTable and MERGE.
When, for example, BulkMerge is called, it dynamically creates a temporary staging table (#TempTable) that match schema. It then bulk-inserts data into that staging table(often utilizing SqlBulkCopy internally if you are on SQL Server)
Finally, it generates and executes a highly optimized T-SQL MERGE statement to push the data from the temporary table into actual destination table.

When performing bulk insert one of the hardest things is how newly generated database IDENTITY keys are mapped back to C# objects in memory. Dapper Plus handles this flawlessly out of the box. It leverages the OUTPUT inserted.* clause in SQL Server during the operation, catching the newly generated IDs and mapping them straight back to your POCO instances.

Architecture & design implications

The death of the Generic Repository

The most common mistake architects make is trying to force bulk operations into the generic Repository pattern.
Bulk operations are never generic. A telemetry ingestion pipeline requires completely different batch sizes, timeout configurations, and lock escalation strategies than a nightly user-sync job.

CQRS: bypassing the rich domain model

If you are practicing Domain-Driven Design (DDD), a bulk insert operation is the ultimate edge case. Standard DDD dictates that you load an Aggregate Root into memory, mutate its state enforcing business invariants, and save it.
In a Command Query Responsibility Segregation (CQRS) architecture, bulk ingestion must be treated as a Command.
You map the incoming payload directly to flat, anemic DTOs and stream them straight to the database.

Vertical Slice Architecture is the natural fit

Perfect fit for bulk operations is Vertical slice architecture. The entire operation is encapsulated within a single feature slice (e.g., Features/Telemetry/ImportTelemetryCommand.cs).

In slice:

• The DTO represents the exact schema needed for the bulk insert.
• The SqlBulkCopy or Dapper Plus execution happens directly inside the Command Handler.
• The IDbConnection is scoped specifically for this handler, completely sidestepping EF Core's DbContext.

This isolation means you can heavily optimize the telemetry ingestion code -tweaking network packet sizes or transaction scopes - without risking regressions in any other part of the application.

Hidden limitations

These two techniques are not bulletproof and they, like every part of the software in the word, have some limitations and edge cases that are worth mentioning.

The silent bypass

By default, SqlBulkCopy is designed for pure speed, which means it intentionally ignores your database's safety nets.

• The trap: SqlBulkCopy will quietly bypass all CHECK constraints and AFTER INSERT triggers. If your database relies on triggers to maintain audit trails (like ModifiedAt columns) or complex relational integrity, that logic is skipped entirely, leading to silent data corruption that you won't notice until weeks later.
• The fix: You must explicitly pass SqlBulkCopyOptions.FireTriggers and SqlBulkCopyOptions.CheckConstraints. However, be warned: enabling these forces the SQL Server engine to do row-by-row evaluations, which can easily cut your throughput speed in half.

The "partial batch" nightmare

What happens if a network blip or a deadlock occurs exactly four minutes into a five-minute bulk insertion?

• The trap: Standard bulk inserts are not automatically wrapped in atomic transactions. If a failure occurs mid-stream, you are left with a fractured dataset - half your batch is committed, and half is lost. If your retry policy kicks in and simply re-runs the job, you will duplicate the first half of the data.
• The fix: Your bulk ingestion architecture must be explicitly idempotent. You must wrap the operation in a SqlTransaction and use staging tables or Upsert logic (like Dapper Plus's MERGE). This ensures that when your Polly retry policy catches a transient failure, it can safely re-run the entire batch without violating unique constraints or duplicating rows.

Transaction log explosions & cloud throttling

You can write the most optimized, zero-allocation C# code possible, but if you are running in the cloud, you are bound by physical hardware limits.

• The trap: SqlBulkCopy is so fast that sending a million rows at once will cause massive transaction log growth and easily saturate the Transaction Log I/O limits of managed databases (like Azure SQL's vCore limits). When you hit this IOPS ceiling, the cloud provider will actively throttle your connection, resulting in severe SqlException timeouts and potentially locking up the database for other applications.
• The fix: Always use SqlBulkCopyOptions.TableLock to minimize transaction log writes. Furthermore, you must aggressively tune your BatchSize. Break massive payloads down into chunks of 10,000 to 50,000 rows to keep the I/O throughput just below your provider's throttling threshold.

Code implementation

The SqlBulkCopy approach

Few times that hundreds of thousands of records are loaded into a DataTable before passing it to the database. This immediately crushes the Large Object Heap (LOH) and causes massive GC pauses.

Fix is to bypass the DataTable entirely. You can stream our C# objects directly to the SQL Server network socket with virtually zero memory allocations with FastMember library.

using Microsoft.Data.SqlClient;
using FastMember;

public sealed class SimpleTelemetryImporter(string connectionString)
{
    public async Task ImportAsync(IEnumerable<TelemetryDto> data)
    {
        await using var connection = new SqlConnection(connectionString);
        await connection.OpenAsync();

        await using var reader = ObjectReader.Create(
            data, 
            nameof(TelemetryDto.Id), 
            nameof(TelemetryDto.Timestamp), 
            nameof(TelemetryDto.Value));

        using var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, null)
        {
            DestinationTableName = "TelemetryData",
            BatchSize = 10000 // Send data in chunks of 10k
        };

        bulkCopy.ColumnMappings.Add(nameof(TelemetryDto.Id), "Id");
        bulkCopy.ColumnMappings.Add(nameof(TelemetryDto.Timestamp), "Timestamp");
        bulkCopy.ColumnMappings.Add(nameof(TelemetryDto.Value), "MetricValue");

        await bulkCopy.WriteToServerAsync(reader);
    }
}

The Dapper Plus approach

If SqlBulkCopy requires you to manage column mappings and IDataReader wrappers, Dapper Plus takes the opposite approach: it hides everything behind a fluent API.

using Microsoft.Data.SqlClient;
using Z.Dapper.Plus;

public sealed class SimpleTelemetryUpserter(string connectionString)
{
    static SimpleTelemetryUpserter()
    {
        DapperPlusManager.Entity<TelemetryDto>()
                         .Table("TelemetryData")
                         .Key(t => t.Id); 
    }

    public async Task UpsertAsync(IEnumerable<TelemetryDto> data)
    {
        await using var connection = new SqlConnection(connectionString);
        
        await connection.BulkMergeAsync(data);
    }
}

Performance & memory showdown

The anti-pattern: SqlBulkCopy with a DataTable

This involves looping through your C# objects, creating rows, and loading them into a DataTable.
A DataTable is an incredibly heavy in-memory object. If you load 500,000 records into one, you are duplicating that entire dataset in memory.
It directly pushes allocations towards the L0H or Large Object Heap. Gen 2 Garbage Collection pauses become aggressive, causing your CPU to spike and freezing other concurrent requests on that server. Even though it has very low latency when writing to the database, it destroys the health of the application server.

The Ideal: SqlBulkCopy via Streaming (FastMember)

The power of SqlBulkCopy is unlocked when you bypass the DataTable and use an IDataReader.
Instead of buffering the entire dataset in memory, FastMember acts as a pipeline. It reads a single C# object, serializes it directly to the Tabular Data Stream (TDS) protocol, pushes it across the network, and immediately discards the reference.
The result? Your memory allocation stays completely flat.

The Pragmatic Balance: Dapper Plus

Dapper Plus abstracts the underlying complexity, which inherently introduces a minor performance tax - but it is highly optimized.
It must use reflection and expression trees to dynamically map your C# objects to the database columns. While this adds minor CPU cycles compared to a hardcoded SqlBulkCopy map, the overhead is negligible for 99% of applications.
For Upserts, it internally buffers data into a temporary staging table before executing the MERGE script. This requires slightly more database CPU and network round-trips than a pure append-only INSERT, but it completely eliminates the application-side memory bloat of trying to figure out which rows already exist.

Conclusion

When you are architecting a new ingestion pipeline or refactoring a failing one, the choice between these tools should be an engineering decision based on your constraints.

Choose SqlBulkCopy when:

• You want low cost enterprise-grade performance
• You have no plans to migrate to PostgreSQL or MySQL, as SqlBulkCopy is tied directly to the Microsoft TDS protocol
• You need to squeeze every millisecond out of the network and database engine, and you are willing to manage the minor boilerplate to get it

Choose Dapper Plus when:

• You rely heavily on Upserts
• You need IDENTITY mapping
• You require cross-database support