Asp.net MVC 5 Entity Framework :- CRUD Operations Sample Code

Asp.net MVC 5 Entity Framework:- CRUD Operations

Project Sample Code

Step 1.Create Database using MSQL Server

Database (SQL) Code:-

/****** First Execute this Command ******/

Create Database [EntityFrameworkSampleDB]

/****** Create Tables ******/

Use EntityFrameworkSampleDB;

CREATE TABLE [dbo].[Branch](
[BranchID] [bigint] Primary key IDENTITY(1,1) NOT NULL,
[BranchName] [nvarchar](50) NULL,
[Location] [nvarchar](50) NULL )

CREATE TABLE [dbo].[Department](
[DeptID] [bigint] Primary Key IDENTITY(1,1) NOT NULL,
[DeptName] [nvarchar](50) NULL )

CREATE TABLE [dbo].[Employee](
[EmployeeID] [bigint] Primary Key IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Designation] [nvarchar](50) NULL,
[Contact] [nchar](10) NULL,
[Email] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[Salary] [decimal](18, 2) NULL,
[JoiningDate] [datetime] NULL,
[DeptID] [bigint] references Department(DeptID) on Delete set NULL,
[BranchID] [bigint] references Branch(BranchID) on Delete Set NULL )

/******Step 2. Insert Sample Data Using Below Details*********/

SET IDENTITY_INSERT [dbo].[Branch] ON
INSERT [dbo].[Branch] ([BranchID], [BranchName], [Location]) VALUES (1, N'New Delhi Branch', N'New Delhi')
INSERT [dbo].[Branch] ([BranchID], [BranchName], [Location]) VALUES (2, N'Mumbai Branch', N'Mumbai')
INSERT [dbo].[Branch] ([BranchID], [BranchName], [Location]) VALUES (3, N'Puna Branch', N'Puna')
SET IDENTITY_INSERT [dbo].[Branch] OFF
GO

SET IDENTITY_INSERT [dbo].[Department] ON
INSERT [dbo].[Department] ([DeptID], [DeptName]) VALUES (1, N'HR')
INSERT [dbo].[Department] ([DeptID], [DeptName]) VALUES (2, N'Software Development')
SET IDENTITY_INSERT [dbo].[Department] OFF
GO

SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Designation], [Contact], [Email], [City], [Salary], [JoiningDate], [DeptID], [BranchID]) VALUES (1, N'Vikas Vishnoi', N'Software Developer', N'9999999999', N'vikas@gmail.com', N'Rampur', CAST(36000.00 AS Decimal(18, 2)), CAST(N'2020-06-01T00:00:00.000' AS DateTime), 2, 1)
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Designation], [Contact], [Email], [City], [Salary], [JoiningDate], [DeptID], [BranchID]) VALUES (2, N'Iftehkar Alam', N'Senior Software Developer', N'2222222222', N'iftekar@gmail.com', N'Ghaziabad', CAST(63000.00 AS Decimal(18, 2)), CAST(N'2020-04-23T00:00:00.000' AS DateTime), 2, 1)
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Designation], [Contact], [Email], [City], [Salary], [JoiningDate], [DeptID], [BranchID]) VALUES (3, N'Abu Shahma', N'Office Executive', N'3333333333', N'abu@gmail.com', N'Mumbai', CAST(35000.00 AS Decimal(18, 2)), CAST(N'2021-08-11T00:00:00.000' AS DateTime), 1, 2)
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Designation], [Contact], [Email], [City], [Salary], [JoiningDate], [DeptID], [BranchID]) VALUES (4, N'Preeti Singh', N'HR Manager', N'4444444444', N'preeti@gmail.com', N'Delhi', CAST(500000.00 AS Decimal(18, 2)), CAST(N'2020-05-05T00:00:00.000' AS DateTime), 1, 1)
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Designation], [Contact], [Email], [City], [Salary], [JoiningDate], [DeptID], [BranchID]) VALUES (5, N'Rahul Jain', N'Web Designer', N'5555555555', N'rahul@gmail.com', N'Puna', CAST(60000.00 AS Decimal(18, 2)), CAST(N'2022-01-24T00:00:00.000' AS DateTime), 2, 3)
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Designation], [Contact], [Email], [City], [Salary], [JoiningDate], [DeptID], [BranchID]) VALUES (6, N'Ranjeet Singh', N'Software Developer', N'6666666666', N'ranjeet@gmail.com', N'Moradabad', CAST(65000.00 AS Decimal(18, 2)), CAST(N'2022-03-30T00:00:00.000' AS DateTime), 2, 1)
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Designation], [Contact], [Email], [City], [Salary], [JoiningDate], [DeptID], [BranchID]) VALUES (7, N'Gurvinder Singh', N'Database Admi', N'7777777777', N'gurvinder@gmail.com', N'Kanth', CAST(52000.00 AS Decimal(18, 2)), CAST(N'2020-05-18T00:00:00.000' AS DateTime), 2, 1)
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

