How to Import Large CSV Files into SQL Server Using ASP.NET Core 10 and SqlBulkCopy (2026)

Importing a large CSV one insert at a time ties up both the API and SQL Server. Loading the whole upload into memory creates a different bottleneck. Read the request incrementally, validate records in small batches, bulk-copy to a staging table, then commit a set-based insert. This lab rejects duplicates and rolls back the full import when any record is invalid.

Where should validation and bulk copy happen?

Parse text on the application side and enforce data invariants at the database boundary too. The sample normalizes ASCII SKUs to uppercase, rejects invalid quantities, uses a unique staging key, then checks the destination’s constraints during the final insert. All chunks participate in the same external transaction.

Bounded CsvHelper batches go into a temporary SQL Server table in the same transaction as the final insert.
Bounded CsvHelper batches go into a temporary SQL Server table in the same transaction as the final insert.
Design Memory Transaction and recovery
Full in-memory DataTable Grows with upload Simple, but poor for large files
Bounded batches Fixed-size row batches One external transaction in this lab
Permanent staged job Bounded application memory Supports checkpointing and asynchronous completion

Create the database and API

Use the .NET 10 SDK and a disposable SQL Server database with a valid connection. Run schema.sql once to create the destination. The API creates a session-local staging table. Both bulk copy and final SQL use the same open connection and transaction. Otherwise a local temp table from another session is invisible.

schema.sql
CREATE TABLE dbo.Inventory (
    Sku varchar(40) NOT NULL CONSTRAINT PK_Inventory PRIMARY KEY,
    Quantity int NOT NULL CHECK (Quantity >= 0)
);
BASH
dotnet new web -n BulkInventory -f net10.0
cd BulkInventory
dotnet add package CsvHelper
dotnet add package Microsoft.Data.SqlClient

Set ConnectionStrings__Import for that database. The API accepts the raw request body as text/csv, which avoids multipart form buffering. It uses UTF-8, the exact header Sku,Quantity, and a 50 MB server limit. Bound requests at your proxy too. Use a development fixture on loopback and add authenticated, authorized ingestion before deploying.

Program.cs
using System.Data;
using System.Globalization;
using System.Text;
using CsvHelper;
using CsvHelper.Configuration;
using Microsoft.Data.SqlClient;

var builder = WebApplication.CreateBuilder(args);
builder.WebHost.ConfigureKestrel(o => o.Limits.MaxRequestBodySize = 50_000_000);
var app = builder.Build();
app.MapPost("/inventory/import", async (HttpRequest req, IConfiguration config,
    ILogger<Program> log, CancellationToken ct) =>
{
    if (!string.Equals(req.ContentType?.Split(';')[0].Trim(), "text/csv",
        StringComparison.OrdinalIgnoreCase))
        return Results.StatusCode(415);
    await using var db = new SqlConnection(config.GetConnectionString("Import")
        ?? throw new InvalidOperationException("Set ConnectionStrings__Import."));
    await db.OpenAsync(ct);
    await using var tx = (SqlTransaction)await db.BeginTransactionAsync(ct);
    using var cmd = db.CreateCommand();
    cmd.Transaction = tx;
    cmd.CommandText = """
        CREATE TABLE #Stock (Sku varchar(40) COLLATE DATABASE_DEFAULT NOT NULL PRIMARY KEY,
            Quantity int NOT NULL CHECK (Quantity >= 0));
        """;
    await cmd.ExecuteNonQueryAsync(ct);
    using var reader = new StreamReader(req.Body, new UTF8Encoding(false, true),
        detectEncodingFromByteOrderMarks: false, leaveOpen: true);
    using var csv = new CsvReader(reader, new CsvConfiguration(CultureInfo.InvariantCulture)
    {
        MaxFieldSize = 256,
        ExceptionMessagesContainRawData = false
    });
    using var bulk = new SqlBulkCopy(db, SqlBulkCopyOptions.CheckConstraints, tx)
    {
        DestinationTableName = "#Stock", BatchSize = 2000, BulkCopyTimeout = 30
    };
    bulk.ColumnMappings.Add("Sku", "Sku");
    bulk.ColumnMappings.Add("Quantity", "Quantity");
    using var rows = new DataTable();
    rows.Columns.Add("Sku", typeof(string));
    rows.Columns.Add("Quantity", typeof(int));
    var total = 0;
    try
    {
        if (!await csv.ReadAsync()) return Results.BadRequest("Missing header.");
        csv.ReadHeader();
        if (csv.HeaderRecord is not ["Sku", "Quantity"])
            return Results.BadRequest("Expected Sku,Quantity header in UTF-8 without BOM.");
        while (await csv.ReadAsync())
        {
            ct.ThrowIfCancellationRequested();
            var sku = csv.GetField(0)?.Trim();
            if (csv.Parser.Count != 2 || string.IsNullOrEmpty(sku) || sku.Length > 40 ||
                sku.Any(c => !(char.IsAsciiLetterOrDigit(c) || c == '-')) ||
                !int.TryParse(csv.GetField(1), NumberStyles.None,
                    CultureInfo.InvariantCulture, out var quantity))
                return Results.BadRequest(new { record = total + 1, error = "Invalid SKU or quantity." });
            rows.Rows.Add(sku.ToUpperInvariant(), quantity);
            total++;
            if (rows.Rows.Count == 2000)
            {
                await bulk.WriteToServerAsync(rows, ct);
                rows.Clear();
                log.LogInformation("Staged {RowCount} records", total);
            }
        }
        if (total == 0) return Results.BadRequest("No records.");
        if (rows.Rows.Count > 0) await bulk.WriteToServerAsync(rows, ct);
        cmd.CommandText = "INSERT dbo.Inventory(Sku, Quantity) SELECT Sku, Quantity FROM #Stock;";
        await cmd.ExecuteNonQueryAsync(ct);
        await tx.CommitAsync(ct);
        return Results.Ok(new { inserted = total });
    }
    catch (SqlException e) when (e.Number is 2601 or 2627)
    {
        return Results.Conflict("Duplicate SKU in the import or existing inventory.");
    }
    catch (Exception e) when (e is CsvHelperException or DecoderFallbackException)
    {
        return Results.BadRequest("Malformed CSV or invalid UTF-8.");
    }
});
app.Run();

