How To Implement A Sql Builder In Your Mvc App

Table of Contents

• Introduction
• Why Generic Repository Pattern
• Implement a Generic Repository and a Unit of Work Pattern
• An MVC Application Using the Generic Repository Pattern
  • Student List View
  • Create / Edit Student View
  • Delete A Student
• Download
• See Also
• Conclusion

Introduction

This article introduces how to perform create, read, update, and delete operations in the ASP.NET MVC 5 application, using Generic Repository pattern with Unit of Work and Entity Framework 6 Code First approach. To keep the article simple and to make it easy to understand the Generic Repository pattern and Unit of Work, we use a single Student entity in the application.

One of the big benefits of object-oriented programming is code re-use, in which we create a base class and inherit it in a derived class. The derived class can simply override virtual methods or add some new methods to customize the behavior of the base class to meet yours need. The Generics is another mechanism offered by the Common Language Runtime (CLR) and programming languages that provide one more form of code re-use and algorithm re-use.

The Generics provide a code template for creating type-safe code without referring to specific data types. Generics allow us to realize type safety at compile time. It allows us to create a data structure without committing to a specific data type. When the data structure is used, however, the compiler ensures that the types used with it are consistent for type safety.

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.

Suppose we have a single entity project then we can create a repository for that entity but in real life, project has many entities and those have relationship to each other. We know that an entity performs some operations, which are common for other entities,  such as Create, Read, Update, Delete, Sort, Filter/Search and Paging etc. If we create repository for each entity which means we are repeating same code for each entity's operation. The repository for each entity is against DRY (every piece of knowledge must have a single, unambiguous, authoritative representation within a system. It stands for Don't Repeat Yourself) principle in the software engineering; that's why we need a generic repository pattern.

In this article we design a common generic repository for all entities and a Unit of work class. The Unit of Work class creates a repository instance for each entity and instance for DbContext even Entity Framework follows Unit of Work pattern internally. The repository is used to do CURD operations. We create an instance of the UnitOfWork class in the controller then create a repository instance depending on the entity and thereafter use the methods of the repository as per the operations.

The following diagram shows the relationship between the repository and the Entity Framework data context, in which MVC controllers interact with the repository by a Unit of Work rather than directly with the Entity Framework.

Figure 1: Generic Repository Pattern and Unit of Work workflow

A Unit of Work, as it's name applies, does something. In this article a Unit of Work does whatever that we create an instance of it then it instantiates our DbContext thereafter each repository instance uses the same DbContext for database operations. So the Unit of Work is the pattern that ensures that all repositories use the same database context.

↑ Back to top

Implement a Generic Repository and a Unit of Work Pattern

Note: In this article our user interface uses a concrete class object rather than an interface. To keep this short code and only describe the concepts, we removed error handling code from the controller, but you should use error handling in your controller.

In this section of articles we create two projects, one is CF.Data and another is CF.Repo. We are working with Entity Framework Code First Approach, so the project CF.Data contains entities that are needed in the application's database. In the CF.Data project, we create two entities, one is the BaseEntity class that has common properties that will be inherited by each entity and the other is Student. Let's see each entity. The following is a code snippet for the BaseEntity class.

using System;

namespace CF.Data

{

public class BaseEntity

{

public Int64 Id { get ; set ; }

public DateTime AddedDate { get ; set ; }

public DateTime ModifiedDate { get ; set ; }

public string IPAddress { get ; set ; }

}

}

Now we create a Student entity which inherits from the BaseEntity class. The following is a code snippet for the Student entity.

namespace CF.Data

{

public class Student : BaseEntity

{

public string FirstName { get ; set ; }

public string LastName { get ; set ; }

public string Email { get ; set ; }

public string EnrollmentNumber { get ; set ; }

}

}

Now, we define the configuration for the book entity that will be used when the database table will be created by the entity. The following is a code snippet for the Student mapping entity (StudentMap.cs).

using System.ComponentModel.DataAnnotations.Schema;

using System.Data.Entity.ModelConfiguration;

namespace CF.Data

