Dapper in ASP.NET Core with Repository Pattern – Detailed

by | Updated on Jul 17, 2020 | Coding

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 with 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 Repostitory Pattern and Unit of Work. Here is the source code of the entire implementation. Let’s get started.

What is Dapper?

Dapper is a simple Object Mapping Framework or a Micro-ORM that helps us to Map the Data from the Result of an SQL Query to a .NET Class effeciently. It would be as simple as executing a SQL Select Statement using the SQL Client object and returning the result as a Mapped Domain C# Class. It’s more like an Automapper for the SQL World. This powerful ORM was build by the folks at StackOverflow and is definitely faster at querying data when compared to the performance of Entity Framework. This is possible because Dapper works directly with the RAW SQL and hence the time-delay is quite less. This boosts the performance of Dapper.

Implementing Dapper in ASP.NET Core

We’ll build a simple ASP.NET Core 3.1 WebAPI following a Clean Architecture , Repository Pattern and Unit of Work. At the Data Access Layer, we will be using Dapper. I will be using Visual Studio 2019 Community Edition as my IDE , and MS-SQL / SQL Management Studio as my RDBMS.

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. I will add a new database and name is ‘ProductManagementDB’.

create new db

For this demonstration, I will create a simple Product Table with Columns like ID, Name, Description and so on. Set Id as the Primary Key.

set primary key

With Id as the selection, scroll down and Enable the ‘Is Identity’ Property. This makes your ID column auto-increment at every Insert Operation.

set identity

Here is the final schema for the Product Table.

product Table

Alternatively, you can Execute this Script to Create the Required Table as well.

CREATE TABLE [dbo].[Products](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Barcode] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](max) NOT NULL,
	[Rate] [decimal](18, 2) NOT NULL,
	[AddedOn] [datetime] NOT NULL,
	[ModifiedOn] [datetime] NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Getting Started with ASP.NET Core WebApi Project

With the Database and Table done, let’s proceed with creating a new ASP.NET Core 3.1 WebAPI Project. I am naming the Solution and Project as Dapper.WebApi.

Here is what we will build. It will be a real simple WebApi that Performs CRUD Operation using Dapper and Repository Pattern / Unit Of work. We will also follow some Clean Architecture, so that we learn some good practices along with the implementation.

new

I will explain the Architecture that we will follow. So basically, we will have 3 Main Layers.

  1. Core and Application – All the Interfaces and Domain Models live here.
  2. Infrastructure – In this scenatio, Dapper will be present here, along with implementations of Repository and other interfaces
  3. WebApi – API Controllers to access the Repositories.

If you need a more indepth knowledge about Clean Architecture in ASP.NET Core, I have written a highly detailed article on Onion Architecture in ASP.NET Core 3.1 using CQRS Pattern along with the complete source code.

Coming back to our implementation, Let’s now add a new .NET Core Library Project and Name it Dapper.Core.

core project 1

Here, Add a new Folder Entities and Create a new Class in this folder. Since we are developing a simple CRUD Operation Application for Products, Let’s name this class Product.

public class Product
{
    public int Id { get; set; }
    public string Name{ get; set; }
    public string Description { get; set; }
    public string Barcode { get; set; }
    public decimal Rate { get; set; }
    public DateTime AddedOn { get; set; }
    public DateTime ModifiedOn { get; set; }
}

This is everything you need in this Dapper.Core Project. Please note that since our application is simple, the content of the Core Library is also minimal. But in this way, we are also learning a simple implementaion of the Onion Architecture, yeah?

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 Dapper.Application. This is the Application Layer, that has the interfaces defined. So what will happen is, we deinf the interfaces for Repositories here, and implement these interfaces at another layer that is associated with Dataaccess, in our case, Dapper.

Create a New Folder Interfaces, and add 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);
}

As mentioned earlier, we will be using Repository Pattern along with 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.

Add a Reference to the Core Project from the Application Project. The Application project alwats depends on the Core Project Only. Nothing else.

