Repository Design Pattern in C#

Back to: Design Patterns in C# With Real-Time Examples

Repository Design Pattern in C# with Examples

In this article, I am going to discuss the Repository Design Pattern in C# with an Example from the context of Entity Framework and ASP.NET MVC application. Please read our previous article where we discussed Dependency Injection Design Pattern in C# with real-time examples. The Repository Design Pattern in C# is one of the most used design patterns in the real-time application. At the end of this article, you will understand the following pointers in detail.

1. How does a modern data-driven application access data from a database?
2. What is the problem of the above approach?
3. What is Repository Pattern in C#?
4. Why do we need the Repository Pattern?
5. How to Implement Repository Pattern in C# using Entity Framework?

How does a modern data-driven application access data from a database?

Nowadays, most data-driven applications need to access the data residing in one or more other data sources. The easiest or simplest approach is to write all the data access-related code in the main application itself. For example, if you have an ASP.NET MVC controller let say EmployeeController. Then the Employee controller class may have many action methods that can perform the typical CRUD (Create, Read, Update and Delete) operations against the underlying database. Let’s further assume that you are using Entity Framework for doing all these database-related operations. In that case, your application would do something like as shown in the below diagram.

As you can see in the above diagram, the action methods of the Employee controller are directly interacting with the Entity Framework data context class and execute the queries to retrieve the data from the database. They also perform the INSERT, UPDATE, and DELETE operations using the data context and DbSet. The Entity Framework in turn talks with the underlying SQL Server database.

The drawback of the Above Implementation:

The above implementation works as expected. But it suffers from the drawback that the database access code (i.e. creating the data context object, writing the queries, manipulating the data, persisting the changes to the database, etc.) is embedded directly inside the controller action methods. This design or implementation can cause code duplication and further, we need to change the controller even if we do a small change in the data access logic.

For example, if the application is modifying the employee information from two controllers, then each controller will repeat the same data access code. And future modifications also need to be done at two places i.e. the two controllers where we write the same data access code.

What is the Repository Design Pattern in C#?

The Repository Design Pattern in C# Mediates between the domain and the data mapping layers using a collection-like interface for accessing the domain objects.

In other words, we can say that a Repository Design Pattern acts as a middleman or middle layer between the rest of the application and the data access logic. That means a repository pattern isolates all the data access code from the rest of the application. The advantage of doing so is that, if you need to do any changes then you need to do it in one place. Another benefit is that testing your controllers becomes easy because the testing framework need not run against the actual database access code. With repository design pattern, the previous diagram will change to the following diagram:

In the above design, now the Employee controller won’t talk with the Entity Framework data context class directly. Also, now there are no queries or any other data access code written in the action methods of the Employee Controller. All these operations (i.e. CRUD operations) are wrapped by the Employee Repository. The Employee Repository uses the Entity Framework data context class to perform the CRUD operations. As you can see from the above diagram, now the Employee repository has methods such as GetAll(), GetByID(), Insert(), Update() and Delete(). These methods are going to perform the Typical CRUD operations against the underlying database. The Employee controller uses those methods to perform the required database operations.

Why do we need the Repository Design Pattern in C#?

As we already discussed, nowadays, most data-driven applications need to access the data residing in one or more other data sources. Most of the time data sources will be a database. Again, these data-driven applications need to have a good and secure strategy for data access to perform the CRUD operations against the underlying database. One of the most important aspects of this strategy is the separation between the actual database, queries, and other data access logic from the rest of the application. In our example, we need to separate the data access logic from the Employee Controller. The Repository Design Pattern is one of the most popular design patterns to achieve such separation between the actual database, queries, and other data access logic from the rest of the application.

How to Implement Repository Design Pattern in C#

As we already discussed that the Repository Design Pattern in C# is used to create an abstraction layer between the data access layer and the business logic layer of the application. That abstraction layer is generally called the Repository Layer and it will directly communicate with the data access layer, gets the data, and provides it to the business logic layer. 

