Dapper and ADO.NET: High-Performance Data Access in .NET

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

public sealed record OrderRow(int Id, string Number, decimal Total);

public static async Task<List<OrderRow>> GetOrdersAsync(
    string connectionString, int customerId, DateTimeOffset since, CancellationToken ct)
{
    await using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync(ct);

    await using var command = connection.CreateCommand();
    command.CommandText = """
        SELECT Id, Number, Total
        FROM sales.Orders
        WHERE CustomerId = @customerId AND PlacedAt >= @since
        """;
    command.Parameters.Add("@customerId", SqlDbType.Int).Value = customerId;
    command.Parameters.Add("@since", SqlDbType.DateTimeOffset).Value = since;

    var orders = new List<OrderRow>();
    await using var reader = await command.ExecuteReaderAsync(ct);
    int id = reader.GetOrdinal("Id"), number = reader.GetOrdinal("Number");
    int total = reader.GetOrdinal("Total");

    while (await reader.ReadAsync(ct))
    {
        orders.Add(new OrderRow(reader.GetInt32(id), reader.GetString(number),
            reader.GetDecimal(total)));
    }

    return orders;
}

using Dapper;
using Microsoft.Data.SqlClient;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddSingleton(
    new SqlConnectionFactory(builder.Configuration.GetConnectionString("Shop")!));

var app = builder.Build();

app.MapGet("/orders/{id:int}", async (int id, SqlConnectionFactory db, CancellationToken ct) =>
{
    await using var connection = await db.OpenAsync(ct);
    var order = await connection.QuerySingleOrDefaultAsync<OrderDto>(new CommandDefinition(
        "SELECT Id, Number, Status, Total FROM sales.Orders WHERE Id = @id",
        new { id }, cancellationToken: ct));

    return order is null ? Results.NotFound() : Results.Ok(order);
});

app.Run();

public sealed record OrderDto(int Id, string Number, string Status, decimal Total);

public sealed class SqlConnectionFactory(string connectionString)
{
    public async Task<SqlConnection> OpenAsync(CancellationToken ct)
    {
        var connection = new SqlConnection(connectionString);
        try
        {
            await connection.OpenAsync(ct);
            return connection;
        }
        catch
        {
            await connection.DisposeAsync();
            throw;
        }
    }
}