9. Dapper micro-ORM

using Dapper;

await using var conn = new SqlConnection(cs);

// Query → objects (parameters are anonymous objects; always parameterised)
var products = await conn.QueryAsync<Product>(
    "SELECT Id, Name, Price FROM Products WHERE CategoryId = @CategoryId AND Price > @Min",
    new { CategoryId = 3, Min = 500m });

var one = await conn.QuerySingleOrDefaultAsync<Product>("SELECT * FROM Products WHERE Id = @id", new { id });
int rows = await conn.ExecuteAsync("UPDATE Products SET Price = @Price WHERE Id = @Id", new { Id = 5, Price = 99m });

// IN list expansion
var some = await conn.QueryAsync<Product>("SELECT * FROM Products WHERE Id IN @Ids", new { Ids = new[] { 1, 2, 3 } });

// Multi-mapping (JOIN → object graph)
var orders = await conn.QueryAsync<Order, Customer, Order>(
    "SELECT o.*, c.* FROM Orders o JOIN Customers c ON c.Id = o.CustomerId",
    (o, c) => { o.Customer = c; return o; }, splitOn: "Id");

// Multiple result sets
using var multi = await conn.QueryMultipleAsync("SELECT * FROM Orders WHERE Id=@id; SELECT * FROM OrderLines WHERE OrderId=@id", new { id });
var order = await multi.ReadSingleAsync<Order>();
order.Lines = (await multi.ReadAsync<OrderLine>()).ToList();

// Stored procedure
var report = await conn.QueryAsync<SalesRow>("dbo.usp_SalesReport", new { Year = 2026 }, commandType: CommandType.StoredProcedure);

// Transaction
await conn.OpenAsync();
await using var tx = await conn.BeginTransactionAsync();
await conn.ExecuteAsync("INSERT ...", param, tx);
await tx.CommitAsync();