Step 3. Create New Project in Visual Studio

Project Name: EFDBFIrstApproach

Layout Page and Default page Source Code

Home Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace EFDBFIrstApproach.Controllers
{
public class HomeController : Controller
{
// GET: Home
public ActionResult Default()
{
return View();
}
}
}

Branches Controller

Departments Controller

Employees Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using EFDBFIrstApproach.Models;

namespace EFDBFIrstApproach.Controllers
{
public class EmployeesController : Controller
{
// GET: Employees
public ActionResult Index()
{
EntityFrameworkSampleDBEntities db = new EntityFrameworkSampleDBEntities();
List<Employee> emptlist = db.Employees.ToList();
return View(emptlist);
}

[HttpPost]
public ActionResult Index(string search = "")
{
EntityFrameworkSampleDBEntities db = new EntityFrameworkSampleDBEntities();
List<Employee> emptlist = db.Employees.Where(temp => temp.Name.Contains(search)).ToList();
ViewBag.MySearch = search;
return View(emptlist);
}

public ActionResult Details(long id)
{
EntityFrameworkSampleDBEntities db = new EntityFrameworkSampleDBEntities();
Employee employee = db.Employees.Where(temp=>temp.EmployeeID==id).FirstOrDefault();
return View(employee);
}

public ActionResult Create()
{
return View();
}

[HttpPost]
public ActionResult Create(Employee employee)
{
EntityFrameworkSampleDBEntities db = new EntityFrameworkSampleDBEntities();
db.Employees.Add(employee);
db.SaveChanges();
return RedirectToAction("Index");
}
public ActionResult Delete(long id)
{
EntityFrameworkSampleDBEntities db = new EntityFrameworkSampleDBEntities();
Employee employee = db.Employees.Where(temp => temp.EmployeeID == id).FirstOrDefault();
return View(employee);
}

[HttpPost]
public ActionResult Delete(long id, Employee emp)
{
EntityFrameworkSampleDBEntities db = new EntityFrameworkSampleDBEntities();
Employee employee = db.Employees.Where(temp => temp.EmployeeID == id).FirstOrDefault();
db.Employees.Remove(employee);
db.SaveChanges();
return RedirectToAction("Index");
}

public ActionResult Edit(long id)
{
EntityFrameworkSampleDBEntities db = new EntityFrameworkSampleDBEntities();
Employee employee = db.Employees.Where(temp => temp.EmployeeID == id).FirstOrDefault();
return View(employee);
}

[HttpPost]
public ActionResult Edit(Employee emp)
{
EntityFrameworkSampleDBEntities db = new EntityFrameworkSampleDBEntities();
Employee employee = db.Employees.Where(temp => temp.EmployeeID == emp.EmployeeID).FirstOrDefault();
employee.Name = emp.Name;
employee.Designation = emp.Designation;
employee.Salary = emp.Salary;
employee.Contact = emp.Contact;
employee.Email = emp.Email;
employee.City = emp.City;
employee.JoiningDate = emp.JoiningDate;
employee.DeptID = emp.DeptID;
employee.BranchID = emp.BranchID;
db.SaveChanges();
return RedirectToAction("Index");
}
}
}

