In this article, I will demonstrate you about Database First Approach in Entity Framework and also will show how to implement it with Asp.Net MVC. I will show you how to make CRUD operation functionality in Asp.Net MVC. We will also use the Scaffolding feature, using this we can generate code for Create, Read, Update and Delete.
 

 
What is Database First Approach?

There are following approach which is used to connect with database to application.

Database First

Model First

Code First

Today, we will learn about Database First Approach. So, First question is come in mind “What is Database First”. So, Database First is nothing but only a approach to create web application where database is available first and can interact with database. In this database, database is created first and after that we manage the code. The Entity Framework is able to generate a business model based on the tables and columns in a relational database.

Basically, there is some scenario where we need to create database first like all table, primary key and foreign key relationship and then on the basis of database we generate our code. So, this makes our life easy when we use Scaffolding feature with database first approach.

So, you need to create your database First in SQL Server. You can also use the MySQL or any other database source.

 

Create Database and Tables

To create a new database first open “Microsoft SQL Server Management Studio” and Right click on Database node and choose New Database.

 

 

It will open a New Database Dialog where you can define your database structure. You need to provide the database name “TestDemo” and click to OK. 

 

 

It will add a new database for you. You can check it into the Object Explorer.

 

 

After creating the database, we need to create some table which will participate in CRUD operations. In this article, I am going to create two tables “Employee” and “Department” and make relationship between both.

To create these tables on database, we can use following scripts.

 

Use TestDemo
go
CREATE TABLE [dbo].[Department](
	[DepartmentId] [int] IDENTITY(1,1) NOT NULL,
	[DepartmentName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
(
	[DepartmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[Employees](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NULL,
	[Email] [nvarchar](max) NULL,
	[Age] [nvarchar](max) NULL,
	[Address] [nvarchar](max) NULL,
	[DepartmentId] [int] NULL,
 CONSTRAINT [PK_dbo.Employees] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Department] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Department] ([DepartmentId])
GO

ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Department]
GO

 

So, final structure of the database would be like this.

 

We can see that here we have create both table successfully. So, It time to create a Asp.Net MVC application and use this database for CRUD operations.

 

Create New Project

To create new project,  Open Visual Studio, I am using Visual Studio 2013 and click on FileMenu and click on New and choose Project.

 

 

It will open a new window from where you can choose the application type. ChooseWeb from Installed and then choose Asp.Net Web Application. Give it proper project name and also provide the location of project to save and click on OK.

 

This will open a dialog where we can choose New Asp.Net Project, So, here we are going to choose an MVC, and choose OK.

 

 

So, finally we have created a Asp.Net MVC application. When you go through the Solution Explorer, project structure will look like as following. Here we can see different type of folders and files like Controllers, Models etc.

 

So, we have created an MVC project. Now it is time to create models from existing database.

To Add Models, Right Click on Models folder and choose Add and then choose New Item.

 

It will open a dialog where we need to choose Data node then ADO.NET Entity Data Model, provide the valid name and chick OK.

 

 

In the Entity Data Model Wizard, select EF Designer from database and Click Next. 

 

 

Click the New Connection button. where you will define the database connection.

 

 

In the Choose Data Source window, we need to choose Data Source and click to Continue.

 

 

It will open a new dialog where you need to specify everything about database connection. Here we will pass the user name and password and also choose the database. We can also check our database connection to click on Test Connection.

 

 

It will create database connection string in the Entity Data Model Wizard. Click to Next.

 

 

From the next Entity Data Model Wizard, we can choose database items like Tables, Views, Stored Procedures and Function. Choose as per your requirement and pass the Model Name and click to Finish.

 

 

The DemoDataModel.Context.cs file contains a class that derives from the DbContext class. It also provides a property for each model class that corresponds to a database table. The Department.cs and Employee.cs files contain the model classes that represent the databases tables. Here you can see Department.cs and Employee.cs represent the database the database table. They contain all columns as properties.

Following is the code for Department.cs.


namespace DatabaseFirstDemo.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class Department
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Department()
        {
            this.Employees = new HashSet<Employee>();
        }
    
        public int DepartmentId { get; set; }
        public string DepartmentName { get; set; }
    
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Employee> Employees { get; set; }
    }
}

Employee.cs

namespace DatabaseFirstDemo.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Age { get; set; }
        public string Address { get; set; }
        public Nullable<int> DepartmentId { get; set; }
    
        public virtual Department Department { get; set; }
    }
}

 

Before proceeding to move forward you need to build the application. Next we will generate the code from Models, So, before go ahead please build the application.

 

Create User Interface

We have added database with tables and also implemted it with Entity Data Model which has been created model classes. So, it is time to create User Interface [Views] which will used to perform user operation like here we can add data, select data, edit data and also delete data.

To add new controller, Right click on Controller folder and choose Add and choose Add Scaffolded Item.

 

 

