StrongDAO 0.12.0

StrongDAO

StrongDAO is a performant extension library for Microsoft Access DAO that brings modern, strongly-typed querying capabilities to your Access database applications.

Why StrongDAO?

• Strong Typing: Query your Access databases with full type safety using classes, records, tuples, or scalar values - no more brittle string-based field access
• Easy Migration Path: Designed as a drop-in enhancement for existing DAO applications. Start using it alongside your current code without a complete rewrite
• Dapper-Like Experience: Familiar query patterns similar to OLEDB + Dapper, but using native DAO objects - no need to mix DAO and OLEDB in the same codebase (they don't mix well!)
• Fast Performance: Up to 9x faster than traditional DAO.OpenRecordset. Offering performance on par with Dapper in some scenarios
• Dynamic Support: Full support for dynamic queries when you need flexibility, with results castable to IDictionary<string, object>

Installation

Install Nuget package Strong DAO

Added fonctionality

New extension methods

• Database.Query<T>
• Database.QueryFirstOrDefault<T>
• QueryDef.Query<T>
• QueryDef.QueryFirstOrDefault<T>

Replace your OpenRecordset calls with the methods above to get a strongly typed object (or list of object) out of your queries.

And their dynamic counterpart:

• Database.Query
• Database.QueryFirstOrDefault
• QueryDef.Query
• QueryDef.QueryFirstOrDefault

You can directly cast the results of those queries to (IDictionary<string, object?>) if you need a more dictionary-like way of accessing the data.

New class:

• StrongRecordset

With the corresponding extension methods:

• Database.OpenStrongRecordset
• QueryDef.OpenStrongRecordset

Designed as a drop-in replacement for the built-in Recordset class and the OpenRecordset methods. This new recordset offers the same fonctionality as the original one, but it allows caching of the DAO.Fields (caching is activated by default). This can result in a free 2x performance boost. See the performance section for more details.

Enumerable Recordset:

The StrongRecordset also includes the AsEnumerable() method which enables:

• foreach enumeration: Iterate through records using standard foreach loops
• LINQ support: Apply LINQ queries (e.g., Where, Select, OrderBy) directly on the recordset

Basic usage

// Example1: Replacing OpenRecordset with OpenStrongRecordset
_dbEngine = new DAO.DBEngine();
var _db = _dbEngine.CreateDatabase("test.mdb");

var rs = _db.OpenStrongRecordset("SELECT * FROM Users");
rs.Fields["Name"].Value = "StrongDAO";

// Example2: Using AsEnumerable() with LINQ
_dbEngine = new DAO.DBEngine();
var _db = _dbEngine.CreateDatabase("test.mdb");

var rs = _db.OpenStrongRecordset("SELECT * FROM Users");
var results = rs.AsEnumerable()
    .Where(r => (int)r.Fields["Age"].Value > 18)
    .Select(r => new { 
        Id = r.Fields["ID"].Value, 
        Name = r.Fields["Name"].Value 
    })
    .ToList();

// Example 3: Doing strongly typed queries

// Class and record definition
public record PersonRecord(int? Id, string? PersonName, int? Age, string? Email, bool? IsActive, double? Score);
public class PersonClass
{
    public int? Id { get; set; }
    public string? PersonName { get; set; }
    public int? Age { get; set; }
    public string? Email { get; set; }
    public bool? IsActive { get; set; }
    public double? Score { get; set; }
}

_dbEngine = new DAO.DBEngine();
var _db = _dbEngine.CreateDatabase("test.mdb");

// Tuple
var person = _db.Query<(int? Id, string? PersonName, int? Age, string? Email, bool? IsActive, double? Score)>("SELECT * FROM BenchmarkTable");

// Scalar
var id = _db.Query<int?>("SELECT Id FROM BenchmarkTable");

// Class
var personClass = _db.Query<PersonClass>("SELECT * FROM BenchmarkTable");
// Record
var personRecord = _db.Query<PersonRecord>("SELECT * FROM BenchmarkTable");

_db.Close();

Performance

Benchmarks were conducted using BenchmarkDotNet on a AMD Ryzen 5 5600X system with .NET 10.0. All tests queried 50 rows from an Access database.

Key Highlights

• Up to 9x faster than DAO.OpenRecordset
• 2x faster when using OpenStrongRecordset instead of OpenRecordset
• Comparable to Dapper when mapping to Tuple while providing native DAO integration

Benchmark Results

Note: Ratios are relative to DAO_OpenRecordset (Baseline). Lower is better.