Bulk-copy imports with an explicit transaction

A CSV import button becomes a data consistency problem long before it becomes a performance problem. If the last row fails after earlier rows have been written, what should happen when the user uploads the file again? I establish that boundary before optimizing the inserts. The DataTable, SqlBulkCopy and transaction helpers in my project archive provide a useful starting point for explaining the design.

Separate parsing from persistence

The persistence method accepts a DataTable with a known schema, not unprocessed file text. A CSV parser must already have handled encoding, delimiters and quoted fields containing line breaks. Splitting each line on a comma breaks a value such as “Ankara, central”. I would reject that problem with a source row and column, rather than make the database layer guess how the original record was intended to look.

The example deliberately has one required text column named Name. Create the following table in an isolated SQL Server database. The code illustrates an import technique observed in the archive; it is not a complete reconstruction of the original application's import workflow.

CREATE TABLE dbo.ImportDemo
(
    Name nvarchar(100) NOT NULL
);

One connection owns the transaction

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

static async Task ImportAsync(string connectionString, DataTable rows)
{
    if (!rows.Columns.Contains("Name")) throw new ArgumentException("Name required");
    foreach (DataRow row in rows.Rows)
        if (string.IsNullOrWhiteSpace(row.Field<string>("Name")))
            throw new ArgumentException("Empty name");
    await using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();
    using var transaction = connection.BeginTransaction();
    using var bulk = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction);
    bulk.DestinationTableName = "dbo.ImportDemo";
    bulk.ColumnMappings.Add("Name", "Name");
    try { await bulk.WriteToServerAsync(rows); transaction.Commit(); }
    catch { transaction.Rollback(); throw; }
}

The first check verifies the column, and the loop rejects missing names before opening a connection. That keeps avoidable input errors outside the write transaction. Field<string> does not convert an arbitrary column into text: an integer Name column can still cause a cast error. Establishing the import schema once is more predictable than guessing a different type for every row.

The important connection is visible in the SqlBulkCopy constructor. The transaction is created by the same connection and is explicitly passed to bulk copy. Opening a transaction somewhere else does not automatically enlist a bulk object created from a different connection string. Commit runs only after the write completes. On failure the method attempts a rollback and propagates the error, allowing the caller to report an unsuccessful import.

Batch size does not define the commit boundary

Setting BatchSize does not by itself mean that each batch is independently committed. In this example an external transaction surrounds the entire write. Selecting UseInternalTransaction introduces a different contract, with transactions managed per batch; earlier batches may remain after a later batch fails. I would choose one model intentionally and explain whether the result applies to the file, individual rows or numbered batches.

Named ColumnMappings remove the dependency on column order. They do not replace length checks, null handling or validation of other destination columns. CHECK constraints and triggers also need to be considered alongside the selected SqlBulkCopyOptions. An application validation loop is not evidence that every database rule has run.

A second upload needs its own policy

This method does not prevent duplicate imports. Uploading Ada and Ece twice inserts them twice. A practical workflow needs an import identifier, a unique business key or a staging-and-merge policy. Transactions address partial writes; they do not make retries idempotent. The distinction matters when a user sees an error and immediately presses the button again.

DataTable holds the complete input in memory. Streaming through IDataReader can reduce that footprint, but changes the opportunity to validate the whole file before writing. Long transactions can also increase log usage and lock duration. I would measure with realistic files and destination indexes before choosing batch sizes. If the work takes longer than a reasonable request, an import job with visible status is a better boundary than an open browser request.

Check the persisted outcome

Start with two valid rows and verify a count increase of two. A blank name should fail before a connection is opened. Then make the second row violate a unique constraint and confirm that the first row is rolled back as well. A lost connection or missing commit response creates an uncertain outcome; adding an automatic retry at that point requires the duplicate-import policy to be sound. A success notification is the final presentation of that result, not the test for it.

ADO.NET bulk copy transaction behavior; SqlBulkCopyOptions contract.