Performance Guide

using CSharpDB.Engine;

// If the file exists, delete it to start fresh
if (File.Exists("app.db"))
    File.Delete("app.db");

// Configure for hot local lookup workloads
var options = new DatabaseOptions()
    .ConfigureStorageEngine(builder => builder.UseDirectLookupOptimizedPreset());

await using var db = await Database.OpenAsync("app.db", options);

// Create a table and seed some data
await using (await db.ExecuteAsync(
    "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"))
{ }

await db.BeginTransactionAsync();
for (int i = 1; i <= 100; i++)
{
    await using (await db.ExecuteAsync(
        $"INSERT INTO users VALUES ({i}, 'User {i}', 'user{i}@example.com')"))
    { }
}
await db.CommitAsync();

// --- Point read via SQL (hot path) ---
await using var result = await db.ExecuteAsync(
    "SELECT id, name FROM users WHERE id = 42");
if (await result.MoveNextAsync())
{
    long id = result.Current[0].AsInteger;
    string name = result.Current[1].AsText;
    Console.WriteLine($"Found user {id}: {name}");
}

// --- Point read via Collection API (skips SQL parser entirely) ---
var profiles = await db.GetCollectionAsync<UserProfile>("profiles");
await profiles.PutAsync("user-42", new UserProfile("User 42", "user42@example.com"));

var profile = await profiles.GetAsync("user-42");
if (profile is not null)
    Console.WriteLine($"Profile: {profile.Name}, {profile.Email}");

public record UserProfile(string Name, string Email);

using CSharpDB.Engine;

// If the file exists, delete it to start fresh
if (File.Exists("service.db"))
    File.Delete("service.db");

await using var db = await Database.OpenAsync("service.db");

// Set up tables and seed data
await using (await db.ExecuteAsync(
    "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, tier TEXT)"))
{ }
await using (await db.ExecuteAsync(
    "CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL)"))
{ }

await db.BeginTransactionAsync();
await using (await db.ExecuteAsync("INSERT INTO users VALUES (1, 'Alice', 'gold')")) { }
await using (await db.ExecuteAsync("INSERT INTO users VALUES (2, 'Bob', 'silver')")) { }
await using (await db.ExecuteAsync("INSERT INTO orders VALUES (1, 1, 99.95)")) { }
await using (await db.ExecuteAsync("INSERT INTO orders VALUES (2, 1, 149.50)")) { }
await db.CommitAsync();

// --- Reuse a single ReaderSession for a burst of related reads ---
// This is ~3x faster than creating a new session per query.
using var reader = db.CreateReaderSession();

// Query 1: gold-tier users
await using (var users = await reader.ExecuteReadAsync(
    "SELECT id, name FROM users WHERE tier = 'gold'"))
{
    await foreach (var row in users.GetRowsAsync())
    {
        Console.WriteLine($"Gold user: {row[0].AsInteger} - {row[1].AsText}");
    }
}
// QueryResult is disposed before the next query on the same session

// Query 2: orders for a specific customer (same snapshot, no new session cost)
await using (var orders = await reader.ExecuteReadAsync(
    "SELECT id, total FROM orders WHERE customer_id = 1"))
{
    await foreach (var row in orders.GetRowsAsync())
    {
        Console.WriteLine($"Order {row[0].AsInteger}: ${row[1].AsReal:F2}");
    }
}

// Meanwhile, the writer can continue without blocking readers.
await using (await db.ExecuteAsync(
    "INSERT INTO orders VALUES (3, 2, 75.00)"))
{ }
// The reader session still sees the snapshot from before this insert.

using CSharpDB.Engine;

// If the file exists, delete it to start fresh
if (File.Exists("orders.db"))
    File.Delete("orders.db");

await using var db = await Database.OpenAsync("orders.db");

