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

So I decided to compare the two approaches and see where each one fits best.

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

At a glance:

DimensionSqlBulkCopyDapper Plus
Database portabilitySQL Server only (tied to the TDS protocol)Cross-database
Raw insert throughputHighest — no abstraction taxSlightly lower — reflection/expression-tree mapping adds a small tax
Upsert / MERGE supportNot built inBuilt in (#TempTable + MERGE)
IDENTITY key mapping back to C#Manual — no built-in supportBuilt in (OUTPUT inserted.*)
Mid-batch failure handlingManual — wrap in your own SqlTransactionStaging table + MERGE reduces duplicate-row risk on retry
Licensing costFreeCommercial

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 this approach with bulk operations is a resource killer.

So how does the right tool impact execution time, memory allocation, garbage collection pressure, and long-term codebase maintainability? You will learn shortly.

Deep Dive: SqlBulkCopy

In standard Entity Framework or Dapper, INSERT is sent as text-based statements. SQL Server receives that text, parses it, generates an execution plan, and executes it row-by-row. SqlBulkCopy avoids the per-row INSERT statement path, so the query parser and optimiser aren’t re-invoked for every row.

The key is the Tabular Data Stream (TDS) protocol: rows are sent in a binary bulk-insert stream rather than as individual statements, and the server writes them through its bulk-load path. Storage engine rules such as page allocation, indexes, and logging still apply. The clearest example of that power is wrapping your data in an IDataReader, which lets you stream rows from your POCOs to the network socket instead of materialising them all first, keeping memory use roughly flat as the dataset grows.

But it’s not ideal. Let’s say that one of the rows (in a million) fails (“String or binary data would be truncated”). The entire batch is aborted. Diving into debugging to find it can be hard, since you need to binary-tree through the whole batch.

Deep Dive: Dapper Plus

Dapper Plus acts as an orchestration engine. It uses two features: #TempTable and MERGE. When, for example, BulkMerge is called, it dynamically creates a temporary staging table (#TempTable) that matches the 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 the actual destination table.

When performing bulk inserts, one of the hardest things is how newly generated database IDENTITY keys are mapped back to C# objects in memory. Dapper Plus handles this 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 rarely 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

A 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 a 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, including 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 piece of software in the world, 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: pass SqlBulkCopyOptions.FireTriggers and SqlBulkCopyOptions.CheckConstraints explicitly. Be aware that both add per-row work the bulk path would otherwise skip, so they may reduce throughput. How much depends on how expensive your triggers and constraints are. Benchmark it against your own workload rather than assuming a figure.

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. 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: SqlBulkCopyOptions.TableLock is one of the prerequisites for minimal logging, but it is not sufficient on its own. Minimal logging also depends on the database’s recovery model, whether the table is empty, which indexes exist, and whether the table is being replicated. Check your case against the documented requirements instead of assuming the option alone reduces log volume. Tune BatchSize as well: chunks of 10,000 to 50,000 rows keep I/O below most providers’ throttling thresholds.

Code implementation

The SqlBulkCopy approach

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

The fix is to bypass the DataTable entirely. The FastMember library lets you stream your C# objects to the SQL Server network socket as they’re read, so you avoid buffering the whole set in a DataTable. Allocation stays roughly proportional to the batch rather than the total row count.

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 LOH, 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. That adds CPU work compared with a hand-written SqlBulkCopy column map. In most workloads the cost is small next to the network and storage time, but if you are chasing the last few percent, measure it. 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 the highest bulk-insert throughput at zero licensing cost
  • 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