CRUD operation with Entity Framework 6 Database First using MVC 5

• Download MVC Code - 357.6 KB
• Download databse creation code - 15.9 KB

Introduction

In this article, I’ll describe how to perform basic CRUD operations in an MVC5 application. We develop application with the help of Entity Framework 6 database first and scaffolding feature of MVC5 without writing a single line of code. Entity Framework and MVC had advanced themselves to the level that we don’t have to put effort in doing extra work.

Database First

We use Entity Framework designer which is in built feature of Visual Studio for automatically generate a data model with classes and properties of existing database tables and columns. The information about your database structure (store schema), your data model (conceptual model), and the mapping between them is stored in XML in an .edmx file.  Entity Framework designer provides a graphical interface for display and edit the .edmx file.

Prerequisites

Visual Studio 2013 or Visual Studio Express 2013 for Web.

Set up the database

We will first create a database with some pre-filled data, and then create MVC web application that connects with the database. We developed application using LocalDB with either Visual Studio 2013 or Visual Studio Express 2013 for Web.

Step1: Open Visual Studio open Other Language the select SQL Server and create a SQL Server Database Project. Name the project OrganizationEmployeeData.

We have an empty database project.

Step 2: For create the tables Right-click on project and add a new item.

Step 3: Add a new table named Employee.

Step 4: In the table file, replace the T-SQL command with the following code to create the employee table.

CREATE TABLE [dbo].[Employee] (
    [EmployeeID]      INT           IDENTITY (1, 1) NOT NULL,
    [LastName]       NVARCHAR (50) NULL,
    [FirstName]      NVARCHAR (50) NULL,	
    [JoiningDate] DATETIME      NULL,
    PRIMARY KEY CLUSTERED ([EmployeeID] ASC))

Now you can see that design window automatically synchronizes with the code. We can use either the code or designer.

We will add another table Department and use the following T-SQL command.

CREATE TABLE [dbo].[Department] (
    [DepartmentID] INT           IDENTITY (1, 1) NOT NULL,
    [Title]    NVARCHAR (50) NULL,
    [Credits]  INT           NULL,
    PRIMARY KEY CLUSTERED ([DepartmentID] ASC)
)

And, create one more table Enrollment.

