CRUD using the Repository Pattern in MVC

This article introduces the repository pattern in an MVC application. We are developing an application for a Book entity on which we can perform Create, Read, Update, and Delete operations. To keep the article simple and make it easy to understand the repository pattern, we use a single book entity in the application.

Overview of Repository Pattern

The repository pattern is intended to create an abstraction layer between the data access layer and the business logic layer of an application. It is a data access pattern that prompts a more loosely coupled approach to data access. We create the data access logic in a separate class, or set of classes called a repository with the responsibility of persisting the application's business model.

In this article, we will implement a "One-per business model" approach to design a repository in which there is a repository class for each entity type. For the Book entity type, we'll create a repository interface and a repository class. When we instantiate the repository in our controller, we'll use the interface so that the controller will accept a reference to any object that implements the repository interface. When the controller runs under a web server, it receives a repository that works with the Entity Framework.

MVC controllers interact with repositories to load and persist an application business model. By taking advantage of dependency injection (DI), repositories can be injected into a controller's constructor. the following diagram shows the relationship between the repository and Entity Framework data context, in which MVC controllers interact with the repository rather than directly with Entity Framework.

Overview of Entity Framework

The ADO.NET Entity Framework is an Object Relational Mapper (ORM) included with the .NET framework. It basically generates business objects and entities according to the database tables. It provides basic CRUD operations, easily managing relationships among entities with the ability to have an inheritance relationship among entities.

When using EF, we interact with an entity model instead of the application's relational database model. This abstraction allows us to focus on business behavior and the relationships among entities. We use the Entity Framework data context to perform queries. When one of the CRUD operations is invoked, the Entity Framework will generate the necessary SQL to perform the operation.

Working with Data in Entity Framework

The ADO.NET Entity Framework allows developers to choose any one approach among three possible approaches: Database First, Model First, and Code First.

1. Database First: It is a more data-centric design that is based on an existing database. The Entity Framework is able to generate a business model based on the tables and columns in a relational database. The information about our database structure (store schema), our data model (conceptual model), and the mapping among them is stored in XML in a .edmx file.
   
2. Model First: In this approach, we don't have an existing database and the Entity Framework offers a designer that can create a conceptual data model. It also uses a .edmx file to store the model and mapping information. When the model has been created then the Entity Framework designer can generate the database schema that can be used to create the database.
   
3. Code First: Whether you have an existing database or not, you can code your own classes and properties that correspond to tables and columns and use them with the Entity Framework without a .edmx file. In this approach, the Entity Framework does not leverage any kind of configuration file (.edmx file) to store the database schema, because the mapping API uses these conventions to generate the database schema dynamically at runtime.
   

Currently, the Entity Framework Code First approach does not support mapping to Stored Procedures. The ExecuteSqlCommand() and SqlQuery() methods can be used to execute Stored Procedures.

In this article, we use the Code First approach of Entity Framework to develop a data access layer in an MVC application. The driving force behind the Code First approach is the ability to use POCO (Plain Old CLR Objects) classes. Code First uses a set of conventions to map POCO classes but that can be changed using code first data annotation.

1. Primary Key is based on property name ID or ClassNameId. In other words, suppose we have a Book entity that has property Id or BookId that will be the primary key in the generated Books table.
2. Table names are defined using the pluralized form of the entity class name. In other words, suppose we have an entity Book, and that entity would generate a table in the database, that table name will be Books.
3. The column names of the table are derived from the property names of the entity. Column names can be changed using Code First data annotation.
4. The default connection string matches the name of the DataContext class.

Code First data annotation

The Entity Framework includes several data annotation attributes we can use to control how the framework handles mapping entities. Here we have a basic data annotation that will be used for the Book entity.

Sr.No	Property	Description
1.	Table	Used to define the table name to use for an entity.
2.	Column	The database table column name, ordinal position, and data type to map the property to
3.	Key	One or more properties are used to uniquely identify an entity.
4.	Required	Marks a property as being required (non-nullable).
5.	MaxLength	The maximum length for the property (column).
6.	MinLength	The minimum length for the property (column).
7.	StringLength	Define the minimum and maximum length of a field.

