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

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 ...!