Building a REST API with WitDatabase: Direct SQL Control with ADO.NET

Entity Framework Core is fantastic for rapid development. But sometimes you need more control:

• Complex queries that don't translate well to LINQ
• Performance-critical paths where every millisecond counts
• Legacy SQL that you can't easily refactor
• Database-specific features that ORMs don't expose

That's where ADO.NET shines. You write the SQL, you control the execution, you optimize the queries. And with WitDatabase's ADO.NET provider, you get all of this with an embedded database — no server required.

In this tutorial, we'll build a complete REST API with:

• Users API — CRUD operations, search, pagination
• Products API — CRUD, bulk import, statistics, filtering
• Swagger UI — Interactive API documentation
• Transaction support — For bulk operations

By the end, you'll understand when to choose ADO.NET over EF Core and how to structure a clean, maintainable data access layer.

[[Svg Src="./witdatabase-swagger-endpoints.svg" Alt="witdatabase-swagger-endpoints"]]

Let's build it!

Prerequisites & Setup

Requirements

• .NET 9.0 or .NET 10.0 SDK
• Any operating system: Windows, Linux, or macOS
• A REST client: Swagger UI (built-in), Postman, or curl

Running the Sample

# Clone the repository
git clone https://github.com/dmitrat/WitDatabase.git

# Navigate to the sample
cd WitDatabase/Samples/OutWit.Database.Samples.WebApi

# Run the application
dotnet run

Access the API

Once running, open your browser:

The Swagger UI provides an interactive interface to test all endpoints without writing any code.

💡 Tip: The database file (webapi_sample.witdb) is created automatically on first run with seed data.

Project Architecture

Folder Structure

[[Svg Src="./witdatabase-webapi-project-structure.svg" Alt="witdatabase-webapi-project-structure"]]

Architecture Overview

[[Svg Src="./witdatabase-webapi-request-flow.svg" Alt="witdatabase-webapi-request-flow"]]

ADO.NET vs EF Core: When to Choose

Choose ADO.NET when:

• You need precise control over SQL
• Performance is critical
• You have complex queries that don't map well to LINQ
• You're migrating existing raw SQL code

Choose EF Core when:

• Rapid development is the priority
• You want automatic change tracking
• You need migrations
• Your queries are straightforward CRUD

Connection Setup

The foundation of our API is a properly configured database connection. Let's look at Program.cs:

Connection String Configuration

var builder = WebApplication.CreateBuilder(args);

// Get connection string from configuration (with fallback)
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection") 
    ?? "Data Source=webapi_sample.witdb";

In appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=webapi_sample.witdb"
  }
}

Singleton Connection Pattern

For an embedded database, we use a singleton connection that lives for the application's lifetime:

// Create and open the shared connection
var sharedConnection = new WitDbConnection(connectionString);
sharedConnection.Open();

// Register as singleton - will be disposed when app shuts down
builder.Services.AddSingleton(sharedConnection);

// Register services that will use this connection
builder.Services.AddScoped<DatabaseInitializer>();
builder.Services.AddScoped<UserService>();
builder.Services.AddScoped<ProductService>();

Why Singleton? Unlike SQL Server where you'd use connection pooling, embedded databases like WitDatabase work best with a single connection that's reused. This avoids file locking issues and maximizes performance.

Graceful Shutdown

Proper cleanup is essential to prevent data loss:

try
{
    await app.RunAsync();
}
finally
{
    // Ensure data is flushed and connection is properly closed
    Console.WriteLine("Shutting down - flushing database...");
    sharedConnection.Engine?.Flush();
    sharedConnection.Close();
    sharedConnection.Dispose();
    Console.WriteLine("Database closed.");
}

The Flush() call ensures all cached writes are persisted to disk before shutdown.

Complete Program.cs

using OutWit.Database.AdoNet;
using OutWit.Database.Samples.WebApi.Services;

var builder = WebApplication.CreateBuilder(args);

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection") 
    ?? "Data Source=webapi_sample.witdb";

var sharedConnection = new WitDbConnection(connectionString);
sharedConnection.Open();

builder.Services.AddSingleton(sharedConnection);
builder.Services.AddScoped<DatabaseInitializer>();
builder.Services.AddScoped<UserService>();
builder.Services.AddScoped<ProductService>();

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI(c =>
    {
        c.SwaggerEndpoint("/swagger/v1/swagger.json", "WitDatabase WebAPI Sample v1");
        c.RoutePrefix = string.Empty;  // Swagger at root URL
    });
}

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();

