Dapper.CQRS
1.2.1
See the version list below for details.
dotnet add package Dapper.CQRS --version 1.2.1
NuGet\Install-Package Dapper.CQRS -Version 1.2.1
<PackageReference Include="Dapper.CQRS" Version="1.2.1" />
paket add Dapper.CQRS --version 1.2.1
#r "nuget: Dapper.CQRS, 1.2.1"
// Install Dapper.CQRS as a Cake Addin
#addin nuget:?package=Dapper.CQRS&version=1.2.1
// Install Dapper.CQRS as a Cake Tool
#tool nuget:?package=Dapper.CQRS&version=1.2.1
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 | Versions 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. |
-
.NETStandard 2.1
- Dapper (>= 2.0.78)
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 | 275 | 10/15/2023 |
4.0.0 | 115 | 10/15/2023 |
3.1.4 | 436 | 5/2/2023 |
3.1.3 | 146 | 4/29/2023 |
3.1.2 | 152 | 4/28/2023 |
3.1.1 | 161 | 4/22/2023 |
3.1.0 | 428 | 12/28/2022 |
3.0.0 | 281 | 12/27/2022 |
2.0.3 | 327 | 11/27/2022 |
2.0.2 | 296 | 11/27/2022 |
2.0.1-beta | 126 | 11/25/2022 |
2.0.0-beta | 120 | 11/25/2022 |
1.3.3 | 392 | 8/15/2022 |
1.3.2 | 390 | 6/20/2022 |
1.3.1 | 945 | 6/10/2022 |
1.3.0 | 412 | 6/10/2022 |
1.2.7 | 402 | 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,423 | 5/5/2022 |
1.2.2 | 2,885 | 11/13/2021 |
1.2.1 | 314 | 10/14/2021 |
1.2.0 | 278 | 10/14/2021 |
1.1.1 | 326 | 7/29/2021 |
1.1.0 | 1,119 | 7/15/2021 |
1.0.0 | 310 | 6/8/2021 |
Add support for 4 generic query arguments.