{

public class StudentMap: EntityTypeConfiguration<Student>

{

public StudentMap()

{

HasKey(t => t.Id);

Property(t => t.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

Property(t => t.FirstName).IsRequired();

Property(t => t.LastName).IsRequired();

Property(t => t.Email).IsRequired();

Property(t => t.EnrollmentNumber).IsRequired();

ToTable( "Student" );

}

}

}

The CF.Data project also contains DataContext. 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 so we create a context class CurdDbContext (CurdDbContext.cs) class. In this class, we override the OnModelCreating() method. This method is called when the model for a context class (CurdDbContext) has been initialized, but before the model has been locked down and used to initialize the context such that the model can be further configured before it is locked down. The following is the code snippet for the context class.

using System;

using System.Data.Entity;

using System.Data.Entity.ModelConfiguration;

using System.Linq;

using System.Reflection;

namespace CF.Data

{

public class CurdDbContext:DbContext

{

public CurdDbContext()

: base ( "name = CurdConnectionString" )

{

}

public new IDbSet<TEntity> Set<TEntity>() where TEntity : BaseEntity

{

return base .Set<TEntity>();

}

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

var typesToRegister = Assembly.GetExecutingAssembly().GetTypes()

.Where(type => !String.IsNullOrEmpty(type.Namespace))

.Where(type => type.BaseType != null && type.BaseType.IsGenericType

&& type.BaseType.GetGenericTypeDefinition() == typeof (EntityTypeConfiguration<>));

foreach (var type in typesToRegister)

{

dynamic configurationInstance = Activator.CreateInstance(type);

modelBuilder.Configurations.Add(configurationInstance);

}

base .OnModelCreating(modelBuilder);

}

}

}

EF Code First approach follows convention over configuration, so in the constructor, we just pass the connection string name, the same as an App.Config file and it connects to that server. You can also delete this App.Config file because when we run web application than it pick connection string from Web.Config file. In the OnModelCreating() method, we used reflection to map an entity to its configuration class in this specific project.

Now we create a generic repository class. We are not creating an interface for a repository so that our article will be easy to understand. This generic repository has all CURD operation methods. This repository contains a parameterized constructor with a parameter as Context so when we create an instance of the repository we pass a context so that all the repositories for each entity have the same context. We are using the saveChanges() method of the context but we can also use the save method of the Unit of Work class because both have the same context. The following is a code snippet for the Generic Repository under CF.Repo project.

using CF.Data;

using System;

using System.Data.Entity;

using System.Data.Entity.Validation;

using System.Linq;

namespace CF.Repo

{

public class Repository<T> where T : BaseEntity

{

private readonly CurdDbContext context;

private IDbSet<T> entities;

string errorMessage = string .Empty;

public Repository(CurdDbContext context)

{

this .context = context;

}

public T GetById( object id)

{

return this .Entities.Find(id);

}

public void Insert(T entity)

{

try

{

if (entity == null )

{

throw new ArgumentNullException( "entity" );

}

this .Entities.Add(entity);

this .context.SaveChanges();

}

catch (DbEntityValidationException dbEx)

{

foreach (var validationErrors in dbEx.EntityValidationErrors)

{

foreach (var validationError in validationErrors.ValidationErrors)

{

errorMessage += string .Format( "Property: {0} Error: {1}" , validationError.PropertyName, validationError.ErrorMessage) + Environment.NewLine;

}

}

throw new Exception(errorMessage, dbEx);

}

}

public void Update(T entity)

{

try

{

if (entity == null )

{

throw new ArgumentNullException( "entity" );

}

this .context.SaveChanges();

}

catch (DbEntityValidationException dbEx)

{

foreach (var validationErrors in dbEx.EntityValidationErrors)

{

foreach (var validationError in validationErrors.ValidationErrors)

{

errorMessage += Environment.NewLine + string .Format( "Property: {0} Error: {1}" , validationError.PropertyName, validationError.ErrorMessage);

}

}

throw new Exception(errorMessage, dbEx);

}

}

public void Delete(T entity)

{

try

{

if (entity == null )

{

throw new ArgumentNullException( "entity" );

}

this .Entities.Remove(entity);

this .context.SaveChanges();

}

catch (DbEntityValidationException dbEx)

{

foreach (var validationErrors in dbEx.EntityValidationErrors)

{

foreach (var validationError in validationErrors.ValidationErrors)

{

errorMessage += Environment.NewLine + string .Format( "Property: {0} Error: {1}" , validationError.PropertyName, validationError.ErrorMessage);

}

}

throw new Exception(errorMessage, dbEx);

}

}

public virtual IQueryable<T> Table

{

get

{

return this .Entities;

}

}

private IDbSet<T> Entities

{

get

{

if (entities == null )

{

entities = context.Set<T>();

}

return entities;

}

}

}

}