Test success, duplicate rollback and cancellation

Run the app on port 5071. Save a file with header Sku,Quantity and rows MUG-1,12 and CABLE-42,8. Send it with curl and verify the destination table. Send the same file again and expect 409. Try a file with a new valid SKU followed by a negative quantity and verify the new SKU is absent after the request.

BASH
curl -i http://localhost:5071/inventory/import \
  -H 'Content-Type: text/csv' --data-binary @inventory.csv

A successful response is {"inserted":2}. A malformed row returns 400 and the transaction is disposed without commit. Kestrel can reject an oversized request before the endpoint returns a result. Also test a duplicate after more than 2,000 records to prove the earlier batch rolled back. Use a throttled local upload and cancel before commit to test cleanup. A lost HTTP response after commit is ambiguous, so give production clients a job or import identifier they can query.

Scale beyond a synchronous upload

Reading incrementally does not eliminate the cost of a long SQL transaction. Holding it through an upload can increase log use, lock duration, and recovery time. For imports that outlive the request budget, receive an approved object into private storage, create a persistent job, and stage in chunks keyed by import ID. Validate the staged dataset, publish progress from durable state, and commit changes with a separate short final transaction where feasible.

Use counters for staged rows, committed rows, rejected imports, latency, and aborted requests. The sample log reports staged rows, which may still be rolled back. Do not report them as committed. Measure batch size against memory, database log growth, and lock duration. Avoid disabling constraints or changing server-wide settings to improve a benchmark without testing the full workload.

Why is my staging table missing?

A local SQL Server temp table belongs to the creating session. Keep the connection open for create, bulk copy and the final insert. Changing transactions or connections changes the visibility and atomicity assumptions.

Does SqlBulkCopy deduplicate rows?

The unique constraints enforce uniqueness. Choose whether the business rules should reject, skip, or update duplicates. This sample rejects the entire import and reports a conflict. A real upsert needs its own concurrency policy.

Make duplicate behavior and restart semantics explicit

This endpoint treats the input file as a set of new SKUs. Duplicates inside the file and duplicates already in Inventory both reject the import. If your users instead expect an update, document whether the file is authoritative for all fields, whether existing values may change during import, and which version wins. A case-insensitive database collation and an uppercase SKU contract should be agreed before loading, rather than discovered after conflicting inserts.

For a staged job, use a stable job ID, reserve storage before accepting the upload, and track states such as received, parsing, ready, committed, and rejected. A worker restart can resume a durable parsing position only if the input is immutable and its offset aligns with a CSV record boundary. Because quoted fields may contain newlines, a byte offset is more reliable than a count of text lines only when the parser exposes a valid restart boundary. Otherwise, replay safely with a stage key.

Use a query for job status rather than holding the upload connection open for status notifications. If an HTTP client loses the final response, the status record should distinguish rejected work from an already committed job. Test the retry policy against that ambiguity.

Takeaway. Use bounded parsing and staging for memory control, and choose a separate recovery contract for uploads that must survive restarts or network loss.

References

Comments