Web API CRUD with Dapper and SQL Server on .NET 10

Last updated: November 3, 2026 · Tested with .NET 10.0.12 (SDK 10.0.401), Dapper 2.1.89, Microsoft.Data.SqlClient 7.1.1, SQL Server 2025 LocalDB, Windows 11

Short answer: for CRUD in an ASP.NET Core Web API with Dapper, register a connection factory (not a shared SqlConnection), open a new connection per call, return the new row with OUTPUT INSERTED.*, and use the rows-affected count from ExecuteAsync to choose between 204, 404 and 409. The full controller is below. The second half covers the Dapper errors I hit on SQL Server: IN (@ids), the 2,100-parameter limit, stored procedures, transactions, column mapping and cancellation.

This post replaces my 2022 .NET 6 version, which used Entity Framework Core. If you want EF Core, read the EF Core Web API tutorial on .NET 10 instead. This one is for people who want to write the SQL themselves.

Symptom	Cause	Fix
Incorrect syntax near ','	WHERE Id IN (@ids)	Drop the parentheses: IN @ids
The server supports a maximum of 2100 parameters	Dapper sends one parameter per list item	Table-valued parameter or OPENJSON
expects parameter '@Id', which was not supplied	Procedure called as text (EXEC ..., or a trailing space)	commandType: CommandType.StoredProcedure
requires the command to have a transaction	transaction: not passed to a call	Pass it to every call inside the transaction
Properties are 0 or null, no error	Column names don’t match (first_name)	Aliases, or MatchNamesWithUnderscores at startup
A parameterless default constructor or one matching signature	Positional record doesn’t match the columns	Same order and types, or a class with setters
The connection does not support MultipleActiveResultSets, 500s under load	One SqlConnection shared by requests	Connection factory, one connection per call

How I Tested It

One project creates a fresh database on SQL Server LocalDB, starts the API on a real Kestrel port and calls it over HTTP, then runs each Dapper scenario against the same database. Every output block below is copied from that run. LocalDB is the full SQL Server engine (here 2025, version 17.0), so the errors and query plans match a normal SQL Server instance. Timings are from a laptop and are only useful as comparisons.

Packages

<PackageReference Include="Dapper" Version="2.1.89" />
<PackageReference Include="Microsoft.Data.SqlClient" Version="7.1.1" />
<!-- SqlClient 7.1.1 pulls Pkcs 9.0.18 (NU1903); pin the patched version -->
<PackageReference Include="System.Security.Cryptography.Pkcs" Version="10.0.14" />

The third line surprised me. Microsoft.Data.SqlClient 7.1.1 brings in System.Security.Cryptography.Pkcs 9.0.18, and the restore warned about it twice:

warning NU1902: Package 'System.Security.Cryptography.Pkcs' 9.0.18 has a known moderate severity vulnerability, https://github.com/advisories/GHSA-2fmv-rv2c-59x7
warning NU1903: Package 'System.Security.Cryptography.Pkcs' 9.0.18 has a known high severity vulnerability, https://github.com/advisories/GHSA-846f-f764-76mx

Pinning 10.0.14 directly cleared it: dotnet list package --vulnerable --include-transitive then reported no vulnerable packages. Use Microsoft.Data.SqlClient, not the old System.Data.SqlClient, which is deprecated.

The Table

CREATE TABLE dbo.Employees (
  Id         int IDENTITY(1,1) PRIMARY KEY,
  Name       nvarchar(100) NOT NULL,
  Email      varchar(200)  NOT NULL,
  Salary     decimal(18,2) NOT NULL,
  CreatedUtc datetime2     NOT NULL DEFAULT SYSUTCDATETIME(),
  RowVersion rowversion
);

The rowversion column is for optimistic concurrency: SQL Server changes it on every update, so a PUT can tell whether someone else changed the row first.

Model, DTOs and the Connection Factory

public sealed class Employee
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Email { get; set; } = "";
    public decimal Salary { get; set; }
    public DateTime CreatedUtc { get; set; }
    public byte[] RowVersion { get; set; } = [];
}

public sealed record EmployeeDto(string Name, string Email, decimal Salary);
public sealed record EmployeeUpdateDto(string Name, string Email, decimal Salary, string RowVersion);

public interface IDbConnectionFactory
{
    SqlConnection Create();
}