Now we create a class named UnitOfWork for the Unit of Work. This class inherits from an IDisposable interface so that its instance will be disposed of in each controller. This class initiates the application DataContext. This class heart is the Repository() method that returns a repository for the entity and that entity inherits from the BaseEntity class. The following is a code snippet for the UnitOfWork class.

using CF.Data;

using System;

using System.Collections.Generic;

namespace CF.Repo

{

public class UnitOfWork

{

private readonly CurdDbContext context;

private bool disposed;

private Dictionary< string , object > repositories;

public UnitOfWork(CurdDbContext context)

{

this .context = context;

}

public UnitOfWork()

{

context = new CurdDbContext();

}

public void Dispose()

{

Dispose( true );

GC.SuppressFinalize( this );

}

public void Save()

{

context.SaveChanges();

}

public virtual void Dispose( bool disposing)

{

if (!disposed)

{

if (disposing)

{

context.Dispose();

}

}

disposed = true ;

}

public Repository<T> Repository<T>() where T : BaseEntity

{

if (repositories == null )

{

repositories = new Dictionary< string , object >();

}

var type = typeof (T).Name;

if (!repositories.ContainsKey(type))

{

var repositoryType = typeof (Repository<>);

var repositoryInstance = Activator.CreateInstance(repositoryType.MakeGenericType( typeof (T)), context);

repositories.Add(type, repositoryInstance);

}

return (Repository<T>)repositories[type];

}

}

}

↑ Back to top

An MVC Application Using the Generic Repository Pattern

Now we create a MVC application (CURDCodeFirst). This is our third project of the application, this project contains user interface for a Student entity's CURD operations and the controller to do these operations. First we proceed to the controller. Create a StudentController under the Controllers folder of the application. This controller has all ActionResult methods for each user interface of a CRUD operation. We first create a Unit of Work class instance then the controller's constructor initiates the repository as per the required entity. The following is a code snippet for the StudentController.

using CF.Data;

using CF.Repo;

using CURDCodeFirst.Models;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web.Mvc;

namespace CURDCodeFirst.Controllers

{

public class StudentController : Controller

{

private UnitOfWork unitOfWork;

private Repository<Student> studentRepository;

public StudentController()

{

unitOfWork = new UnitOfWork();

studentRepository = unitOfWork.Repository<Student>();

}

[HttpGet]

public ActionResult Index()

{

IEnumerable<StudentViewModel> students = studentRepository.Table.AsEnumerable().Select(s => new StudentViewModel

{

Id = s.Id,

Name = $ "{s.FirstName} {s.LastName}" ,

Email = s.Email,

EnrollmentNo = s.EnrollmentNumber

});

return View(students);

}

[HttpGet]

public PartialViewResult AddEditStudent( long ? id)

{

StudentViewModel model = new StudentViewModel();

if (id.HasValue)

{

Student student = studentRepository.GetById(id.Value);

model.Id = student.Id;

model.FirstName = student.FirstName;

model.LastName = student.LastName;

model.EnrollmentNo = student.EnrollmentNumber;

model.Email = student.Email;

}

return PartialView( "~/Views/Student/_AddEditStudent.cshtml" , model);

}

[HttpPost]

public ActionResult AddEditStudent( long ? id, StudentViewModel model)

{

try

{

if (ModelState.IsValid)

{

bool isNew = !id.HasValue;

Student student = isNew ? new Student

{

AddedDate = DateTime.UtcNow

} : studentRepository.GetById(id.Value);

student.FirstName = model.FirstName;

student.LastName = model.LastName;

student.EnrollmentNumber = model.EnrollmentNo;

student.Email = model.Email;

student.IPAddress = Request.UserHostAddress;

student.ModifiedDate = DateTime.UtcNow;

if (isNew)

{

studentRepository.Insert(student);

}

else

{

studentRepository.Update(student);

}

}

}

catch (Exception ex)

{

throw ex;

}

return RedirectToAction( "Index" );

}

[HttpGet]

public PartialViewResult DeleteStudent( long id)

{

Student student = studentRepository.GetById(id);

StudentViewModel model = new StudentViewModel

{

Name = $ "{student.FirstName} {student.LastName}"

};

return PartialView( "~/Views/Student/_DeleteStudent.cshtml" , model);

}

[HttpPost]

public ActionResult DeleteStudent( long id, FormCollection form)

{

Student student = studentRepository.GetById(id);

studentRepository.Delete(student);

return RedirectToAction( "Index" );

}

}

}