The main advantage to use the repository design pattern is to isolate the data access logic and business logic. So that if we do any changes in any of this logic, then that should affect other logic. So let us discuss the step-by-step procedure to implement the Repository Design Pattern in C#.

Step1: Create the Required Database tables

We are going to use the following Employee table to understand the Repository Design Pattern in C#.

Please use the below SQL script to create and populate the Employee table with the required data that we are going to use in our application.

-- Create EmployeeDB database
CREATE DATABASE EmployeeDB
GO

USE EmployeeDB
GO

-- Create Employee Table
CREATE TABLE Employee
(
  EmployeeID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(100),
  Gender VARCHAR(100),
  Salary INT,
  Dept VARCHAR(50)
)
GO

-- Populate some test data into Employee table
INSERT INTO Employee VALUES('Pranaya', 'Male', 10000, 'IT' )
INSERT INTO Employee VALUES('Anurag', 'Male', 15000, 'HR' )
INSERT INTO Employee VALUES('Priyanka', 'Female', 22000, 'HR' )
INSERT INTO Employee VALUES('Sambit', 'Male', 20000, 'IT' )
INSERT INTO Employee VALUES('Preety', 'Female', 25000, 'IT' )
INSERT INTO Employee VALUES('Hina', 'Female', 20000, 'HR' )
GO

SELECT * FROM Employee
GO

Step2: Create a new ASP.NET MVC application

Open Visual Studio and create a new project. To do so, Select File => New => Project option as shown in the below image.

After clicking on the “Project” link a new dialog will pop up. In that, we are going to select web templates from the left pane. From the middle pane, we need to select “ASP.NET Web Application“. Provide a meaningful name for the project such as “RepositoryUsingEFinMVC” and then click on the OK button as shown in the below image.

Once you click on the OK button, it will open a new dialog pop up with the Name “New ASP.NET Project” for selecting project Templates. Here, we are going to choose the MVC project template. Then we are going to choose the Authentication type for our application. For selecting the authentication, just click on the Change Authentication button, a new dialog will pop up with the name “Change Authentication” and from there we are going to choose No Authentication and then click on the OK button as shown below.

Once you click on the OK button, it will take some time to create the project for us. Once the project is created next we need to add ADO.NET Entity Data Model

Step3: Adding ADO.NET Entity Data Model

First, add a folder with the name DAL to our project. To do so, right-click on the Project => Add => New Folder and then rename the folder name as DAL.

Next, add ADO.NET Entity Data Model inside DAL Folder. To do so, right-click on the DAL folder then Add => New Item. Then select ADO.NET Entity Data Model, Provide a meaningful name such as “EmployeeDataModel” and finally click on the ADD button as shown in the below image.

From the Choose Model Content Screen choose “Generate From Database” and click on the Next button as shown below.

From the Connection Properties screen click on New Connection and provide the necessary details, select the database and click on OK as shown below.

In the next step provide a meaningful name “EmployeeDBContext” for the Connection String that is going to create in the Web.config file and click on the Next button as shown below.

From the Choose your version screen, choose Entity Framework 6.x and click on the Next button as shown below.

In the next step, from Choose your database objects screen, choose the Employee object, provide the namespace name and click on the Finish button as shown below.

Once you click on the Finish button, then it will create the Employee model as shown below.

The Folder structure for the EmployeeDataModel.edmx file is shown below.

Following is the auto-generated Employee entity generated by Entity Framework

