This article will demonstrate how to get the data using Dapper and Repository Pattern in Web API and how to use Dependency Injection using the Unit of Work in Web API. I will show you how to implement the Generic Repository with Custom Repository for CRUD operations.
dapper
Here, we are using Dapper.NET for accessing the data through .NET data access provider. So, the first question that comes to mind is what actually a Dapper is and why we use it. So, let's move through this one by one, with examples.

What is Dapper?

It is an ORM [Object Relational Mapper] which basically is an open source and light-weight ORM for developers who prefer to work with ADO.NET technology. It is in top most ORMs which ensure the high performance. It works with both, static and dynamic, objects. It extends the IDbConnection 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-a-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 Layer and Business Logic Layer of an application. Repository directly communicates with data access layer [DAL] and gets the data and provides it to the business logic layer [BAL]. The main advantage to use repository pattern is to isolate the data access logic and business logic, so that if you make changes in any of this logic it can't effect directly on other logic. For more information, please go through the Repository Pattern Article.

Web API Project with Data Access Layer

Now, it is time for a practical example of how to implement Dapper and Repository Pattern with Web API Project. Create a solution named "DapperAndWebAPI" with a class library project named "DataAccess" and make the following folders for differnet activities.
Our project structure will be like the following image.
project structure
Inside the Infrastructure folder, create an interface named as IConnectionFactory which contains the GetConnection property that returns IDbConnection type connection. Implement IConnectionFactory interface with ConnectionFactory class. IDbConnection handles all the database connection related queries.
  1. public class ConnectionFactory : IConnectionFactory
  2. {
  3. private readonly string connectionString = ConfigurationManager.ConnectionStrings["DTAppCon"].ConnectionString;
  4. public IDbConnection GetConnection
  5. {
  6. get
  7. {
  8. var factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
  9. var conn = factory.CreateConnection();
  10. conn.ConnectionString = connectionString;
  11. conn.Open();
  12. return conn;
  13. }
  14. }
  15. }
Create two entity classes named "Blog" and "Category" inside the Entities folder which contains the characters for Blog and Category classes. All the sample classes can be found in Download Source.
  1. public class Blog
  2. {
  3. public int PostId { get; set; }
  4. public string PostTitle { get; set; }
  5. public string ShortPostContent { get; set; }
  6. public string FullPostContent { get; set; }
  7. public string MetaKeywords { get; set; }
  8. public string MetaDescription { get; set; }
  9. public DateTime PostAddedDate { get; set; }
  10. public DateTime PostUpdatedDate { get; set; }
  11. public bool IsCommented { get; set; }
  12. public bool IsShared { get; set; }
  13. public bool IsPrivate { get; set; }
  14. public int NumberOfViews { get; set; }
  15. public string PostUrl { get; set; }
  16. public virtual int CategoryId { get; set; }
  17. public virtual Category Categories { get; set; }
  19. }
Now, it is time to create repositories. So, first, we will create a GenericRepository which will include all the common methods which can be used for CRUD operations like Add, Delete, and Update etc.
Note: Best practice: We are working with Repository to create an Interface and implement it with class for removing complexity and making methods reusable.
  1. public interface IGenericRepository<TEntity> where TEntity : class
  2. {
  3. TEntity Get(int Id);
  4. IEnumerable<TEntity> GetAll();
  5. void Add(TEntity entity);
  6. void Delete(TEntity entity);
  7. void Update(TEntity entity);
  8. }
The following class "GenericRepository" is implemented in the IGenericRepository. I am not adding the implementation code here because I am using Custom Repository for this demonstration. You can implement it if you require.
  1. public class GenericRepository<TEntity> : IGenericRepository<TEntity> where TEntity : class
  2. {
  3. public void Add(TEntity entity)
  4. {
  5. throw new NotImplementedException();
  6. }
  8. public void Delete(TEntity entity)
  9. {
  10. throw new NotImplementedException();
  11. }
  13. public void Update(TEntity entity)
  14. {
  15. throw new NotImplementedException();
  16. }
  18. public TEntity Get(int Id)
  19. {
  20. throw new NotImplementedException();
  21. }
  23. public IEnumerable<TEntity> GetAll()
  24. {
  25. throw new NotImplementedException();
  26. }
  27. }

Implementation Dapper with Data Access Project

