Implement Read/Write Operations Using CQRS And Dapper In ASP.NET Core - Detailed

Introduction

In this article, we will look into the implementation of reading Queries and Write Commands using CQRS and Dapper ORM in the ASP.Net Core 5.0 template. CQRS is a popular architecture pattern because it addresses a common problem with most enterprise applications. Separating write behavior from reading behavior, which is the essence of the CQRS architectural pattern, provides stability and scalability to enterprise applications while also improving overall performance.

In scenarios, when you have complex business logic CQRS may simplify understanding of the domain by dividing the problem into the command and query parts. In situations, when your UI is based on workflows and utilizes the Interface pattern it is easier to identify user's intents and translate them into domain events.

You can find the complete source code of this implementation here.

Prerequisites

• Visual Studio 2019 - Download from here
• .Net 5.0 SDK - Download from here

Table of Contents

1. Setup the Project
2. Tables Schema
3. Adding the Domain Models
4. Commands & Queries
5. Adding the Dependency Injection
6. Adding the MediatR Handlers and Controllers
7. Testing
8. Summary

Setup the Project

• Open Visual Studio and select "Create a new project" and click the "Next" button.
• Add the "project name" and "solution name", also choose the path to save the project in that location, click on "Next".
• Now choose the target framework ".Net 5.0" which we get once we install the SDK and also will get one more option to configure Open API support by default with that check box option.

Tables Schema

Create a Database in the SQL Server to execute the below schema under that database to create respective tables. If the database already exists then we can directly execute the schema without creating the database. Here we have created two tables Order & Product to work with CRUD Operations.