CREATE TABLE [dbo].[Enrollment] (
    [EnrollmentID] INT IDENTITY (1, 1) NOT NULL,
    [Band]        DECIMAL(3, 2) NULL,
    [DepartmentID]     INT NOT NULL,
    [EmployeeID]    INT NOT NULL,
    PRIMARY KEY CLUSTERED ([EnrollmentID] ASC),
    CONSTRAINT [FK_dbo.Enrollment_dbo.Department_DepartmentID] FOREIGN KEY ([DepartmentID]) 
        REFERENCES [dbo].[Department] ([DepartmentID]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.Enrollment_dbo.Employee_EmployeeID] FOREIGN KEY ([EmployeeID]) 
        REFERENCES [dbo].[Employee] ([EmployeeID]) ON DELETE CASCADE

We will populate database with data through a script that is run after the database is deployed. Add a Post-Deployment Script to the project. We can use the default name.

Now add the following T-SQL code to the post-deployment script. This script simply adds data to the database when no matching record is found. It does not overwrite or delete any data you may have entered into the database.

MERGE INTO Department AS Target 
USING (VALUES 
        (1, 'Microsoft', 3), 
        (2, 'Java', 3), 
        (3, 'Php', 4)
) 
AS Source (DepartmentID, Title, Credits) 
ON Target.DepartmentID = Source.DepartmentID 
WHEN NOT MATCHED BY TARGET THEN 
INSERT (Title, Credits) 
VALUES (Title, Credits);


MERGE INTO Employee AS Target
USING (VALUES 
        (1, 'Ark', 'Roop', '2013-09-01'), 
        (2, 'Akash', 'Gupta', '2012-01-13'), 
	(3, 'Saurabh', 'Gupta', '2011-09-03')
)
AS Source (EmployeeID, LastName, FirstName, JoiningDate)
ON Target.EmployeeID = Source.EmployeeID
WHEN NOT MATCHED BY TARGET THEN
INSERT (LastName, FirstName, JoiningDate)
VALUES (LastName, FirstName, JoiningDate);


MERGE INTO Enrollment AS Target
USING (VALUES 
	(1, 2.00, 1, 1),
	(2, 3.50, 1, 2),
	(3, 4.00, 2, 3),
	(4, 1.80, 2, 1),
	(5, 3.20, 3, 1),
	(6, 4.00, 3, 2)
)
AS Source (EnrollmentID, Band, DepartmentID, EmployeeID)
ON Target.EnrollmentID = Source.EnrollmentID
WHEN NOT MATCHED BY TARGET THEN
INSERT (Band, DepartmentID, EmployeeID)
VALUES (Band, DepartmentID, EmployeeID);

It is important to note that the post-deployment script is run every time you deploy your database project.

We have 4 SQL script files but no actual tables. In Visual Studio, click the Start button (or F5) to build and deploy your database project to localdb.

To see that the new database has been created, open SQL Server Object Explorer and look for the name of the project in the correct local database server (in this case (localdb)\Projects)

Generate the models

We will use Entity Framework 6. We check the version of Entity Framework in the project if the version is less than 6 then use Manage NuGet Packages for, update version of Entity Framework.

Now we will create Entity Framework models from the database tables.

Step 1: Right-click the Models folder, and select Add and New Item.

Step 2: In the Add New Item window, select Data in the left pane and ADO.NET Entity Data Model from the center pane. Name the new model file OrgModel and Click Add.

Step 3: In the Entity Data Model Wizard, select Generate from database and Click Next.

Step 4: Click the New Connection button.

Step 5: In the Connection Properties window, provide the name of the local server where database was created (in this case (localdb)\Projects). After providing the server name, select the OrganizationEmployee from the available databases then click ok.

Step 6: You can use the default name for connection for save in the Web.Config file and click next.

Step 7: Select Tables to generate models for all three tables and click finish.

The Models folder now includes many new files related to the models that were generated from the database.

The OrgModel.Context.cs file contains a class that derives from the DbContext class, and provides a property for each model class that corresponds to a database table. The Department.cs, Enrollment.cs, and Employee.cs files contain the model classes that represent the databases tables. You will use both the context class and the model classes when working with scaffolding.

Before proceeding with this tutorial, build the project. In the next section, you will generate code based on the data models, but that section will not work if the project has not been built.

Add scaffold

Step 1: Add the new controller to the existing Controllers folder. Right-click the Controllers folder, and select Add – New Scaffolded Item.

Step 2: Select the MVC 5 Controller with views, using Entity Framework option. This option will generate the controller and views for updating, deleting, creating and displaying the data in your model.

Step 3: Add the controller name as EmployeeController  then select Employee for the model class, and select the OrganizationEmployeeEntities for the context class now click add.

If you receive an error, it may be because you did not build the project in the previous section. If so, try building the project, and then add the scaffolded item again.

After the code generation process is complete, you will see a new controller and views in your project.

Perform the same steps again, but add a scaffold for the Enrollment class. When finished, you should have an EnrollmentsController.cs file, and a folder under Views named Enrollments with the Create, Delete, Details, Edit and Index views.

Step 4: We see our employee controller prepared with all the CRUD operation actions as shown below:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using OrganizationDetails.Models;

namespace OrganizationDetails.Controllers
{
    public class EmployeeController : Controller
    {
        private OrganizationEmployeeEntities db = new OrganizationEmployeeEntities();

        // GET: /Employee/
        public ActionResult Index()
        {
            return View(db.Employees.ToList());
        }

        // GET: /Employee/Details/5
        public ActionResult Details(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Employee employee = db.Employees.Find(id);
            if (employee == null)
            {
                return HttpNotFound();
            }
            return View(employee);
        }

        // GET: /Employee/Create
        public ActionResult Create()
        {
            return View();
        }

        // POST: /Employee/Create
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create([Bind(Include="EmployeeID,LastName,FirstName,JoiningDate")] Employee employee)
        {
            if (ModelState.IsValid)
            {
                db.Employees.Add(employee);
                db.SaveChanges();
                return RedirectToAction("Index");
            }

            return View(employee);
        }

        // GET: /Employee/Edit/5
        public ActionResult Edit(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Employee employee = db.Employees.Find(id);
            if (employee == null)
            {
                return HttpNotFound();
            }
            return View(employee);
        }

        // POST: /Employee/Edit/5
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit([Bind(Include="EmployeeID,LastName,FirstName,JoiningDate")] Employee employee)
        {
            if (ModelState.IsValid)
            {
                db.Entry(employee).State = EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            return View(employee);
        }

        // GET: /Employee/Delete/5
        public ActionResult Delete(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Employee employee = db.Employees.Find(id);
            if (employee == null)
            {
                return HttpNotFound();
            }
            return View(employee);
        }

        // POST: /Employee/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public ActionResult DeleteConfirmed(int id)
        {
            Employee employee = db.Employees.Find(id);
            db.Employees.Remove(employee);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }
    }
}

Add links to new views

To make it easier for you to navigate to your new views, you can add a couple of hyperlinks to the Index views for students and enrollments. Open the file at Views/Home/Index.cshtml, which is the home page for your site. Add the following code.

Conclusion

In this tutorial, we learnt to setup environment for MVC 5 and Entity Framework 6 and perform CRUD operations on Employee and Enrollment model without writing a single line of code with scaffolding feature of MVC 5. You can expand the application by adding multiple Controllers, Models and Views.