CRUD operation using Database First Approach in ASP.NET MVC

Hello Friends, In this tutorial we are going to see Database First Approach of Entity Framework in ASP.NET. We will also see how to perform CRUD operation using Database First approach in ASP.NET MVC.

What is Database First Approach?

Database First Approach is an approach toward web application development where you generate the entities and context from an existing database. This approach is useful where we need to create Database first then based on the database we generate classes, properties, and Database Context class.

When should we use the Database First Approach?

Database First Approach is useful when you have an existing database or your database is developed by the DBA or developed separately. You can create the Table, Stored Procedure, Views at the Database level and then generate model classes, properties, and database context.

CRUD Operation using Database First Approach in ASP.NET MVC:

Let’s start creating a simple ASP.NET MVC project to understand Database First Approach more clearly.

Create Database and Table:

CREATE Database [Debugonweb]

USE [Debugonweb]
GO

CREATE TABLE [dbo].[Employee](
[EmployeeID] [bigint] IDENTITY(1,1) NOT NULL,
[EmployeeName] [varchar](50) NULL,
[Salary] [float] NULL,
[Gender] [varchar](10) NULL,
[Address] [varchar](50) NULL,
[State] [varchar](50) NULL,
[City] [varchar](50) NULL,
[Status] [varchar](20) NULL,
CONSTRAINT [PK__Employee__3214EC274AA9A371] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE an ASP.NET MVC application:

Step 1: Open Visual Studio and Create an ASP.NET MVC application called using DatabaseFirstApproachDemo.

Step 2: Choose Empty MVC Template as shown in the below image and Click OK.

Add ADO.NET Entity Model:

Step 1: To generate Model classes, Properties and Database Context class we need to add ADO.NET Entity Model class to our ASP.NET MVC project. To add an ADO.NET Entity Model class right click on the Model folder → Add → New Item then select ADO.NET Entity Model as shown in the below image.

Step 2: Click on the Add button. This will launch the Entity Data Model Wizard dialog. Select EF Designer from the database and click the Next button.

Step 3: Select the Existing Database which we have created previously.

Step 4 − Choose Entity Framework 6.x and click Next.

Step 5: Select the Employee table and Click on the Finish button.

You will see the following Entity Model is generated from the database.

Adding Controller:

We are done with Entity Model part. Now we need to generate Controller for our ASP.NET MVC application. For adding controller right click on the Controller folder and select Add Controller. Add a Controller with the name EmployeesController by selecting the following properties.

After clicking the Add button the following Views will be generated in the Views>Employees folder.

Note:If you are not able to find the Model class then rebuild the application.

Our controller EmployeesControll.cs file will contain code like 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 DatabaseFirstApproachDemor.Models;

namespace DatabaseFirstApproachDemor.Controllers
{
public class EmployeesController : Controller
{
private DebugonwebEntities db = new DebugonwebEntities();

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

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

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

// POST: Employees/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,EmployeeName,Salary,Gender,Address,State,City,Status")] Employee employee)
{
if (ModelState.IsValid)
{
db.Employees.Add(employee);
db.SaveChanges();
return RedirectToAction("Index");
}

return View(employee);
}

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

// POST: Employees/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,EmployeeName,Salary,Gender,Address,State,City,Status")] Employee employee)
{
if (ModelState.IsValid)
{
db.Entry(employee).State = EntityState.Modified;
db.SaveChanges();
return RedirectToAction("Index");
}
return View(employee);
}

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

// POST: Employees/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(long 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);
}
}
}

Now run the application. The output window will look like this.

Click on the Create New Link.

Fill the details and click on the Save button.

View More:

• How to handle multiple submit buttons on single view in ASP.NET MVC.
• Paging and Sorting using PageList in ASP.NET MVC.
• Autocomplete TextBox in ASP.NET MVC using Jquery.
• How to Export to PDF using jspdf in ASP.NET MVC.

Conclusion:

I hope you would love this post. Your feedback and suggestions are appreciated. Please don’t hesitate to comment for any technical help.

Thank You.