USE [OrderDb]
GO
/****** Object:  Table [dbo].[Orders]    Script Date: 17-08-2021 10:54:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
	[OrderId] [int] IDENTITY(1,1) NOT NULL,
	[OrderDetails] [nvarchar](max) NULL,
	[IsActive] [bit] NOT NULL,
	[OrderedDate] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Products]    Script Date: 17-08-2021 10:54:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
	[ProductId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NULL,
	[Price] [real] NOT NULL,
	[isDisCountApplied] [bit] NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Adding the Domain Models

Let's create the table models inside the project. To separate the things from each other we will be following the Onion Architecture pattern in which we will maintain all our Models under the Domain Layer. Create a Class library named Domain Layer and add the below class files inside that.

Order.cs

using System;

namespace DomainLayer
{
   public class Order
    {
       public int OrderId { get; set;}
       public string OrderDetails { get; set; }
       public bool IsActive { get; set; }
       public DateTime OrderedDate { get; set; }
    }
}

Product.cs

namespace DomainLayer
{
    public class Product
    {
       public int ProductId { get; set; }
       public string Name { get; set; }
       public float Price { get; set; }
       public bool isDisCountApplied  { get; set; }
    }
}

Domain Layer 

Commands & Queries

Commands are nothing but task-based operations where they will perform the write operations to the database. Here will need one more project (Class Library) to maintain all these Read/Write Operations so that will have layered architecture with fewer dependencies. Create a class library named ApplicationLayer to have the commands and queries based on requirements.

Create a folder named Commands and Query and inside that will add the classes based on the tables and operations. Below is the image of the Application Layer

Adding the Required Packages in Application Layer

As referenced before, the Application Layer will contain the CQRS Commands and Queries that are explicit for this application. 

Right off the bat, Add Reference to the Domain Project. 

Then, at that point, introduce the necessary bundles through Console.

Install-Package MediatR.Extensions.Microsoft.DependencyInjection

Adding the Dependency Injection

This is another variation that I have seen in numerous tremendous arrangements. Suppose you have around 100 interfaces and 100 executions. Do you add this load of 100 lines of code to the Startup.cs to enroll them in the holder? That would be crazy according to the viability perspective. To keep things clean, what we can do is, Create a DependencyInjection static Class for each layer of the arrangement and just add the comparing expected administrations to the comparing Class. 

Along these lines, we are decentralizing the code lines and keeping our Startup class slick and clean. Here is an augmentation technique over the IServiceCollection.

using MediatR;
using Microsoft.Extensions.DependencyInjection;
using System.Reflection;

namespace ApplicationLayer
{
    public static class DependencyInjection
    {
        #region Services Injection
        public static void AddApplication(this IServiceCollection services)
        {
            services.AddMediatR(Assembly.GetExecutingAssembly());
        }
        #endregion

    }
}

Basically, these classes would cover our CRUD Operations implementation by using the SQL Queries - Dapper

CreateOrUpdateOrderCommand.cs

using Dapper;
using MediatR;
using Microsoft.Extensions.Configuration;
using System.ComponentModel.DataAnnotations;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;

namespace ApplicationLayer.Commands.Orders
{
    public class CreateOrUpdateOrderCommand : IRequest<int>
    {
        public int OrderId { get; set; }
        [Required]
        public string OrderDetails { get; set; }
        public class CreateOrUpdateOrderCommandHandler : IRequestHandler<CreateOrUpdateOrderCommand, int>
        {
            private readonly IConfiguration configuration;
            public CreateOrUpdateOrderCommandHandler(IConfiguration configuration)
            {
                this.configuration = configuration;
            }
            public async Task<int> Handle(CreateOrUpdateOrderCommand command, CancellationToken cancellationToken)
            {
                if (command.OrderId > 0)
                {
                    var sql = "Update Orders set OrderDetails = @OrderDetails Where OrderId = @OrderId";
                    using (var connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection")))
                    {
                        connection.Open();
                        var result = await connection.ExecuteAsync(sql, command);
                        return result;
                    }
                }
                else
                {
                    var sql = "Insert into Orders (OrderDetails) VALUES (@OrderDetails)";
                    using (var connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection")))
                    {
                        connection.Open();
                        var result = await connection.ExecuteAsync(sql, new { ClientName = command.OrderDetails });
                        return result;
                    }
                }
            }
        }
    }
}

DeleteProductByIdCommand.cs

using Dapper;
using MediatR;
using Microsoft.Extensions.Configuration;
using System.ComponentModel.DataAnnotations;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;

namespace ApplicationLayer.Commands.Products
{
    public class DeleteProductByIdCommand : IRequest<int>
    {
        [Required]
        public int ProductId { get; set; }
        public class DeleteProductByIdCommandHandler : IRequestHandler<DeleteProductByIdCommand, int>
        {
            private readonly IConfiguration _configuration;
            public DeleteProductByIdCommandHandler(IConfiguration configuration)
            {
                _configuration = configuration;
            }
            public async Task<int> Handle(DeleteProductByIdCommand command, CancellationToken cancellationToken)
            {
                var sql = "DELETE FROM Products WHERE ProductId = @ProductId";
                using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
                {
                    connection.Open();
                    var result = await connection.ExecuteAsync(sql, new { ClientID = command.ProductId });
                    return result;
                }
            }
        }
    }
}

GetAllORdersQuery.cs

using Dapper;
using DomainLayer;
using MediatR;
using Microsoft.Extensions.Configuration;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace ApplicationLayer.Queries.Orders
{
    public class GetAllOrdersQuery : IRequest<IList<Order>>
    {
        public class GetAllOrderQueryHandler : IRequestHandler<GetAllOrdersQuery, IList<Order>>
        {
            private readonly IConfiguration _configuration;
            public GetAllOrderQueryHandler(IConfiguration configuration)
            {
                _configuration = configuration;
            }
            public async Task<IList<Order>> Handle(GetAllOrdersQuery query, CancellationToken cancellationToken)
            {
                var sql = "Select * from Orders";
                using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
                {
                    connection.Open();
                    var result = await connection.QueryAsync<Order>(sql);
                    return result.ToList();
                }
            }
        }
    }
}

GetAllProductsQuery.cs

using Dapper;
using DomainLayer;
using MediatR;
using Microsoft.Extensions.Configuration;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace ApplicationLayer.Queries.Products
{
    public class GetAllProductsQuery : IRequest<IList<Product>>
    {
        public class GetAllOrderQueryHandler : IRequestHandler<GetAllProductsQuery, IList<Product>>
        {
            private readonly IConfiguration _configuration;
            public GetAllOrderQueryHandler(IConfiguration configuration)
            {
                _configuration = configuration;
            }
            public async Task<IList<Product>> Handle(GetAllProductsQuery query, CancellationToken cancellationToken)
            {
                var sql = "Select * from Products";
                using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
                {
                    connection.Open();
                    var result = await connection.QueryAsync<Product>(sql);
                    return result.ToList();
                }
            }
        }
    }
}

Firstly, add a connection string to the appsettings.json found in the WebApi Project.

appsettings.json

 "ConnectionStrings": {
    "DefaultConnection": "Server=**********;Database=OrderDb;Trusted_Connection=True;"
  }

Furthermore, in the Startup class/ConfigureServices strategy for the WebApi Just Add the accompanying line. You would now be able to see the benefit of this sort of approach.

#region Dependency Injection
    services.AddApplication();
#endregion

Adding the MediatR Handler and Controllers

This is the last step of setting up Onion Architecture In ASP.NET Core. We should wire up a regulator to the Application Layer. 

Make a Base API Controller. This will be an Empty API Controller which will have a MediatR object. What is the point of this Base Controller? It is simply to diminish the lines of code. Say, we add another regulator. We won't need to re-characterize the MediatR object. Be that as it may, we will simply add the BaseAPI Controller as the base class. Get it? I will show it in execution. 

Add another Empty API Controller in the Controllers envelope and name it BaseController.

BaseController.cs

using MediatR;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.DependencyInjection;

namespace OnionArchitecture_CQRS_Dapper.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public abstract class BaseController : ControllerBase
    {
        #region Property
        private IMediator _mediator;
        #endregion
        protected IMediator Mediator => _mediator ??= HttpContext.RequestServices.GetService<IMediator>();
    }
}

OrderController.cs

using ApplicationLayer.Commands.Orders;
using ApplicationLayer.Queries.Orders;
using Microsoft.AspNetCore.Mvc;
using System.Threading.Tasks;

namespace OnionArchitecture_CQRS_Dapper.Controllers
{
    public class OrderController : BaseController
    {
        /// <summary>
        /// Save newly added order to database
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        [HttpPost(nameof(SaveOrderData))]
        public async Task<IActionResult> SaveOrderData(CreateOrUpdateOrderCommand command) => Ok(await Mediator.Send(command));
        /// <summary>
        /// Fetch all data from the Orders table.
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetAllOrders() => Ok(await Mediator.Send(new GetAllOrdersQuery()));
    }
}

ProductController.cs

using ApplicationLayer.Commands.Products;
using ApplicationLayer.Queries.Products;
using Microsoft.AspNetCore.Mvc;
using System.Threading.Tasks;

namespace OnionArchitecture_CQRS_Dapper.Controllers
{
    public class ProductController : BaseController
    {
        /// <summary>
        /// Delete Product from the Products Table
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        [HttpDelete(nameof(DeleteProduct))]
        public async Task<IActionResult> DeleteProduct(DeleteProductByIdCommand command) => Ok(await Mediator.Send(command));
        /// <summary>
        /// Fetch all Product Data from the Database
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetAllProducts() => Ok(await Mediator.Send(new GetAllProductsQuery()));
    }
}

Testing

Run the application and open up Swagger. We will do a simple test to ensure that our solution works.

Summary

I trust that this is very reasonable to all, particularly the ones beginning with the whole Solution Architecturing Stuff. Tell me your input and the extent of progress in this methodology.

Thank you for reading, please let me know your questions, thoughts, or feedback in the comments section. I appreciate your feedback and encouragement.

keep Learning ...!