An MVC Application Using the Repository Pattern

We now have sufficient theory. Let's now start the real fun of implementing it in an MVC application. We create an MVC application (BookStore Application) using Visual Studio 2010, MVC 4, and Entity Framework 5.

Step 1. From the Visual Studio Start Page, click "New Project".

Step 2. Choose "MVC 4 Project Template".

We get the New Project window in which we choose "MVC 4 Project Template" and provide an appropriate name to both the Project and Solution then click on the "Ok" button.

We then get another window to choose an MVC application template. We choose "Internet Application" from the templates and "Razor" as the view engine.

Click on "OK" and our default application is ready.

We are developing an MVC application using MVC 4 with a razor view engine so our default MVC internet application includes an EntityFramework reference so there is no need to add a reference or install a Nuget package for Entity Framework.

Step 3. Create Model.

We create a model for the Book under the Models folder. This model is actually a class that uses an entity and entity set. We create the Book class under Models and implement Code First data annotation for the database table that will be created by it.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace BookStore.Models
{
    public class Book
    {
        [Key]
        public int Id { get; set; }

        [Required]
        [MaxLength(30)]
        public string Title { get; set; }

        public string Authors { get; set; }

        [Column("Year")]
        [Display(Name = "Publish Year")]
        public string PublishYear { get; set; }

        [Column("Price")]
        [Display(Name = "Price")]
        public decimal BasePrice { get; set; }
    }
}

Our Book model is ready and now we proceed to the Data Context.

Step 4. Create Data Context class.

The ADO.NET Entity Framework Code First data access approach requires us to create a data access context class that inherits from the DbContext class. This class must contain properties for each of the entities in the domain model.

Here is an Entity Framework Code First data context that contains one entity, Book. We create this context class (BookContext) under the new folder DAL. Below is the definition of the data context class that has a constructor to pass a connection string that is defined in the web. config file. By default, the connection string name is the same name as the data context class but we can use a different name for the connection string so that all the data contexts can use a single connection string.

using System.Data.Entity;
using BookStore.Models;

namespace BookStore.DAL
{
    public class BookContext : DbContext
    {
        public BookContext()
            : base("name=BookStoreConnectionString")
        {
        }

        public DbSet<Book> Books { get; set; }
    }
}

The connection string in the web. config file is.

<connectionStrings>
  <add name="BookStoreConnectionString" 
       connectionString="Data Source=sandeepss-PC;Initial Catalog=BookStore;User ID=shekhawat;Password=******" 
       providerName="System.Data.SqlClient" />
</connectionStrings>

Step 5. Create Repository.

In the DAL folder create an IBookRepository interface that has the filename IBookRepository.cs. This interface code declares a typical set of CRUD methods, including two read methods; one that returns all Book entity sets, and one that finds a single Book entity by ID.

using System;
using System.Collections.Generic;
using BookStore.Models;

namespace BookStore.DAL
{
    public interface IBookRepository : IDisposable
    {
        IEnumerable<Book> GetBooks();
        Book GetBookByID(int bookId);
        void InsertBook(Book book);
        void DeleteBook(int bookID);
        void UpdateBook(Book book);
        void Save();
    }
}

In the DAL folder, create a class file named "BookRepository.cs" The class file implements the "IBookRepository" interface and the "IBookRepository" inherits the IDisposable interface so the IDisposable interface is indirectly implemented by the BookRespository class. The database context is defined in a class variable, and the constructor expects the calling object to pass in an instance of the context.

Here we are passing the BookContext instance to the constructor.

using System;
using System.Collections.Generic;
using System.Linq;
using BookStore.Models;
using System.Data;

namespace BookStore.DAL
{
    public class BookRepository : IBookRepository
    {
        private BookContext _context;

        public BookRepository(BookContext bookContext)
        {
            this._context = bookContext;
        }

