Dapper.CQRS 2.0.2

There is a newer version of this package available.
See the version list below for details.
dotnet add package Dapper.CQRS --version 2.0.2
NuGet\Install-Package Dapper.CQRS -Version 2.0.2
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Dapper.CQRS" Version="2.0.2" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Dapper.CQRS --version 2.0.2
#r "nuget: Dapper.CQRS, 2.0.2"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install Dapper.CQRS as a Cake Addin
#addin nuget:?package=Dapper.CQRS&version=2.0.2

// Install Dapper.CQRS as a Cake Tool
#tool nuget:?package=Dapper.CQRS&version=2.0.2

Description

Dapper doodle is a CQRS wrapper for dapper. The idea behind dapper doodle is to get up and running with dapper quickly, by providing a set of base classes and interfaces that can assist you to get up and running without much fuss, while still offering the flexibility to drop down to native SQL queries.

WTF is Dapper?

Dapper is a lightweight micro-ORM developed by Stack-Overflow as an alternative to entity framework. A developer at stack overflow built it to solve the issue they were having with Entity Frameworks bulky slow queries. Dapper solves that by being almost as fast as ADO.NET but easier to use and map objects against each other. Dapper gives the developer more control by offering the ability to map objects against native SQL queries.

WTF is CQRS?

CQRS stands for Command Query Responsibility Segregation. The idea behind it is to be able to separate your commands (DML) and your queries (DQL). CQRS is a great pattern to follow for small or large systems and offers the flexibility to keep all your database interactions very structured and orderly as the app scales. It is quite similar to the repository pattern, however instead of using interfaces as abstractions we are abstracting every database transaction as a class instead of the context-based model that is used in EF.

Getting Started

To get started you need to go and download the main DapperDoodle nuget package from nuget.org. The package is called DapperDoodle if you would prefer to download it directly from Nuget. Or you can download the .dll here and import the dependency directly into your project.

After creating a ASP.NET Web app you can follow these steps to hook up dapper doodle to your project.

Examples

If you would like to view a few examples of how to implement this in a live production application, look no further. https://github.com/caybokotze/dapper-doodle-examples

Startup.cs

Below, you can register DapperDoodle, which will setup all the required dependencies for DapperDoodle to work. The ConfigureDapperDoodle() method has support for 2 parameters, the connection string and the Database type.

Note: You can only use the null parameter with the DBMS.SQLite option which will create a default connection string on your behalf. All other database types will throw a Argument Null Exception.

public void ConfigureServices(IServiceCollection services)
{
		services.AddControllers();
    services.ConfigureDapperDoodle(null, DBMS.SQLite);
}

Now you are ready to get started using the CQRS setup within the project. All you have to do now is to start creating your command and query classes and plug them into your controllers.

Resolving Dependencies within Controllers

Because the decencies are already setup, no need to go about registering your own manual dependencies. All you need to do is insert ICommandExecutor and IQueryExecutor into the constructor and your dependencies will resolve.

public class HomeController : Controller
{
	public ICommandExecutor CommandExecutor { get; }
	public IQueryExecutor QueryExecutor { get; }
	
	public HomeController(
	    ICommandExecutor commandExecutor, 
	    IQueryExecutor queryExecutor)
	{
	    CommandExecutor = commandExecutor;
	    QueryExecutor = queryExecutor;
	}
}

Commands

When calling the Execute() method, you do need to supply at least 1 parameter. Otherwise you end up with a recursive call, in this case it will lead to a stack overflow exception.

Non-Generic

Non-Generic

public class PersistSomething : Command
{
    public override void Execute()
    {
        Execute("INSERT INTO People(name, surname) VALUES ('John', 'Williams');");
    }
}

Generic

With Generic implementations you can define the expected return type in the base class. For commands this will commonly only be an integer in use cases where retrieving the ID of the inserted record is required.

public class PersistSomething : Command<int>
{
    public override void Execute()
    {
        var returnId = 
                Execute("INSERT INTO People(name, surname) VALUES ('John', 'Williams'); SELECT last_insert_id();");
    }
}

Using the Command Builder


The command builder is a set of extensions build into dapper doodle to allow you to be able to build up SQL queries a bit faster using CQRS, for the common queries where writing them manually might not be necessary.

When using the command builder, there is no need to define any generics for the command class as they are defined in the command builder.

public class PersistSomething : Command
{
    public override void Execute()
    {
        var id = InsertAndReturnId("INSERT INTO People(name, surname) VALUES ('John', 'Williams');");
    }
}

Insert Builder

The insert builder is overloaded with update by default, so if the record does already exist, it will just update the information, not insert it again, which will most likely lead to an exception.

The insert builder returns the id of the created record, which can be persisted.

public override void Execute()
{
    var id = BuildInsert<Person>();
}

Update Builder

public override void Execute()
{
    var id = BuildUpdate<Person>();
}

Person Model

public class Person
{
    public string Name { get; set; }
    public string Surname { get; set; }
}

Queries

Query results are mapped onto the Result Interface which also specifies the return type of the Generically defined class. When you would like to build a query you need to create a class that inherits from Query and specify the return type as a generic argument.

Generic

public class SelectAPerson : Query<Person>
{
    private readonly int _id;