public sealed class SqlConnectionFactory(string connectionString) : IDbConnectionFactory
{
    public SqlConnection Create() => new(connectionString);
}

builder.Services.AddControllers();
builder.Services.AddSingleton<IDbConnectionFactory>(new SqlConnectionFactory(Db.Conn));
builder.Services.AddScoped<EmployeeRepository>();

The factory is a singleton because it only holds a string. The connections it creates are short-lived, and ADO.NET pools them, so opening one per call is cheap. Later I show what happens when you register a single SqlConnection instead.

The Repository

public async Task<Employee?> GetByIdAsync(int id, CancellationToken ct)
{
    await using var conn = factory.Create();
    return await conn.QuerySingleOrDefaultAsync<Employee>(new CommandDefinition(
        "SELECT Id, Name, Email, Salary, CreatedUtc, RowVersion FROM dbo.Employees WHERE Id = @id",
        new { id }, cancellationToken: ct));
}

public async Task<Employee> CreateAsync(EmployeeDto dto, CancellationToken ct)
{
    await using var conn = factory.Create();
    return await conn.QuerySingleAsync<Employee>(new CommandDefinition("""
        INSERT INTO dbo.Employees (Name, Email, Salary)
        OUTPUT INSERTED.Id, INSERTED.Name, INSERTED.Email, INSERTED.Salary,
               INSERTED.CreatedUtc, INSERTED.RowVersion
        VALUES (@Name, @Email, @Salary)
        """, dto, cancellationToken: ct));
}

// Returns rows affected: 0 = missing or stale RowVersion.
public async Task<int> UpdateAsync(int id, EmployeeUpdateDto dto, CancellationToken ct)
{
    await using var conn = factory.Create();
    return await conn.ExecuteAsync(new CommandDefinition("""
        UPDATE dbo.Employees
        SET Name = @Name, Email = @Email, Salary = @Salary
        WHERE Id = @id AND RowVersion = @rv
        """,
        new { id, dto.Name, dto.Email, dto.Salary, rv = Convert.FromBase64String(dto.RowVersion) },
        cancellationToken: ct));
}

public async Task<int> DeleteAsync(int id, CancellationToken ct)
{
    await using var conn = factory.Create();
    return await conn.ExecuteAsync(new CommandDefinition(
        "DELETE FROM dbo.Employees WHERE Id = @id", new { id }, cancellationToken: ct));
}

Three things here are deliberate. Every value goes in as a parameter (@id, @Name), never concatenated into the SQL; my SQL injection post shows what string concatenation does. Dapper opens the connection for you when it’s closed, so there’s no OpenAsync. And every call uses CommandDefinition, because that’s the only way to pass a CancellationToken; more on that below.

The Controller

[ApiController]
[Route("api/[controller]")]
public sealed class EmployeesController(EmployeeRepository repo) : ControllerBase
{
    [HttpGet]
    public async Task<IEnumerable<Employee>> GetAll(CancellationToken ct) => await repo.GetAllAsync(ct);

    [HttpGet("{id:int}")]
    public async Task<ActionResult<Employee>> GetById(int id, CancellationToken ct)
    {
        var e = await repo.GetByIdAsync(id, ct);
        return e is null ? NotFound() : e;
    }

    [HttpPost]
    public async Task<ActionResult<Employee>> Create(EmployeeDto dto, CancellationToken ct)
    {
        var e = await repo.CreateAsync(dto, ct);
        return CreatedAtAction(nameof(GetById), new { id = e.Id }, e);
    }

    [HttpPut("{id:int}")]
    public async Task<IActionResult> Update(int id, EmployeeUpdateDto dto, CancellationToken ct)
    {
        if (await repo.UpdateAsync(id, dto, ct) == 1) return NoContent();
        return await repo.ExistsAsync(id, ct) ? Conflict() : NotFound();
    }

    [HttpDelete("{id:int}")]
    public async Task<IActionResult> Delete(int id, CancellationToken ct)
        => await repo.DeleteAsync(id, ct) == 1 ? NoContent() : NotFound();
}

What the API Returns

