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.
| 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.
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.
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.
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.
Comments
Post a Comment