Layout View (Location : View/Shared/_LayoutPage.cshtml)

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>@ViewBag.Title</title>
<link href="~/Content/bootstrap.css" rel="stylesheet" />
<script src="~/Scripts/jquery-3.6.0.js"></script>
<script src="~/Scripts/umd/popper.js"></script>
<script src="~/Scripts/bootstrap.js"></script>
</head>
<body>
<nav class="navbar navbar-expand-sm bg-success navbar-dark">
<a class="navbar-brand" href="/Home/Default">Enitity Framework in MVC</a>
<div class="col-8">
<ul class="navbar-nav">
<li class="nav-item">
<a href="/Home/Index" class="nav-link">Home</a>
</li>
<li class="nav-item">
<a href="/Branches/Index" class="nav-link">Branch List</a>
</li>
<li class="nav-item">
<a href="/Departments/Index" class="nav-link">Department List</a>
</li>
<li class="nav-item">
<a href="/Employees/Index" class="nav-link">Employee List</a>
</li>
</ul>
</div>
<div class="col-4 text-white">
@ViewBag.Message
</div>
</nav>
<div class="container-fluid">
<div class="row">
@RenderBody()
</div>
</div>
</body>
</html>

Home View , Controller:Home(Location : Home/Default.cshtml)

@{
ViewBag.Title = "Default";
Layout = "~/Views/Shared/_LayoutPage1.cshtml";
}
<div style="padding:50px" class="text-center">
<h3 class="alert alert-danger">
CRUD Operations in ASP.NET MVC using Entity-Framework,
Database MS SQL Server, Bootstrap 5 Framework, Visual Studio
</h3>
<h4 class="alert alert-info">
Select, Insert, Delete,
Update Operations using MVC Entity Framework
</h4>
<blockquote>
<strong>Entity Framework: </strong> It is a data access framework which used to create and test data in the visual studio. It is part of . NET Framework and Visual Studio.
</blockquote>
<div class="row">
<div class="col-6">
<div class="figure">
<img src="~/img/web2.png" style="width:70%" />
</div>
</div>
<div class="col-6">
<h3 class="alert alert-primary">What you will learn in Entity Framework</h3>
<div class="list-group" style="text-align:left!important">
<a href="#" class="list-group-item">Bootstrap 5</a>
<a href="#" class="list-group-item">Select Operation (Reteriving all rows from database, condition based select)</a>
<a href="#" class="list-group-item">Search Operation</a>
<a href="#" class="list-group-item">Insert Operation</a>
<a href="#" class="list-group-item">Update Operation</a>
<a href="#" class="list-group-item">Delete Operation</a>
<a href="#" class="list-group-item">Navigation Properties</a>
<a href="#" class="list-group-item">Dropdown List (Static and Dynamic)</a>
<a href="#" class="list-group-item">Sorting</a>
<a href="#" class="list-group-item">Paging</a>
<a href="#" class="list-group-item">Saving Images</a>
</div>
</div>
</div>
<div class="row"><br />
<h1>Sample Code Link: <a href="https://qaisturk.blogspot.com/2022/06/aspnet-mvc-5-entity-framework-crud.html" target="_blank">
<br/>
https://qaisturk.blogspot.com/
</a> </h1>
</div>
</div>

Branches Index View, Controller: Branches (Location : Branches/Index.cshtml)

@model List<EFDBFIrstApproach.Models.Branch>
@{
ViewBag.Title = "Index";
Layout = "~/Views/Shared/_LayoutPage1.cshtml";
}
<h2 class="alert alert-danger">Branch List</h2>
<div style="padding:50px">
<table class="table">
<thead>
<tr>
<th>Branch Id</th>
<th>Branch Name</th>
<th>Location</th>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>@item.BranchID</td>
<td>@item.BranchName</td>
<td>@item.Location</td>
</tr>
}
</tbody>
</table>
</div>

Department Index View, Controller: Department (Location : Department /Index.cshtml)

@model List<EFDBFIrstApproach.Models.Department>
@{
ViewBag.Title = "Index";
Layout = "~/Views/Shared/_LayoutPage1.cshtml";
}
<h2 class="alert alert-primary">Departments List</h2>
<div style="padding:50px">
<table class="table">
<thead>
<tr>
<th>Department Id</th>
<th>Department Name</th>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>@item.DeptID</td>
<td>@item.DeptName</td>
</tr>
}
</tbody>
</table>
</div>