    public SelectAPerson(int Id)
    {
        _id = Id;
    }
    public override void Execute()
    {
        Result = BuildSelect<Person>("where id = @Id", new { Id = _id });
    }
}

BuildSelect has a override for the where clause that can be appended to the end of the select statement. The second argument exists to specify the SQL query parameters.

Non-Generic

Although this is possible it doesn't really make sense to do so because if you are writing a query, you would always expect something in return. You can in turn follow the same pattern as a Command and just call the Execute() override method if return values are not important.

Writing Native Commands

You can also use the Dapper Doodle package to write native (SQL) commands if the command builders do not quite solve your problem.

List Parameter Arguments

The DapperDoodle package supports the option for list parameters for Commands.

public class InsertManyPeople : Command
{
    public List<Person> People = new List<Person>()
    {
        new Person()
        {
            Name = GetRandomString(),
            Surname = GetRandomString(),
            Email = GetRandomEmail()
        },
        new Person()
        {
            Name = GetRandomString(),
            Surname = GetRandomString(),
            Email = GetRandomEmail()
        }
    };
    
    public override void Execute()
    {
        Execute(@"INSERT INTO People ( name, surname, email )
                VALUES (@Name, @Surname, @Email)",
                People);
    }
}

Using Dapper Directly

Fetching the IDbConnection Instance

The dapper library is an extension library that sits on top of the IDbConnection interface. So we can use Dapper Doodle's dependency registrations to fetch our IDbConnection instance to write Dapper queries natively for more complex queries.

public class TestBaseExecutor : Query<int>
{
    public override void Execute()
    {
        var connectionInstance = GetIDbConnection();
        var result = connectionInstance.QueryFirst("SELECT 1;");
        Result = result;
    }
}

More complex join statements to achieve deep linking (Eager Loading)

public Person MapPersonAndAddress(int personId)
{
    using(IDbConnection cnn = GetIDbConnection()))
    {
        var param = new
        {
            Id = personId
        };
        // This SqlCommand returns a person with the address of that person.
        var sql = @"select u.*, a.* from `USERS` u 
        left join ADDRESSES a 
        on u.address_id = a.Id WHERE u.Id = @Id";
        
        // This is a dapper command that takes in two generic parameter objects and the last one will be the one that is mapped to, which in this case is the same model
        var people = cnn.Query<Person, Address, Person>(sql, (person, address) =>
            {
                person.Address = address;
                return person;
            },
            param
        );
        return people.FirstOrDefault();
    }
}

Extension Libraries

Core library is built with .NET Standard 2.1. However currently the main package dependencies are for:

  • Dapper (2.0.78)
  • Microsoft.Exentions.DependencyInjection (5.0.1)
  • MySql.Data (8.0.22)
  • PluralizeService.Core (1.2.19)
  • Microsoft.Data.Sqlite (5.0.1)

Database Support

  • SQLite
  • MySQL
  • MSSQL (Still needs to be added and tested)

Feature List

  • Command Builder
  • Query Builder
  • Auto-Insert Statements
  • Auto-Update Statements
  • Auto-Select Statements
  • CQRS Interface helpers
  • Support For MySql
  • Support For MSSQL
  • Support For SqlLite
  • Support For PostgreSql

Pull Requests

If you would like to contribute to this package you are welcome to do so. Just fork this repository, create a PR against it and add me as a reviewer.

Testing Coverage

Testing coverage currently covers:

  • QueryExecutor dependency injection
  • CommandExecutor dependency Injection
  • CommandBuilder queries for MySql, Sqlite, MSSQL.

Coverage to be added

  • Query Builder
  • Variations on query builder
  • Variations on command builder
  • Testing Extension/Helper Classes
  • Dependency registrations for MSSQL.
  • Dependency registrations for MySql.
  • Dependency registrations for SqlLite.
Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.1 is compatible. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (1)

Showing the top 1 NuGet packages that depend on Dapper.CQRS:

Package Downloads
Ntk8

Auth-Ntk8 (authenticate) is a basic authentication package that could assist you to setup auth within your project a lot quicker. Use the provided base classes and services to quickly setup authentication within your project.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
4.0.1 263 10/15/2023
4.0.0 111 10/15/2023
3.1.4 408 5/2/2023
3.1.3 141 4/29/2023
3.1.2 148 4/28/2023
3.1.1 157 4/22/2023
3.1.0 421 12/28/2022
3.0.0 275 12/27/2022
2.0.3 323 11/27/2022
2.0.2 294 11/27/2022
2.0.1-beta 125 11/25/2022
2.0.0-beta 120 11/25/2022
1.3.3 391 8/15/2022
1.3.2 389 6/20/2022
1.3.1 944 6/10/2022
1.3.0 412 6/10/2022
1.2.7 401 6/9/2022
1.2.6 402 6/9/2022
1.2.5 406 6/9/2022
1.2.4 394 6/9/2022
1.2.3 1,422 5/5/2022
1.2.2 2,883 11/13/2021
1.2.1 313 10/14/2021
1.2.0 278 10/14/2021
1.1.1 325 7/29/2021
1.1.0 1,118 7/15/2021
1.0.0 309 6/8/2021

BaseSqlExecutor Logger should not be marked as nullable