We have now developed the StudentController to handle CURD operation request for a Student entity. Now we develop the user interface for the CRUD operations. We develop it for the views for adding and editing a student, a student listing, student delete. Let's see each one by one.

This is the first view when the application is accessed or the entry point of the application is executed. It shows the student listing as in Figure 2. We display student data in tabular format and on this view we create links to add a new student, edit a student and delete a student. This view is an index view and the following is a code snippet for index.cshtml under the Student folder of Views.

@model IEnumerable< CURDCodeFirst.Models.StudentViewModel >

@using CURDCodeFirst.Models

@using CURDCodeFirst.Code

< div class = "panel panel-primary" >

< div class = "panel-heading panel-head" >Students</ div >

< div class = "panel-body" >

< div class = "btn-group" >

< a id = "createEditStudentModal" data-toggle = "modal" href = "@Url.Action(" AddEditStudent")" data-target = "#modal-action-student" class = "btn btn-primary" >

< i class = "glyphicon glyphicon-plus" ></ i >  Add Student

</ a >

</ div >

< div class = "top-buffer" ></ div >

< table class = "table table-bordered table-striped table-condensed" >

< thead >

< tr >

< th >Name</ th >

< th >Enrollment No</ th >

< th >Email</ th >

< th >Action</ th >

</ tr >

</ thead >

< tbody >

@foreach (var item in Model)

{

< tr >

< td >@item.Name</ td >

< td >@item.EnrollmentNo</ td >

< td >@item.Email</ td >

< td >

< a id = "editUserModal" data-toggle = "modal" href = "@Url.Action(" AddEditStudent", new {id=@item.Id })" data-target = "#modal-action-student" class = "btn btn-info" >

< i class = "glyphicon glyphicon-pencil" ></ i >  Edit

</ a >

< a id = "deleteUserModal" data-toggle = "modal" href = "@Url.Action(" DeleteStudent", new {id=@item.Id })" data-target = "#modal-action-student" class = "btn btn-danger" >

< i class = "glyphicon glyphicon-trash" ></ i >  Delete

</ a >

</ td >

</ tr >

}

</ tbody >

</ table >

</ div >

</ div >

@Html.Partial("_Modal", new BootstrapModel { ID = "modal-action-student", AreaLabeledId = "modal-action-student-label", Size = ModalSize.Medium })

@section scripts{

@Scripts.Render("~/Scripts/student-index.js")

}

When we run the application and call the index() action with a HttpGet request then we get all the students listed in the UI as in Figure 2. This UI has options for CRUD operations.

Create / Edit Student View

We create a common view to create and edit a student so we create a single student view model. The following code snippet for StudentViewModel.cs.

using System.ComponentModel.DataAnnotations;

namespace CURDCodeFirst.Models

{

public class StudentViewModel

{

public long Id { get ; set ; }

[Display(Name= "First Name" )]

public string FirstName { get ; set ; }

[Display(Name = "Last Name" )]

public string LastName { get ; set ; }

public string Name { get ; set ; }

public string Email { get ; set ; }

[Display(Name = "Enrollment No" )]

public string EnrollmentNo { get ; set ; }

}

}