// Initialize database
using (var scope = app.Services.CreateScope())
{
    var initializer = scope.ServiceProvider.GetRequiredService<DatabaseInitializer>();
    await initializer.InitializeAsync();
}

try
{
    await app.RunAsync();
}
finally
{
    sharedConnection.Engine?.Flush();
    sharedConnection.Close();
    sharedConnection.Dispose();
}

Database Initialization

The DatabaseInitializer service creates tables and seeds initial data on application startup.

Schema Creation

public class DatabaseInitializer
{
    private readonly WitDbConnection _connection;

    public DatabaseInitializer(WitDbConnection connection)
    {
        _connection = connection;
    }

    public async Task InitializeAsync()
    {
        await CreateTablesAsync();
        await SeedDataAsync();
    }

    private async Task CreateTablesAsync()
    {
        using var command = _connection.CreateCommand();

        // Users table
        command.CommandText = """
            CREATE TABLE IF NOT EXISTS Users (
                Id BIGINT PRIMARY KEY AUTOINCREMENT,
                Name VARCHAR(100) NOT NULL,
                Email VARCHAR(255) NOT NULL,
                CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
            )
            """;
        await command.ExecuteNonQueryAsync();

        // Unique index on email
        command.CommandText = """
            CREATE UNIQUE INDEX IF NOT EXISTS IX_Users_Email 
            ON Users(Email)
            """;
        await command.ExecuteNonQueryAsync();

        // Products table
        command.CommandText = """
            CREATE TABLE IF NOT EXISTS Products (
                Id BIGINT PRIMARY KEY AUTOINCREMENT,
                Name VARCHAR(200) NOT NULL,
                Description TEXT,
                Price DECIMAL(10, 2) NOT NULL,
                Stock INT NOT NULL DEFAULT 0,
                CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
            )
            """;
        await command.ExecuteNonQueryAsync();

        // Index for product search
        command.CommandText = """
            CREATE INDEX IF NOT EXISTS IX_Products_Name 
            ON Products(Name)
            """;
        await command.ExecuteNonQueryAsync();
    }
}

Seed Data

private async Task SeedDataAsync()
{
    using var command = _connection.CreateCommand();

    // Check if data exists
    command.CommandText = "SELECT COUNT(*) FROM Users";
    var count = Convert.ToInt64(await command.ExecuteScalarAsync());
    
    if (count > 0)
        return;  // Already seeded

    // Seed users
    command.CommandText = """
        INSERT INTO Users (Name, Email) VALUES
            ('Alice Johnson', 'alice@example.com'),
            ('Bob Smith', 'bob@example.com'),
            ('Carol Williams', 'carol@example.com')
        """;
    await command.ExecuteNonQueryAsync();

    // Seed products
    command.CommandText = """
        INSERT INTO Products (Name, Description, Price, Stock) VALUES
            ('Laptop', 'High-performance laptop', 1299.99, 50),
            ('Mouse', 'Wireless ergonomic mouse', 49.99, 200),
            ('Keyboard', 'Mechanical keyboard', 149.99, 100),
            ('Monitor', '27-inch 4K monitor', 499.99, 30),
            ('Headphones', 'Noise-canceling headphones', 299.99, 75)
        """;
    await command.ExecuteNonQueryAsync();

    Console.WriteLine("Database seeded with initial data.");
}

Key Points

Service Layer Pattern

The service layer encapsulates all database operations. Let's examine UserService as an example.

Basic Structure

public class UserService
{
    private readonly WitDbConnection _connection;

    public UserService(WitDbConnection connection)
    {
        _connection = connection;
    }

    // Methods for CRUD and queries...
}

Reading Data: SELECT

public async Task<List<User>> GetAllAsync()
{
    var users = new List<User>();

    using var command = _connection.CreateCommand();
    command.CommandText = "SELECT Id, Name, Email, CreatedAt FROM Users ORDER BY Id";

    using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        users.Add(new User
        {
            Id = reader.GetInt64(0),
            Name = reader.GetString(1),
            Email = reader.GetString(2),
            CreatedAt = reader.GetDateTime(3)
        });
    }

    return users;
}

Reading Single Row

public async Task<User?> GetByIdAsync(long id)
{
    using var command = _connection.CreateCommand();
    command.CommandText = "SELECT Id, Name, Email, CreatedAt FROM Users WHERE Id = @id";
    command.Parameters.Add(new WitDbParameter("@id", id));

    using var reader = await command.ExecuteReaderAsync();
    if (await reader.ReadAsync())
    {
        return new User
        {
            Id = reader.GetInt64(0),
            Name = reader.GetString(1),
            Email = reader.GetString(2),
            CreatedAt = reader.GetDateTime(3)
        };
    }

    return null;
}