namespace RepositoryUsingEFinMVC.DAL
{
    public partial class Employee
    {
        public int EmployeeID { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public Nullable<int> Salary { get; set; }
        public string Dept { get; set; }
    }
}

Following is auto-generated Context class i.e. EmployeeDBContext generated by Entity Framework

namespace RepositoryUsingEFinMVC.DAL
{
    public partial class EmployeeDBContext : DbContext
    {
        public EmployeeDBContext()
            : base("name=EmployeeDBContext")
        {
        }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
        public virtual DbSet<Employee> Employees { get; set; }
    }
}

Once we create the ADO.NET Entity Data model the next step is to create the Employee Repository for our application.

Step4: Creating Employee Repository

A repository typically does at least five operations as follows:

1. Selecting all records from a table
2. Selecting a single record based on its primary key
3. Insert
4. Update
5. Delete

This list, however, is not fixed. You may have more or fewer operations in the repository. For the sake of our example let’s decide that these five operations are needed from the Employee repository. To achieve this first we will create an Interface (i.e. IEmployeeRepository) with these five methods and then we will implement this interface in a class (i.e. EmployeeRepositpry)

First, add a folder with the name Repository to your project. To do so, right-click on the Project => Add => New Folder and then rename the folder name as Repository. Now add an Interface within the Repository folder with the name IEmployeeRepository.cs and then copy and paste the below code in it.

IEmployeeRepository.cs

using RepositoryUsingEFinMVC.DAL;
using System.Collections.Generic;
namespace RepositoryUsingEFinMVC.Repository
{
    public interface IEmployeeRepository
    {
        IEnumerable<Employee> GetAll();
        Employee GetById(int EmployeeID);
        void Insert(Employee employee);
        void Update(Employee employee);
        void Delete(int EmployeeID);
        void Save();
    }
}

Understanding the Responsibility of each method:

1. GetAll(): This method is used to return all the Employee entities as an enumerable collection (such as a generic List).
2. GetById(): This method accepts an integer parameter representing an Employee ID (EmployeeID is an integer column in the Employee table in the database) and returns a single Employee entity matching that Employee ID.
3. Insert(): This method accepts an Employee object as the parameter and adds that Employee object to the Employees DbSet.
4. Update(): This method accepts an Employee object as a parameter and marks that Employee object as a modified Employee in the DbSet.
5. Delete(): This method accepts an EmployeeID as a parameter and removes that Employee entity from the Employees DbSet.
6. Save(): This method saves changes to the EmployeeDB database.

Adding EmployeeRepository Class

Now, add EmployeeRepository class and implement IEmployeeRepository in it. To do so, add a class file within the Repository folder with the name EmployeeRepository.cs and copy and paste the below code.

using RepositoryUsingEFinMVC.DAL;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;

namespace RepositoryUsingEFinMVC.Repository
{
    public class EmployeeRepository : IEmployeeRepository
    {
        private readonly EmployeeDBContext _context;

        public EmployeeRepository()
        {
            _context = new EmployeeDBContext();
        }

        public EmployeeRepository(EmployeeDBContext context)
        {
            _context = context;
        }

        public IEnumerable<Employee> GetAll()
        {
            return _context.Employees.ToList();
        }

        public Employee GetById(int EmployeeID)
        {
            return _context.Employees.Find(EmployeeID);
        }

        public void Insert(Employee employee)
        {
            _context.Employees.Add(employee);
        }
        public void Update(Employee employee)
        {
            _context.Entry(employee).State = EntityState.Modified;
        }
        public void Delete(int EmployeeID)
        {
            Employee employee = _context.Employees.Find(EmployeeID);
            _context.Employees.Remove(employee);
        }

        public void Save()
        {
            _context.SaveChanges();
        }
        private bool disposed = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    _context.Dispose();
                }
            }
            this.disposed = true;
        }

        public void Dispose()
        {
            Dispose(true);

            GC.SuppressFinalize(this);
        }
    }
}

The above EmployeeRepository class implements all the five methods discussed above. Notice that it has two constructor definitions – one that takes no parameters and the one that takes the data context instance as the parameter. The second version will be useful when you wish to pass the context from outside (such as during testing or while using the Unit of Work pattern). We will discuss this in detail when we discuss Unit Of Work concepts in a later article.

Step5: Using Employee Repository in a Controller