It will open a Add Scaffold window, Here we need to choose the Controller type. Here we need to choose MVC5 Controller with views, using Entity Framework and choose Add.

 

 

From the Add Controller window, we need to select the Model Class and Data Context Class. We can also select layout page. In the Controller Name section, we can provide the specific name for the controller "EmployeeController". 

 

 

When we click on Ok. It will Scaffold and create the EmployeeController as well as Views for Employee Controller. 

Note: If you don't make build before scaffolding then it will generate the error as following.

 

 

So, It will create EmployeeController with following code. In this code you can see that here all the operation has been defined by default. From database instance creation to getting data, deleting data, editing data etc everything has defined by the code.

EmployeeController.cs

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 DatabaseFirstDemo.Models;

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

        // GET: Employee
        public ActionResult Index()
        {
            var employees = db.Employees.Include(e => e.Department);
            return View(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()
        {
            ViewBag.DepartmentId = new SelectList(db.Departments, "DepartmentId", "DepartmentName");
            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 = "Id,Name,Email,Age,Address,DepartmentId")] Employee employee)
        {
            if (ModelState.IsValid)
            {
                db.Employees.Add(employee);
                db.SaveChanges();
                return RedirectToAction("Index");
            }

            ViewBag.DepartmentId = new SelectList(db.Departments, "DepartmentId", "DepartmentName", employee.DepartmentId);
            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();
            }
            ViewBag.DepartmentId = new SelectList(db.Departments, "DepartmentId", "DepartmentName", employee.DepartmentId);
            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 = "Id,Name,Email,Age,Address,DepartmentId")] Employee employee)
        {
            if (ModelState.IsValid)
            {
                db.Entry(employee).State = EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            ViewBag.DepartmentId = new SelectList(db.Departments, "DepartmentId", "DepartmentName", employee.DepartmentId);
            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);
        }
    }
}

 

Following is the structure of the code after adding EmployeeController. We can see here a Employee folder has been added inside the Views and all the view like Index.cshtml, edit.cshtml etc also has added.

 

 

So, this is the time of running the project. To run the project Press F5. It will open a tab in your browser as following.

 

 

As we have added the Employee controller. So, we need to specify the address of Employee Controller with Index page. Here you can see all the added Employee record with a Create New Button.

Index.cshtml

@model IEnumerable<DatabaseFirstDemo.Models.Employee>

@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Email)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Age)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Address)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Department.DepartmentName)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Email)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Age)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Address)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Department.DepartmentName)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.Id }) |
            @Html.ActionLink("Details", "Details", new { id=item.Id }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.Id })
        </td>
    </tr>
}

</table>

 

Click on Create New, It will redirect to create new Employee Url, Here we can add new employee record.

Create.cshtml

@model DatabaseFirstDemo.Models.Employee

@{
    ViewBag.Title = "Create";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>Create</h2>


@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>Employee</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Email, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Email, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Email, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Age, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Age, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Age, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.DepartmentId, "DepartmentId", htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownList("DepartmentId", null, htmlAttributes: new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.DepartmentId, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

 

After adding the record, we can see all the record here. Here some other links are also available for edit the record, Details of record or for deleting the record.

 

 

For delete the record, click on Delete link, It will ask for your confirmation to delete the record. If you will click on Delete again then It will delete the record finally.

Delete.cshtml

@model DatabaseFirstDemo.Models.Employee

@{
    ViewBag.Title = "Delete";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>Delete</h2>

<h3>Are you sure you want to delete this?</h3>
<div>
    <h4>Employee</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.Name)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Name)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Email)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Email)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Age)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Age)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Address)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Address)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Department.DepartmentName)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Department.DepartmentName)
        </dd>

    </dl>

    @using (Html.BeginForm()) {
        @Html.AntiForgeryToken()

        <div class="form-actions no-color">
            <input type="submit" value="Delete" class="btn btn-default" /> |
            @Html.ActionLink("Back to List", "Index")
        </div>
    }
</div>

 

To view the single employee details, we need to click on Details link.

Details.cshtml

@model DatabaseFirstDemo.Models.Employee

@{
    ViewBag.Title = "Details";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>Details</h2>

<div>
    <h4>Employee</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.Name)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Name)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Email)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Email)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Age)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Age)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Address)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Address)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Department.DepartmentName)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Department.DepartmentName)
        </dd>

    </dl>
</div>
<p>
    @Html.ActionLink("Edit", "Edit", new { id = Model.Id }) |
    @Html.ActionLink("Back to List", "Index")
</p>

 

Conclusion:

So, today we have learned about what is database first approach and how to implement it in Asp.Net MVC application.

I hope this post will help you. Please put your feedback using comment which helps me to improve myself for next post. If you have any doubts please ask your doubts or query in the comment section and if you like this post, please share it with your friends. Thanks