Dapper in ASP.NET Core with Repository Pattern – Detailed
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. 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 efficiently. 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 built 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 the name is ‘ProductManagementDB’.

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.

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

Here is the final schema for the 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 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.

I will explain the Architecture that we will follow. So basically, we will have 3 Main Layers.
- Core and Application – All the Interfaces and Domain Models live here.
- Infrastructure – In this scenario, Dapper will be present here, along with implementations of Repository and other interfaces
- WebApi – API Controllers to access the Repositories.
If you need more in-depth 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.

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 implementation 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 define the interfaces for Repositories here, and implement these interfaces at another layer that is associated with Data access, 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 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.
Add a Reference to the Core Project from the Application Project. The Application project always 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 the 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 to 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 the 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 access 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 into the Products Table.
Line 12 – Using the connection string from the appsettings.json, we open a new SQL Connection.
Line 15 – We pass the product object and the SQL command to the Execute Function.
Similarly, we wrote the other CRUD operations.
Line 29 – Function to get all Products
Line 35 – Here we use Dapper to Map all the products from the database to a list of Product Classes. 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; } }
Remember we have a few Interfaces and it’s implementation. 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 an 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 extension 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 into the Controller’s constructor. After that, we create separate 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 favorite 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.

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 endpoints of your API. Pretty neat, yeah?

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

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.

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

I will leave the remaining endpoints for you to test. With that let’s wind up this article.
support me
Thank you for visiting. If you like my content and code, support me by buying a couple of coffees so that I can find enough time to research & write new articles. Cheers!
Summary
In this detailed article, we have learned much more than just Dapper in ASP.NET Core. We were able to follow a clean architecture to organize the code, Implement a Repository pattern along with a Unit Of Work, Implement Swagger for efficient testing, and much more. You can find the completed source code here. 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!
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.
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
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.
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
Hey Arjunan , Thanks a lot for the feedback!
I have already written an article on Logging with Serilog – https://codewithmukesh.com/blog/serilog-in-aspnet-core-3-1/
Caching is in the list already, Will post soon.
Thanks and Regards
Please make a downloadable version of the article for people who don’t like reading online in real-time. Thanks.
Thanks for the suggestion. I will look into it.
Regards
Just download it or print it. It’s not hard.
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.
Have never heard of SQL+. Thanks for the tip! will give it a look.
Regards
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?
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.
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?
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.
Hi, For a clearer Implementation of UOW / Repository pattern, please follow this article – https://codewithmukesh.com/blog/repository-pattern-in-aspnet-core/
Regards
Do you have any sample for the approach which you have mentioned above?
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?
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
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?
how to handle common class and objects in the above layers.(i mean which layer used for common functionality)
Which layer can I use to add Identity an Jwt to my project
it gives the error while i try to add multiple repositories
like UserRepository and CompanyRepositor
Thanks Mukesh for sharing this post.
Good Article. Very helpful for me
Hi Mukesh,
Very nice and detailed article. Keep up the good work
Hi Mukesh,
Thanks for a wonderful article can you please make an article on how to apply paging using dapper same as you have done with entity framework.thanks
Hi @mukesh, is there a way to achieve the audit tracking similar to how we achieve that using EF core(Either via Generic repository or DbContext via tracking). I mean without writing the auditing inside each repository like ProductRepo, SalesRepo etc.
Hi, yes it’s totally possible at the DBContext level. I have written a guide on it. Please refer – https://codewithmukesh.com/blog/audit-trail-implementation-in-aspnet-core/
I keep getting an error: System.InvalidOperationException: Invalid operation. The connection is closed. can anyone help
Hello Mukesh,
I am not able to find the link to the source code on this page. Has it been removed since?
Please help.
Thank you!
Hi, can you please check now? Anyways, here is the link to the repository – https://github.com/iammukeshm/Dapper.WebApi
Thank you very much. Hugely appreciated.
On github, I went to your repository and searched for ‘Dapper.WebApi’.
Thanks, nonetheless!
Hey brow, awesome article but i was wondering just one point.
Why don’t you have a GenericRepository like in this other article? ====> https://codewithmukesh.com/blog/repository-pattern-in-aspnet-core/
is there no need of it?
It’s based on your project actually. There is no rule that you have to always use the Generic Repository Pattern everywhere. If the complexity of the project is too small, I would happily use this. In some cases, there might not be a real need to have a Repository pattern all together. For instance, in some of my recent projects, I completely disregarded the idea of writing my own repository class, and instead am using the Ardalis.Specification package.
Thanks
I working into project with full DAPPER, including CRUD operations, my expirience is not god, we having low performance due the relationated entities, and more cases.
The acrchitect always excuses “the dapper is fast and EF is slow” .
Now we have a challenge to trasnform this application in SAS and Multitenancy.
Mukesh.
Is it possible to get a variable declared in your controller e request it inside ProductRepository like a ViewData or TempData?
As far i know there is no ViewData, ViewBag or TempData in .NET CORE web api correct? I’ve been searching for days something like.
CONTROLLER
[HttpGet]
public async Task GetAll()
{
===> var Sample = “abc”;
var data = await unitOfWork.Products.GetAllAsync();
return Ok (data);
}
ProductRepository
public async Task<IReadOnlyList> GetAllAsync()
{
===> var RequestedSample = Sample;
var sql = “SELECT * FROM Products”;
using (var connection = new SqlConnection(configuration.GetConnectionString(“DefaultConnection”)))
{
connection.Open();
var result = await connection.QueryAsync(sql);
return result.ToList();
}
}