Now that we have a generic Interface, let’s build the product Specific Repository Interface. Add a new interface and name it IProductRepository. We will Inherit the IGenericRepository Interface with T as Product.

public interface IProductRepository : IGenericRepository<Product>
{
}

Finally, add the last Interface, IUnitOfWork.

public interface IUnitOfWork
{
    IProductRepository  Products { get; }
}

That’s everything you need to add in the Dapper.Application Project.

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 Dapper.WebApi Project and add the following

  "ConnectionStrings": {
    "DefaultConnection": "Data Source=DESKTOP-QCM5AL0;Initial Catalog=ProductManagementDB;Integrated Security=True;MultipleActiveResultSets=True"
  }

Make sure that you add in your actual connection string.

With that out of the way, create another .NET Core Class Library Project, Dapper.Infrastructure. Here, we will add the implementation of the Interfaces.

Once the Project is created, Let’s install the required Packages to the Dapper.Infrastructure Project.

Install-Package Dapper
Install-Package Microsoft.Extensions.Configuration
Install-Package Microsoft.Extensions.DependencyInjection.Abstractions
Install-Package System.Data.SqlClient

Next, Add a new folder, Repositories in th Dapper.Infrastructure Project.

Add a reference to the Application Project from the Infrastructure Project. You can slowly get the idea of this entire architecture, right?

Let’s first implement the IProductRepository Interface. Create a new class, ProductRepository.cs

