OutWit.Database.AdoNet 1.0.0

OutWit.Database.AdoNet

ADO.NET data provider for WitDatabase - a high-performance embedded database engine for .NET.

Overview

This package provides a full ADO.NET implementation for WitDatabase, allowing you to use familiar patterns like DbConnection, DbCommand, DbDataReader, and DbTransaction with WitDatabase.

Features

• Full ADO.NET 2.0 compatible implementation
• Support for in-memory and file-based databases
• Connection pooling
• Transaction support with multiple isolation levels
• MVCC (Multi-Version Concurrency Control)
• Encryption support (AES-GCM, ChaCha20-Poly1305)
• Multiple storage engines (B-Tree, LSM-Tree)
• Async/await support throughout
• Cross-platform (.NET 9+)

Installation

dotnet add package OutWit.Database.AdoNet

Quick Start

Basic Usage

using OutWit.Database.AdoNet;

// Create and open connection
using var connection = new WitDbConnection("Data Source=mydb.witdb");
connection.Open();

// Create table
using var cmd = connection.CreateCommand();
cmd.CommandText = "CREATE TABLE Users (Id INT PRIMARY KEY, Name VARCHAR(100), Email VARCHAR(255))";
cmd.ExecuteNonQuery();

// Insert data
cmd.CommandText = "INSERT INTO Users (Id, Name, Email) VALUES (@id, @name, @email)";
cmd.Parameters.Add(new WitDbParameter("@id", 1));
cmd.Parameters.Add(new WitDbParameter("@name", "John Doe"));
cmd.Parameters.Add(new WitDbParameter("@email", "john@example.com"));
cmd.ExecuteNonQuery();

// Query data
cmd.CommandText = "SELECT * FROM Users";
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine($"{reader["Id"]}: {reader["Name"]} - {reader["Email"]}");
}

In-Memory Database

using var connection = new WitDbConnection("Data Source=:memory:");
connection.Open();
// Database exists only for the lifetime of the connection

With Transactions

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

using var transaction = connection.BeginTransaction();
try
{
    using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;
    
    cmd.CommandText = "INSERT INTO Accounts (Id, Balance) VALUES (1, 1000)";
    cmd.ExecuteNonQuery();
    
    cmd.CommandText = "INSERT INTO Accounts (Id, Balance) VALUES (2, 500)";
    cmd.ExecuteNonQuery();
    
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

With Encryption

using var connection = new WitDbConnection(
    "Data Source=secure.witdb;Encryption=aes-gcm;Password=MySecurePassword123");
connection.Open();
// All data is encrypted at rest

Connection String Reference

Core Properties

Storage Engine

Available storage engines:

• btree - B-Tree based storage (default, good for general use)
• lsm - LSM-Tree storage (optimized for write-heavy workloads)
• inmemory - In-memory storage (fastest, non-persistent)

Encryption

Available encryption algorithms:

• aes-gcm - AES-256-GCM (recommended)
• chacha20-poly1305 - ChaCha20-Poly1305

Caching

Available cache algorithms:

• clock - Clock algorithm (default)
• lru - Least Recently Used

Transaction Settings

Available isolation levels:

• ReadUncommitted - Allows dirty reads
• ReadCommitted - Only committed data visible (default)
• RepeatableRead - Read locks held for transaction duration
• Serializable - Full serialization
• Snapshot - MVCC snapshot isolation

Connection Pooling

Timeouts

Connection String Examples

Simple File Database

Data Source=myapp.witdb

Read-Only Access

Data Source=myapp.witdb;Mode=ReadOnly

In-Memory Database

Data Source=:memory:

With Encryption

Data Source=secure.witdb;Encryption=aes-gcm;Password=MyPassword123

LSM Storage Engine

Data Source=./data;Store=lsm

Full Configuration

Data Source=app.witdb;Store=btree;Cache=clock;CacheSize=5000;Encryption=aes-gcm;Password=secret;MVCC=true;Isolation Level=Snapshot

With Connection Pooling

Data Source=app.witdb;Pooling=true;Min Pool Size=5;Max Pool Size=20

Using DbProviderFactory

// Register provider (typically done at application startup)
DbProviderFactories.RegisterFactory(
    WitDbProviderFactory.PROVIDER_INVARIANT_NAME, 
    WitDbProviderFactory.Instance);

// Use factory
var factory = DbProviderFactories.GetFactory(WitDbProviderFactory.PROVIDER_INVARIANT_NAME);
using var connection = factory.CreateConnection();
connection.ConnectionString = "Data Source=mydb.witdb";
connection.Open();

ADO.NET Components

Supported Data Types

Thread Safety

• WitDbConnection is not thread-safe; each thread should use its own connection
• Connection pooling handles concurrent connection requests safely
• Multiple readers can operate concurrently with MVCC enabled
• Write operations are serialized at the database level

Error Handling

try
{
    using var cmd = connection.CreateCommand();
    cmd.CommandText = "SELECT * FROM NonExistentTable";
    cmd.ExecuteReader();
}
catch (WitDbException ex)
{
    Console.WriteLine($"Error Code: {ex.ErrorCode}");
    Console.WriteLine($"Message: {ex.Message}");
    
    switch (ex.ErrorCode)
    {
        case WitDbErrorCode.Syntax:
            // Handle syntax error
            break;
        case WitDbErrorCode.Constraint:
            // Handle constraint violation
            break;
        case WitDbErrorCode.Lock:
            // Handle lock timeout
            break;
    }
}

Performance Tips

1. Use connection pooling for applications with frequent connect/disconnect patterns
2. Use transactions for multiple related operations
3. Use parameters instead of string concatenation to avoid SQL parsing overhead
4. Choose appropriate storage engine: B-Tree for balanced workloads, LSM for write-heavy
5. Configure cache size based on available memory and working set size
6. Use async methods for I/O-bound operations

Compatibility

• .NET 9.0 or later
• .NET 10.0 or later
• Cross-platform (Windows, Linux, macOS)

License

Licensed under the Apache License, Version 2.0. See LICENSE.

Attribution (optional)

If you use OutWit.Database.AdoNet in a product, a mention is appreciated (but not required), for example: "Powered by WitDatabase https://witdatabase.io/".

Trademark / Project name

"WitDatabase" and the WitDatabase logo are used to identify the official project by Dmitry Ratner.

You may:

• refer to the project name in a factual way (e.g., "built with WitDatabase");
• use the name to indicate compatibility (e.g., "WitDatabase-compatible").

You may not:

• use "WitDatabase" as the name of a fork or a derived product in a way that implies it is the official project;
• use the WitDatabase logo to promote forks or derived products without permission.

Related Packages

• OutWit.Database.Core - Core database engine
• OutWit.Database.EntityFramework - Entity Framework Core provider

See Also

• ROADMAP.md - Version 2.0 planned features
• ROADMAP.md - Main project roadmap