POST /api/employees -> 201 Created  Location: http://127.0.0.1:5079/api/Employees/1
{"id":1,"name":"Asha Rao","email":"asha@example.com","salary":85000.00,"createdUtc":"2026-11-03T01:39:18.8330182","rowVersion":"AAAAAAAAB9E="}
GET /api/employees/99 -> 404 NotFound
PUT /api/employees/1 (current RowVersion) -> 204 NoContent
PUT /api/employees/1 (same old RowVersion again) -> 409 Conflict
PUT /api/employees/99 -> 404 NotFound
DELETE /api/employees/2 -> 204 NoContent
DELETE /api/employees/2 again -> 404 NotFound

The second PUT is the case most CRUD tutorials get wrong. It sends the RowVersion the client read before the first update, so it describes a row that no longer exists. Without the RowVersion check in the WHERE, it would silently overwrite the first update (last write wins). With it, ExecuteAsync returns 0 and the API answers 409, and the client can reload and retry. The extra ExistsAsync query only runs on that failure path, to tell 409 from 404.

Validation comes for free from [ApiController] and nullable reference types. A POST without email never reaches the database:

POST without Email (non-nullable record member) -> 400 BadRequest
{"type":"https://tools.ietf.org/html/rfc9110#section-15.5.1","title":"One or more validation errors occurred.","status":400,"errors":{"Email":["The Email field is required."]},"traceId":"0HNPP9D3GTL54:0000000B"}

One thing I’d change in a real API: the createdUtc value has no Z at the end. See “DateTime comes back without a time zone” below.

Getting the New Id After an INSERT

This is the most-viewed Dapper question on Stack Overflow. All of these work on SQL Server:

ExecuteScalarAsync (object) SCOPE_IDENTITY() -> 8 (Decimal)
ExecuteScalarAsync<int> SCOPE_IDENTITY() -> 9
QuerySingleAsync<int> SCOPE_IDENTITY() -> 10
QuerySingleAsync<int> OUTPUT INSERTED.Id -> 11
ExecuteAsync INSERT -> 1 (rows affected, not the Id)

SCOPE_IDENTITY() returns decimal, which you’ll see if you read it as object; Dapper converts it when you ask for int. I prefer OUTPUT INSERTED.* because it returns the whole row in the same statement, including the defaults and the RowVersion, so the API can return it without a second SELECT. The last line is a common mistake: ExecuteAsync returns the number of rows affected, so it returns 1 for every successful insert, never the Id.

WHERE Id IN: Parentheses and the 2,100 Limit

var inRows = await conn.QueryAsync<int>("SELECT Id FROM dbo.Employees WHERE Id IN @ids", new { ids });

IN @ids [1,3,4] -> [1,3,4]
IN (@ids) with parentheses -> SqlException: Incorrect syntax near ','.
IN @ids with empty array -> [] (no SQL error)
IN @ids with 2,101 ids -> SqlException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
TVP dbo.IntList with 2,101 ids -> 11 rows
OPENJSON(@ids) with 2,101 ids -> 11 rows

Dapper expands IN @ids into IN (@ids1, @ids2, @ids3), so if you add your own parentheses you get two sets and a syntax error. An empty array is handled for you. The real trap is size: each item is one SQL parameter, and SQL Server allows 2,100 per request. Code that works in testing with 50 ids fails in production the day someone selects 2,101. Two fixes that both handled 2,101 ids in one parameter:

var viaTvp = await conn.QueryAsync<int>("SELECT e.Id FROM dbo.Employees e JOIN @ids i ON i.Value = e.Id",
    new { ids = tvp.AsTableValuedParameter("dbo.IntList") });

var viaJson = await conn.QueryAsync<int>(
    "SELECT Id FROM dbo.Employees WHERE Id IN (SELECT CAST(value AS int) FROM OPENJSON(@ids))",
    new { ids = JsonSerializer.Serialize(many) });

The table-valued parameter needs a type in the database (CREATE TYPE dbo.IntList AS TABLE (Value int NOT NULL PRIMARY KEY)) and a DataTable in C#. OPENJSON needs nothing extra, but your database compatibility level must be 130 or higher.

Stored Procedures

I expected this call, without commandType, to fail. It didn’t:

no commandType -> Asha Rao
"EXEC dbo.usp_GetEmployee" as text -> SqlException: Procedure or function 'usp_GetEmployee' expects parameter '@Id', which was not supplied.
"dbo.usp_GetEmployee " (trailing space) -> SqlException: Procedure or function 'usp_GetEmployee' expects parameter '@Id', which was not supplied.
CommandType.StoredProcedure -> Asha Rao, salary 90000.00