public class ProductRepository : IProductRepository
{
    private readonly IConfiguration configuration;
    public ProductRepository(IConfiguration configuration)
    {
        this.configuration = configuration;
    }
    public async Task<int> AddAsync(Product entity)
    {
        entity.AddedOn = DateTime.Now;
        var sql = "Insert into Products (Name,Description,Barcode,Rate,AddedOn) VALUES (@Name,@Description,@Barcode,@Rate,@AddedOn)";
        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 Products 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<Product>> GetAllAsync()
    {
        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();
        }
    }
    public async Task<Product> GetByIdAsync(int id)
    {
        var sql = "SELECT * FROM Products WHERE Id = @Id";
        using (var connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection")))
        {
            connection.Open();
            var result = await connection.QuerySingleOrDefaultAsync<Product>(sql, new { Id = id });
            return result;
        }
    }
    public async Task<int> UpdateAsync(Product entity)
    {
        entity.ModifiedOn = DateTime.Now;
        var sql = "UPDATE Products SET Name = @Name, Description = @Description, Barcode = @Barcode, Rate = @Rate, ModifiedOn = @ModifiedOn  WHERE Id = @Id";
        using (var connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection")))
        {
            connection.Open();
            var result = await connection.ExecuteAsync(sql, entity);
            return result;
        }
    }
}

Line 3 – We added the connection string to the appsettings.json, Remember? We need to accesss that string in another project, Dapper.Infrastructure. Hence we use the IConfiguration interface to make the connection string available throughout the application.
Line 6 – Injecting the IConfiguration to the constructor of the ProductRepository.

Line 11 – A straightforward SQL Command to Insert data to the Products Table.
Line 12 – Using the connection string from the appsettings.json, we open a new SQL Connection.
Lne 15 – We pass the product object and the SQL command to the Execute Function.

Similary we written the other CRUD Operations.

Line 29 – Function to get all Products
Line 35 – Here we use Dapper to Map all the products from database to a list of Product Class. Here we use the QueryAsync Method. We use this for the GetById Function as well.

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

public class UnitOfWork : IUnitOfWork
{
    public UnitOfWork(IProductRepository productRepository)
    {
        Products = productRepository;
    }
    public IProductRepository Products { get; } 
}

Remeber we have a few Interfaces and it;s implemention. Our next step is to register these interfaces with the implementations to the Service Container of ASP.NET Core. Add a new class in the Infrastructure Project and name it ServiceRegistration.

public static class ServiceRegistration
{
    public static void AddInfrastructure(this IServiceCollection services)
    {
        services.AddTransient<IProductRepository, ProductRepository>();
        services.AddTransient<IUnitOfWork, UnitOfWork>();
    }
}

This is more or less a extension method for the IServiceCollection. Here we add the interfaces with the Concrete Classes. Finally go to the Startup.cs/ConfigureServices method in the WebApi Project and let’s call the above made extenstion method.

services.AddInfrastructure();

Finally, let’s wire up the Repository to the Controller. Ideally, you may need a Service layer in between the Controller and the Repository Classes. But it would be an overkill for this implementation. Let’s keep things simple and proceed.

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

[Route("api/[controller]")]
[ApiController]
public class ProductController : ControllerBase
{
    private readonly IUnitOfWork unitOfWork;
    public ProductController(IUnitOfWork unitOfWork)
    {
        this.unitOfWork = unitOfWork;
    }
    [HttpGet]
    public async Task<IActionResult> GetAll()
    {
        var data = await unitOfWork.Products.GetAllAsync();
        return Ok (data);
    }
    [HttpGet("{id}")]
    public async Task<IActionResult> GetById(int id)
    {
        var data = await unitOfWork.Products.GetByIdAsync(id);
        if (data == null) return Ok();
        return Ok(data);
    }
    [HttpPost]
    public async Task<IActionResult> Add(Product product)
    {
        var data = await unitOfWork.Products.AddAsync(product);
        return Ok(data);
    }
    [HttpDelete]
    public async Task<IActionResult> Delete(int id)
    {
        var data = await unitOfWork.Products.DeleteAsync(id);
        return Ok(data);
    }
    [HttpPut]
    public async Task<IActionResult> Update(Product product)
    {
        var data = await unitOfWork.Products.UpdateAsync(product);
        return Ok(data);
    }
}

Here we will just define the IUnitOfWork and inject it to the Controller’s cosntructor. After that, we create seperate Action Methods for each CRUD operation and use the unit of work object. That’s it for the implementation. Let’s test it.

Testing with Swagger

Swagger is the favourite API testing tool for nearly every developer. It makes your life so easy. Let’s add swagger to our WebApi and test our implementation so far.

First, Install the required packages to the WebApi Project.

Install-Package Swashbuckle.AspNetCore
Install-Package Swashbuckle.AspNetCore.Swagger

Open Startup.cs/ConfigureServices method and add the following.

services.AddSwaggerGen(c =>
{
    c.IncludeXmlComments(string.Format(@"{0}\Dapper.WebApi.xml", System.AppDomain.CurrentDomain.BaseDirectory));
    c.SwaggerDoc("v1", new OpenApiInfo
    {
        Version = "v1",
        Title = "Dapper - WebApi",
    });
});

Next, in the Configure method, let’s add the Swager Middleware.

app.UseSwagger();
app.UseSwaggerUI(c =>
{
    c.SwaggerEndpoint("/swagger/v1/swagger.json", "Dapper.WebApi");
});

Finally, Open up your WebApi Project Properties , enable the XML Documentation file and give the same file path.

swagger

Now, build the application and Run it. Navigate to localhost:xxxx/swagger. This is your Swagger UI. Here you get to see all the available enpoints of your API. Pretty neat, yeah?

swaggerui

Let’s add a new Product. Click on the POST tab and enter in your Product Object. Click Execute. It’s that easy to add a new record through Swagger. Makes the testing process blazing fast.

post

I added a couple of Products. Now let’s see all the added products. Go to the GET tab and Click Execute. Here are all the Added Products.

get

If you want to get a product by id, Click the Get Tab ({id}) and enter in the required ID.

getbyid

I will leave the remaing endpoints for you to test. With that let’s wind up this article.

If you found this article helpful, consider supporting.

Buy me a coffeeBuy me a coffee

Summary

In this detailed article, we have learnt much more than just Dapper in ASP.NET Core. We were able to follow a clean architecture to organize the code, Implement Repository pattern along with Unit Of Work, Implement Swagger for effecient testing and much more.You can find the completed source code here. I hope you learnt 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! 😀

18 Comments

  1. Victor

    Dapper looks like a great alternative to EF. However, I feel one has to have a great grasp of writing plain SQL statements when using it. Also how will it work with lazyloading and eagerloading? Great article still Mukesh.

    Reply
    • Mukesh Murugan

      I really dont’t think Dapper can be an alternative of EFCore. Neither can EFCore replace Dapper. An Ideal use case would be to use both these ORMs in our applications. Dapper is much faster than EFCore while querying complex and huge joins. EFCore has it’s own set of features where it is the KING. Using both these ORMs side by side would take ASP.NET Core Applications to the next level!

      Thanks for the regular support! 🙂 Regards

      Reply
    • Fredrik

      We tend to use Dapper for parts where reading needs to be tuned since it’s faster than EF for pulling out data.
      EF is better for writes since it will handle all rollbacks and tracking for you, it’s much less hassle and boilerplate code needed. However, I tend to always go with EF for personal projects since I love the migrations and code first approach.

      Reply
  2. Arjunan

    Thanks for this amazing work. You added so many posts within 2 weeks. I need to go thru everything in this weekend. 😀 Could you please post articles related to Logging, Caching practices etc

    Reply
  3. Taofeek LASISI

    Please make a downloadable version of the article for people who don’t like reading online in real-time. Thanks.

    Reply
    • Mukesh Murugan

      Thanks for the suggestion. I will look into it.
      Regards

      Reply
    • David

      Just download it or print it. It’s not hard.

      Reply
  4. vincent

    If you like working with SQL, SQL+ is the best way to do what you are doing. It’s nearly twice as fast as Dapper and a lot less work.

    Reply
    • Mukesh Murugan

      Have never heard of SQL+. Thanks for the tip! will give it a look.
      Regards

      Reply
  5. Taofeek LASISI

    Looking at your implementation of the UnitOfWork, what happens when the number of entities increases? Are you going to pass all of them through the constructor in the class?

    Reply
    • Mukesh Murugan

      Hi, it actually depends on the scope of the project. If the project is somewhat a smaller one, Injecting the Repositories to the Constructor is a easier way to get around. But, when the Repository count keeps on increase, it is better to separate the Repository away from the UNIT of work and inject both the UOW and Repository in the constructor of the calling class.

      Thanks and Regards.

      Reply
      • Fredrik

        Isn’t the point of Unit of Work to keep track of all changes and then do a complete Save call with rollback management?
        Like if you have the repositories for products, orders and customers in the same UoW and need to modifications for all of them inside that scope? EF handles this for you, but with dapper I assume you would manually have to manage transactions and do rollbacks?

        Reply
        • Mikael

          Yes, that is correct. I thought that the article would give some interesting ideas on that matter. The UoW in the article is not really an UoW. It just seems to be a collection of repositories.

          Reply
  6. Duong Nam

    Hi. This is great. If I want to use EFCore use connect = context.Database.GetConnection(), then use Dapper connect.Query and use UnitOfWork for Dbcontext . Is this okay?

    Reply
    • Mukesh Murugan

      Hi, Thanks for the feedback.
      I wouldnt use it that way. But it depends on the developer and the exact scenario you are at. The basic idea is to decouple everything. With this approach, you are forcing Dapper to depend on EFCore, which isn’t actually needed. Try to make the connection centralized. But, yeah you could still use this if that’s what your application wants and doesn’t cause an issue later on down the road.

      Thanks and regards

      Reply
  7. Nick Stavrou

    I can understand that after the use of the ”using” connections will close. Isnt it better to ensure that you will close every connection in your repository methods or at least use the Dispose Pattern?

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Follow codewithmukesh

Support Me!

Buy me a coffeeBuy me a coffee

ASP.NET Core 3.x Hosting

Pin It on Pinterest