We already created our Employee Repository. Let’s use this Employee Repository in a controller. Add a controller class inside the Controllers folder and name it EmployeeController. To do so, right-click on Controllers Folder and select Add => Controller. Then select MVC5 Controller – Empty as shown in the below image.

Once we click on Add button one popup will open for providing the Controller name as shown below.

Provide the controller name as EmployeeController and click on ADD button which will add the Employee Controller within the Controllers folder. Now, copy and paste the below code in Employee Controller.

using System.Web.Mvc;
using RepositoryUsingEFinMVC.DAL;
using RepositoryUsingEFinMVC.Repository;
namespace RepositoryUsingEFinMVC.Controllers
{
    public class EmployeeController : Controller
    {
        private IEmployeeRepository _employeeRepository;

        public EmployeeController()
        {
            _employeeRepository = new EmployeeRepository(new EmployeeDBContext());
        }
        public EmployeeController(IEmployeeRepository employeeRepository)
        {
            _employeeRepository = employeeRepository;
        }

        [HttpGet]
        public ActionResult Index()
        {
            var model = _employeeRepository.GetAll();
            return View(model);
        }

        [HttpGet]
        public ActionResult AddEmployee()
        {
            return View();
        }

        [HttpPost]
        public ActionResult AddEmployee(Employee model)
        {
            if (ModelState.IsValid)
            {
                _employeeRepository.Insert(model);
                _employeeRepository.Save();
                return RedirectToAction("Index", "Employee");
            }
            return View();
        }

        [HttpGet]
        public ActionResult EditEmployee(int EmployeeId)
        {
            Employee model = _employeeRepository.GetById(EmployeeId);
            return View(model);
        }

        [HttpPost]
        public ActionResult EditEmployee(Employee model)
        {
            if (ModelState.IsValid)
            {
                _employeeRepository.Update(model);
                _employeeRepository.Save();
                return RedirectToAction("Index", "Employee");
            }
            else
            {
                return View(model);
            }
        }

        [HttpGet]
        public ActionResult DeleteEmployee(int EmployeeId)
        {
            Employee model = _employeeRepository.GetById(EmployeeId);
            return View(model);
        }

        [HttpPost]
        public ActionResult Delete(int EmployeeID)
        {
            _employeeRepository.Delete(EmployeeID);
            _employeeRepository.Save();
            return RedirectToAction("Index", "Employee");
        }
    }
}

The Employee controller has two versions of the constructor and seven action methods. Notice that there is a private variable of type IEmployeeRepository at the class level. The parameterless constructor sets this variable to an instance of the EmployeeRepository. The other version of the constructor accepts an implementation of the IEmployeeRepository from the external world and sets it to the private variable. This second version is useful during testing where you will supply a mock implementation of the Employee repository from the test project.

The seven methods defined by the Employee controller are as follows:

1. Index(): This action method displays an Index view and passes a List of Employee entities as its model.
2. AddEmployee(): Displays the Add employee view.
3. AddEmployee(Employee model): Add Employee view submits data to this method. It receives the data as an Employee instance and then inserts an Employee using the repository.
4. EditEmployee(int EmployeeId): Displays the Edit Employee view. It accepts an Employee ID as the parameter and populates the Edit Employee view with the data of the existing Employee whose ID it accepts as the parameter.
5. EditEmployee(Employee model): Edit Employee view submits the data to this method. It receives the data as an Employee instance and then updates the Employee using the repository.
6. DeleteEmployee(int EmployeeId): Displays the Delete Employee view.
7. Delete(int EmployeeId): Delete Employee view submits the data to this action method. The action then deletes the Employee using the repository.

Step6: Adding Views:

Index.cshtml View