// Create a table with several columns (simulating a wide row)
await using (await db.ExecuteAsync(@"
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY,
        customer_id INTEGER,
        created_at INTEGER,
        status TEXT,
        total REAL,
        notes TEXT,
        shipping_address TEXT
    )"))
{ }

// Build indexes that match the query shapes we care about
await using (await db.ExecuteAsync(
    "CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at)"))
{ }
await using (await db.ExecuteAsync(
    "CREATE INDEX IF NOT EXISTS idx_orders_status_created ON orders(status, created_at)"))
{ }

// Seed 1000 rows
await db.BeginTransactionAsync();
for (int i = 1; i <= 1000; i++)
{
    string status = i % 3 == 0 ? "shipped" : i % 3 == 1 ? "pending" : "delivered";
    await using (await db.ExecuteAsync(
        $"INSERT INTO orders VALUES ({i}, {i % 50}, {1700000000 + i * 3600}, '{status}', {i * 1.5}, 'Note {i}', 'Address {i}')"))
    { }
}
await db.CommitAsync();

// GOOD: Narrow/covered projection -- engine stays on index data
// The covered index-order scan is ~2.8x faster than the full-row fetch path.
await using (var narrow = await db.ExecuteAsync(@"
    SELECT id, created_at
    FROM orders
    WHERE created_at BETWEEN 1700000000 AND 1700050000
    ORDER BY created_at
    LIMIT 100"))
{
    int count = 0;
    await foreach (var row in narrow.GetRowsAsync())
        count++;
    Console.WriteLine($"Narrow range query returned {count} rows");
}

// GOOD: Composite index matches left-to-right predicate shape
await using (var composite = await db.ExecuteAsync(@"
    SELECT id, status, created_at
    FROM orders
    WHERE status = 'shipped' AND created_at > 1700100000
    ORDER BY created_at
    LIMIT 50"))
{
    var rows = await composite.ToListAsync();
    Console.WriteLine($"Composite index query returned {rows.Count} rows");
}

using CSharpDB.Engine;

// If the file exists, delete it to start fresh
if (File.Exists("reporting.db"))
    File.Delete("reporting.db");

await using var db = await Database.OpenAsync("reporting.db");

// Schema
await using (await db.ExecuteAsync(@"
    CREATE TABLE IF NOT EXISTS customers (
        id INTEGER PRIMARY KEY, name TEXT, region TEXT)"))
{ }
await using (await db.ExecuteAsync(@"
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL, created_at INTEGER)"))
{ }
await using (await db.ExecuteAsync(@"
    CREATE TABLE IF NOT EXISTS order_items (
        id INTEGER PRIMARY KEY, order_id INTEGER, product TEXT, quantity INTEGER, price REAL)"))
{ }

// Indexes on join keys and selective filter columns
await using (await db.ExecuteAsync(
    "CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id)"))
{ }
await using (await db.ExecuteAsync(
    "CREATE INDEX IF NOT EXISTS idx_items_order ON order_items(order_id)"))
{ }
await using (await db.ExecuteAsync(
    "CREATE INDEX IF NOT EXISTS idx_customers_region ON customers(region)"))
{ }

// Bulk load
await db.BeginTransactionAsync();
for (int i = 1; i <= 200; i++)
{
    string region = i % 4 == 0 ? "West" : i % 4 == 1 ? "East" : i % 4 == 2 ? "North" : "South";
    await using (await db.ExecuteAsync(
        $"INSERT INTO customers VALUES ({i}, 'Customer {i}', '{region}')"))
    { }
}
await db.CommitAsync();

// Run ANALYZE so the planner can make informed join-order and build-side decisions.
// Skipping this leaves planner quality on the table.
await using (await db.ExecuteAsync("ANALYZE")) { }

// Inspect the stats the planner is now using
await using (var tableStats = await db.ExecuteAsync(
    "SELECT * FROM sys.table_stats ORDER BY table_name"))
{
    Console.WriteLine("=== Table Stats ===");
    await foreach (var row in tableStats.GetRowsAsync())
    {
        Console.WriteLine($"  {row[0].AsText}: {row[1].AsInteger} rows");
    }
}

// Multi-table join -- planner uses stats for build-side choice and join ordering.
// With ANALYZE: planner swaps build side for ~37% faster joins (7.16ms vs 11.43ms).
await using (var report = await db.ExecuteAsync(@"
    SELECT c.name, c.region, COUNT(oi.id) AS item_count, SUM(oi.price * oi.quantity) AS revenue
    FROM customers c
    INNER JOIN orders o ON o.customer_id = c.id
    INNER JOIN order_items oi ON oi.order_id = o.id
    WHERE c.region = 'West'
    GROUP BY c.name, c.region
    ORDER BY revenue
    LIMIT 20"))
{
    Console.WriteLine("\n=== Revenue Report (West Region) ===");
    await foreach (var row in report.GetRowsAsync())
    {
        string name = row[0].AsText;
        long items = row[2].AsInteger;
        double revenue = row[3].AsReal;
        Console.WriteLine($"  {name}: {items} items, ${revenue:F2}");
    }
}

using CSharpDB.Engine;
using System.Diagnostics;

// If the files exist, delete them to start fresh
if (File.Exists("ingest.db"))
    File.Delete("ingest.db");
if (File.Exists("ingest2.db"))
    File.Delete("ingest2.db");

var options = new DatabaseOptions()
    .ConfigureStorageEngine(builder => builder.UseWriteOptimizedPreset());

await using var db = await Database.OpenAsync("ingest.db", options);

await using (await db.ExecuteAsync(@"
    CREATE TABLE IF NOT EXISTS events (
        id INTEGER PRIMARY KEY,
        timestamp_ticks INTEGER,
        source TEXT,
        payload TEXT
    )"))
{ }

var events = Enumerable.Range(1, 10_000)
    .Select(i => (Id: i, Ticks: DateTime.UtcNow.Ticks + i,
                   Source: $"sensor-{i % 20}", Payload: $"data-{i}"))
    .ToArray();

// --- GOOD: explicit transaction batching (~197K rows/sec at batch=1000) ---
var sw = Stopwatch.StartNew();
const int batchSize = 1000;

for (int offset = 0; offset < events.Length; offset += batchSize)
{
    var batch = events[offset..Math.Min(offset + batchSize, events.Length)];

    await db.BeginTransactionAsync();
    try
    {
        foreach (var e in batch)
        {
            await using (await db.ExecuteAsync(
                $"INSERT INTO events VALUES ({e.Id}, {e.Ticks}, '{e.Source}', '{e.Payload}')"))
            { }
        }
        await db.CommitAsync();
    }
    catch
    {
        await db.RollbackAsync();
        throw;
    }
}

sw.Stop();
Console.WriteLine($"Inserted {events.Length} events in {sw.ElapsedMilliseconds}ms");

// --- EVEN BETTER: use InsertBatch for structured bulk inserts ---
await using var db2 = await Database.OpenAsync("ingest2.db", options);
await using (await db2.ExecuteAsync(@"
    CREATE TABLE IF NOT EXISTS events (
        id INTEGER PRIMARY KEY, timestamp_ticks INTEGER, source TEXT, payload TEXT)"))
{ }

sw.Restart();
await db2.BeginTransactionAsync();
var insertBatch = db2.PrepareInsertBatch("events");
foreach (var e in events)
{
    insertBatch.AddRow(
        DbValue.FromInteger(e.Id + 100_000),
        DbValue.FromInteger(e.Ticks),
        DbValue.FromText(e.Source),
        DbValue.FromText(e.Payload)
    );
}
int inserted = await insertBatch.ExecuteAsync();
await db2.CommitAsync();
sw.Stop();
Console.WriteLine($"InsertBatch: {inserted} rows in {sw.ElapsedMilliseconds}ms");

using CSharpDB.Engine;
using System.Diagnostics;

// If the file exists, delete it to start fresh
if (File.Exists("contention.db"))
    File.Delete("contention.db");

// Baseline: write-optimized preset (good default for contention too)
var baseOptions = new DatabaseOptions()
    .ConfigureStorageEngine(builder => builder.UseWriteOptimizedPreset());

// For 8+ concurrent writers, WAL preallocation can help throughput
var preallocOptions = new DatabaseOptions()
    .ConfigureStorageEngine(builder => builder
        .UseWriteOptimizedPreset()
        .UseWalPreallocationChunkBytes(1024 * 1024)); // 1 MiB prealloc

// For 4 concurrent writers, a small batch window can coalesce commits
// Do NOT use this for single-writer workloads (it hurts there)
var batchWindowOptions = new DatabaseOptions()
    .ConfigureStorageEngine(builder => builder
        .UseWriteOptimizedPreset()
        .UseDurableCommitBatchWindow(TimeSpan.FromMicroseconds(250)));

await using var db = await Database.OpenAsync("contention.db", baseOptions);

await using (await db.ExecuteAsync(
    "CREATE TABLE IF NOT EXISTS counters (id INTEGER PRIMARY KEY, value INTEGER)"))
{ }
await using (await db.ExecuteAsync("INSERT INTO counters VALUES (1, 0)")) { }

// Simulate 8 concurrent writers each doing single-row auto-commits.
// CSharpDB is single-writer: auto-commit serializes through the write gate,
// so concurrent tasks safely queue up without explicit transaction management.
int writerCount = 8;
int writesPerWriter = 50;
var sw = Stopwatch.StartNew();

var tasks = Enumerable.Range(0, writerCount).Select(async w =>
{
    for (int i = 0; i < writesPerWriter; i++)
    {
        await using (await db.ExecuteAsync(
            "UPDATE counters SET value = value + 1 WHERE id = 1"))
        { }
    }
}).ToArray();

await Task.WhenAll(tasks);
sw.Stop();

int totalCommits = writerCount * writesPerWriter;
Console.WriteLine($"{writerCount} writers: {totalCommits * 1000.0 / sw.ElapsedMilliseconds:F0} commits/sec");

using CSharpDB.Engine;

// If the file exists, delete it to start fresh
if (File.Exists("large-dataset.db"))
    File.Delete("large-dataset.db");

// UseDirectColdFileLookupPreset uses memory-mapped reads, best for one-shot probes
var coldOptions = new DatabaseOptions()
    .ConfigureStorageEngine(builder => builder.UseDirectColdFileLookupPreset());

await using var db = await Database.OpenAsync("large-dataset.db", coldOptions);

// Seed a table so there is something to probe
await using (await db.ExecuteAsync(
    "CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY, source TEXT, payload TEXT)"))
{ }
await using (await db.ExecuteAsync(
    "CREATE INDEX IF NOT EXISTS idx_events_source ON events(source)"))
{ }

await db.BeginTransactionAsync();
for (int i = 1; i <= 500; i++)
{
    await using (await db.ExecuteAsync(
        $"INSERT INTO events VALUES ({i}, 'sensor-{i % 10}', 'data-{i}')"))
    { }
}
await db.CommitAsync();

// Probe schema
var tables = db.GetTableNames();
Console.WriteLine($"Tables: {string.Join(", ", tables)}");

foreach (var tableName in tables)
{
    var schema = db.GetTableSchema(tableName);
    if (schema is not null)
    {
        var cols = string.Join(", ", schema.Columns.Select(c => $"{c.Name} {c.Type}"));
        Console.WriteLine($"  {tableName}({cols})");
    }
}

// Probe indexes
var indexes = db.GetIndexes();
foreach (var idx in indexes)
{
    Console.WriteLine($"  Index {idx.IndexName} on {idx.TableName}({string.Join(", ", idx.Columns)})");
}

// Quick analytics query on cold data
await using (var result = await db.ExecuteAsync(
    "SELECT COUNT(*), MIN(id), MAX(id) FROM events"))
{
    if (await result.MoveNextAsync())
    {
        Console.WriteLine($"Events: count={result.Current[0].AsInteger}, " +
            $"min_id={result.Current[1].AsInteger}, max_id={result.Current[2].AsInteger}");
    }
}

// --- Bounded-cache workload ---
// Close the first instance before reopening with a different preset
await db.DisposeAsync();

// Start at 2048 pages, not 16 (tiny caches are expensive)
var boundedOptions = new DatabaseOptions()
    .ConfigureStorageEngine(builder => builder.UseHybridFileCachePreset(2048));

await using var db2 = await Database.OpenAsync("large-dataset.db", boundedOptions);

using CSharpDB.Engine;
using System.Diagnostics;

// If the file exists, delete it to start fresh
if (File.Exists("hotset.db"))
    File.Delete("hotset.db");

// First, create a database with data to warm up
await using (var setup = await Database.OpenAsync("hotset.db"))
{
    await using (await setup.ExecuteAsync(
        "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, tier TEXT)"))
    { }

    await setup.BeginTransactionAsync();
    for (int i = 1; i <= 1000; i++)
    {
        await using (await setup.ExecuteAsync(
            $"INSERT INTO users VALUES ({i}, 'User {i}', 'gold')"))
        { }
    }
    await setup.CommitAsync();
}

// Open in hybrid mode with hot-set warming
// Open cost is ~87-128ms, but steady-state reads hit ~625-707K ops/sec
var sw = Stopwatch.StartNew();

await using var db = await Database.OpenHybridAsync(
    "hotset.db",
    new DatabaseOptions(),
    new HybridDatabaseOptions
    {
        PersistenceMode = HybridPersistenceMode.IncrementalDurable,
        HotTableNames = ["users"]
    });

Console.WriteLine($"Hybrid open (with hot-set warming): {sw.ElapsedMilliseconds}ms");

// Now reads against hot tables are served from memory-resident pages
sw.Restart();
int ops = 0;
for (int i = 0; i < 10_000; i++)
{
    int id = (i % 1000) + 1;
    await using (var r = await db.ExecuteAsync($"SELECT name FROM users WHERE id = {id}"))
    {
        if (await r.MoveNextAsync()) ops++;
    }
}
sw.Stop();
Console.WriteLine($"Hot-set reads: {ops} lookups in {sw.ElapsedMilliseconds}ms");

// Writes are still durable -- they go through WAL + incremental checkpointing
await db.BeginTransactionAsync();
await using (await db.ExecuteAsync("INSERT INTO users VALUES (9999, 'New User', 'platinum')")) { }
await db.CommitAsync();

using CSharpDB.Engine;
using System.Diagnostics;

// --- Ephemeral in-memory store ---
await using var cache = await Database.OpenInMemoryAsync();

await using (await cache.ExecuteAsync(
    "CREATE TABLE cache_entries (cache_key TEXT PRIMARY KEY, value TEXT, expires_at INTEGER)"))
{ }

// In-memory writes are ~315K ops/sec (vs ~270 ops/sec durable single-row).
// That's a ~1000x difference -- the biggest lever in the repo.
var sw = Stopwatch.StartNew();
await cache.BeginTransactionAsync();
for (int i = 0; i < 10_000; i++)
{
    await using (await cache.ExecuteAsync(
        $"INSERT INTO cache_entries VALUES ('key-{i}', 'value-{i}', {DateTimeOffset.UtcNow.ToUnixTimeSeconds() + 3600})"))
    { }
}
await cache.CommitAsync();
sw.Stop();
Console.WriteLine($"In-memory: 10K inserts in {sw.ElapsedMilliseconds}ms");

// Snapshot to disk at an explicit persistence boundary
await cache.SaveToFileAsync("cache-snapshot.db");

// --- Import-process-export workflow ---
// Load an existing file into memory, process it fast, then save back
await using var workspace = await Database.LoadIntoMemoryAsync("cache-snapshot.db");

// All operations now run at in-memory speed
await using (await workspace.ExecuteAsync(
    "DELETE FROM cache_entries WHERE expires_at < 1700000000"))
{ }

await using (var stats = await workspace.ExecuteAsync(
    "SELECT COUNT(*) FROM cache_entries"))
{
    if (await stats.MoveNextAsync())
        Console.WriteLine($"Remaining entries: {stats.Current[0].AsInteger}");
}

// Save the processed result back
await workspace.SaveToFileAsync("cache-processed.db");

using CSharpDB.Engine;

await using var db = await Database.OpenInMemoryAsync();

var users = await db.GetCollectionAsync<User>("users");

// Create indexes on the fields and paths you actually query.
// Each index costs ~12-47us per write, so only index what matters.
await users.EnsureIndexAsync(x => x.Email);           // field-level index
await users.EnsureIndexAsync("$.address.city");        // nested path index
await users.EnsureIndexAsync("$.tags[]");              // array element index

// Seed documents
for (int i = 0; i < 500; i++)
{
    string city = i % 5 == 0 ? "Seattle" : i % 5 == 1 ? "Portland"
                 : i % 5 == 2 ? "Denver"  : i % 5 == 3 ? "Austin" : "Boston";
    await users.PutAsync($"user-{i}", new User(
        $"User {i}", $"user{i}@example.com",
        new Address(city, i % 5 == 0 ? "WA" : "Other"),
        i % 3 == 0 ? ["admin", "editor"] : ["viewer"]
    ));
}

// --- Key lookup: fastest path, skips SQL entirely ---
var user = await users.GetAsync("user-42");
Console.WriteLine($"Key lookup: {user?.Name}");

// --- Indexed field query (~569ns per match) ---
await foreach (var kv in users.FindByIndexAsync(x => x.Email, "user42@example.com"))
{
    Console.WriteLine($"By email index: {kv.Key} -> {kv.Value.Name}");
}

// --- Indexed path query for nested fields (~475-599ns per match) ---
int seattleCount = 0;
await foreach (var kv in users.FindByPathAsync("$.address.city", "Seattle"))
{
    seattleCount++;
}
Console.WriteLine($"Path query ($.address.city = Seattle): {seattleCount} matches");

// --- Indexed array element query ---
int adminCount = 0;
await foreach (var kv in users.FindByPathAsync("$.tags[]", "admin"))
{
    adminCount++;
}
Console.WriteLine($"Array path query ($.tags[] = admin): {adminCount} matches");

// --- Path range query ---
int rangeCount = 0;
await foreach (var kv in users.FindByPathRangeAsync("$.address.city", "D", "Q"))
{
    rangeCount++;
}
Console.WriteLine($"Range query (cities D..Q): {rangeCount} matches");

public record Address(string City, string State);
public record User(string Name, string Email, Address Address, string[] Tags);

using CSharpDB.Data;
using System.Data;
using System.Data.Common;
using System.Diagnostics;

// If the file exists, delete it to start fresh
if (File.Exists("ado-app.db"))
    File.Delete("ado-app.db");

// --- Pooled connections: open+close drops from ~8.9ms to ~1.9us ---
var pooledConnStr = "Data Source=ado-app.db;Pooling=true;Max Pool Size=16";

await using (var conn = new CSharpDbConnection(pooledConnStr))
{
    await conn.OpenAsync();

    using var createCmd = conn.CreateCommand();
    createCmd.CommandText = @"
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            price REAL NOT NULL,
            category TEXT
        )";
    await createCmd.ExecuteNonQueryAsync();

    // Insert with parameterized commands (safe from injection)
    using var insertCmd = conn.CreateCommand();
    insertCmd.CommandText = "INSERT INTO products (id, name, price, category) VALUES (@id, @name, @price, @cat)";
    var pId = insertCmd.CreateParameter(); pId.ParameterName = "@id";
    var pName = insertCmd.CreateParameter(); pName.ParameterName = "@name";
    var pPrice = insertCmd.CreateParameter(); pPrice.ParameterName = "@price";
    var pCat = insertCmd.CreateParameter(); pCat.ParameterName = "@cat";
    insertCmd.Parameters.Add(pId);
    insertCmd.Parameters.Add(pName);
    insertCmd.Parameters.Add(pPrice);
    insertCmd.Parameters.Add(pCat);

    for (int i = 1; i <= 100; i++)
    {
        pId.Value = i;
        pName.Value = $"Product {i}";
        pPrice.Value = 9.99 + i;
        pCat.Value = i % 3 == 0 ? "Electronics" : i % 3 == 1 ? "Books" : "Clothing";
        await insertCmd.ExecuteNonQueryAsync();
    }
}

// Demonstrate the pooling benefit: rapid open/close cycles
var sw = Stopwatch.StartNew();
for (int i = 0; i < 1000; i++)
{
    await using var conn = new CSharpDbConnection(pooledConnStr);
    await conn.OpenAsync();
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT COUNT(*) FROM products";
    var count = await cmd.ExecuteScalarAsync();
}
sw.Stop();
Console.WriteLine($"Pooled: 1000 open/query/close in {sw.ElapsedMilliseconds}ms");

// --- Private in-memory via ADO.NET ---
await using (var memConn = new CSharpDbConnection("Data Source=:memory:"))
{
    await memConn.OpenAsync();
    using var cmd = memConn.CreateCommand();
    cmd.CommandText = "CREATE TABLE temp (id INTEGER PRIMARY KEY, value TEXT)";
    await cmd.ExecuteNonQueryAsync();
    cmd.CommandText = "INSERT INTO temp VALUES (1, 'fast')";
    await cmd.ExecuteNonQueryAsync();
    cmd.CommandText = "SELECT value FROM temp WHERE id = 1";
    var val = await cmd.ExecuteScalarAsync();
    Console.WriteLine($"In-memory ADO.NET: {val}");
}

// --- DbDataReader for streaming results ---
await using (var conn = new CSharpDbConnection(pooledConnStr))
{
    await conn.OpenAsync();
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT id, name, price FROM products WHERE category = 'Electronics' ORDER BY price LIMIT 10";

    await using var reader = await cmd.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        int id = reader.GetInt32(0);
        string name = reader.GetString(1);
        double price = reader.GetDouble(2);
        Console.WriteLine($"  [{id}] {name} - ${price:F2}");
    }
}

// --- DbProviderFactory pattern (for DI / framework integration) ---
DbProviderFactory factory = CSharpDbFactory.Instance;
await using var factoryConn = factory.CreateConnection()!;
factoryConn.ConnectionString = pooledConnStr;
await factoryConn.OpenAsync();

using var factoryCmd = factoryConn.CreateCommand();
factoryCmd.CommandText = "SELECT COUNT(*) FROM products";
Console.WriteLine($"Factory pattern count: {await factoryCmd.ExecuteScalarAsync()}");