Click here to Skip to main content
16,016,894 members
Articles / Web Development / HTML
Tip/Trick

CRUD operation with Entity Framework 6 Database First using MVC 5

Rate me:
Please Sign up or sign in to vote.
4.79/5 (18 votes)
26 Jan 2015CPOL5 min read 184.5K   43   11
In this article, I’ll describe how to perform basic CRUD operations in an MVC5 application.

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.

Image 1

We have an empty database project.

Image 2

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

Image 3

Step 3: Add a new table named Employee.

Image 4

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

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

Image 5

Image 6

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

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

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

Image 7

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.

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

Image 8

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.

Image 9

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

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

Image 10

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.

Image 11

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

Image 12

Step 4: Click the New Connection button.

Image 13

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.

Image 14

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

Image 15

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

Image 16

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

Image 17

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 AddNew Scaffolded Item.

Image 18

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.

Image 19

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.

Image 20

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.

Image 21

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

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

C#
 
@Html.ActionLink("List of employee", "Index", "Employee")
@Html.ActionLink("List of enrollments", "Index", "Enrollment")

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.

License

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


Written By
Architect TalentTuner.com
India India
We are set of professionals who love technical things – Architecting, Coding, Fixing issues & Talk & Write on technologies. We do consultancy especially for Analysis & Architecture applications, we keep developing our own products and we also give trainings on our expertise areas.
This is a Organisation (No members)


Comments and Discussions

 
Questionnot deploying to localdb Pin
Member 134472365-Oct-17 0:22
Member 134472365-Oct-17 0:22 
Questioncreate table query is not executing Pin
Member 128044735-Apr-17 21:51
Member 128044735-Apr-17 21:51 
Questiontable Enrollment. Pin
Member 1236284929-Mar-17 18:12
professionalMember 1236284929-Mar-17 18:12 
QuestionGreat post, but how do you access the other elements via the navigation? Pin
wesdev19779-Feb-16 1:34
wesdev19779-Feb-16 1:34 
Questionthank you Pin
Mahmoud Adb-Elghany18-Dec-15 11:10
Mahmoud Adb-Elghany18-Dec-15 11:10 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun26-Jan-15 21:54
Humayun Kabir Mamun26-Jan-15 21:54 
QuestionImages still broken Pin
BigWCat26-Jan-15 8:12
professionalBigWCat26-Jan-15 8:12 
Please fixed this.
AnswerRe: Images still broken Pin
TalentTuner26-Jan-15 15:42
professionalTalentTuner26-Jan-15 15:42 
QuestionImages are broken Pin
tyaramis26-Jan-15 4:03
tyaramis26-Jan-15 4:03 
AnswerRe: Images are broken Pin
TalentTuner26-Jan-15 15:42
professionalTalentTuner26-Jan-15 15:42 
GeneralMY VOTE OF 5 Pin
Member 1131357326-Jan-15 3:59
Member 1131357326-Jan-15 3:59 

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.