1. Setup and the core methods

// dotnet add package Dapper
// dotnet add package Microsoft.Data.SqlClient
using Dapper;
using Microsoft.Data.SqlClient;

public record Product(int Id, string Name, string Category, decimal Price);

await using var conn = new SqlConnection(connectionString);

// Many rows
IEnumerable<Product> all = await conn.QueryAsync<Product>("SELECT Id, Name, Category, Price FROM dbo.Products");

// Exactly one row expected
Product p = await conn.QuerySingleAsync<Product>("SELECT * FROM dbo.Products WHERE Id = @Id", new { Id = 5 });

// First or nothing
Product? maybe = await conn.QueryFirstOrDefaultAsync<Product>(
    "SELECT TOP 1 * FROM dbo.Products WHERE Category = @Cat ORDER BY Price", new { Cat = "Pens" });

// Scalar value
int count = await conn.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM dbo.Products");

// INSERT/UPDATE/DELETE — returns affected rows
int rows = await conn.ExecuteAsync("UPDATE dbo.Products SET Price = Price * 1.1 WHERE Category = @Cat", new { Cat = "Pens" });

// Insert and get the new id
int newId = await conn.QuerySingleAsync<int>(
    "INSERT INTO dbo.Products (Name, Category, Price) OUTPUT INSERTED.Id VALUES (@Name, @Category, @Price)",
    new { Name = "Gel pen", Category = "Pens", Price = 25m });