Dapper 2.1.89 treated a command with no whitespace as a procedure name. Add a trailing space, or write EXEC, and it’s sent as plain text, so your parameters are never bound to the procedure and SQL Server reports the classic “expects parameter” error. Don’t rely on the guess; always pass the command type:

var p2 = await conn.QuerySingleOrDefaultAsync<Employee>("dbo.usp_GetEmployee", new { Id = 1 },
    commandType: CommandType.StoredProcedure);

For output parameters and return values, use DynamicParameters:

var dp = new DynamicParameters(new { Name = "Proc User", Email = "proc@example.com", Salary = 61000m });
dp.Add("NewId", dbType: DbType.Int32, direction: ParameterDirection.Output);
dp.Add("ret", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
var reader = await conn.QueryAsync<(int Id, string Name)>("dbo.usp_AddEmployee", dp, commandType: CommandType.StoredProcedure);

usp_AddEmployee result set -> (13, Proc User); @NewId = 13; RETURN = 42
unbuffered, read @NewId while the reader is open -> NullReferenceException: Object reference not set to an instance of an object.
unbuffered, after the reader is drained -> 14

SQL Server sends output parameters after the last result set. The normal QueryAsync reads everything first, so dp.Get works. With QueryUnbufferedAsync, reading the output parameter while rows are still streaming gave me a NullReferenceException, not a helpful message. Drain the rows first.

Transactions

Starting a transaction on the connection isn’t enough; every Dapper call inside it needs transaction:. Forget one and the call throws:

Execute without transaction: -> InvalidOperationException: BeginExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.

await conn.ExecuteAsync("INSERT dbo.Employees (Name, Email, Salary) VALUES ('T1','t1@x',1)", transaction: tx);
await conn.ExecuteAsync("INSERT dbo.Employees (Name, Email, Salary) VALUES (NULL,'t2@x',1)", transaction: tx);
await tx.CommitAsync();

second INSERT failed (515: Cannot insert the value NULL into column 'Name', table 'DapperCrudDemo.dbo.Employees'; column does not allow nulls. INSERT fails.) -> rolled back
rows before 13, after 13

The first insert was rolled back with the second, which is the point. If you use a repository per table, the repositories need to share the connection and transaction; the Unit of Work post covers that pattern.

Column Names That Don’t Match

On an old database with first_name columns, Dapper doesn’t throw. It just leaves the properties empty:

no mapping: StaffId=0 FirstName=null LastName=null  (no exception)
aliases:    StaffId=1 FirstName=Asha LastName=Rao
MatchNamesWithUnderscores=true, same SQL as before: StaffId=0 FirstName=null LastName=null
MatchNamesWithUnderscores=true, new SQL text:    StaffId=0 FirstName=null LastName=null
same SQL as before, after SqlMapper.PurgeQueryCache(): StaffId=1 FirstName=Asha LastName=Rao

Aliases (first_name AS FirstName) always work. DefaultTypeMap.MatchNamesWithUnderscores = true also works, but lines 3 and 4 show the catch: I turned it on after the first query, and it had no effect, even with different SQL text. Dapper caches how it maps a set of columns to a type, and that cache was built before the setting changed. Only after SqlMapper.PurgeQueryCache() did it map. In an app, set it once in Program.cs before any query runs.

Records: Order and Types Must Match

public sealed record EmpRow(int Id, string Name, decimal Salary);

record (int Id, string Name, decimal Salary) -> EmpRow { Id = 1, Name = Asha Rao, Salary = 90000.00 }
record with double Salary -> InvalidOperationException: A parameterless default constructor or one matching signature (System.Int32 Id, System.String Name, System.Decimal Salary) is required for EmpRowDouble materialization
record, columns in another order -> InvalidOperationException: A parameterless default constructor or one matching signature (System.Int32 Id, System.Decimal Salary, System.String Name) is required for EmpRow materialization
record, SELECT * -> InvalidOperationException: A parameterless default constructor or one matching signature (System.Int32 Id, System.String Name, System.String Email, System.Decimal Salary, System.DateTime CreatedUtc, System.Byte[] RowVersion) is required for EmpRow materialization

A positional record has no parameterless constructor, so Dapper needs a constructor whose parameters match the columns exactly: same count, same order, same .NET types. A decimal column into a double parameter fails, and so does SELECT *. The error message helps once you know how to read it: the signature it prints is the one your query returned. Either list the columns in constructor order or use a class with setters, which maps by name.

Cancellation and Timeouts

QueryAsync(sql, param) has no CancellationToken parameter, so a token in scope does nothing unless you pass it through CommandDefinition:

ExecuteAsync(sql) with a 200 ms token in scope (not passed): 3015 ms
CommandDefinition with token -> SqlException: A severe error occurred on the current command.  The results, if any, should be discarded.
CommandDefinition with token: 204 ms
connection state after cancel: Open, next query -> 1
35 s query, default timeout -> SqlException: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
after 30004 ms

In a Web API this matters because ASP.NET Core passes the request’s token to your action. When the client disconnects, a query that received the token stops; one that didn’t keeps the database busy. Note the exception type: cancelling gave me a SqlException with a “severe error” message, not an OperationCanceledException, so check ct.IsCancellationRequested in your error handling before logging it as a database failure. The connection was still usable afterwards. Separately, the default command timeout is 30 seconds; set commandTimeout on CommandDefinition for reports that need longer.

One SqlConnection for the Whole App

Registering a single connection looks tidy and works when you test one request at a time. I sent 20 requests at once to an endpoint that used one shared connection, and to the same endpoint using the factory:

/bad/1: 200 x1, 500 x19
/good/1: 200 x20
shared connection, 5 parallel queries -> InvalidOperationException: The connection does not support MultipleActiveResultSets.

19 of 20 requests failed. A SqlConnection isn’t thread-safe and runs one command at a time. Don’t reach for MARS in the connection string: it lets one caller run several commands on a connection, it doesn’t make the connection safe to share between requests. The factory plus connection pooling is the fix. The same lifetime problem shows up with EF Core’s DbContext; I tested that in cannot consume scoped service from singleton.

DateTime Comes Back Without a Time Zone

CreatedUtc Kind = Unspecified, JSON = "2026-11-03T01:39:18.8330182"

The column is filled by SYSUTCDATETIME(), but datetime2 stores no zone, so it comes back as DateTimeKind.Unspecified and the JSON has no Z. A JavaScript client then reads it as local time. Use datetimeoffset for new columns, or mark the value as UTC when you read it.

String Parameters and varchar Columns

Dapper sends a C# string as nvarchar. My Email column is varchar with an index, and with 30,013 rows in the table I captured the actual plans:

string param   -> 3 row, ops: Index Scan; CONVERT_IMPLICIT on Email; rows read 30013
DbString IsAnsi -> 3 row, ops: Index Seek; no implicit conversion; rows read 3

new { email = new DbString { Value = "bulk5000@example.com", IsAnsi = true, Length = 200 } });