        public IEnumerable<Book> GetBooks()
        {
            return _context.Books.ToList();
        }

        public Book GetBookByID(int id)
        {
            return _context.Books.Find(id);
        }

        public void InsertBook(Book book)
        {
            _context.Books.Add(book);
        }

        public void DeleteBook(int bookID)
        {
            Book book = _context.Books.Find(bookID);
            _context.Books.Remove(book);
        }

        public void UpdateBook(Book book)
        {
            _context.Entry(book).State = EntityState.Modified;
        }

        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);
        }
    }
}

Step 6. Create Controller and Views for CRUD operations.

Now our model, data context, and repository are ready, so now proceed to the Controller. So we create a controller (BookController) for Book under the Controllers folder.

We need the following "using" in the controller to perform CRUD operations.

using System.Data;
using System.Linq;
using System.Web.Mvc;
using BookStore.DAL;
using BookStore.Models;

We create an instance of the Book repository interface in the Book Controller and initialize the book repository in the constructor of Book Controller (BookController.cs) as in the following.

private IBookRepository _bookRepository;

public BookController()
{
    this._bookRepository = new BookRepository(new BookContext());
}

We will use Scaffold templates to create a view for the CRUD operations. We use five scaffold templates, List, Create, Edit, Delete, and Details. So create a controller that has a post and get action results depending on the operation.

Operation 1. Show List of All Books.

Create an action in the controller named Index. The Index action returns a list of books.

public ActionResult Index()
{
    var books = from book in _bookRepository.GetBooks()
                select book;
    
    return View(books);
}

Now we create a view. To create the view use the following procedure.

1. Compile the source code successfully
2. Right-click on Action Method Index.
3. The View Name is already filled in so don't change it.
4. The View Engine already selected Razor so don't change it.
5. Check the checkbox "Create a strongly-typed view" because we are creating a strongly typed view.
6. Choose the Model class "Book" so it can be bound with the view.
7. Choose "List" from the Scaffold template so rapid development can be done and we get the view with the code for showing the list of Books.
8. Check both checkboxes "Reference script libraries" and "Use a layout or master page".

These are the common steps to follow for each operation and the only change will be the Scaffold template. The following picture shows the Index view that has a List Scaffold template.

Operation 2. Show Details of Book.

Create an action in the controller named Details. The Details action returns the details of the book.

public ViewResult Details(int id)
{
    Book book = _bookRepository.GetBookByID(id);
    return View(book);
}

Now we create the view. To create the view use the following procedure.

1. Right-click on Action Method Details.
2. The View Name is already filled in so don't change it.
3. The View Engine already selected Razor so don't change it.
4. Check the checkbox "Create a strongly-typed view" because we are creating a strongly typed-view.
5. Choose the Model class "Book" so it can be bound with the view.
6. Choose "Details" from the Scaffold template so we can do rapid development and get the view with the code for showing the details of the book.
7. Check both the checkboxes "Reference script libraries" and "Use a layout or master page".

Operation 3. Create a New Book.

Create two actions in the controller, one for the new book to create a view (Get Action) and another for submitting new book details to the repository (Post Action). These have the same name, Create.

public ActionResult Create()
{
    return View(new Book());
}

[HttpPost]
public ActionResult Create(Book book)
{
    try
    {
        if (ModelState.IsValid)
        {
            _bookRepository.InsertBook(book);
            _bookRepository.Save();
            return RedirectToAction("Index");
        }
    }
    catch (DataException)
    {
        ModelState.AddModelError("", "Unable to save changes. Try again, and if the problem persists see your system administrator.");
    }
    
    return View(book);
}

Now we create a view. To create the view use the following procedure.

1. Right-click on the Action Method Create (GET).
2. The View Name is already filled in so don't change it.
3. The View Engine already selected Razor so don't change it.
4. Check the checkbox "Create a strongly-typed view" because we are creating a strongly typed-view.
5. Choose the Model class "Book" so it can be bound with the view.
6. Choose "Create" from the Scaffold template so we can do rapid development and we get the view for creating the new book.
7. Check both checkboxes "Reference script libraries" and "Use a layout or master page".

