Exercises

using Jaunty.Attributes;

[Table("categories")]
public partial class Category
{
    [Key]
    [Column("category_id")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Column("category_name")]
    public string Name { get; set; }

    [Column("description")]
    public string Description { get; set; }
}

var category = new Category { Name = "Beverages", Description = "Soft drinks, coffees, teas" };

var newId = connection.Insert(category);
Console.WriteLine($"Inserted category with id {newId}");

var loaded = connection.QueryPartial<Category>(
    "SELECT category_id, category_name, description FROM categories WHERE category_id = @Id",
    (int)newId);

foreach (var c in loaded)
    Console.WriteLine($"{c.Id}: {c.Name} - {c.Description}");

System.InvalidOperationException: Strict mapping failed: property 'Discontinued' has no matching column

var details = connection.Query<ProductDetails>(
    "SELECT product_id AS Id, product_name AS Name, unit_price AS Price, discontinued AS Discontinued FROM products");

var details = connection.QueryPartial<ProductDetails>(
    "SELECT product_id AS Id, product_name AS Name, unit_price AS Price FROM products");
// details[i].Discontinued is left at its default value (false)

public class PriceListEntry
{
    public string Name { get; set; }
    public decimal Price { get; set; }
}

var cheapEntries = connection.QueryPartial<PriceListEntry>(
    "SELECT product_name AS Name, unit_price AS Price FROM products WHERE unit_price < @MaxPrice",
    20.00m);

foreach (var e in cheapEntries)
    Console.WriteLine($"{e.Name}: ${e.Price:F2}");

// Query<PriceListEntry> would also succeed today, because the SQL selects exactly the two
// columns PriceListEntry needs — strict mode is satisfied by coincidence. QueryPartial<T> is
// still the right choice: it documents the intent ("this is a deliberate projection") so that
// if someone later adds a Sku property to PriceListEntry without updating this SQL, nothing
// breaks. With Query<T> the same change would throw at runtime the next time this code path
// ran, because Sku would have no matching column.

using System.Data;
using Jaunty.Core;
using Jaunty.Configuration;
using Jaunty.Interceptors;

public class ProductRow
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

static ProductRow MapProductRow(IDataReader reader)
{
    return new ProductRow
    {
        Id = reader.GetInt32(reader.GetOrdinal("product_id")),
        Name = reader.GetString(reader.GetOrdinal("product_name")),
        Price = (decimal)reader.GetDouble(reader.GetOrdinal("unit_price"))
    };
}

public class QueryCountingInterceptor : ICommandInterceptor
{
    public int Count { get; private set; }

    public ValueTask OnCommandExecutingAsync(CommandContext context, CancellationToken ct)
    {
        Count++;
        return new ValueTask();
    }

    public ValueTask OnCommandExecutedAsync(CommandContext context, CancellationToken ct)
    {
        return new ValueTask();
    }

    public ValueTask OnCommandFailedAsync(CommandContext context, Exception ex, CancellationToken ct)
    {
        return new ValueTask();
    }
}

var counter = new QueryCountingInterceptor();
JauntyConfig.InterceptorPipeline = new InterceptorPipeline(new[] { counter });

var options = CommandOptions<ProductRow>.WithMapper(MapProductRow);
var rows = connection.Query<ProductRow>(
    "SELECT product_id, product_name, unit_price FROM products", options: options);

Console.WriteLine($"Queries executed so far: {counter.Count}");
// Run another query and confirm the count increases again
connection.QueryScalar<long>("SELECT COUNT(*) FROM products");
Console.WriteLine($"Queries executed after a second call: {counter.Count}");

using System.Diagnostics;
using Jaunty.Attributes;

[Table("products")]
public partial class BenchProduct
{
    [Key]
    [Column("product_id")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Column("product_name")]
    public string Name { get; set; }

    [Column("unit_price")]
    public decimal Price { get; set; }

    [Column("discontinued")]
    public bool Discontinued { get; set; }
}

var loopBatch = Enumerable.Range(1, 500)
    .Select(i => new BenchProduct { Name = $"Loop Item {i}", Price = i * 1.5m, Discontinued = false })
    .ToList();

var bulkBatch = Enumerable.Range(1, 500)
    .Select(i => new BenchProduct { Name = $"Bulk Item {i}", Price = i * 1.5m, Discontinued = false })
    .ToList();

var loopStopwatch = Stopwatch.StartNew();
using (var transaction = connection.BeginTransaction())
{
    foreach (var product in loopBatch)
        connection.Insert(product, CommandOptions.WithTransaction(transaction));

    transaction.Commit();
}
loopStopwatch.Stop();

var bulkStopwatch = Stopwatch.StartNew();
using (var transaction = connection.BeginTransaction())
{
    connection.BulkInsert(bulkBatch, CommandOptions.WithTransaction(transaction));
    transaction.Commit();
}
bulkStopwatch.Stop();

Console.WriteLine($"Loop insert (500 rows, one transaction): {loopStopwatch.ElapsedMilliseconds} ms");
Console.WriteLine($"BulkInsert (500 rows, one transaction): {bulkStopwatch.ElapsedMilliseconds} ms");

SELECT p.product_id, p.product_name, p.unit_price, p.discontinued, p.category_id
FROM products p
INNER JOIN categories c ON (p.category_id = c.category_id)
WHERE products.unit_price > 15
      ^^^^^^^^

Microsoft.Data.Sqlite.SqliteException
SQLite Error 1: 'no such column: products.unit_price'.

.Where("p.unit_price > 15")     // 4 rows

var rows = connection.From<ProductEntity>()
    .InnerJoin<Category>()
    .On("products.category_id", "categories.category_id")
    .Where("products.unit_price > 15")
    .SelectBoth();

.Where("p.product_id IN (SELECT product_id FROM products WHERE discontinued = 0)")