Same 3 rows, but the plain string made SQL Server convert every Email value and read all 30,013 rows instead of seeking to 3. This won’t show up with 20 test rows. Use DbString with IsAnsi = true for varchar columns, or make the columns nvarchar.

Inserting Many Rows

ExecuteAsync(sql, list) -> 10000 rows in 2588 ms (one INSERT per item)
same, inside one transaction -> 10000 rows in 1107 ms
SqlBulkCopy -> 10000 rows in 167 ms

Passing a list to ExecuteAsync looks like a bulk insert, but Dapper runs the statement once per item. A transaction around it was about 2.3 times faster here, and SqlBulkCopy was about 15 times faster than the plain loop. For an import endpoint, use SqlBulkCopy.

Checklist

• Register a connection factory as a singleton; never a shared SqlConnection.
• Pass every value as a parameter.
• Return new rows with OUTPUT INSERTED.*; ExecuteAsync returns rows affected.
• Use the rows-affected count for 404, and a rowversion in the WHERE for 409.
• Write IN @ids without parentheses, and switch to a TVP or OPENJSON when the list can pass 2,100.
• Always pass CommandType.StoredProcedure.
• Pass transaction: to every call inside a transaction.
• Set MatchNamesWithUnderscores at startup, or use aliases.
• Use CommandDefinition to pass the request’s CancellationToken.
• Use DbString { IsAnsi = true } for varchar columns.
• Check dotnet list package --vulnerable --include-transitive after adding SqlClient.

Related: the same API with EF Core on .NET 10 · SQL injection prevention in C# · Repository and Unit of Work · Dapper on GitHub · all ASP.NET Core tutorials