Click here to Skip to main content
15,868,164 members
Articles / Web Development / HTML

CRUD Operations Using the Repository Pattern in MVC

Rate me:
Please Sign up or sign in to vote.
4.67/5 (72 votes)
27 Nov 2014CPOL14 min read 260.7K   5.9K   134   40
This article introduces the Repository pattern in an MVC application.

Introduction

This article introduces the Repository pattern in MVC applications. 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 to make it easy to understand the Repository pattern, we use a single book entity in the application.

Overview of the 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 the Entity Framework data context, in which MVC controllers interact with the repository rather than directly with Entity Framework.

Repository working flow in MVC

Figure 1.1: Repository Workflow in MVC

Our Roadmap towards Learning MVC 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.

Work 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.

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 an .edmx file.

Database First Approach in EF

Figure 1.2: Database First Approach in Entity Framework

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 an .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.

Database First Approach in EF

Figure 1.3: Model First Approach in Entity Framework

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 Entity Framework without an .edmx file. In this approach 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.

Database First Approach in EF

Figure 1.4: Code First Approach in Entity Framework

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:

  • 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.
  • 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.
  • 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.
  • 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 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 a MVC application template. We choose "Internet Application" from the templates and "Razor" as the view engine.

Create MVC Application Using Razor View Engine

Figure 1.5 : Create MVC Application Using Razor View Engine

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

We are developing a MVC application using MVC 4 with 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 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 implements Code First data annotation for database table that will be created by it.

C#
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 Authers { 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 the definition of the data context class that has a constructor to pass a connection string that is defined in 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.

C#
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; }
    }
}</book>

The connection string in the web.config file is:

XML
<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.

C#
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.

C#
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);
        }
    }
}</book>

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:

C#
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:

C#
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 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.

C#
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.

Add new view

Figure 1.6 : Add new view

Operation 2: Show Details of Book

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

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

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 we 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 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.

C#
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.

C#
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 we 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 then the Post action is performed on it.

C#
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 we 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

View Structure

Figure 1.7: Book CURD Operations View

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.

Index View

Figure 1.8: Index View of Application

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

Database structure

Figure 1.9: Database Created by Application

Create new books.

Create Book View

Figure 1.10: Create Book View

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

List of Books

Figure 1.11: List of Books View

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

Edit View for Book

Figure 1.12 : Edit View for Book

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

Updated Lists of Books

Figure 1.13 : Updated Lists of Books

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

Detail of Book

Figure 1.14 : Detail of Book

Click on Back to List and click on the Delete link button and we get the delete confirmation window.

Book Delete View

Figure 1.15 :Book Delete View

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 Razor and Code First approach in MVC then here are my other articles:

I have 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
India India
He is awarded for Microsoft TechNet Guru, CodeProject MVP and C# Corner MVP. http://l-knowtech.com/

Comments and Discussions

 
Questionvery nice . Pin
Member 1183343414-Jul-15 23:01
Member 1183343414-Jul-15 23:01 
Questiongood job Pin
maimaibenemyeu3-May-15 23:09
maimaibenemyeu3-May-15 23:09 
QuestionStored Procedures In SQL SERVER DB FIRST repository pattern Pin
Member 114868031-May-15 5:53
Member 114868031-May-15 5:53 
Questionthis is awesome Pin
Riaz_25-Feb-15 3:34
Riaz_25-Feb-15 3:34 
QuestionCan I have an example by adding publisher in the above article Pin
PrakashJoshi15-Jan-15 19:32
PrakashJoshi15-Jan-15 19:32 
AnswerRe: Can I have an example by adding publisher in the above article Pin
Sandeep Singh Shekhawat15-Jan-15 22:05
professionalSandeep Singh Shekhawat15-Jan-15 22:05 
GeneralMy vote of 5 Pin
Gaurav Aroraa12-Jan-15 20:21
professionalGaurav Aroraa12-Jan-15 20:21 
GeneralMy vote of 5 Pin
Pascualito7-Jan-15 5:27
professionalPascualito7-Jan-15 5:27 
GeneralRe: My vote of 5 Pin
Sandeep Singh Shekhawat7-Jan-15 17:06
professionalSandeep Singh Shekhawat7-Jan-15 17:06 
QuestionComplex or complicated? Pin
Rollin Shultz3-Dec-14 1:24
Rollin Shultz3-Dec-14 1:24 
GeneralSave Issue Pin
Member 10745681-Dec-14 18:57
Member 10745681-Dec-14 18:57 
GeneralRe: Save Issue Pin
Yy Wan2-Jul-17 11:30
Yy Wan2-Jul-17 11:30 
GeneralMy vote of 1 Pin
Member 10745681-Dec-14 18:54
Member 10745681-Dec-14 18:54 
GeneralRe: My vote of 1 Pin
Sandeep Singh Shekhawat1-Dec-14 19:50
professionalSandeep Singh Shekhawat1-Dec-14 19:50 
GeneralRe: My vote of 1 Pin
Member 10745681-Dec-14 22:06
Member 10745681-Dec-14 22:06 
GeneralRe: My vote of 1 Pin
Sandeep Singh Shekhawat1-Dec-14 22:18
professionalSandeep Singh Shekhawat1-Dec-14 22:18 
GeneralRe: My vote of 1 Pin
Member 10745681-Dec-14 23:38
Member 10745681-Dec-14 23:38 
GeneralRe: My vote of 1 Pin
Sandeep Singh Shekhawat2-Dec-14 0:34
professionalSandeep Singh Shekhawat2-Dec-14 0:34 
GeneralMy vote of 5 Pin
darkliahos30-Nov-14 5:06
darkliahos30-Nov-14 5:06 
GeneralRe: My vote of 5 Pin
Sandeep Singh Shekhawat30-Nov-14 17:44
professionalSandeep Singh Shekhawat30-Nov-14 17:44 
Thanks Smile | :)
GeneralMy vote of 5 Pin
Eldon Elledge20-Nov-14 2:31
Eldon Elledge20-Nov-14 2:31 
GeneralRe: My vote of 5 Pin
Sandeep Singh Shekhawat20-Nov-14 3:19
professionalSandeep Singh Shekhawat20-Nov-14 3:19 
QuestionDB Pin
TinaFMills19-Oct-14 16:03
TinaFMills19-Oct-14 16:03 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun11-Sep-14 20:29
Humayun Kabir Mamun11-Sep-14 20:29 
GeneralRe: My vote of 5 Pin
Sandeep Singh Shekhawat14-Sep-14 14:42
professionalSandeep Singh Shekhawat14-Sep-14 14:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.