Samples & Tutorials

Practical, end-to-end examples showing CSharpDB in real-world scenarios.

In-Memory Database as a Cache in ASP.NET

CSharpDB's in-memory mode makes an excellent structured cache for ASP.NET applications. Unlike IMemoryCache or IDistributedCache, you get full SQL queries, indexes, and typed collections over your cached data — with zero serialization overhead.

Why use CSharpDB as a cache?

  • Queryable: SQL and Collection API let you filter, join, and aggregate cached data — not just key-value lookups
  • Indexed: Secondary indexes on any property for O(log n) lookups across any field
  • Transactional: Atomic bulk updates — readers always see consistent state
  • No external service: No Redis, no network hop. Everything runs in-process
  • Snapshot persistence: Optionally save cache state to disk on shutdown and reload on startup

1. Register the Database as a Singleton

Create a hosted service that manages the in-memory database lifecycle and registers it in DI.

using CSharpDB.Engine;

public sealed class CacheDbHostedService : IHostedLifecycleService, IAsyncDisposable
{
    private readonly ILogger<CacheDbHostedService> _logger;
    private readonly string? _snapshotPath;
    private Database? _db;

    public Database Db => _db
        ?? throw new InvalidOperationException("Cache not initialized");

    public CacheDbHostedService(
        ILogger<CacheDbHostedService> logger,
        IConfiguration config)
    {
        _logger = logger;
        _snapshotPath = config["CacheDb:SnapshotPath"];
    }

    public async Task StartingAsync(CancellationToken ct)
    {
        // Load from snapshot if available, otherwise create fresh
        if (_snapshotPath is not null && File.Exists(_snapshotPath))
        {
            _logger.LogInformation("Loading cache from {Path}", _snapshotPath);
            _db = await Database.LoadIntoMemoryAsync(_snapshotPath, ct);
        }
        else
        {
            _db = await Database.OpenInMemoryAsync(ct);
        }

        // Create cache tables
        await _db.ExecuteAsync(@"
            CREATE TABLE IF NOT EXISTS ProductCache (
                Id       INTEGER PRIMARY KEY,
                Sku      TEXT NOT NULL,
                Name     TEXT NOT NULL,
                Price    REAL,
                Category TEXT,
                CachedAt TEXT NOT NULL
            )");

        await _db.ExecuteAsync(
            "CREATE INDEX IF NOT EXISTS idx_product_sku ON ProductCache (Sku)");
        await _db.ExecuteAsync(
            "CREATE INDEX IF NOT EXISTS idx_product_cat ON ProductCache (Category)");

        _logger.LogInformation("Cache database ready");
    }

    public Task StartAsync(CancellationToken ct) => Task.CompletedTask;
    public Task StartedAsync(CancellationToken ct) => Task.CompletedTask;
    public Task StoppingAsync(CancellationToken ct) => Task.CompletedTask;
    public Task StopAsync(CancellationToken ct) => Task.CompletedTask;

    public async Task StoppedAsync(CancellationToken ct)
    {
        // Save snapshot on graceful shutdown
        if (_db is not null && _snapshotPath is not null)
        {
            _logger.LogInformation("Saving cache snapshot to {Path}", _snapshotPath);
            await _db.SaveToFileAsync(_snapshotPath, ct);
        }
    }

    public async ValueTask DisposeAsync()
    {
        if (_db is not null)
            await _db.DisposeAsync();
    }
}
var builder = WebApplication.CreateBuilder(args);

// Register the cache database as a hosted service + singleton
builder.Services.AddSingleton<CacheDbHostedService>();
builder.Services.AddHostedService(
    sp => sp.GetRequiredService<CacheDbHostedService>());

// Expose the Database instance for injection
builder.Services.AddSingleton(
    sp => sp.GetRequiredService<CacheDbHostedService>().Db);

var app = builder.Build();

2. Build a Cache Service

Wrap the in-memory database in a typed service that your controllers and endpoints can inject.

using CSharpDB.Engine;

public sealed class ProductCacheService
{
    private readonly Database _db;
    private readonly ILogger<ProductCacheService> _logger;

    public ProductCacheService(Database db, ILogger<ProductCacheService> logger)
    {
        _db = db;
        _logger = logger;
    }

    /// <summary>Look up a product by SKU using the index.</summary>
    public async Task<CachedProduct?> GetBySkuAsync(string sku)
    {
        var result = await _db.ExecuteAsync(
            $"SELECT Id, Sku, Name, Price, Category FROM ProductCache WHERE Sku = '{sku}'");

        var rows = await result.ToListAsync();
        if (rows.Count == 0) return null;

        var r = rows[0];
        return new CachedProduct(
            r[0].AsInteger, r[1].AsText, r[2].AsText,
            r[3].AsReal, r[4].AsText);
    }

    /// <summary>Query products by category — uses the category index.</summary>
    public async Task<List<CachedProduct>> GetByCategoryAsync(string category)
    {
        var result = await _db.ExecuteAsync(
            $"SELECT Id, Sku, Name, Price, Category FROM ProductCache WHERE Category = '{category}' ORDER BY Name");

        var products = new List<CachedProduct>();
        await foreach (var r in result.GetRowsAsync())
        {
            products.Add(new(
                r[0].AsInteger, r[1].AsText, r[2].AsText,
                r[3].AsReal, r[4].AsText));
        }
        return products;
    }

