This article will demonstrate about how to get data using Dapper and Repository Pattern in Web API and how to use Dependency Injection using Unit of Work in Web API. I will show you how to implement Generic Repository with Custom Repository for CRUD operations.


Here we are using Dapper.Net for accessing the data through .net data access provide. So, first question comes in mind is that what is actually a Dapper and Why we use it. So, let move one by one with example.


What is Dapper

It is an ORM [Object Relational Mapper], basically a open source and lightweight ORM for developers who prefer to work with ADO.Net technology. It is in top most ORM which ensures the high performance. Main theme to develop it by Stack OverFlow team is the fastest database transaction. It works with both static and dynamic objects. It extends theIDbConnection interface to make connection and execute the database operations.

What is Web API

Web API is a framework that is used to make HTTP services. As you know, now days we are using mobiles, tablets, apps and different types of services, so Web API is a simple and reliable platform to create HTTP enabled services that can reach wide range of clients. It is used to create complete REST services. To know more just visit my article Who is the Winner Web API or WCF article on CsharpCorner.

What is Repository Pattern

Repository Pattern is used to create an abstraction layer between Data Access Layerr and Business Logic Layer of an application. Repository directly communicates with data access layer [DAL] and get the data and provides it to business logic layer [BAL]. The main advantage to use repository pattern to isolate the data access logic and business logic. So that if you make changes in any of this logic that can't effect directly on other logic. For more information, please gone through by Repository Pattern Article.


Web API Project with Data Access Layer

Now it is time to practical example how to implement Dapper and Repository Pattern with Web API Project. Create a solution name as "DapperAndWebAPI" with a class library project name as "DataAccess" and make following folders for differnet activities.

Entities: This will contain all the entity class files.

Infrastructure: It will include all data access required file like connection class.

Repositories: This will include Generic and Custom Repositories.

Services: It includes all the business logic related classes.

UnitOfWork: This is important folder for this demonstration which includes UnitOfWork Class for transaction.

Test API: It is a Web API project for creating HTTP enabled services.


Our project structure will be like as following image shown below.


Inside the Infrastructure folder create an interface named as IConnectionFactory which contains the GetConnection property which returns IDbConnection type connection.Implement IConnectionFactory interface with ConnectionFactory class.IDbConnection handles all the database connection related queries.

public class ConnectionFactory : IConnectionFactory
        private readonly string connectionString = ConfigurationManager.ConnectionStrings["DTAppCon"].ConnectionString;
        public IDbConnection GetConnection
                var factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
                var conn = factory.CreateConnection();
                conn.ConnectionString = connectionString;
                return conn;


Create two entity classes as named "Blog" and "Category" inside the Entities folder which contains the characters for Blog and Category classes as following. All the sample classes we can find from Download Source.

 public class Blog
        public int PostId { get; set; }
        public string PostTitle { get; set; }
        public string ShortPostContent { get; set; }
        public string FullPostContent { get; set; }
        public string MetaKeywords { get; set; }
        public string MetaDescription { get; set; }
        public DateTime PostAddedDate { get; set; }
        public DateTime PostUpdatedDate { get; set; }
        public bool IsCommented { get; set; }
        public bool IsShared { get; set; }
        public bool IsPrivate { get; set; }
        public int NumberOfViews { get; set; }       
        public string PostUrl { get; set; }
        public virtual int CategoryId { get; set; }
        public virtual Category Categories { get; set; }


Now it is time to create repositories, so first we will create GenericRepository which will include all the common methods which can be used for CRUD operations like Add, Delete and Update etc.

Note: Best practice when we are working with Repository to create Interface and implement it with class for remove complexity and make methods reusable.


public interface IGenericRepository<TEntity> where TEntity : class
        TEntity Get(int Id);
        IEnumerable<TEntity> GetAll();
        void Add(TEntity entity);
        void Delete(TEntity entity);
        void Update(TEntity entity);


Following class as name "GenericRepository" is implementing the IGenericRepository. I am not here adding the implementation code here because I am using Custom Repository for this demonstration. You can implement it if you require.

 public class GenericRepository<TEntity> : IGenericRepository<TEntity> where TEntity : class
        public void Add(TEntity entity)
            throw new NotImplementedException();

        public void Delete(TEntity entity)
            throw new NotImplementedException();

        public void Update(TEntity entity)
            throw new NotImplementedException();

        public TEntity Get(int Id)
            throw new NotImplementedException();

        public IEnumerable<TEntity> GetAll()
            throw new NotImplementedException();


Implementation Dapper with Data Access Project

For add Dapper is with your project, just open Package Manager Console from Tools menu and install Dapper using this command Install-Package Dapper. It will also add and resolve dependent dependencies for Dapper. At last it will show successfully installed message for Dapper.

Note: Don't forget to select Default Project name as DataAccess.


Custom Repository and Implementation

Create a new repository class name as "BlogRepository" which implementGenericRepository and IBlogRepository. For this demonstration, I am using Dependency Injection, so for creating the object, I am using constructor dependency injection. I have created a GetAllBlogByPageIndex method which will return list of blog using dapper asynchrony. I am here using very popular feature of C# as "Async" and "Await" for asyncronous process.

Here SqlMapper is Dapper object which provides variety of methods to perform different operation without writing too much of codes.