Employees Index View, Controller: Employees (Location : Employees /Index.cshtml)

@model List<EFDBFIrstApproach.Models.Employee>
@{
ViewBag.Title = "Index";
Layout = "~/Views/Shared/_LayoutPage1.cshtml";
}
<h2 class="alert alert-success">Employees List</h2>
<div style="padding:5px; background-color:coral">
<form action="/Employees/Index" method="post">
<div class="row">
<div class="col-md-2">
 
</div>
<div class="col-md-2">Search Employee</div>
<div class="col-md-2">
<input type="search" name="search" id="search" value="@ViewBag.MySearch" placeholder="Employee Name" />
</div>
<div class="col-md-2">
<button type="submit">Submit</button>
</div>
</div>
</form>
</div>
<div class="row" style="text-align:right; padding-top:20px; padding-right:20px">
<a href="/Employees/Create" class="#">Add Employee</a>
</div>
<div style="padding:50px">
<table class="table" style="font-size:small">
<thead>
<tr>
<th>Employee ID</th>
<th>Employee Name</th>
<th>Designation</th>
<th>Conatact No</th>
<th>Email Id</th>
<td>City</td>
<th>Salary</th>
<th>Date of Joining</th>
<th>Department Id</th>
<th>Branch Id</th>
<th>Action</th>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>@item.EmployeeID</td>
<td>@item.Name</td>
<td>@item.Designation</td>
<td>@item.Contact</td>
<td>@item.Email</td>
<td>@item.City</td>
<td>@item.Salary.Value.ToString("C")</td>
<td>@item.JoiningDate.Value.ToString("dd-MM-yyyy")</td>
<td>@item.DeptID</td>
<td>@item.BranchID</td>
<td>
<span> <a href="/Employees/Details/@item.EmployeeID">Details</a></span>
<span>| <a href="/Employees/Delete/@item.EmployeeID">Delete</a> |</span>
<a href="/Employees/Edit/@item.EmployeeID">Edit</a>
</td>
</tr>
}
</tbody>
</table>
</div>

Employees Create View, Controller: Employees (Location: Employees /Create.cshtml)

ViewBag.Title = "Create";

Layout = "~/Views/Shared/_LayoutPage1.cshtml";

}

<h2 class="alert alert-danger">Add Employee</h2>

<label for="EmployeeName">Employee Name</label>

</div>

<label for="Designation">Designation</label>

</div>

<label for="Contactno">Contact No</label>

</div>

<label for="emailid">Email Id</label>

</div>

</div>

<label for="Gender">Salary</label>

</div>

<label for="joining">Joining Date</label>

</div>

<label for="DepartmentId">Department Id</label>

</div>

<label for="Branchid">Branch Id</label>

</div>

</div>

<a href="/Employees/Index" class="btn btn-danger btn-lg">Cancel</a>

</div>

</form>

</div>

Employees Delete View, Controller: Employees (Location: Employees /Delete.cshtml)

@model EFDBFIrstApproach.Models.Employee
@{
ViewBag.Title = "Delete";
Layout = "~/Views/Shared/_LayoutPage1.cshtml";
}
<h2 class="alert alert-danger">Delete</h2>
<div style="padding:50px">
<form action="/Employees/Delete/@Model.EmployeeID" method="post">
<div class="row">
<div class="col-md-10">
<table class="table">
<thead>
<tr>
<th>Employee Id</th>
<td>@Model.EmployeeID</td>
</tr>
<tr>
<th>Employee Name</th>
<td>@Model.Name</td>
</tr>
</thead>
</table>
<h4 class="text-danger">Are you sure! To Delete</h4>
<div class="row">
<div class="col-2"> <button type="submit" class="btn btn-danger">Delete</button></div>
<div class="col-2">
<a href="/Employees/Index" class="btn btn-success">Cancel</a>
</div>
</div>
</div>
</div>
</form>
</div>

Employees Details View, Controller: Employees (Location: Employees /Details.cshtml)