Creating Data: INSERT with RETURNING

The RETURNING clause is incredibly useful — you get the inserted row back without a second query:

public async Task<User> CreateAsync(string name, string email)
{
    using var command = _connection.CreateCommand();
    command.CommandText = """
        INSERT INTO Users (Name, Email)
        VALUES (@name, @email)
        RETURNING Id, Name, Email, CreatedAt
        """;
    command.Parameters.Add(new WitDbParameter("@name", name));
    command.Parameters.Add(new WitDbParameter("@email", email));

    using var reader = await command.ExecuteReaderAsync();
    await reader.ReadAsync();

    return new User
    {
        Id = reader.GetInt64(0),
        Name = reader.GetString(1),
        Email = reader.GetString(2),
        CreatedAt = reader.GetDateTime(3)
    };
}

Updating Data: UPDATE with RETURNING

public async Task<User?> UpdateAsync(long id, string name, string email)
{
    using var command = _connection.CreateCommand();
    command.CommandText = """
        UPDATE Users 
        SET Name = @name, Email = @email
        WHERE Id = @id
        RETURNING Id, Name, Email, CreatedAt
        """;
    command.Parameters.Add(new WitDbParameter("@id", id));
    command.Parameters.Add(new WitDbParameter("@name", name));
    command.Parameters.Add(new WitDbParameter("@email", email));

    using var reader = await command.ExecuteReaderAsync();
    if (await reader.ReadAsync())
    {
        return new User
        {
            Id = reader.GetInt64(0),
            Name = reader.GetString(1),
            Email = reader.GetString(2),
            CreatedAt = reader.GetDateTime(3)
        };
    }

    return null;  // User not found
}

Deleting Data: DELETE

public async Task<bool> DeleteAsync(long id)
{
    using var command = _connection.CreateCommand();
    command.CommandText = "DELETE FROM Users WHERE Id = @id";
    command.Parameters.Add(new WitDbParameter("@id", id));

    var affected = await command.ExecuteNonQueryAsync();
    return affected > 0;
}

Parameterized Queries: Security First

Always use parameters to prevent SQL injection:

// ❌ NEVER do this - SQL injection vulnerability!
command.CommandText = 

        
        

            
            Loading...
        
quot;SELECT * FROM Users WHERE Name = '{userInput}'";

// ✅ Always use parameters
command.CommandText = "SELECT * FROM Users WHERE Name = @name";
command.Parameters.Add(new WitDbParameter("@name", userInput));

Helper Method: Reading to Entity

For cleaner code, extract the mapping logic:

private User ReadUser(WitDbDataReader reader)
{
    return new User
    {
        Id = reader.GetInt64(reader.GetOrdinal("Id")),
        Name = reader.GetString(reader.GetOrdinal("Name")),
        Email = reader.GetString(reader.GetOrdinal("Email")),
        CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt"))
    };
}

Controllers & Endpoints

Controllers handle HTTP requests and delegate to services. Here's the pattern:

UsersController

[ApiController]
[Route("api/[controller]")]
public class UsersController : ControllerBase
{
    private readonly UserService _userService;

    public UsersController(UserService userService)
    {
        _userService = userService;
    }

    [HttpGet]
    public async Task<ActionResult<List<UserDto>>> GetAll()
    {
        var users = await _userService.GetAllAsync();
        return users.Select(u => new UserDto(u)).ToList();
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<UserDto>> GetById(long id)
    {
        var user = await _userService.GetByIdAsync(id);
        if (user == null)
            return NotFound();

        return new UserDto(user);
    }

    [HttpPost]
    public async Task<ActionResult<UserDto>> Create([FromBody] CreateUserRequest request)
    {
        // Check for duplicate email
        var existing = await _userService.GetByEmailAsync(request.Email);
        if (existing != null)
            return BadRequest("Email already in use");

        var user = await _userService.CreateAsync(request.Name, request.Email);
        return CreatedAtAction(nameof(GetById), new { id = user.Id }, new UserDto(user));
    }

    [HttpPut("{id}")]
    public async Task<ActionResult<UserDto>> Update(long id, [FromBody] UpdateUserRequest request)
    {
        var user = await _userService.UpdateAsync(id, request.Name, request.Email);
        if (user == null)
            return NotFound();

        return new UserDto(user);
    }

    [HttpDelete("{id}")]
    public async Task<ActionResult> Delete(long id)
    {
        var deleted = await _userService.DeleteAsync(id);
        if (!deleted)
            return NotFound();

        return NoContent();
    }
}

DTO Pattern

We use DTOs to control what's exposed in the API:

// Entity (internal)
public class User
{
    public long Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; }
}