Operation 4. Update Book Details.

Create two actions in the controller, one for an existing book edit view (Get Action) and another for submitting the updated book details to the repository (Post Action). These have the same name Create. The Get action fills in the book details on the form by the id of the book so we would pass the id to the action.

public ActionResult Edit(int id)
{
    Book book = _bookRepository.GetBookByID(id);
    return View(book);
}

[HttpPost]
public ActionResult Edit(Book book)
{
    try
    {
        if (ModelState.IsValid)
        {
            _bookRepository.UpdateBook(book);
            _bookRepository.Save();
            return RedirectToAction("Index");
        }
    }
    catch (DataException)
    {
        ModelState.AddModelError("", "Unable to save changes. Try again, and if the problem persists see your system administrator.");
    }
    return View(book);
}

Now we create the view. To create the view use the following procedure.

1. Right-click on Action Method Edit (GET).
2. The View Name is already filled in so don't change it.
3. The View Engine already selected Razor so don't change it.
4. Check the checkbox "Create a strongly-typed view" because we are creating a strongly typed-view.
5. Choose the Model class "Book" so it can be bound with the view.
6. Choose "Edit" from the Scaffold template so we can do rapid development and get the view for updating an existing book.
7. Check both checkboxes "Reference script libraries" and "Use a layout or master page".

Operation 5. Delete Book.

Create two actions in the controller, one to show the details of the book after clicking on the Delete link (Get Action) and another to Delete the book (Post Action). One Delete action but another overrides the Delete Action that overrides the DeleteConfirmed method. The Get action fills in book details on the form by the ID of the book, and then the Post action is performed on it.

public ActionResult Delete(int id, bool? saveChangesError)
{
    if (saveChangesError.GetValueOrDefault())
    {
        ViewBag.ErrorMessage = "Unable to save changes. Try again, and if the problem persists see your system administrator.";
    }
    
    Book book = _bookRepository.GetBookByID(id);
    return View(book);
}

[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
    try
    {
        Book book = _bookRepository.GetBookByID(id);
        _bookRepository.DeleteBook(id);
        _bookRepository.Save();
    }
    catch (DataException)
    {
        return RedirectToAction("Delete", new System.Web.Routing.RouteValueDictionary
        {
            { "id", id },
            { "saveChangesError", true }
        });
    }
    
    return RedirectToAction("Index");
}

Now we create the view. To create the view use the following procedure.

1. Right-click on Action Method Delete.
2. The View Name is already filled in so don't change it.
3. The View Engine already selected Razor so don't change it.
4. Check the checkbox "Create a strongly-typed view" because we are creating a strongly typed-view.
5. Choose the Model class "Book" so it can be bound with the view.
6. Choose "Delete" from the Scaffold template so we can do rapid development and get the view of the delete for the existing book.
7. Check both checkboxes "Reference script libraries" and "Use a layout or master page".

Now the view and action are ready to perform CRUD operations.

Step 7. Run the application.

Call the Book controller http://localhost:4736/Book from the browser and we get the default empty list of books.

Check that our database is created by the data context when the application calls the book entity.

Create new books.

Click on the "Create" button and a new book is created.

Now click on the "Edit" link button of a row and show the book details in the edited form.

Click on the "Save" button and see the updated book list.

Now click on the "Details" button and we get the details of the book.

Click on the back to list and now click on the delete link button then we get the delete confirmation window.

Click on the delete button and the book is deleted.

We perform all CRUD operations successfully. I didn't use many basic screenshots here to create the controller, model, and views because I know that you are already familiar with these. If you want to know more about the basics of MVC razors then here is my other article.

Getting Started with Razor View Engine in MVC 3

I attached source code (Models, Views, Controllers folder, and web. config file) for this book store application so you can try to develop it. Feel free to post a comment on any issue and questions that you find during the development of this application.