We show form in bootstrap modal popup and submit using ajax post that's why we create a javascript file which contains method for remove loaded data.

( function ($) {

function Student() {

var $ this = this ;

function initilizeModel() {

$( "#modal-action-student" ).on( 'loaded.bs.modal' , function (e) {

}).on( 'hidden.bs.modal' , function (e) {

$( this ).removeData( 'bs.modal' );

});

}

$ this .init = function () {

initilizeModel();

}

}

$( function () {

var self = new Student();

self.init();

})

}(jQuery))

Now define a create/edit student partial view and the following is a code snippet for _AddEditStudent.cshtml.

@model CURDCodeFirst.Models.StudentViewModel

@using CURDCodeFirst.Models

@using (Html.BeginForm())

{

@Html.Partial("_ModalHeader", new ModalHeader { Heading = String.Format("{0} Student", @Model.Id == 0 ? "Add" : "Edit") })

< div class = "modal-body form-horizontal" >

< div class = "form-group" >

@Html.LabelFor(model => model.FirstName, new { @class = "col-lg-3 col-sm-3 control-label" })

< div class = "col-lg-6" >

@Html.TextBoxFor(model => model.FirstName, new { @class = "form-control " })

</ div >

</ div >

< div class = "form-group" >

@Html.LabelFor(model => model.LastName, new { @class = "col-lg-3 col-sm-3 control-label" })

< div class = "col-lg-6" >

@Html.TextBoxFor(model => model.LastName, new { @class = "form-control " })

</ div >

</ div >

< div class = "form-group" >

@Html.LabelFor(model => model.EnrollmentNo, new { @class = "col-lg-3 col-sm-3 control-label" })

< div class = "col-lg-6" >

@Html.TextBoxFor(model => model.EnrollmentNo, new { @class = "form-control " })

</ div >

</ div >

< div class = "form-group" >

@Html.LabelFor(model => model.Email, new { @class = "col-lg-3 col-sm-3 control-label" })

< div class = "col-lg-6" >

@Html.TextBoxFor(model => model.Email, new { @class = "form-control " })

</ div >

</ div >

</ div >

@Html.Partial("_ModalFooter", new ModalFooter { })

}

Now run the application and click on Edit button of listing which call AddEditStudnet action method, then we get the UI as in Figure 3 to edit a Student.

Figure 3: Edit a Student UI.

To delete a student, we follow the process of clicking on the Delete button that exists in the Student listing data then a modal popup shows to ask "Are you want to delete xxx?" after clicking on the Delete button that exists in the popup view such as in Figure 4 then it makes a HttpPost request that calls the DeleteStudent() action method and deletes the student. The following is a code snippet for _DeleteStudent.cshtml.

@model CURDCodeFirst.Models.StudentViewModel

@using CURDCodeFirst.Models

@using (Html.BeginForm())

{

@Html.Partial("_ModalHeader", new ModalHeader { Heading = "Delete Student" })

< div class = "modal-body form-horizontal" >

Are you want to delete @Model.Name?

</ div >

@Html.Partial("_ModalFooter", new ModalFooter { SubmitButtonText="Delete"})

}

Figure 4: Delete Confirmation

You can download complete source code from MSDN Source code using following link CRUD Operations in MVC using Generic Repository Pattern and Entity Framework. This holds complete VS2015 solution for CURD operations.

It's recommended to read these articles, which describe Entity Framework Code First approach basics. These are:

1. Relationship in Entity Framework Using Code First Approach With Fluent API
2. Code First Migrations in Entity Framework

This article introduced the generic repository pattern with unit of work. We used bootstrap CSS and JavaScript for the user interface design in this application. What do you think about this article? Please provide your feedback.

How To Implement A Sql Builder In Your Mvc App

Source: https://social.technet.microsoft.com/wiki/contents/articles/35860.crud-operations-in-mvc-using-generic-repository-pattern-and-entity-framework.aspx

Posted by: jacobsbeasto.blogspot.com

Share this post