For adding Dapper with your project, just open Package Manager Console from the Tools menu and install Dapper using this command
Install-Package Dapper
It will also add and resolve the dependent dependencies for Dapper. At last, it will show success message for installation of Dapper.
Note: Don't forget to select Default Project name as DataAccess.
code

Custom Repository and Implementation

Create a new repository class named "BlogRepository" which implements GenericRepository 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 asynchronous process.
Here SqlMapper is Dapper object which provides variety of methods to perform different operations without writing too many codes.
  1. public class BlogRepository : GenericRepository<Blog>, IBlogRepository
  2. {
  3. IConnectionFactory _connectionFactory;
  5. public BlogRepository(IConnectionFactory connectionFactory)
  6. {
  7. _connectionFactory = connectionFactory;
  8. }
  9. public async Task<IEnumerable<Blog>> GetAllBlogByPageIndex(int pageIndex, int pageSize)
  10. {
  11. var query = "usp_GetAllBlogPostByPageIndex";
  12. var param = new DynamicParameters();
  13. param.Add("@PageIndex", pageIndex);
  14. param.Add("@PageSize", pageSize);
  15. var list = await SqlMapper.QueryAsync<Blog>(_connectionFactory.GetConnection, query, param, commandType: CommandType.StoredProcedure);
  16. return list;
  17. }
  18. }
Here I am using UnitOfWork class to make all required actions in one transaction. This class keeps track of everything when doing business transactions and at the end gathers all the information and submits it to the database in one transaction. As I have figured out when we are working with multiple repositories and have changes with these repositories and saved it using SaveChanges() method in one transaction, here UnitOfWork comes in the picture to do the same task.
Read a very good article by Asp.Net for Repository Pattern and UnitOfWork.
  1. public class UnitOfWork : IUnitOfWork
  2. {
  3. private readonly IBlogRepository _blogRepository;
  4. public UnitOfWork(IBlogRepository blogRepository)
  5. {
  6. _blogRepository = blogRepository;
  7. }
  9. void IUnitOfWork.Complete()
  10. {
  11. throw new NotImplementedException();
  12. }
  14. public IBlogRepository BlogRepository
  15. {
  16. get
  17. {
  18. return _blogRepository;
  19. }
  20. }
  21. }
Following is the class which handles all the business logic using UnitOfWork. When we work with enterprise applications with multiple components like Repository, Business logic, Services, Third Party Tools etc., then this service class plays a vital role in this.
  1. public class BlogService : IBlogService
  2. {
  3. IUnitOfWork _unitOfWork;
  4. public BlogService(IUnitOfWork unitOfWork)
  5. {
  6. _unitOfWork = unitOfWork;
  7. }
  8. public async Task<IEnumerable<Blog>> GetAllBlogByPageIndex(int pageIndex, int pageSize)
  9. {
  10. return await _unitOfWork.BlogRepository.GetAllBlogByPageIndex(pageIndex, pageSize);
  11. }
  12. }

Implement UnityResolver with Web API

First, I am going to add Unity package library with TestAPI project to run the UnityResolver class. To install Unity from Package Manager Console, just use "Install-Package Unity" command and press enter. It will resolve all the dependent dependencies and add the Unity package with TestAPI project.
command
Dependency Resolution with the Unity Container
Following class is a UnityResolver class which resolves all the dependencies. I have taken this class from this article. This class resolves the dependency for the class and returns the service instance.
As per Asp.Net
An IoC container is a software component that is responsible for managing dependencies. You register types with the container, and then use the container to create objects. The container automatically figures out the dependency relations. Many IoC containers also allow you to control things like object lifetime and scope.
  1. public class UnityResolver : IDependencyResolver
  2. {
  3. protected IUnityContainer container;
  5. public UnityResolver(IUnityContainer container)
  6. {
  7. if (container == null)
  8. {
  9. throw new ArgumentNullException("container");
  10. }
  11. this.container = container;
  12. }
  14. public object GetService(Type serviceType)
  15. {
  16. try
  17. {
  18. return container.Resolve(serviceType);
  19. }
  20. catch (ResolutionFailedException)
  21. {
  22. return null;
  23. }
  24. }
  26. public IEnumerable<object> GetServices(Type serviceType)
  27. {
  28. try
  29. {
  30. return container.ResolveAll(serviceType);
  31. }
  32. catch (ResolutionFailedException)
  33. {
  34. return new List<object>();
  35. }
  36. }
  38. public IDependencyScope BeginScope()
  39. {
  40. var child = container.CreateChildContainer();
  41. return new UnityResolver(child);
  42. }
  44. public void Dispose()
  45. {
  46. container.Dispose();
  47. }
  48. }
