ASP.NET MVC 5: Using a Simple Repository Pattern for Performing Database Operations

Recently a client of mine asked for a solution to isolate the direct dependency of the Data Access Layer with the rest of the layers of the application. In this article we will discuss one of the concepts I suggested to him for an ASP.NET MVC application.

Typically in multi-layer applications, the business logic or Domain logic retrieves data from various sources, e.g. Database, Web/WCF services, etc. In this type of direct access, the business logic is tightly coupled with the data access code which may result in code maintenance issues; because if the Database or Service changes, then the dependency code also needs to be changed. So the point here is that how to minimize this maintenance effort?

• Implementing an isolation layer for the data access which can be effectively tested.
• Implement consistent data access logic irrespective of the data source locations.
• Implementing maintainable code by separating business logic from data access logic.
• Implementing an easy domain model development for complex business logic.

The following diagram gives a general overview of the repository pattern:

The repository layer isolates Business layer from the Data Access Layer. The Repository contains Data Mapper entity. This entity can be used as a model entity for providing schema of the data for performing CRUD operations, by using the CRUD operations defined in the repository. The Business layer performs Data Access using repository layer. If the application makes use of WCF/Web services for data access, then repository can be used to isolate these service references from the Business layer.

Repository in case of ASP.NET MVC

In case of ASP.NET MVC, we uses the Model layer for interacting with Data Access and Controller talks to the Model for performing the Data Access operations. In MVC, it is important to think of the repository pattern so that if the Model layers needs some breaking changes, then it should have minimum or no maintenance impact on the controller layer. In MVC, finally the Controller is responsible for exposing data to the View or accessing Http posted data from View and send it further to the Model. So here, if the Controller has tight-coupling dependency on the Model, then any change in the Model will impact the Controller and hence sometimes the View too. In this case, we can use the Repository pattern as shown in the following diagram:

The above diagram explains the repository process flow. When using EntityFramework in MVC, we have Model Entities available which can be used by all layers of the application. Once the Web Server passed the request to Controller for Actions (e.g. Read/Write), the Controller calls repository and passes the Model Entity to it. It is recommended that the Controller should use Dependency Injection to instantiate the Repository and call methods from it. The Repository further passes the Model Entity to Data Access implemented using Entity Framework for performing operation requested by the Controller. Here Repository must also use Dependency Injection to instantiate the DbContext of Entity Framework. To implement Dependency Injection, any DI framework can be used e.g. Unity, Ninject, etc.

Implementing Repository in ASP.NET MVC

Step 1: Open Visual Studio and create a new Empty MVC application. Name this as MVC_Repository. In this application, add a new Sql Server database of name Application.mdf. In this database add new table called EmployeeInfo as shown in the following schema:

CREATE TABLE [dbo].[EmployeeInfo] (
    [EmpNo]       INT          IDENTITY (1, 1) NOT NULL,
    [EmpName]     VARCHAR (50) NOT NULL,
    [Salary]      INT          NOT NULL,
    [DeptName]    VARCHAR (50) NOT NULL,
    [Designation] VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([EmpNo] ASC)
);

Insert the following Test Data in this table as shown in the following statement

INSERT INTO [dbo].[EmployeeInfo] ([EmpNo], [EmpName], [Salary], [DeptName], [Designation]) VALUES (1, N'MS', 45000, N'Tech', N'Manager')
INSERT INTO [dbo].[EmployeeInfo] ([EmpNo], [EmpName], [Salary], [DeptName], [Designation]) VALUES (2, N'LS', 55000, N'HR', N'Manager')
INSERT INTO [dbo].[EmployeeInfo] ([EmpNo], [EmpName], [Salary], [DeptName], [Designation]) VALUES (3, N'TS', 85000, N'Store', N'Manager')

Step 2: In the Models folder, add a new ADO.NET Entity Data Model. This will start the wizard, in this wizard select Application.mdf database and the EmployeeInfo table. After completion of the wizard, we will generate code using EntityFramework. This will generate Entity Model class of name EmployeeInfo as shown in the following code:

public partial class EmployeeInfo
{
    public int EmpNo { get; set; }
    public string EmpName { get; set; }
    public int Salary { get; set; }
    public string DeptName { get; set; }
    public string Designation { get; set; }
}

The DbContext class gets generated as shown in the following code:

public partial class ApplicationEntities : DbContext
{
    public ApplicationEntities()
        : base("name=ApplicationEntities")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    public virtual DbSet<EmployeeInfo> EmployeeInfoes { get; set; }
}

This completes the Data Access layer creation. The DbContext class manages the Database table mapping and data read/write operations.

Step 3: Since we will be using Dependency Injection for instantiating the Repository object and DbContext object, we need to add a Unity Container in this project using NuGet Package manager as shown in the following image:

This will add UnityConfig.cs file in the App_Start folder of the project. We will visit this file in the forthcoming steps.

Step 4: To isolate the Data Access from the MVC controllers, we need to now create Repository. To implement this in the project, add a folder of the name Repositories. In this folder, add a new interface file of the name IRepository.cs. In this file, add the following code:

using System.Collections.Generic;

namespace MVC_Repository.Repositories
{
    //The Generic Interface Repository for Performing Read/Add/Delete operations
    public interface IRepository<TEnt, in TPk> where TEnt :class
    {
        IEnumerable<TEnt> Get();
        TEnt Get(TPk id);
        void Add(TEnt entity);
        void Remove(TEnt entity);
    }
}

The above code is the generic interface which has TEny and TPk generic type. TEnt is set with the constraints as class. This interface defines methods for Read/Write operations. We are implementing this interface as generic interface so that we can use it for all model entities for performing CRUD operations.

In the folder, add a new class file of name EmployeeInfoRepository.cs. Add the following code in it:

using Microsoft.Practices.Unity;
using MVC_Repository.Models;
using System.Collections.Generic;
using System.Linq;

namespace MVC_Repository.Repositories
{
    //The EmployeeInfo Repository Class. This is used to 
    //Isolate the EntityFramework based Data Access Layer from
    //the MVC Controller class
    public class EmployeeInfoRepository : IRepository<EmployeeInfo,int>
    {
        [Dependency]
        public ApplicationEntities context{get;set;}

        public IEnumerable<EmployeeInfo> Get()
        {
            return context.EmployeeInfoes.ToList();
        }

        public EmployeeInfo Get(int id)
        {
            return context.EmployeeInfoes.Find(id);
        }

        public void Add(EmployeeInfo entity)
        {
            context.EmployeeInfoes.Add(entity);
            context.SaveChanges();
        }

        public void Remove(EmployeeInfo entity)
        {
            var obj = context.EmployeeInfoes.Find(entity.EmpNo);
            context.EmployeeInfoes.Remove(obj);
            context.SaveChanges();
        }
    }
}

The above EmployeeInfoRepository class implements IRepository interface by passing TEnt value as EmployeeInfo, and TPk value as int, so that we can implement Read/Write operations for EmployeeInfo model entity and use the int type for id parameter to read Employee based on id.

The most important part in the above code is the following:

[Dependency]
public ApplicationEntities context{get;set;}

Using the [Dependency] attribute means that the context property of the type ApplicationEntities is target for Dependency injection in the current type. When the Unity Container instantiates, the property types marked with [Dependency] is instantiated and injected.

Step 5: Open the UnityConfig.cs from the App_Start folder, and register the EmployeeInfoRepository in the UnityContainer as shown in the following code: (highlighted)

public static void RegisterComponents()
{
    var container = new UnityContainer();
    //Register the Repository in the Unity Container
    container.RegisterType<IRepository<EmployeeInfo,int>,EmployeeInfoRepository>();
    DependencyResolver.SetResolver(new UnityDependencyResolver(container));
}

Open the Global.asax and add the following line in Application_Start

UnityConfig.RegisterComponents(); //Method call to Complete the Component Registration

Step 6: In the Controllers folder add a new Empty MVC controller of the name EmployeeInfoController.cs. Add the following code in it:

using System.Web.Mvc;

using MVC_Repository.Models;
using MVC_Repository.Repositories;

namespace MVC_Repository.Controllers
{
    public class EmployeeInfoController : Controller
    {
        //Property of the type IRepository <TEnt, in TPk>
        private IRepository<EmployeeInfo, int> _repository;

        //The Dependency Injection of the IRepository<TEnt, in TPk>
        public EmployeeInfoController(IRepository<EmployeeInfo, int> repo)
        {
            _repository = repo;
        }

        // GET: EmployeeInfo
        public ActionResult Index()
        {
            var Emps = _repository.Get();

            return View(Emps);
        }

        public ActionResult Create()
        {
            var Emp = new EmployeeInfo();
            return View(Emp);
        }


        [HttpPost]
        public ActionResult Create(EmployeeInfo Emp)
        {
            _repository.Add(Emp);
            return RedirectToAction("Index");
        }

        public ActionResult Delete(int id)
        {
            var Emp = _repository.Get(id);
            return View(Emp);
        }

        [HttpPost]
        public ActionResult Delete(int id,EmployeeInfo emp)
        {
            var Emp = _repository.Get(id);
            _repository.Remove(Emp);
            return RedirectToAction("Index");
        }
    }
}

The above controller class uses the EmployeeInfoRepository using Dependency Injection with constructor injection. Using this implementation of Repository, we have implemented isolation between the Controller class and the Data Access using entity Framework. All action method from the controller uses an instance of the Repository for performing Read/Write operations. Generate Views from Index, Create and Delete action methods by using Model class as EmployeeInfo as shown in the following image

Run and Test the Index, Create and Delete operations.

Conclusion: The Repository pattern decouples your application and shield it from changes by creating a boundary between your data access code and the other parts of your application.

Download the entire source code of this article (Github)

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

Mahesh Sabnis is a DotNetCurry author and a Microsoft MVP having over two decades of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions), and Front-end technologies like Angular and React. Follow him on twitter @maheshdotnet or connect with him on LinkedIn