6. Buffering, streaming, type handlers and dynamic results

// Unbuffered: stream rows one by one (huge exports) — keep the connection open while iterating
await foreach (var row in conn.QueryUnbufferedAsync<Order>("SELECT * FROM dbo.Orders"))
    await writer.WriteLineAsync($"{row.Id},{row.OrderDate:O}");

// dynamic results when you don't have a class
var rows = await conn.QueryAsync("SELECT Category, COUNT(*) AS Total FROM dbo.Products GROUP BY Category");
foreach (dynamic r in rows) Console.WriteLine($"{r.Category}: {r.Total}");

// Custom type handler: store a JSON column as a typed object
public class JsonHandler<T> : SqlMapper.TypeHandler<T>
{
    public override T Parse(object value) => JsonSerializer.Deserialize<T>((string)value)!;
    public override void SetValue(IDbDataParameter p, T? value) { p.DbType = DbType.String; p.Value = JsonSerializer.Serialize(value); }
}
SqlMapper.AddTypeHandler(new JsonHandler<Address>());

// DateOnly etc. need a handler on older Dapper versions