@model EFDBFIrstApproach.Models.Employee
@{
ViewBag.Title = "Details";
Layout = "~/Views/Shared/_LayoutPage1.cshtml";
}
<h2 class="alert alert-danger">Employess Details</h2>
<div style="padding:50px">
<table class="table">
<thead>
<tr>
<th colspan="2"><span class="text-center text-danger">Employee Deatils: @Model.Name</span></th>
</tr>
</thead>
<tr>
<th>Employee Id</th>
<td>@Model.EmployeeID</td>
</tr>
<tr>
<th>Employee Name</th>
<td>@Model.Name</td>
</tr>
<tr>
<th>Designation</th>
<td>@Model.Designation</td>
</tr>
<tr>
<th>Salary</th>
<td>@Model.Salary.Value.ToString("C")</td>
</tr>
<tr>
<th>Contact No</th>
<td>@Model.Contact</td>
</tr>
<tr>
<th>Email Id</th>
<td>@Model.Email</td>
</tr>
<tr>
<th>City</th>
<td>@Model.City</td>
</tr>
<tr>
<th>Joining Date</th>
<td>@Model.JoiningDate</td>
</tr>
<tr>
<th>Department Id</th>
<td>@Model.DeptID</td>
</tr>
<tr>
<th>Branch ID</th>
<td>@Model.BranchID</td>
</tr>
</table>
<div class="clearfix"> </div>
<div class="row" style="margin-left:300px">
<div class="col-md-3">
<a href="/Employees/Index" class="btn btn-danger btn-lg">Back</a>
</div>
</div>
</div>

Employees Edit View, Controller: Employees (Location: Employees /Edit.cshtml)

@model EFDBFIrstApproach.Models.Employee
@{
ViewBag.Title = "Edit";
Layout = "~/Views/Shared/_LayoutPage1.cshtml";
}
<h2 class="alert alert-primary">Edit</h2>
<div style="padding:50px">
<form action="/Employees/Edit/@Model.EmployeeID" method="post">
<div class="row">
<div class="col-md-6 form-group">
<input type="hidden" name="EmployeeID" value="@Model.EmployeeID" />
<label for="EmployeeName">Employee Name</label>
<input type="text" class="form-control" name="Name" value="@Model.Name" placeholder="Employee Name" />
</div>
<div class="col-md-6 form-group">
<label for="Designation">Designation</label>
<input type="text" class="form-control" name="Designation" value="@Model.Designation" placeholder="Designation" />
</div>
</div>
<div class="row">
<div class="col-md-6 form-group">
<label for="Contactno">Contact No</label>
<input type="number" class="form-control" name="Contact" value="@Model.Contact" placeholder="ContactNo" />
</div>
<div class="col-md-6 form-group">
<label for="emailid">Email Id</label>
<input type="email" class="form-control" name="Email" value="@Model.Email" placeholder="Email Id" />
</div>
</div>
<div class="row">
<div class="col-md-6 form-group">
<label for="City">City</label>
<input type="text" class="form-control" name="City" value="@Model.City" placeholder="City" />
</div>
<div class="col-md-6 form-group">
<label for="Salary">Salary</label>
<input type="number" class="form-control" name="Salary" value="@Model.Salary" placeholder="Salary" />
</div>
</div>
<div class="row">
<div class="col-md-6 form-group">
<label for="DepartmentId">Department Id</label>
<input type="number" class="form-control" name="DeptID" value="@Model.DeptID" placeholder="Department Id" />
</div>
<div class="col-md-6 form-group">
<label for="Branchid">Branch Id</label>
<input type="number" class="form-control" name="BranchID" value=@Model.BranchID placeholder="Branch Id" />
</div>
</div>
<div class="row">
<div class="col-md-6 form-group">
<label for="joining">Joining Date</label>
@{
string val = null;
if (Model.JoiningDate != null)
{
val = Model.JoiningDate.Value.ToString("yyyy-MM-dd");
}
}

<div class="row" style="margin-left:300px">
<div class="col-md-3">
<button type="submit" class="btn btn-success btn-lg">Update</button>
</div>
<div class="col-md-3">
<a href="/Employees/Index" class="btn btn-danger btn-lg">Cancel</a>
</div>
</div>
</form>
</div>

Programming with Qais Pasha

Search This Blog

Asp.net MVC 5 Entity Framework :- CRUD Operations Sample Code

Labels

Comments

Post a Comment