    /// <summary>Aggregate cached data — avg price per category.</summary>
    public async Task<List<(string Category, double AvgPrice, long Count)>>
        GetCategoryStatsAsync()
    {
        var result = await _db.ExecuteAsync(@"
            SELECT Category, AVG(Price), COUNT(*)
            FROM ProductCache
            GROUP BY Category
            ORDER BY COUNT(*) DESC");

        var stats = new List<(string, double, long)>();
        await foreach (var r in result.GetRowsAsync())
            stats.Add((r[0].AsText, r[1].AsReal, r[2].AsInteger));
        return stats;
    }

    /// <summary>Bulk-refresh the cache from an external source.</summary>
    public async Task RefreshAsync(IReadOnlyList<CachedProduct> products)
    {
        _logger.LogInformation("Refreshing cache with {Count} products", products.Count);

        // Atomic swap — readers see old data until commit
        await _db.ExecuteAsync("DELETE FROM ProductCache");

        var now = DateTime.UtcNow.ToString("o");
        foreach (var p in products)
        {
            await _db.ExecuteAsync(
                $"INSERT INTO ProductCache VALUES ({p.Id}, '{p.Sku}', '{p.Name}', {p.Price}, '{p.Category}', '{now}')");
        }

        _logger.LogInformation("Cache refresh complete");
    }
}

public record CachedProduct(
    long Id, string Sku, string Name,
    double Price, string Category);
builder.Services.AddSingleton<ProductCacheService>();

3. Use It from Endpoints

app.MapGet("/products/{sku}", async (string sku, ProductCacheService cache) =>
{
    var product = await cache.GetBySkuAsync(sku);
    return product is not null
        ? Results.Ok(product)
        : Results.NotFound();
});

app.MapGet("/products/category/{category}", async (
    string category, ProductCacheService cache) =>
{
    var products = await cache.GetByCategoryAsync(category);
    return Results.Ok(products);
});

app.MapGet("/products/stats", async (ProductCacheService cache) =>
{
    var stats = await cache.GetCategoryStatsAsync();
    return Results.Ok(stats);
});

4. Background Cache Refresh

Use a background service to periodically refresh the cache from your primary data source (e.g., an external API, primary database, or message queue).

public sealed class CacheRefreshWorker : BackgroundService
{
    private readonly ProductCacheService _cache;
    private readonly IHttpClientFactory _httpFactory;
    private readonly ILogger<CacheRefreshWorker> _logger;
    private readonly TimeSpan _interval = TimeSpan.FromMinutes(5);

    public CacheRefreshWorker(
        ProductCacheService cache,
        IHttpClientFactory httpFactory,
        ILogger<CacheRefreshWorker> logger)
    {
        _cache = cache;
        _httpFactory = httpFactory;
        _logger = logger;
    }

    protected override async Task ExecuteAsync(CancellationToken ct)
    {
        while (!ct.IsCancellationRequested)
        {
            try
            {
                var client = _httpFactory.CreateClient("ProductApi");
                var products = await client
                    .GetFromJsonAsync<List<CachedProduct>>("/api/products", ct);

                if (products is not null)
                    await _cache.RefreshAsync(products);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Cache refresh failed");
            }

            await Task.Delay(_interval, ct);
        }
    }
}
builder.Services.AddHttpClient("ProductApi",
    c => c.BaseAddress = new Uri("https://api.example.com"));
builder.Services.AddHostedService<CacheRefreshWorker>();

5. Collection API as a Cache

For typed object caching without SQL, use the Collection API instead. This is ideal when you want to cache complex objects and query by indexed properties.

using CSharpDB.Engine;

public record UserSession(
    string UserId,
    string DisplayName,
    string[] Roles,
    string LastActivity);

public sealed class SessionCacheService
{
    private readonly Database _db;
    private Collection<UserSession>? _sessions;

    public SessionCacheService(Database db) => _db = db;

    private async ValueTask<Collection<UserSession>> GetCollectionAsync()
    {
        if (_sessions is not null) return _sessions;

        _sessions = await _db.GetCollectionAsync<UserSession>("sessions");

        // Index the UserId for fast lookups
        await _sessions.EnsureIndexAsync(s => s.UserId);
        return _sessions;
    }

    public async Task SetSessionAsync(string sessionId, UserSession session)
    {
        var col = await GetCollectionAsync();
        await col.PutAsync(sessionId, session);
    }

    public async Task<UserSession?> GetSessionAsync(string sessionId)
    {
        var col = await GetCollectionAsync();
        return await col.GetAsync(sessionId);
    }

    public async Task RemoveSessionAsync(string sessionId)
    {
        var col = await GetCollectionAsync();
        await col.DeleteAsync(sessionId);
    }