Register all the dependencies with UnityContainer in the WebAPIConfig file and provide UnityContainerinstance to DependencyResolver to resolve all the dependency at run time.
  1. public static class WebApiConfig
  2. {
  3. public static void Register(HttpConfiguration config)
  4. {
  5. var container = new UnityContainer();
  7. container.RegisterType<IBlogRepository, BlogRepository>();
  8. container.RegisterType<IConnectionFactory, ConnectionFactory>();
  9. container.RegisterType<IUnitOfWork, UnitOfWork>();
  10. container.RegisterType<IBlogService, BlogService>();
  11. config.DependencyResolver = new UnityResolver(container);
  14. // Web API configuration and services
  15. // Configure Web API to use only bearer token authentication.
  16. config.SuppressDefaultHostAuthentication();
  17. config.Filters.Add(new HostAuthenticationFilter(OAuthDefaults.AuthenticationType));
  19. // Web API routes
  20. config.MapHttpAttributeRoutes();
  22. config.Routes.MapHttpRoute(
  23. name: "DefaultApi",
  24. routeTemplate: "api/{controller}/{id}",
  25. defaults: new { id = RouteParameter.Optional }
  26. );
  27. }
  28. }
  29. }
Now everything is done, so it's time to implement a API controller as BlogController to get the data using service class, which will get the data from the database using Dapper and return it as IHttpActionResult.
  1. public class BlogController : ApiController
  2. {
  3. IBlogService _blogService;
  5. public BlogController()
  6. {
  8. }
  9. public BlogController(IBlogService blogService)
  10. {
  11. _blogService = blogService;
  12. }
  13. public async Task<IHttpActionResult> GetAllBlogPostsByPageIndex()
  14. {
  15. var resultData = await _blogService.GetAllBlogByPageIndex(3, 4);
  16. if (resultData == null)
  17. {
  18. return NotFound();
  19. }
  20. return Ok(resultData);
  21. }
  22. }
When we run the API project with the following URL as shown in below image, it will return the output as follows. Before running the project please check your database connection and database table, there should be some data available.
Please make sure as we mention database name in web.config. A table should be there as the following script.
  1. CREATE TABLE [dbo].[NextPosts](
  2. [PostId] [int] NOT NULL,
  3. [PostTitle] [nvarchar](500) NULL,
  4. [ShortPostContent] [ntext] NULL,
  5. [FullPostContent] [ntext] NULL,
  6. [MetaKeywords] [nvarchar](255) NULL,
  7. [MetaDescription] [nvarchar](500) NULL,
  8. [PostAddedDate] [smalldatetime] NOT NULL,
  9. [PostUpdatedDate] [smalldatetime] NOT NULL,
  10. [IsCommented] [bit] NOT NULL,
  11. [IsShared] [bit] NOT NULL,
  12. [IsPrivate] [bit] NOT NULL,
  13. [NumberOfViews] [int] NOT NULL,
  14. [PostUrl] [nvarchar](255) NULL,
  15. [CategoryId] [int] NOT NULL
  17. )
And to get the data from table, I have created a stored procedure as follows.
  1. CREATE PROC [dbo].[usp_GetAllBlogPostByPageIndex](@PageIndex Int, @PageSize INT)
  2. AS
  3. BEGIN
  4. SELECT * FROM NextPosts ORDER BY PostId OFFSET((@PageIndex-1)*@PageSize) ROWS
  5. FETCH NEXT @PageSize ROWS ONLY;
  6. END
  7. GO
And please make sure connection string will change as per our server, database and credentials for SQL server. Following is the sample example for connection string.
  1. <connectionStrings>
  2. <add name="DTAppCon" connectionString="server=My-computer;database=Test;UId=mukesh; Password=mukesh" providerName="System.Data.SqlClient" />
  3. </connectionStrings>
Now run the project as TestAPI and following output will be shown.
project

Conclusion

So, today we learned what Dapper is and why to use it. We also learned what is Repository Pattern and how dapper and repository pattern can be implemented with WebAPI using UnitOfWork.
I hope this post will help you. Please put your feedback in the comments section which helps me improve myself for the next post. If you have any doubts, please ask your doubts or queries in comments. And, if you like this post, please share it with your friends.