Quick Start

Sample output:

╔══════════════════════════════════════════════════════════════╗
║              Encrypted Password Manager                      ║
╚══════════════════════════════════════════════════════════════╝

Enter master password: MySecureVault123!
✓ Vault unlocked successfully!

✓ Added 3 sample credentials

Stored Credentials:
──────────────────────────────────────────────────────────────────────
  Site:     aws.amazon.com
  Username: admin@company.com
  Password: Kx**************9#
  Notes:    Production AWS account

  Site:     database.example.com
  Username: dbadmin
  Password: 7Q**********************&m
  Notes:    PostgreSQL admin

  Site:     github.com
  Username: myuser
  Password: Np************@3
  Notes:    Main development account

Show password for site (or press Enter to skip): github
✓ Password for github.com: NpK$8mL2xQ#4nJ@3

✓ Vault locked. Data remains encrypted on disk.

Verifying Encryption

To verify your database is actually encrypted, try opening it without the password:

// Create encrypted database
var db1 = new WitDatabaseBuilder()
    .WithFilePath("encrypted.witdb")
    .WithBTree()
    .WithEncryption("correct-password")
    .Build();

db1.Put("key"u8.ToArray(), "secret value"u8.ToArray());
db1.Dispose();

// Try to open without password - should fail
try
{
    var db2 = new WitDatabaseBuilder()
        .WithFilePath("encrypted.witdb")
        .WithBTree()
        // No encryption!
        .Build();
    
    var value = db2.Get("key"u8.ToArray());
    Console.WriteLine("ERROR: Database opened without password!");
}
catch (Exception ex)
{
    Console.WriteLine(

        
        

            
            Loading...
        
quot;✓ Correctly failed: {ex.Message}");
}

// Try with wrong password - should fail
try
{
    var db3 = new WitDatabaseBuilder()
        .WithFilePath("encrypted.witdb")
        .WithBTree()
        .WithEncryption("wrong-password")
        .Build();
    
    var value = db3.Get("key"u8.ToArray());
    Console.WriteLine("ERROR: Database opened with wrong password!");
}
catch (Exception ex)
{
    Console.WriteLine(

        
        

            
            Loading...
        
quot;✓ Correctly failed: {ex.Message}");
}

Encryption Summary

Security checklist:

• ✅ Use strong passwords (12+ characters)
• ✅ Don't hardcode passwords in source code
• ✅ Use environment variables or secure configuration
• ✅ Use ChaCha20 for Blazor WebAssembly
• ✅ Consider per-user key derivation for multi-user apps

Using Transactions

Transactions ensure that a group of operations either all succeed or all fail together. This is essential for maintaining data integrity — you never end up with partially completed operations.

Why Transactions?

Consider transferring money between accounts:

// ❌ Without transaction - DANGEROUS!
account1.Balance -= 100;  // Debit
SaveChanges();
// What if the app crashes here?
account2.Balance += 100;  // Credit
SaveChanges();

If the application crashes after the debit but before the credit, $100 vanishes! With transactions:

// ✅ With transaction - SAFE
BeginTransaction();
account1.Balance -= 100;  // Debit
account2.Balance += 100;  // Credit
Commit();  // Both happen, or neither happens

ACID properties guaranteed by transactions:

ADO.NET Transactions

Basic Transaction

using OutWit.Database.AdoNet;

using var connection = new WitDbConnection("Data Source=bank.witdb");
connection.Open();

// Create accounts table
using var setupCmd = connection.CreateCommand();
setupCmd.CommandText = """
    CREATE TABLE IF NOT EXISTS Accounts (
        Id INTEGER PRIMARY KEY,
        Name VARCHAR(100) NOT NULL,
        Balance DECIMAL(18,2) NOT NULL DEFAULT 0
    )
    """;
setupCmd.ExecuteNonQuery();

// Begin transaction
using var transaction = connection.BeginTransaction();

try
{
    using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;  // Associate command with transaction
    
    // Debit from account 1
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @id";
    cmd.Parameters.Add(new WitDbParameter("@amount", 100.00m));
    cmd.Parameters.Add(new WitDbParameter("@id", 1));
    cmd.ExecuteNonQuery();
    
    // Credit to account 2
    cmd.Parameters.Clear();
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance + @amount WHERE Id = @id";
    cmd.Parameters.Add(new WitDbParameter("@amount", 100.00m));
    cmd.Parameters.Add(new WitDbParameter("@id", 2));
    cmd.ExecuteNonQuery();
    
    // All succeeded - commit
    transaction.Commit();
    Console.WriteLine("Transfer complete!");
}
catch (Exception ex)
{
    // Something failed - rollback all changes
    transaction.Rollback();
    Console.WriteLine(

        
        

            
            Loading...
        
quot;Transfer failed: {ex.Message}");
}

Transaction with Validation

Check business rules before committing:

using var transaction = connection.BeginTransaction();

try
{
    using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;
    
    // Check source account has sufficient funds
    cmd.CommandText = "SELECT Balance FROM Accounts WHERE Id = @id";
    cmd.Parameters.Add(new WitDbParameter("@id", sourceAccountId));
    var balance = Convert.ToDecimal(cmd.ExecuteScalar());
    
    if (balance < transferAmount)
    {
        throw new InvalidOperationException("Insufficient funds");
    }
    
    // Perform transfer
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @id";
    cmd.Parameters.Clear();
    cmd.Parameters.Add(new WitDbParameter("@amount", transferAmount));
    cmd.Parameters.Add(new WitDbParameter("@id", sourceAccountId));
    cmd.ExecuteNonQuery();
    
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance + @amount WHERE Id = @id";
    cmd.Parameters.Clear();
    cmd.Parameters.Add(new WitDbParameter("@amount", transferAmount));
    cmd.Parameters.Add(new WitDbParameter("@id", targetAccountId));
    cmd.ExecuteNonQuery();
    
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Bulk Operations with Transaction

Transactions dramatically improve performance for bulk operations:

using var connection = new WitDbConnection("Data Source=bulk.witdb");
connection.Open();

// Without transaction: ~1000 disk writes
// With transaction: 1 disk write at commit

using var transaction = connection.BeginTransaction();

using var cmd = connection.CreateCommand();
cmd.Transaction = transaction;
cmd.CommandText = "INSERT INTO Items (Name, Value) VALUES (@name, @value)";

var nameParam = new WitDbParameter("@name", "");
var valueParam = new WitDbParameter("@value", 0);
cmd.Parameters.Add(nameParam);
cmd.Parameters.Add(valueParam);
cmd.Prepare();

var stopwatch = System.Diagnostics.Stopwatch.StartNew();

for (int i = 0; i < 10000; i++)
{
    nameParam.Value = 

        
        

            
            Loading...
        
quot;Item {i}";
    valueParam.Value = i * 10;
    cmd.ExecuteNonQuery();
}

transaction.Commit();
stopwatch.Stop();

Console.WriteLine(

        
        

            
            Loading...
        
quot;Inserted 10,000 rows in {stopwatch.ElapsedMilliseconds}ms");
// Typically 50-100ms with transaction vs 5-10 seconds without

Entity Framework Core Transactions

Implicit Transactions

EF Core automatically wraps SaveChanges() in a transaction:

using var context = new AppDbContext();

// All changes are in a single implicit transaction
context.Accounts.Add(new Account { Name = "Alice", Balance = 1000 });
context.Accounts.Add(new Account { Name = "Bob", Balance = 500 });

// Both inserts succeed or both fail
await context.SaveChangesAsync();

Explicit Transactions

For multiple SaveChanges() calls or complex logic:

using var context = new BankDbContext();

using var transaction = await context.Database.BeginTransactionAsync();

try
{
    // Find accounts
    var source = await context.Accounts.FindAsync(sourceId);
    var target = await context.Accounts.FindAsync(targetId);
    
    if (source == null || target == null)
        throw new InvalidOperationException("Account not found");
    
    if (source.Balance < amount)
        throw new InvalidOperationException("Insufficient funds");
    
    // Perform transfer
    source.Balance -= amount;
    target.Balance += amount;
    
    // Log the transaction
    context.TransactionLogs.Add(new TransactionLog
    {
        FromAccountId = sourceId,
        ToAccountId = targetId,
        Amount = amount,
        Timestamp = DateTime.UtcNow
    });
    
    await context.SaveChangesAsync();
    
    // Commit both the transfer and the log
    await transaction.CommitAsync();
    
    Console.WriteLine(

        
        

            
            Loading...
        
quot;Transferred {amount:C} from {source.Name} to {target.Name}");
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

ExecutionStrategy for Retries

Handle transient failures with automatic retries:

var strategy = context.Database.CreateExecutionStrategy();

await strategy.ExecuteAsync(async () =>
{
    using var transaction = await context.Database.BeginTransactionAsync();
    
    try
    {
        // Your operations here
        source.Balance -= amount;
        target.Balance += amount;
        
        await context.SaveChangesAsync();
        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
});

Core API Transactions

Basic Transaction

using OutWit.Database.Core.Builder;

using var db = WitDatabase.CreateOrOpen("app.witdb");

using var tx = db.BeginTransaction();

try
{
    tx.Put("key1"u8.ToArray(), "value1"u8.ToArray());
    tx.Put("key2"u8.ToArray(), "value2"u8.ToArray());
    tx.Delete("old-key"u8.ToArray());
    
    tx.Commit();
}
catch
{
    tx.Rollback();
    throw;
}

Async Transaction

await using var tx = await db.BeginTransactionAsync();

try
{
    await tx.PutAsync(key1, value1);
    await tx.PutAsync(key2, value2);
    
    await tx.CommitAsync();
}
catch
{
    await tx.RollbackAsync();
    throw;
}

Savepoints

Savepoints allow partial rollbacks within a transaction. Useful for complex operations where you want to undo part of the work without losing everything.

ADO.NET Savepoints

using var connection = new WitDbConnection("Data Source=app.witdb");
connection.Open();

using var transaction = connection.BeginTransaction();

try
{
    using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;
    
    // Step 1: Create order
    cmd.CommandText = "INSERT INTO Orders (CustomerId, Total) VALUES (@custId, @total) RETURNING Id";
    cmd.Parameters.Add(new WitDbParameter("@custId", customerId));
    cmd.Parameters.Add(new WitDbParameter("@total", 0));
    var orderId = Convert.ToInt32(cmd.ExecuteScalar());
    
    Console.WriteLine(

        
        

            
            Loading...
        
quot;Created order {orderId}");
    
    // Create savepoint after order creation
    transaction.Save("after_order");
    
    decimal orderTotal = 0;
    
    foreach (var item in cartItems)
    {
        // Create savepoint before each item
        transaction.Save("before_item");
        
        try
        {
            // Check inventory
            cmd.CommandText = "SELECT Stock FROM Products WHERE Id = @productId";
            cmd.Parameters.Clear();
            cmd.Parameters.Add(new WitDbParameter("@productId", item.ProductId));
            var stock = Convert.ToInt32(cmd.ExecuteScalar());
            
            if (stock < item.Quantity)
            {
                Console.WriteLine(

        
        

            
            Loading...
        
quot;  Skipping {item.ProductName}: insufficient stock");
                transaction.Rollback("before_item");  // Rollback just this item
                continue;
            }
            
            // Add order item
            cmd.CommandText = """
                INSERT INTO OrderItems (OrderId, ProductId, Quantity, Price) 
                VALUES (@orderId, @productId, @qty, @price)
                """;
            cmd.Parameters.Clear();
            cmd.Parameters.Add(new WitDbParameter("@orderId", orderId));
            cmd.Parameters.Add(new WitDbParameter("@productId", item.ProductId));
            cmd.Parameters.Add(new WitDbParameter("@qty", item.Quantity));
            cmd.Parameters.Add(new WitDbParameter("@price", item.Price));
            cmd.ExecuteNonQuery();
            
            // Update inventory
            cmd.CommandText = "UPDATE Products SET Stock = Stock - @qty WHERE Id = @productId";
            cmd.Parameters.Clear();
            cmd.Parameters.Add(new WitDbParameter("@qty", item.Quantity));
            cmd.Parameters.Add(new WitDbParameter("@productId", item.ProductId));
            cmd.ExecuteNonQuery();
            
            orderTotal += item.Price * item.Quantity;
            Console.WriteLine(

        
        

            
            Loading...
        
quot;  Added {item.ProductName} x{item.Quantity}");
        }
        catch (Exception ex)
        {
            Console.WriteLine(

        
        

            
            Loading...
        
quot;  Error with {item.ProductName}: {ex.Message}");
            transaction.Rollback("before_item");
        }
    }
    
    if (orderTotal == 0)
    {
        // No items added - rollback entire order
        transaction.Rollback("after_order");
        Console.WriteLine("Order cancelled - no items available");
    }
    else
    {
        // Update order total
        cmd.CommandText = "UPDATE Orders SET Total = @total WHERE Id = @orderId";
        cmd.Parameters.Clear();
        cmd.Parameters.Add(new WitDbParameter("@total", orderTotal));
        cmd.Parameters.Add(new WitDbParameter("@orderId", orderId));
        cmd.ExecuteNonQuery();
        
        transaction.Commit();
        Console.WriteLine(

        
        

            
            Loading...
        
quot;Order {orderId} completed: {orderTotal:C}");
    }
}
catch
{
    transaction.Rollback();
    throw;
}

EF Core Savepoints

using var transaction = await context.Database.BeginTransactionAsync();

try
{
    // Create order
    var order = new Order { CustomerId = customerId };
    context.Orders.Add(order);
    await context.SaveChangesAsync();
    
    // Savepoint after order creation
    await transaction.CreateSavepointAsync("after_order");
    
    foreach (var item in cartItems)
    {
        await transaction.CreateSavepointAsync("before_item");
        
        try
        {
            var product = await context.Products.FindAsync(item.ProductId);
            
            if (product == null || product.Stock < item.Quantity)
            {
                await transaction.RollbackToSavepointAsync("before_item");
                continue;
            }
            
            order.Items.Add(new OrderItem
            {
                ProductId = item.ProductId,
                Quantity = item.Quantity,
                Price = item.Price
            });
            
            product.Stock -= item.Quantity;
            await context.SaveChangesAsync();
        }
        catch
        {
            await transaction.RollbackToSavepointAsync("before_item");
        }
    }
    
    if (!order.Items.Any())
    {
        await transaction.RollbackToSavepointAsync("after_order");
        throw new InvalidOperationException("No items could be added");
    }
    
    order.Total = order.Items.Sum(i => i.Price * i.Quantity);
    await context.SaveChangesAsync();
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Isolation Levels

Isolation levels control how transactions interact with each other. Higher isolation prevents more anomalies but may reduce concurrency.

Available Isolation Levels

Setting Isolation Level (ADO.NET)

using System.Data;

// Specify isolation level when beginning transaction
using var transaction = connection.BeginTransaction(IsolationLevel.Snapshot);

using var cmd = connection.CreateCommand();
cmd.Transaction = transaction;

// All reads see a consistent snapshot of data
cmd.CommandText = "SELECT * FROM Accounts";
// ... reads see data as of transaction start

transaction.Commit();

Setting Isolation Level (EF Core)

using var transaction = await context.Database
    .BeginTransactionAsync(IsolationLevel.Snapshot);

// All queries in this transaction see consistent snapshot
var accounts = await context.Accounts.ToListAsync();

await transaction.CommitAsync();

Setting Default Isolation Level

// Connection string
"Data Source=app.witdb;Isolation Level=Snapshot"

// Builder
var db = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithBTree()
    .WithTransactions()
    .WithDefaultIsolationLevel(IsolationLevel.Snapshot)
    .Build();

MVCC (Multi-Version Concurrency Control)

WitDatabase uses MVCC to allow readers and writers to work simultaneously without blocking each other.

How MVCC Works

Time →

Writer Transaction:                  Reader Transaction:
─────────────────────────────────   ─────────────────────────────────
BEGIN                               
UPDATE Account SET Balance=900      BEGIN
                                    SELECT Balance FROM Account
                                    → Returns 1000 (old version)
COMMIT                              
                                    SELECT Balance FROM Account  
                                    → Still returns 1000 (snapshot)
                                    COMMIT
                                    
                                    BEGIN (new transaction)
                                    SELECT Balance FROM Account
                                    → Returns 900 (committed value)

Benefits:

• Readers don't block writers
• Writers don't block readers
• Consistent reads throughout a transaction
• No dirty reads

Enabling MVCC

// Connection string (enabled by default)
"Data Source=app.witdb;MVCC=true"

// Builder
var db = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithBTree()
    .WithTransactions()
    .WithMvcc()
    .Build();

Complete Example: Bank Transfer System

using System.Data;
using OutWit.Database.AdoNet;

// ═══════════════════════════════════════════════════════════════════
// Bank Transfer System - Transaction Example
// ═══════════════════════════════════════════════════════════════════

using var connection = new WitDbConnection("Data Source=bank.witdb");
connection.Open();

// Setup database
InitializeDatabase(connection);

Console.WriteLine("╔══════════════════════════════════════════════════════════════╗");
Console.WriteLine("║              Bank Transfer System                            ║");
Console.WriteLine("╚══════════════════════════════════════════════════════════════╝\n");

// Show initial balances
ShowBalances(connection);

// Perform some transfers
Console.WriteLine("\nPerforming transfers:");
Console.WriteLine("─".PadRight(50, '─'));

Transfer(connection, "Alice", "Bob", 200);
Transfer(connection, "Bob", "Charlie", 50);
Transfer(connection, "Charlie", "Alice", 75);

// This should fail - insufficient funds
Transfer(connection, "Alice", "Bob", 10000);

// Show final balances
Console.WriteLine("\nFinal balances:");
ShowBalances(connection);

// Show transaction history
ShowHistory(connection);

// ═══════════════════════════════════════════════════════════════════
// Helper Methods
// ═══════════════════════════════════════════════════════════════════

static void InitializeDatabase(WitDbConnection conn)
{
    using var cmd = conn.CreateCommand();
    
    cmd.CommandText = """
        CREATE TABLE IF NOT EXISTS Accounts (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Name VARCHAR(100) NOT NULL UNIQUE,
            Balance DECIMAL(18,2) NOT NULL DEFAULT 0 CHECK (Balance >= 0)
        )
        """;
    cmd.ExecuteNonQuery();
    
    cmd.CommandText = """
        CREATE TABLE IF NOT EXISTS Transactions (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            FromAccount VARCHAR(100),
            ToAccount VARCHAR(100),
            Amount DECIMAL(18,2) NOT NULL,
            Status VARCHAR(20) NOT NULL,
            Timestamp DATETIME DEFAULT NOW(),
            ErrorMessage TEXT
        )
        """;
    cmd.ExecuteNonQuery();
    
    // Seed accounts if empty
    cmd.CommandText = "SELECT COUNT(*) FROM Accounts";
    if (Convert.ToInt32(cmd.ExecuteScalar()) == 0)
    {
        cmd.CommandText = """
            INSERT INTO Accounts (Name, Balance) VALUES 
            ('Alice', 1000.00),
            ('Bob', 500.00),
            ('Charlie', 250.00)
            """;
        cmd.ExecuteNonQuery();
    }
}

static void Transfer(WitDbConnection conn, string fromName, string toName, decimal amount)
{
    using var transaction = conn.BeginTransaction(IsolationLevel.Serializable);
    
    try
    {
        using var cmd = conn.CreateCommand();
        cmd.Transaction = transaction;
        
        // Get source account
        cmd.CommandText = "SELECT Id, Balance FROM Accounts WHERE Name = @name";
        cmd.Parameters.Add(new WitDbParameter("@name", fromName));
        
        int fromId;
        decimal fromBalance;
        
        using (var reader = cmd.ExecuteReader())
        {
            if (!reader.Read())
                throw new InvalidOperationException(

        
        

            
            Loading...
        
quot;Account '{fromName}' not found");
            fromId = reader.GetInt32(0);
            fromBalance = reader.GetDecimal(1);
        }
        
        // Get target account
        cmd.Parameters.Clear();
        cmd.Parameters.Add(new WitDbParameter("@name", toName));
        
        int toId;
        
        using (var reader = cmd.ExecuteReader())
        {
            if (!reader.Read())
                throw new InvalidOperationException(

        
        

            
            Loading...
        
quot;Account '{toName}' not found");
            toId = reader.GetInt32(0);
        }
        
        // Check sufficient funds
        if (fromBalance < amount)
        {
            throw new InvalidOperationException(
                

        
        

            
            Loading...
        
quot;Insufficient funds: {fromName} has {fromBalance:C}, needs {amount:C}");
        }
        
        // Debit source
        cmd.CommandText = "UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @id";
        cmd.Parameters.Clear();
        cmd.Parameters.Add(new WitDbParameter("@amount", amount));
        cmd.Parameters.Add(new WitDbParameter("@id", fromId));
        cmd.ExecuteNonQuery();
        
        // Credit target
        cmd.CommandText = "UPDATE Accounts SET Balance = Balance + @amount WHERE Id = @id";
        cmd.Parameters.Clear();
        cmd.Parameters.Add(new WitDbParameter("@amount", amount));
        cmd.Parameters.Add(new WitDbParameter("@id", toId));
        cmd.ExecuteNonQuery();
        
        // Log successful transaction
        LogTransaction(cmd, fromName, toName, amount, "SUCCESS", null);
        
        transaction.Commit();
        Console.WriteLine(

        
        

            
            Loading...
        
quot;  ✓ {fromName} → {toName}: {amount:C}");
    }
    catch (Exception ex)
    {
        transaction.Rollback();
        
        // Log failed transaction (in a new transaction)
        using var logCmd = conn.CreateCommand();
        LogTransaction(logCmd, fromName, toName, amount, "FAILED", ex.Message);
        
        Console.WriteLine(

        
        

            
            Loading...
        
quot;  ✗ {fromName} → {toName}: {amount:C} - {ex.Message}");
    }
}

static void LogTransaction(WitDbCommand cmd, string from, string to, decimal amount, string status, string? error)
{
    cmd.CommandText = """
        INSERT INTO Transactions (FromAccount, ToAccount, Amount, Status, ErrorMessage)
        VALUES (@from, @to, @amount, @status, @error)
        """;
    cmd.Parameters.Clear();
    cmd.Parameters.Add(new WitDbParameter("@from", from));
    cmd.Parameters.Add(new WitDbParameter("@to", to));
    cmd.Parameters.Add(new WitDbParameter("@amount", amount));
    cmd.Parameters.Add(new WitDbParameter("@status", status));
    cmd.Parameters.Add(new WitDbParameter("@error", error ?? (object)DBNull.Value));
    cmd.ExecuteNonQuery();
}

static void ShowBalances(WitDbConnection conn)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT Name, Balance FROM Accounts ORDER BY Name";
    
    Console.WriteLine("Account Balances:");
    Console.WriteLine("─".PadRight(30, '─'));
    
    using var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine(

        
        

            
            Loading...
        
quot;  {reader["Name"],-15} {reader.GetDecimal(1),12:C}");
    }
}

static void ShowHistory(WitDbConnection conn)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = """
        SELECT FromAccount, ToAccount, Amount, Status, Timestamp 
        FROM Transactions 
        ORDER BY Timestamp DESC 
        LIMIT 10
        """;
    
    Console.WriteLine("\nRecent Transactions:");
    Console.WriteLine("─".PadRight(70, '─'));
    
    using var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        var status = reader["Status"].ToString() == "SUCCESS" ? "✓" : "✗";
        var timestamp = reader.GetDateTime(4).ToString("HH:mm:ss");
        Console.WriteLine(

        
        

            
            Loading...
        
quot;  {status} [{timestamp}] {reader["FromAccount"]} → {reader["ToAccount"]}: {reader.GetDecimal(2):C}");
    }
}

Sample output:

╔══════════════════════════════════════════════════════════════╗
║              Bank Transfer System                            ║
╚══════════════════════════════════════════════════════════════╝

Account Balances:
──────────────────────────────
  Alice              $1,000.00
  Bob                  $500.00
  Charlie              $250.00

Performing transfers:
──────────────────────────────────────────────────
  ✓ Alice → Bob: $200.00
  ✓ Bob → Charlie: $50.00
  ✓ Charlie → Alice: $75.00
  ✗ Alice → Bob: $10,000.00 - Insufficient funds: Alice has $875.00, needs $10,000.00

Final balances:
Account Balances:
──────────────────────────────
  Alice                $875.00
  Bob                  $650.00
  Charlie              $225.00

Recent Transactions:
──────────────────────────────────────────────────────────────────────
  ✗ [14:32:01] Alice → Bob: $10,000.00
  ✓ [14:32:01] Charlie → Alice: $75.00
  ✓ [14:32:01] Bob → Charlie: $50.00
  ✓ [14:32:01] Alice → Bob: $200.00

Transactions Summary

Best practices:

• ✅ Always use transactions for multi-step operations
• ✅ Keep transactions short — don't hold them during user input
• ✅ Use using to ensure proper disposal
• ✅ Use savepoints for complex operations with partial rollback needs
• ✅ Choose appropriate isolation level for your use case
• ✅ Enable MVCC for better concurrency

Quick Reference

A handy reference card for common WitDatabase operations. Bookmark this page!

Connection Strings

Common Patterns:

// Simple file database
"Data Source=app.witdb"

// In-memory (testing)
"Data Source=:memory:"

// With encryption
"Data Source=secure.witdb;Encryption=aes-gcm;Password=MySecret123!"

// ChaCha20 for Blazor WASM
"Data Source=app.witdb;Encryption=chacha20;Password=secret"

// Read-only mode
"Data Source=app.witdb;Mode=ReadOnly"

// With all options
"Data Source=app.witdb;Store=btree;Encryption=aes-gcm;Password=secret;MVCC=true;Isolation Level=Snapshot"

All Parameters

Core API (Key-Value)

using OutWit.Database.Core.Builder;

// Create / Open
using var db = WitDatabase.CreateInMemory();
using var db = WitDatabase.Create("app.witdb");
using var db = WitDatabase.Open("app.witdb");
using var db = WitDatabase.CreateOrOpen("app.witdb");
using var db = WitDatabase.CreateOrOpen("app.witdb", "password");

// Builder pattern
using var db = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithBTree()                    // or .WithLsmTree()
    .WithEncryption("password")     // or .WithBouncyCastleEncryption()
    .WithTransactions()
    .WithMvcc()
    .Build();

// Basic operations
db.Put(key, value);                 // Store
var value = db.Get(key);            // Retrieve (null if not found)
db.Delete(key);                     // Remove
bool exists = db.Exists(key);       // Check existence

// Async versions
await db.PutAsync(key, value);
var value = await db.GetAsync(key);
await db.DeleteAsync(key);

// Scanning
foreach (var (k, v) in db.Scan(startKey, endKey))
{
    // Process key-value pairs in order
}

// Transactions
using var tx = db.BeginTransaction();
tx.Put(key1, value1);
tx.Put(key2, value2);
tx.Commit();  // or tx.Rollback();

ADO.NET

Connection

using OutWit.Database.AdoNet;

using var connection = new WitDbConnection("Data Source=app.witdb");
connection.Open();
// ... use connection
// Automatically closed and disposed

Execute Commands

using var cmd = connection.CreateCommand();

// Non-query (CREATE, INSERT, UPDATE, DELETE)
cmd.CommandText = "CREATE TABLE Users (Id INTEGER PRIMARY KEY, Name TEXT)";
int rowsAffected = cmd.ExecuteNonQuery();

// Scalar (single value)
cmd.CommandText = "SELECT COUNT(*) FROM Users";
var count = cmd.ExecuteScalar();

// Reader (multiple rows)
cmd.CommandText = "SELECT * FROM Users";
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    var id = reader.GetInt32(0);
    var name = reader["Name"].ToString();
}

Parameters

cmd.CommandText = "INSERT INTO Users (Name, Email) VALUES (@name, @email)";
cmd.Parameters.Add(new WitDbParameter("@name", "Alice"));
cmd.Parameters.Add(new WitDbParameter("@email", "alice@example.com"));
cmd.ExecuteNonQuery();

// Clear for reuse
cmd.Parameters.Clear();

Transactions

using var transaction = connection.BeginTransaction();

try
{
    using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;
    
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1";
    cmd.ExecuteNonQuery();
    
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2";
    cmd.ExecuteNonQuery();
    
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Async

await connection.OpenAsync();

using var cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM Users";

await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    // Process row
}

Entity Framework Core

DbContext Setup

using Microsoft.EntityFrameworkCore;
using OutWit.Database.EntityFramework;

public class AppDbContext : DbContext
{
    public DbSet<User> Users => Set<User>();
    public DbSet<Post> Posts => Set<Post>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseWitDb("Data Source=app.witdb");
}

// With DI (ASP.NET Core)
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb("Data Source=app.witdb"));

CRUD Operations

using var context = new AppDbContext();
context.Database.EnsureCreated();

// Create
context.Users.Add(new User { Name = "Alice" });
await context.SaveChangesAsync();

// Read
var users = await context.Users.ToListAsync();
var user = await context.Users.FindAsync(1);
var alice = await context.Users.FirstOrDefaultAsync(u => u.Name == "Alice");

// Update
user.Name = "Alice Smith";
await context.SaveChangesAsync();

// Delete
context.Users.Remove(user);
await context.SaveChangesAsync();

Common LINQ Queries

// Filter
var active = context.Users.Where(u => u.IsActive);

// Sort
var sorted = context.Users.OrderBy(u => u.Name);
var sortedDesc = context.Users.OrderByDescending(u => u.CreatedAt);

// Select specific columns
var names = context.Users.Select(u => new { u.Id, u.Name });

// Pagination
var page = context.Users
    .OrderBy(u => u.Id)
    .Skip(20)
    .Take(10);

// Aggregates
var count = await context.Users.CountAsync();
var any = await context.Users.AnyAsync(u => u.IsAdmin);
var max = await context.Users.MaxAsync(u => u.Age);

// Include related data
var usersWithPosts = context.Users
    .Include(u => u.Posts)
    .ToList();

// Group
var byCountry = context.Users
    .GroupBy(u => u.Country)
    .Select(g => new { Country = g.Key, Count = g.Count() });

Transactions

// Implicit (single SaveChanges)
context.Users.Add(user1);
context.Users.Add(user2);
await context.SaveChangesAsync();  // All or nothing

// Explicit
using var transaction = await context.Database.BeginTransactionAsync();
try
{
    // Multiple operations
    await context.SaveChangesAsync();
    await context.SaveChangesAsync();
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

SQL Reference

Data Types

DDL (Data Definition)

-- Create table
CREATE TABLE Users (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(255) UNIQUE,
    Age INTEGER DEFAULT 0,
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT NOW()
);

-- Create index
CREATE INDEX idx_users_email ON Users(Email);
CREATE UNIQUE INDEX idx_users_name ON Users(Name);

-- Drop
DROP TABLE IF EXISTS Users;
DROP INDEX IF EXISTS idx_users_email;

-- Alter (limited support)
ALTER TABLE Users ADD COLUMN Phone VARCHAR(20);

DML (Data Manipulation)

-- Insert
INSERT INTO Users (Name, Email) VALUES ('Alice', 'alice@example.com');
INSERT INTO Users (Name, Email) VALUES ('Bob', 'bob@example.com') RETURNING Id;

-- Select
SELECT * FROM Users;
SELECT Id, Name FROM Users WHERE IsActive = TRUE ORDER BY Name LIMIT 10;
SELECT COUNT(*), Country FROM Users GROUP BY Country HAVING COUNT(*) > 5;

-- Update
UPDATE Users SET Name = 'Alice Smith' WHERE Id = 1;
UPDATE Users SET IsActive = FALSE WHERE LastLogin < '2024-01-01' RETURNING Id, Name;

-- Delete
DELETE FROM Users WHERE Id = 1;
DELETE FROM Users WHERE IsActive = FALSE RETURNING Id;

-- Upsert
INSERT INTO Users (Id, Name) VALUES (1, 'Alice')
ON CONFLICT(Id) DO UPDATE SET Name = excluded.Name;

Joins

-- Inner join
SELECT u.Name, p.Title 
FROM Users u 
INNER JOIN Posts p ON u.Id = p.UserId;

-- Left join
SELECT u.Name, COUNT(p.Id) as PostCount
FROM Users u
LEFT JOIN Posts p ON u.Id = p.UserId
GROUP BY u.Id;

Common Functions

-- String
LENGTH(str), UPPER(str), LOWER(str), TRIM(str)
SUBSTR(str, start, length), REPLACE(str, old, new)
CONCAT(str1, str2), INSTR(str, substr)

-- Numeric
ABS(n), ROUND(n, decimals), CEIL(n), FLOOR(n)
MIN(n), MAX(n), SUM(n), AVG(n), COUNT(*)

-- Date/Time
NOW(), DATE('now'), TIME('now')
STRFTIME('%Y-%m-%d', datetime_col)

-- Null handling
COALESCE(val1, val2, ...), IFNULL(val, default), NULLIF(val1, val2)

-- Conditional
CASE WHEN condition THEN result ELSE default END
IIF(condition, true_val, false_val)

Common Patterns

"I want to..." Quick Guide

Error Handling

try
{
    using var connection = new WitDbConnection(connectionString);
    connection.Open();
    // ... operations
}
catch (WitDbException ex) when (ex.Message.Contains("locked"))
{
    // Database is locked by another process
    Console.WriteLine("Database is busy, try again later");
}
catch (WitDbException ex) when (ex.Message.Contains("decrypt"))
{
    // Wrong password
    Console.WriteLine("Invalid password");
}
catch (WitDbException ex)
{
    // Other database errors
    Console.WriteLine(

        
        

            
            Loading...
        
quot;Database error: {ex.Message}");
}

Testing Pattern

public class UserServiceTests
{
    private AppDbContext CreateTestContext()
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseWitDb("Data Source=:memory:")
            .Options;
        
        var context = new AppDbContext(options);
        context.Database.EnsureCreated();
        return context;
    }

    [Fact]
    public async Task CreateUser_ShouldAssignId()
    {
        // Arrange
        using var context = CreateTestContext();
        var service = new UserService(context);
        
        // Act
        var user = await service.CreateUserAsync("Alice", "alice@test.com");
        
        // Assert
        Assert.True(user.Id > 0);
        Assert.Equal("Alice", user.Name);
    }
}

Repository Pattern

public interface IRepository<T> where T : class
{
    Task<T?> GetByIdAsync(int id);
    Task<IEnumerable<T>> GetAllAsync();
    Task AddAsync(T entity);
    Task UpdateAsync(T entity);
    Task DeleteAsync(T entity);
}

public class Repository<T> : IRepository<T> where T : class
{
    private readonly AppDbContext _context;
    private readonly DbSet<T> _dbSet;

    public Repository(AppDbContext context)
    {
        _context = context;
        _dbSet = context.Set<T>();
    }

    public async Task<T?> GetByIdAsync(int id) 
        => await _dbSet.FindAsync(id);

    public async Task<IEnumerable<T>> GetAllAsync() 
        => await _dbSet.ToListAsync();

    public async Task AddAsync(T entity)
    {
        await _dbSet.AddAsync(entity);
        await _context.SaveChangesAsync();
    }

    public async Task UpdateAsync(T entity)
    {
        _dbSet.Update(entity);
        await _context.SaveChangesAsync();
    }

    public async Task DeleteAsync(T entity)
    {
        _dbSet.Remove(entity);
        await _context.SaveChangesAsync();
    }
}

Package Reference