    public async Task<long> GetActiveCountAsync()
    {
        var col = await GetCollectionAsync();
        return await col.CountAsync();
    }
}

Complete Program.cs

Putting it all together:

using CSharpDB.Engine;

var builder = WebApplication.CreateBuilder(args);

// ── CSharpDB in-memory cache ──
builder.Services.AddSingleton<CacheDbHostedService>();
builder.Services.AddHostedService(
    sp => sp.GetRequiredService<CacheDbHostedService>());
builder.Services.AddSingleton(
    sp => sp.GetRequiredService<CacheDbHostedService>().Db);

// ── Cache services ──
builder.Services.AddSingleton<ProductCacheService>();
builder.Services.AddSingleton<SessionCacheService>();

// ── Background refresh ──
builder.Services.AddHttpClient("ProductApi",
    c => c.BaseAddress = new Uri("https://api.example.com"));
builder.Services.AddHostedService<CacheRefreshWorker>();

var app = builder.Build();

// ── Endpoints ──
app.MapGet("/products/{sku}", async (string sku, ProductCacheService cache) =>
{
    var product = await cache.GetBySkuAsync(sku);
    return product is not null ? Results.Ok(product) : Results.NotFound();
});

app.MapGet("/products/category/{category}", async (
    string category, ProductCacheService cache) =>
    Results.Ok(await cache.GetByCategoryAsync(category)));

app.MapGet("/products/stats", async (ProductCacheService cache) =>
    Results.Ok(await cache.GetCategoryStatsAsync()));

app.MapGet("/sessions/{id}", async (string id, SessionCacheService sessions) =>
{
    var session = await sessions.GetSessionAsync(id);
    return session is not null ? Results.Ok(session) : Results.NotFound();
});

app.Run();
Snapshot on shutdown: Set "CacheDb:SnapshotPath" in appsettings.json to persist cache state across restarts. The hosted service loads the snapshot on startup and saves on graceful shutdown. 
{
  "CacheDb": {
    "SnapshotPath": "cache-snapshot.db"
  }
}

Storage Engine Tutorials

The storage tutorial track is a guided path for learning CSharpDB.Storage — from architecture to extensibility to runnable code.

Prerequisites: Familiarity with .NET async patterns and clean architecture concepts. Read the Database Modes guide first for the high-level picture.

Guided Reading

Architecture Deep Dive

How Pager, WAL, B+tree, and SchemaCatalog fit together. File format, transaction flow, and recovery walkthroughs.

Usage & Extensibility

Current extension points — custom storage devices, page caches, checkpoint policies, index providers, and more.

Study Examples

Interactive REPL-based walkthroughs for learning storage concepts in guided slices. Run them all from the shared StorageStudyExamples.Repl host.

Config Store

Hierarchical key-value store with namespaces, versioning, and change history. Demonstrates multi-table schema design, indexed queries, and transactional consistency.

StorageStudyExamples.ConfigStore

Event Log

Append-only audit trail with severity filtering and analytics. Demonstrates bulk inserts, aggregate queries, and log rotation patterns.

StorageStudyExamples.EventLog

Task Queue

Persistent job queue with priority-based dequeue, state machine transitions, and retry logic. Demonstrates SQL-driven state machines.

StorageStudyExamples.TaskQueue

Virtual Drive

Virtual file system with folders, files, and shortcuts supporting path-based navigation. Demonstrates tree structures and BLOB handling.

StorageStudyExamples.VirtualDrive

Graph Store

Social network graph with directed edges and multi-hop traversal. Demonstrates composite indexes, JOINs, and set operations.

StorageStudyExamples.GraphStore

Storage Internals

9 hands-on demos of caching, checksums, interceptors, crash recovery, and checkpoint policies. Demonstrates storage engine tuning.

StorageStudyExamples.StorageInternals

Advanced Standalone Examples

Full domain applications built directly on CSharpDB.Storage, showing how to turn the storage package into a domain-specific engine. Each project includes a CLI REPL and REST API with Web UI.

Graph Database

Three B+trees (nodes, outEdges, inEdges) with composite key encoding for efficient bidirectional edge traversal, BFS, and shortest-path queries.

CSharpDB.GraphDB

Spatial Index

Geographic index using Hilbert space-filling curves to map 2D coordinates to 1D B+tree keys for proximity and bounding-box queries.

CSharpDB.SpatialIndex

Time Series Engine

Append-only time-series store where DateTime.Ticks serves as the natural B+tree key, with cursor-based range queries and aggregation.

CSharpDB.TimeSeries

Virtual File System

Four B+trees (entries, content, pathIndex, children) supporting full CRUD with hash-based path lookup and directory listing via cursor range scans.

CSharpDB.VirtualFS 
# Clone and run the interactive REPL
cd samples/storage-tutorials/examples
dotnet run --project StorageStudyExamples.Repl

# Or run any advanced example directly
cd samples/storage-tutorials/examples/CSharpDB.GraphDB
dotnet run

Next Steps

Database Modes

Deep dive into file-based, in-memory, and hybrid storage

Collection API

Typed document storage with nested path indexing

SQL Reference

Full SQL syntax for querying cached data