Dapper in ASP.NET Core with Repository Pattern

In this article, we will learn all about Dapper in ASP.NET Core and make a small implementation to understand how it works. Let’s not limit it just to Dapper. We will build an application that follows a very simple and clean architecture. In this implementation, we will try to under Repository Pattern and Unit Of Work as well. Everything put together, this article helps you to understand How Dapper can be used in an ASP.NET Core Application following Repository Pattern and Unit of Work.

Creating the MS-SQL Database and Table

Let’s create our Database and Related Table First. Open up SQL Management Studio and connect to your local SQL Server. Execute following sql commands senquentially.

command 1:

CREATE DATABASE TEST

This command creates new database named TEST. Then select TEST database from Aviables Database list and execute next sql command

command 2:

CREATE TABLE Stocks
(
    [Id] INT NOT NULL IDENTITY PRIMARY KEY,
    [Code] VARCHAR(10) NOT NULL,
    [Description] NVARCHAR(100) NOT NULL
)

I have created simple table because of I have tried to make simplest example for all flow.

Getting Started with ASP.NET Core WebApi Project

Let's create new project in Visual Studio as following. Let's name Stocks.WebApi to project name and Stocks to solution name.

Then we should add 3 new projects Stocks.Core, Stocks.Repository and Stocks.Service to our Stocks solution how I did in following images. In every time when we create new projects which is class library, it creates new class named class1 by default. Let's remove all class1 classes.

Now we can setup our interfaces and classes. Firstly, let's add new Stock class to our Stocks.Core project.

public class Stock
    {
        public int Id { get; set; }
        public string Code { get; set; }
        public string Description { get; set; }
    }

Remember, The Core layer is not going to depend on any other Project / Layer. This is very important while following Onion Architecture.

Next, Add another .NET Core Library Project and name it Stocks.Repository. This is the Repository Layer, that has the interfaces defined. So what will happen is, we define the interfaces for Repositories here, and implement these interfaces at another layer that is associated with Data access.

Create a new interface, IGenericRepository.

public interface IGenericRepository<T> where T : class
{
    Task<T> GetByIdAsync(int id);
    Task<IReadOnlyList<T>> GetAllAsync();
    Task<int> AddAsync(T entity);
    Task<int> UpdateAsync(T entity);
    Task<int> DeleteAsync(int id);
}

We will be using Repository Pattern along with the Unit Of work in our Implementation. In IGenericRepository, we are building a generic definition for the repository pattern. These include the most commonly used CRUD Operations like GetById, GetAll, Add, Update and Delete.

Now that we have a generic Interface, let’s build the Stock Specific Repository Interface. Add a new interface and name it IStockRepository. We will Inherit the IGenericRepository Interface with T as the Stock. Finally, add the last Interface, IUnitOfWork.

public interface IUnitOfWork
{
        IStockRepository Stocks { get; }
}

Now, we need to define the connection string of our database, so that the application can connect to our Database for performing CRUD operations. Open up the appsettings.json file in the Stocks.WebApi Project and add the following

"ConnectionStrings": {
    "DefaultConnection": "Data Source=.;Initial Catalog=TEST;Integrated Security=True;"
  }

Then let's call following console commands in Stocks.Service project. Select Tools menu in Visual Studio, select NuGet Package Manager and then select Package Manager Console. Make sure Stocks.Service is selected in Default project dropdown list. Then execute following commands sequentially.

command 1:

Install-Package Dapper

command 2:

Install-Package Microsoft.Extensions.Configuration

command 3:

Install-Package Microsoft.Extensions.DependencyInjection.Abstractions

command 4:

Install-Package System.Data.SqlClient

Then add StockService class to Stocks.Service project as following.

public class StockService : IStockRepository
    {
        private readonly IConfiguration configuration;
        public StockService(IConfiguration configuration)
        {
            this.configuration = configuration;
        }

        public async Task<int> AddAsync(Stock entity)
        {
            var sql = "Insert into Stocks (Code,Description) VALUES (@Code,@Description)";
            using (var connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection")))
            {
                connection.Open();
                var result = await connection.ExecuteAsync(sql, entity);
                return result;
            }
        }

        public async Task<int> DeleteAsync(int id)
        {
            var sql = "DELETE FROM Stocks WHERE Id = @Id";
            using (var connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection")))
            {
                connection.Open();
                var result = await connection.ExecuteAsync(sql, new { Id = id });
                return result;
            }
        }

        public async Task<IReadOnlyList<Stock>> GetAllAsync()
        {
            var sql = "SELECT * FROM Stocks";
            using (var connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection")))
            {
                connection.Open();
                var result = await connection.QueryAsync<Stock>(sql);
                return result.ToList();
            }
        }

        public async Task<Stock> GetByIdAsync(int id)
        {
            var sql = "SELECT * FROM Stocks WHERE Id = @Id";
            using (var connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection")))
            {
                connection.Open();
                var result = await connection.QuerySingleOrDefaultAsync<Stock>(sql, new { Id = id });
                return result;
            }
        }

        public async Task<int> UpdateAsync(Stock entity)
        {
            var sql = "UPDATE Stocks SET Code = @Code, Description = @Description WHERE Id = @Id";
            using (var connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection")))
            {
                connection.Open();
                var result = await connection.ExecuteAsync(sql, entity);
                return result;
            }
        }
    }

Next, Let’s implement the IUnitOfWork. Create a new class, UnitOfWork, and inherit from the interface IUnitOfWork.

public class UnitOfWork : IUnitOfWork
    {
        public UnitOfWork(IStockRepository stockRepository)
        {
            Stocks = stockRepository;
        }

        public IStockRepository Stocks { get; }
    }

Finally, go to the program.cs (Startup.cs/ConfigureServices method in project which's version unders NET 5) in the WebApi Project, and let’s call the above-made extension method.

builder.Services.AddTransient<IStockRepository, StockService>(); builder.Services.AddTransient<IUnitOfWork, UnitOfWork>();

In the WebApi Project, Add a new Controller under the Controllers folder. Let’s name it Stock Controller.

[Route("api/[controller]")]
    [ApiController]
    public class StockController : ControllerBase
    {
        private readonly IUnitOfWork unitOfWork;
        public StockController(IUnitOfWork unitOfWork)
        {
            this.unitOfWork = unitOfWork;
        }

        [HttpGet]
        public async Task<IActionResult> GetAll()
        {
            var data = await unitOfWork.Stocks.GetAllAsync();
            return Ok(data);
        }

        [HttpGet("{id}")]
        public async Task<IActionResult> GetById(int id)
        {
            var data = await unitOfWork.Stocks.GetByIdAsync(id);
            if (data == null) return Ok();
            return Ok(data);
        }

        [HttpPost]
        public async Task<IActionResult> Add(Stock stock)
        {
            var data = await unitOfWork.Stocks.AddAsync(stock);
            return Ok(data);
        }

        [HttpDelete]
        public async Task<IActionResult> Delete(int id)
        {
            var data = await unitOfWork.Stocks.DeleteAsync(id);
            return Ok(data);
        }

        [HttpPut]
        public async Task<IActionResult> Update(Stock stock)
        {
            var data = await unitOfWork.Stocks.UpdateAsync(stock);
            return Ok(data);
        }
    }

Now, we can test our projects with swagger which is default start page.

I hope you learned something new and detailed in this article. If you have any comments or suggestions, please leave them behind in the comments section below. Do not forget to share this article within your developer community. Thanks and Happy Coding!

Best regards...

Blog

Dapper in ASP.NET Core with Repository Pattern

Elchin Abraham

Join Our Newsletter. No Spam, Only the good stuff.

Related