// DTO (external API contract)
public record UserDto(long Id, string Name, string Email, DateTime CreatedAt)
{
    public UserDto(User user) : this(user.Id, user.Name, user.Email, user.CreatedAt) { }
}

// Request models
public record CreateUserRequest(string Name, string Email);
public record UpdateUserRequest(string Name, string Email);

Complete Endpoint Reference

Users API

Products API

Transaction Management

For operations that modify multiple rows, transactions ensure atomicity — either all changes succeed or none do.

Bulk Import with Transaction

The /api/products/bulk endpoint imports multiple products in a single transaction:

[HttpPost("bulk")]
public async Task<ActionResult<BulkImportResult>> BulkImport(
    [FromBody] List<CreateProductRequest> products)
{
    if (products == null || products.Count == 0)
        return BadRequest("No products provided");

    var result = await _productService.BulkImportAsync(products);
    return Ok(result);
}

Service Implementation

public async Task<BulkImportResult> BulkImportAsync(List<CreateProductRequest> products)
{
    var imported = 0;
    var failed = 0;
    var errors = new List<string>();

    // Start transaction
    using var transaction = _connection.BeginTransaction();

    try
    {
        using var command = _connection.CreateCommand();
        command.Transaction = transaction;

        foreach (var product in products)
        {
            try
            {
                command.CommandText = """
                    INSERT INTO Products (Name, Description, Price, Stock)
                    VALUES (@name, @desc, @price, @stock)
                    """;
                command.Parameters.Clear();
                command.Parameters.Add(new WitDbParameter("@name", product.Name));
                command.Parameters.Add(new WitDbParameter("@desc", product.Description ?? ""));
                command.Parameters.Add(new WitDbParameter("@price", product.Price));
                command.Parameters.Add(new WitDbParameter("@stock", product.Stock));

                await command.ExecuteNonQueryAsync();
                imported++;
            }
            catch (Exception ex)
            {
                failed++;
                errors.Add(

        
        

            
            Loading...
        
quot;Failed to import '{product.Name}': {ex.Message}");
            }
        }

        // Commit all changes
        transaction.Commit();
    }
    catch (Exception)
    {
        // Rollback on any error
        transaction.Rollback();
        throw;
    }

    return new BulkImportResult(imported, failed, errors);
}

Result Model

public record BulkImportResult(
    int Imported,
    int Failed,
    List<string> Errors
);

Testing Bulk Import

curl -X POST http://localhost:5184/api/products/bulk \
  -H "Content-Type: application/json" \
  -d '[
    {"name": "USB Cable", "description": "USB-C cable", "price": 9.99, "stock": 500},
    {"name": "Webcam", "description": "HD webcam", "price": 79.99, "stock": 50},
    {"name": "Mouse Pad", "description": "Large gaming mouse pad", "price": 19.99, "stock": 200}
  ]'

Response:

{
  "imported": 3,
  "failed": 0,
  "errors": []
}

Transaction Best Practices

Advanced Queries

Beyond basic CRUD, the sample demonstrates several advanced query patterns.

Search with LIKE

public async Task<List<User>> SearchByNameAsync(string searchTerm)
{
    var users = new List<User>();

    using var command = _connection.CreateCommand();
    command.CommandText = """
        SELECT Id, Name, Email, CreatedAt 
        FROM Users 
        WHERE Name LIKE @term
        ORDER BY Name
        """;
    command.Parameters.Add(new WitDbParameter("@term", 

        
        

            
            Loading...
        
quot;%{searchTerm}%"));

    using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        users.Add(ReadUser(reader));
    }

    return users;
}

Usage:

curl "http://localhost:5184/api/users/search?q=alice"

Pagination with LIMIT/OFFSET

public async Task<(List<User> Users, int TotalCount)> GetPagedAsync(int page, int pageSize)
{
    using var command = _connection.CreateCommand();

    // Get total count
    command.CommandText = "SELECT COUNT(*) FROM Users";
    var totalCount = Convert.ToInt32(await command.ExecuteScalarAsync());

    // Get page
    command.CommandText = """
        SELECT Id, Name, Email, CreatedAt 
        FROM Users 
        ORDER BY Id
        LIMIT @limit OFFSET @offset
        """;
    command.Parameters.Add(new WitDbParameter("@limit", pageSize));
    command.Parameters.Add(new WitDbParameter("@offset", (page - 1) * pageSize));

    var users = new List<User>();
    using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        users.Add(ReadUser(reader));
    }

    return (users, totalCount);
}

