); } }

**Rationale:** The gateway does not manage connection pooling or disposal. It only instantiates connections on demand. Connection pooling is handled automatically by `Microsoft.Data.Sqlite` when connections are properly disposed. This separation keeps the gateway stateless and thread-safe.

### Step 2: Define the Query Specification Contract

Hardcoding SQL strings inside execution methods creates tight coupling and prevents parameterization. Instead, encapsulate the query text and parameters in a dedicated specification object.

```csharp
// QuerySpecification.cs
using Microsoft.Data.Sqlite;

namespace DataAccess.Abstractions;

public sealed class QuerySpecification
{
    public string SqlText { get; }
    public Dictionary<string, object> Parameters { get; }

    public QuerySpecification(string sqlText)
    {
        SqlText = sqlText ?? throw new ArgumentNullException(nameof(sqlText));
        Parameters = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
    }

    public QuerySpecification WithParameter(string name, object value)
    {
        Parameters[name] = value;
        return this;
    }

    internal void ApplyTo(SqliteCommand command)
    {
        foreach (var kvp in Parameters)
        {
            command.Parameters.AddWithValue(kvp.Key, kvp.Value ?? DBNull.Value);
        }
    }
}

Rationale: The fluent WithParameter method enables readable query construction. The ApplyTo method bridges the specification to the ADO.NET command object, ensuring parameters are bound safely without string interpolation. This eliminates SQL injection vectors by design.

Step 3: Implement the Generic Execution Engine

The core of the framework is a generic method that resolves connections, executes commands, and maps results. It relies on a mapping contract to keep the engine decoupled from domain models.

// IRecordMapper.cs
using System.Data;

namespace DataAccess.Abstractions;

public interface IRecordMapper<T>
{
    T Map(IDataRecord record);
}

// SqlExecutionEngine.cs
using Microsoft.Data.Sqlite;
using System.Data;

namespace DataAccess.Implementations;

public class SqlExecutionEngine
{
    private readonly IConnectionGateway _gateway;

    public SqlExecutionEngine(IConnectionGateway gateway)
    {
        _gateway = gateway;
    }

    public async Task<List<T>> ExecuteQueryAsync<T>(QuerySpecification spec) 
        where T : IRecordMapper<T>, new()
    {
        var results = new List<T>();
        
        await using var connection = _gateway.CreateConnection();
        await connection.OpenAsync();
        
        await using var command = connection.CreateCommand();
        command.CommandText = spec.SqlText;
        spec.ApplyTo(command);
        
        await using var reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            results.Add(new T().Map(reader));
        }

        return results;
    }
}

Rationale:

• await using ensures deterministic disposal of connections, commands, and readers, even if exceptions occur.
• The generic constraint where T : IRecordMapper<T>, new() guarantees that the engine can instantiate the target type and invoke its mapping logic without reflection overhead.
• Separating execution from mapping allows the engine to remain provider-agnostic in structure, while the IRecordMapper contract handles domain-specific column resolution.

Step 4: Wire Domain Models to the Mapping Contract

Domain models implement IRecordMapper<T> to define how raw database records translate into C# objects. Extension methods on IDataRecord reduce repetitive ordinal lookups and null checks.

// DataRecordExtensions.cs
using System.Data;

namespace DataAccess.Utilities;

public static class DataRecordExtensions
{
    public static int ReadInt32(this IDataRecord record, string columnName)
    {
        var ordinal = record.GetOrdinal(columnName);
        return record.IsDBNull(ordinal) ? 0 : record.GetInt32(ordinal);
    }

    public static string ReadString(this IDataRecord record, string columnName)
    {
        var ordinal = record.GetOrdinal(columnName);
        return record.IsDBNull(ordinal) ? string.Empty : record.GetString(ordinal);
    }

    public static decimal ReadDecimal(this IDataRecord record, string columnName)
    {
        var ordinal = record.GetOrdinal(columnName);
        return record.IsDBNull(ordinal) ? 0m : record.GetDecimal(ordinal);
    }
}

// Product.cs
using System.Data;
using DataAccess.Abstractions;
using DataAccess.Utilities;

namespace Domain.Models;

public class Product : IRecordMapper<Product>
{
    public int Id { get; set; }
    public string Sku { get; set; } = string.Empty;
    public string Name { get; set; } = string.Empty;
    public decimal UnitPrice { get; set; }

    public Product Map(IDataRecord record)
    {
        Id = record.ReadInt32("id");
        Sku = record.ReadString("sku");
        Name = record.ReadString("name");
        UnitPrice = record.ReadDecimal("unit_price");
        return this;
    }
}

Rationale: Mapping logic lives inside the domain model, keeping the execution engine clean. Extension methods centralize DBNull handling and ordinal resolution, preventing InvalidCastException errors when nullable columns are encountered. The Map method returns this to satisfy the generic instantiation pattern without requiring factory delegates.

Step 5: Register and Consume

Dependency injection ties the components together. Registration takes three lines, and consumption requires only a query specification and a target type.

// Program.cs
using DataAccess.Abstractions;
using DataAccess.Implementations;
using Domain.Models;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddSingleton<IConnectionGateway, SqliteConnectionGateway>();
builder.Services.AddSingleton<SqlExecutionEngine>();

var app = builder.Build();

app.MapGet("/products", async (SqlExecutionEngine engine) =>
{
    var spec = new QuerySpecification("SELECT id, sku, name, unit_price FROM products;");
    var products = await engine.ExecuteQueryAsync<Product>(spec);
    return Results.Ok(products);
});

app.Run();

Rationale: Singleton registration is safe because both the gateway and engine are stateless. The execution engine creates fresh connections per request, leveraging ADO.NET's built-in connection pooling. This pattern scales horizontally without connection contention.

Pitfall Guide

1. Connection Leaks from Missing await using

Explanation: Forgetting to dispose SqliteConnection, SqliteCommand, or SqliteDataReader exhausts the connection pool, causing TimeoutException under load. Fix: Always wrap ADO.NET objects in await using blocks. Never rely on garbage collection for connection cleanup.

2. The new() Constraint Trap

Explanation: Generic execution fails at runtime if the target model lacks a parameterless constructor. EF Core proxies or record types with required parameters break this pattern. Fix: Ensure all mapped models expose a public parameterless constructor. Use init properties or explicit setters instead of required constructor parameters.

3. DBNull Crashes on Nullable Columns

Explanation: Calling GetInt32() or GetString() on a database NULL throws InvalidCastException. Fix: Always check record.IsDBNull(ordinal) before reading, or use the extension methods provided above that default to safe fallback values.

4. String Interpolation for Parameters

Explanation: Building queries like $"SELECT * FROM users WHERE id = {userId}" bypasses parameter binding and opens SQL injection vulnerabilities. Fix: Never concatenate user input into SQL strings. Always use QuerySpecification.WithParameter() to bind values safely through SqliteParameter.

5. Synchronous Blocking in Async Pipelines

Explanation: Calling .Result or .Wait() on async ADO.NET methods causes thread pool starvation and deadlocks in ASP.NET Core. Fix: Propagate async/await all the way to the endpoint. Use ExecuteReaderAsync(), ReadAsync(), and OpenAsync() consistently.

6. Overcomplicating the Mapper Contract

Explanation: Adding validation, logging, or business logic inside Map() violates single responsibility and slows down high-volume reads. Fix: Keep Map() strictly focused on column-to-property assignment. Move validation and transformation to a separate domain service or DTO mapper.

7. Ignoring Connection Pooling Behavior

Explanation: Creating long-lived singleton connections instead of short-lived per-request connections breaks ADO.NET pooling and causes state corruption. Fix: Always create and dispose connections within the scope of a single operation. Let the provider manage the underlying physical connections.

Production Bundle

Action Checklist

• Install Microsoft.Data.Sqlite and verify connection string format matches Data Source=...
• Register IConnectionGateway and SqlExecutionEngine as singletons in DI
• Implement IRecordMapper<T> on all domain models requiring database hydration
• Add DBNull-safe extension methods for IDataRecord to prevent runtime crashes
• Replace all raw SQL strings with QuerySpecification instances using parameter binding
• Audit all data access methods for await using disposal patterns
• Configure connection pooling limits in appsettings.json if exceeding 100 concurrent requests

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Simple CRUD with explicit SQL control	Custom ADO.NET Gateway	Zero dependencies, full query transparency, deterministic performance	Low (maintenance only)
Complex relational graphs with lazy loading	EF Core	Change tracking, navigation properties, migration tooling	Medium (memory + CPU overhead)
High-throughput microservice reads	Dapper or Custom Gateway	Micro-ORM speed without boilerplate; custom gateway adds zero external deps	Low
Legacy database with stored procedures	Custom Gateway	Precise parameter binding, result set control, no ORM mapping conflicts	Low
Rapid prototyping / internal tools	EF Core	Fastest initial development, auto-migrations, scaffolding	Medium

Configuration Template

// appsettings.json
{
  "ConnectionStrings": {
    "Default": "Data Source=./data/application.db;Pooling=true;Max Pool Size=100;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning"
    }
  }
}

// DI Registration (Program.cs)
builder.Services.AddSingleton<IConnectionGateway, SqliteConnectionGateway>();
builder.Services.AddSingleton<SqlExecutionEngine>();

// Optional: Add health check for database connectivity
builder.Services.AddHealthChecks()
    .AddCheck<SqliteHealthCheck>("database", tags: new[] { "db" });

Quick Start Guide

1. Initialize Project: Run dotnet new webapi -n DataGatewayDemo and navigate into the directory.
2. Install Provider: Execute dotnet add package Microsoft.Data.Sqlite.
3. Create Database: Use sqlite3 app.db to create a local file, then run CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT, name TEXT, unit_price REAL);
4. Seed Data: Insert test rows: INSERT INTO products VALUES (1, 'SKU-001', 'Widget', 12.50);
5. Wire & Run: Copy the DI registration, gateway, engine, and mapper code into your project. Start the app with dotnet run and hit /products to verify hydration.

This architecture delivers explicit SQL control, predictable resource management, and ORM-like ergonomics without external dependencies. By enforcing generic mapping contracts and parameterized specifications, you eliminate common data access anti-patterns while maintaining full visibility into query execution.