Skip to content

Home

Jump to bottom
Anrijs Vitolins edited this page Mar 25, 2023 · 10 revisions

Dapper.Cqrs

Packages provides ability to use CQRS (Command Query Responsibility Segregation) pattern for database data reading (Queries) or its modification (Commands). It includes ambient connection and transaction handling, so after easy set-up, developers are only required to write Query and Command classes which usually are database SQL statements, wrapped in separate classes, with a little bit of parameter handling (business as usual).

Solution is using Dapper Micro-ORM, a simple object mapper for .Net built by StackOverflow developers and is one of the most performing ORMs in .Net landscape (if not fastest).

As Dapper uses "magic strings" for database statements, package is built in a way it exposes these magic string statements for testing onto database engine (and defined structures).

Expected external dependency on Microsoft.Extensions.Logging ILogger<T> interface allows to see its internal doings as logging records during development (debugging).

Simplistic short example on how usage might look in your code:

public class MyBusinessLogic : IMyBusinessLogic
{
    // field, assigned in constructor, which provides ability 
    // to execute Queries and Commands onto database
    private readonly ICommandQueryContext _db;
    
    // Class constructor - inject DB access context into field above
    public MyBusinessLogic(ICommandQueryContext db)
    {
        _db = db;
    }
    
    // Business logic method
    public async Task<MyObject> BusinessOperation(int myObjectId)
    {
        // COMMAND: Executing some INSERT into Audit table implemented in ICommand class
        await _db.ExecuteAsync(new AuditRecordCommand(auditData));
        
        // QUERY: Get the object to return implemented in IQuery class
        return await _db.QueryAsync(new GetMyObjectQuery(myObjectId));
    }
}

// ICommand class used in Logic class above to insert Audit record
public sealed class AuditRecordCommand : MsSqlCommandBase
{
    private readonly AuditRecord _dbObject;
    public AuditRecordCommand(AuditRecord dbObject) => _dbObject = dbObject;
    public override object Parameters => _dbObject;
    public override string SqlStatement => @"INSERT INTO Audit (Field1, Field2...) VALUES (@Field1, @Field2)";
}

// IQuery class used to retrieve record in logic class above
public sealed class GetMyObjectQuery : MsSqlQuerySingleBase<MyObject>
{
    public override string SqlStatement => "SELECT * FROM MyObject";
}

When first command (AuditRecordInsert) is executed, dbContext object creates and opens DB connection and creates a transaction on it, then executes the INSERT command with passed object as parameter for it. After - query object (GetMyObjectQuery) is reusing the same connection and transaction to get the data and return it to caller. Upon operation end and dbContext dispose - transaction is committed and connection closed. In case there are exceptions in business logic (including database operations) - transaction is rollback for entire operation (unit-of-work).

If you are worried about SQL statements being strings in query and command classes - there is a separate package providing ability to check these strings by validating them against database engine to pinpoint structural inconsistencies or simply syntax errors.

This approach provides a big flexibility of using database data in any of your business logic without relaying onto numerous repositories, as well as by using native database language for optimized data modification and retrieval, giving way bigger performance benefits in compare to magically generated SQL statements by OOP language (like in Entity Framework, Linq-to-SQL).

Packages are built with developer productivity in mind, implementing extensive logging for debugging, ability to easily retrieve object internals, giving out timings on SQL statement execution, highly documented for Intellisense as fast help to choose functionalities. Additionally GitHub repository contains installable Visual Studio templates to generate boilerplate code for classes in File -> New menus in Visual Studio.

Code in packages are covered by unit-tests and assembly-tests to levels more than 90% to ensure they are of the best quality possible and to work as expected.

Package suite

Code is separated into three assemblies (packages):

Abstractions

Provide common interfaces and base functionalities suitable for all database engines. This package does not demand specific database client as dependency. It contains IQuery and ICommand interfaces as well as internal interfaces and implementation to employ Unit-of-Work database connection and transaction handling.

Database specific

(Currently only MS SQL)

Classes and functionalities to work with database engine, handling its specific connection by opening, closing and transaction handling transparently for application which is using it.

Functionality testing

(Currently only for xUnit)

Classes and functionalities to be able to write integration/assembly tests, using real database engine, which eases checking SQL statements in Queries and Commands, Compares C# POCO data objects with database structure and saving/reading test data.

Is it better than Entity Framework?

As with everything - yes and no. Depending on... (your lifestyle and preferences here)

Using Dapper and similar in projects are usually tied to need for solid database data retrieval performance or getting data from very complex queries. Latest EF Core 6 introducing quite a performance boost, so it might not be the case lately, however...

When using Entity Framework you are rarely concerned about SQL syntax and what magic is hidden behind your LINQ statements. It works perfectly for simple solutions and probably would be recommended approach for projects/people who do not want to deal with database query language (effectively making data storage mechanism a second class citizen to application).

But as soon as database becomes more complex and required data retrieval involves many objects - not following generated SQL behind LINQ can lead to considerable performance problems (N+1, Cartesian product). Then different non-standard side approaches are implemented (Stored Procedures, Views, Custom solutions - ADO.NET or using some MicroORM as fallback for such situations).

So using Dapper will make you write SQL statements and do more boilerplate coding, but it increases control over how and when data is read and write to database and bringing data storage as a first class citizen in your application.

This project just wraps Dapper into CQRS approach and allows to get all project SQL statements in "magic strings" easily located by C# code (e.g. for automated validation).

Clone this wiki locally