Usage:

curl "http://localhost:5184/api/users/page?page=1&size=10"

Response:

{
  "items": [...],
  "page": 1,
  "pageSize": 10,
  "totalCount": 25,
  "totalPages": 3
}

Statistics with Aggregations

public async Task<ProductStatistics> GetStatisticsAsync()
{
    using var command = _connection.CreateCommand();
    command.CommandText = """
        SELECT 
            COUNT(*) AS TotalProducts,
            SUM(Stock) AS TotalStock,
            AVG(Price) AS AveragePrice,
            MIN(Price) AS MinPrice,
            MAX(Price) AS MaxPrice,
            SUM(Price * Stock) AS TotalInventoryValue
        FROM Products
        """;

    using var reader = await command.ExecuteReaderAsync();
    await reader.ReadAsync();

    return new ProductStatistics
    {
        TotalProducts = reader.GetInt32(0),
        TotalStock = reader.GetInt32(1),
        AveragePrice = reader.GetDecimal(2),
        MinPrice = reader.GetDecimal(3),
        MaxPrice = reader.GetDecimal(4),
        TotalInventoryValue = reader.GetDecimal(5)
    };
}

Usage:

curl http://localhost:5184/api/products/statistics

Response:

{
  "totalProducts": 5,
  "totalStock": 455,
  "averagePrice": 459.99,
  "minPrice": 49.99,
  "maxPrice": 1299.99,
  "totalInventoryValue": 156746.50
}

Price Range Filtering

public async Task<List<Product>> GetByPriceRangeAsync(decimal minPrice, decimal maxPrice)
{
    using var command = _connection.CreateCommand();
    command.CommandText = """
        SELECT Id, Name, Description, Price, Stock, CreatedAt 
        FROM Products 
        WHERE Price >= @min AND Price <= @max
        ORDER BY Price
        """;
    command.Parameters.Add(new WitDbParameter("@min", minPrice));
    command.Parameters.Add(new WitDbParameter("@max", maxPrice));

    var products = new List<Product>();
    using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        products.Add(ReadProduct(reader));
    }

    return products;
}

Usage:

curl "http://localhost:5184/api/products/price-range?min=50&max=200"

Testing with Swagger

The built-in Swagger UI makes testing effortless.

Using Swagger UI

1. Navigate to http://localhost:5184
2. Click on any endpoint to expand it
3. Click "Try it out"
4. Fill in parameters (if required)
5. Click "Execute"
6. View the response

Example: Creating a User

1. Expand POST /api/users
2. Click "Try it out"
3. Enter the request body:

{
  "name": "John Doe",
  "email": "john@example.com"
}

4. Click "Execute"
5. See the response:

{
  "id": 4,
  "name": "John Doe",
  "email": "john@example.com",
  "createdAt": "2025-01-15T14:30:00"
}

Testing with curl

# Get all users
curl http://localhost:5184/api/users

# Create a user
curl -X POST http://localhost:5184/api/users \
  -H "Content-Type: application/json" \
  -d '{"name": "John Doe", "email": "john@example.com"}'

# Update a user
curl -X PUT http://localhost:5184/api/users/4 \
  -H "Content-Type: application/json" \
  -d '{"name": "John Smith", "email": "john.smith@example.com"}'

# Delete a user
curl -X DELETE http://localhost:5184/api/users/4

# Search products
curl "http://localhost:5184/api/products/search?q=laptop"

# Get statistics
curl http://localhost:5184/api/products/statistics

# Bulk import
curl -X POST http://localhost:5184/api/products/bulk \
  -H "Content-Type: application/json" \
  -d '[{"name":"Item 1","price":10,"stock":100},{"name":"Item 2","price":20,"stock":50}]'

Summary

In this tutorial, we built a complete REST API using WitDatabase's ADO.NET provider:

When to Use This Approach

✅ Use ADO.NET when:

• You need precise SQL control
• Performance is critical
• You have complex queries
• You're comfortable with SQL

❌ Consider EF Core when:

• Rapid development is priority
• You want automatic change tracking
• You need migrations
• Queries are straightforward CRUD

Get the Code

git clone https://github.com/dmitrat/WitDatabase.git
cd WitDatabase/Samples/OutWit.Database.Samples.WebApi
dotnet run

Have questions? Open an issue on GitHub or star the repo if you found this useful!