@model IEnumerable<RepositoryUsingEFinMVC.DAL.Employee>
@{
    ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
    @Html.ActionLink("Add Employee", "AddEmployee")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Gender)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Salary)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Dept)
        </th>
        <th></th>
    </tr>

    @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.Name)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Gender)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Salary)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Dept)
            </td>
            <td>
                @Html.ActionLink("Edit", "EditEmployee", new { EmployeeId = item.EmployeeID }) |
                @Html.ActionLink("Delete", "DeleteEmployee", new { EmployeeId = item.EmployeeID })
            </td>
        </tr>
    }
</table>

AddEmployee.cshtml View

@model RepositoryUsingEFinMVC.DAL.Employee

@{
    ViewBag.Title = "AddEmployee";
}
@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>Add Employee</h4>
        <hr />
        @Html.ValidationSummary(true)

        <div class="form-group">
            @Html.LabelFor(model => model.Name, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Name)
                @Html.ValidationMessageFor(model => model.Name)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Gender, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Gender)
                @Html.ValidationMessageFor(model => model.Gender)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Salary, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Salary)
                @Html.ValidationMessageFor(model => model.Salary)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Dept, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Dept)
                @Html.ValidationMessageFor(model => model.Dept)
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to Employee List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")

EditEmployee.cshtml View

@model RepositoryUsingEFinMVC.DAL.Employee
@{
    ViewBag.Title = "EditEmployee";
}
@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>Edit Employee</h4>
        <hr />
        @Html.ValidationSummary(true)
        @Html.HiddenFor(model => model.EmployeeID)

        <div class="form-group">
            @Html.LabelFor(model => model.Name, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Name)
                @Html.ValidationMessageFor(model => model.Name)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Gender, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Gender)
                @Html.ValidationMessageFor(model => model.Gender)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Salary, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Salary)
                @Html.ValidationMessageFor(model => model.Salary)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Dept, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Dept)
                @Html.ValidationMessageFor(model => model.Dept)
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Update" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to Employee List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

DeleteEmployee.cshtml View

@model RepositoryUsingEFinMVC.DAL.Employee
@{
    ViewBag.Title = "DeleteEmployee";
}
<h3>Are you sure you want to delete this?</h3>
<div>
    @using (Html.BeginForm("Delete", "Employee", FormMethod.Post))
    {
        @Html.HiddenFor(e => e.EmployeeID)
        <h4>Delete Employee</h4>
        <hr />
        <dl class="dl-horizontal">
            <dt>
                @Html.DisplayNameFor(model => model.Name)
            </dt>

            <dd>
                @Html.DisplayFor(model => model.Name)
            </dd>

            <dt>
                @Html.DisplayNameFor(model => model.Gender)
            </dt>

            <dd>
                @Html.DisplayFor(model => model.Gender)
            </dd>

            <dt>
                @Html.DisplayNameFor(model => model.Salary)
            </dt>

            <dd>
                @Html.DisplayFor(model => model.Salary)
            </dd>

            <dt>
                @Html.DisplayNameFor(model => model.Dept)
            </dt>

            <dd>
                @Html.DisplayFor(model => model.Dept)
            </dd>
        </dl>
        <div class="form-actions no-color">
            <input type="submit" value="Delete" class="btn btn-default" /> |
            @Html.ActionLink("Back to Employee List", "Index")
        </div>
    }
</div>

Once you created the four views now let’s change the default route to Employee Controller and Index action method in the RouteConfig class as shown below.

namespace RepositoryUsingEFinMVC
{
    public class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "Employee", action = "Index", id = UrlParameter.Optional }
            );
        }
    }
}

Now run the application and perform the CRUD operation and see everything is working as expected. If you want to learn GoF 23 Design Patterns, then please read our Design Patterns in C# course by visiting the following link.

Design Patterns in C# with Real-time Examples.

In the next article, I am going to discuss how to implement the Generic Repository Pattern in ASP.NET MVC application using Entity Framework. Here, in this article, I try to explain the basics of the Repository Design Pattern in C# with Examples. I hope you understood the basics of the Repository Design Pattern in C#. I hope you understood the need and use of the basic repository pattern in C# with Examples.