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.
Let’s first create a database with tables & procedures. Execute the below script to create tables & procedures within the MyEmployeeDB database
USE [MyEmployeeDB]
GO
/****** Object: StoredProcedure [dbo].[GetEmployeeByEmployeeId] Script Date: 1/28/2016 5:17:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetEmployeeByEmployeeId]
-- Add the parameters for the stored procedure here
@EmployeeId int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT [Id]
,[Name]
,[Address]
,[DOB]
FROM [dbo].[Employees]
WHERE [Id] = @EmployeeId
END
GO
/****** Object: StoredProcedure [dbo].[sp_DeleteEmployee] Script Date: 1/28/2016 5:17:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DeleteEmployee]
-- Add the parameters for the stored procedure here
@Id int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DELETE FROM [dbo].[Employees]
WHERE [Id] = @Id;
END
GO
/****** Object: StoredProcedure [dbo].[sp_InsertEmployee] Script Date: 1/28/2016 5:17:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_InsertEmployee]
-- Add the parameters for the stored procedure here
@Name NVARCHAR(MAX),
@Address NVARCHAR(MAX),
@DOB NVARCHAR(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO [dbo].[Employees]([Name],[Address],[DOB])
VALUES(@Name, @Address,@DOB)
SELECT SCOPE_IDENTITY() AS Id
END
GO
/****** Object: StoredProcedure [dbo].[sp_UpdateEmployee] Script Date: 1/28/2016 5:17:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_UpdateEmployee]
-- Add the parameters for the stored procedure here
@Id int,
@Name NVARCHAR(MAX),
@Address NVARCHAR(MAX),
@DOB NVARCHAR(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE [dbo].[Employees]
SET [Name] = @Name,[Address] = @Address, [DOB] = @DOB
WHERE [Id] = @Id;
END
GO
/****** Object: Table [dbo].[Employees] Script Date: 1/28/2016 5:17:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Address] [nvarchar](max) NOT NULL,
[DOB] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_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
SET IDENTITY_INSERT [dbo].[Employees] ON
INSERT [dbo].[Employees] ([Id], [Name], [Address], [DOB]) VALUES (1, N'Tarun1', N'Kolkata1', N'2016-01-01')
INSERT [dbo].[Employees] ([Id], [Name], [Address], [DOB]) VALUES (3, N'Tarun3', N'Kolkata3', N'2016-01-01')
INSERT [dbo].[Employees] ([Id], [Name], [Address], [DOB]) VALUES (4, N'Tarun4', N'Kolkata4', N'2016-01-03')
INSERT [dbo].[Employees] ([Id], [Name], [Address], [DOB]) VALUES (1002, N'Tarun8', N'Kolkata8', N'2016-01-01')
SET IDENTITY_INSERT [dbo].[Employees] OFF
Now, we will be creating the models in Visual studio from the database
Open Visual Studio 2013 and click on New Project.
Select the MVC Project Template and click on OK.
In this section, we will add the ADO.NET Entity Data Model to the application. We will create the generate from database model in here. Use the following procedure.
In the Solution Explorer, right-click on the Models folder and click on ADO.NET Entity Data Model.
Click on Next & then new connection
Select the server name & database name where we have created all the tables & procedures.
Click on Ok & then Next
Select the tables and procedures you wanted to include into the model
Click on Finish
It will take some times to create the entity model under the solution
Below is the code generated automatically in EmployeeModel.Context.cs
//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace EntityFrameworkDemo.Models
{
using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Core.Objects;
using System.Linq;
public partial class MyEmployeeDBEntities : DbContext
{
public MyEmployeeDBEntities()
: base("name=MyEmployeeDBEntities")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
throw new UnintentionalCodeFirstException();
}
public DbSet<Employee> Employees { get; set; }
public virtual ObjectResult<GetEmployeeByEmployeeId_Result> GetEmployeeByEmployeeId(Nullable<int> employeeId)
{
var employeeIdParameter = employeeId.HasValue ?
new ObjectParameter("EmployeeId", employeeId) :
new ObjectParameter("EmployeeId", typeof(int));
return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<GetEmployeeByEmployeeId_Result>("GetEmployeeByEmployeeId", employeeIdParameter);
}
public virtual int sp_DeleteEmployee(Nullable<int> id)
{
var idParameter = id.HasValue ?
new ObjectParameter("Id", id) :
new ObjectParameter("Id", typeof(int));
return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("sp_DeleteEmployee", idParameter);
}
public virtual ObjectResult<Nullable<decimal>> sp_InsertEmployee(string name, string address, string dOB)
{
var nameParameter = name != null ?
new ObjectParameter("Name", name) :
new ObjectParameter("Name", typeof(string));
var addressParameter = address != null ?
new ObjectParameter("Address", address) :
new ObjectParameter("Address", typeof(string));
var dOBParameter = dOB != null ?
new ObjectParameter("DOB", dOB) :
new ObjectParameter("DOB", typeof(string));
return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Nullable<decimal>>("sp_InsertEmployee", nameParameter, addressParameter, dOBParameter);
}
public virtual int sp_UpdateEmployee(Nullable<int> id, string name, string address, string dOB)
{
var idParameter = id.HasValue ?
new ObjectParameter("Id", id) :
new ObjectParameter("Id", typeof(int));
var nameParameter = name != null ?
new ObjectParameter("Name", name) :
new ObjectParameter("Name", typeof(string));
var addressParameter = address != null ?
new ObjectParameter("Address", address) :
new ObjectParameter("Address", typeof(string));
var dOBParameter = dOB != null ?
new ObjectParameter("DOB", dOB) :
new ObjectParameter("DOB", typeof(string));
return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("sp_UpdateEmployee", idParameter, nameParameter, addressParameter, dOBParameter);
}
}
}
Now, within the Models folder we will be creating a custom business class named as EmployeeBusinessLayer
public class EmployeeBusinessLayer
{
int num = 0;
public int EditEmployee(Employee emp)
{
MyEmployeeDBEntities empDal = new MyEmployeeDBEntities();
num = empDal.sp_UpdateEmployee(emp.Id, emp.Name, emp.Address, emp.DOB);
return num;
}
public ObjectResult<Nullable<decimal>> InsertEmployee(Employee emp)
{
ObjectResult<Nullable<decimal>> objResult = null;
MyEmployeeDBEntities empDal = new MyEmployeeDBEntities();
objResult = empDal.sp_InsertEmployee(emp.Name, emp.Address, emp.DOB);
return objResult;
}
public int DeleteEmployee(int empID)
{
MyEmployeeDBEntities empDal = new MyEmployeeDBEntities();
num = empDal.sp_DeleteEmployee(empID);
return num;
}
public List<Employee> GetEmployee()
{
MyEmployeeDBEntities empDal = new MyEmployeeDBEntities();
return empDal.Employees.ToList();
}
public ObjectResult<GetEmployeeByEmployeeId_Result> GetEmployeeByEmployeeID(int empID)
{
MyEmployeeDBEntities empDal = new MyEmployeeDBEntities();
return empDal.GetEmployeeByEmployeeId(empID);
}
}
Next we will be modifying the Controller
public class HomeController : Controller
{
EmployeeBusinessLayer db = new EmployeeBusinessLayer();
public ActionResult Index()
{
return View(db.GetEmployee());
}
public ActionResult Create()
{
return View();
}
[HttpPost]
public ActionResult Create(Employee emp)
{
try
{
db.InsertEmployee(emp);
return RedirectToAction("Index");
}
catch
{
return View();
}
}
public ActionResult Edit(int id)
{
Employee emp = new Employee();
List<GetEmployeeByEmployeeId_Result> result = db.GetEmployeeByEmployeeID(id).ToList();
emp.Id = result[0].Id;
emp.Name = result[0].Name;
emp.Address = result[0].Address;
emp.DOB = result[0].DOB;
return View(emp);
}
[HttpPost]
public ActionResult Edit(int id, Employee emp)
{
try
{
db.EditEmployee(emp);
return RedirectToAction("Index");
}
catch
{
return View();
}
}
public ActionResult Delete(int id)
{
Employee emp = new Employee();
List<GetEmployeeByEmployeeId_Result> result = db.GetEmployeeByEmployeeID(id).ToList();
emp.Id = result[0].Id;
emp.Name = result[0].Name;
emp.Address = result[0].Address;
emp.DOB = result[0].DOB;
return View(emp);
}
[HttpPost]
public ActionResult Delete(int id, Employee emp)
{
try
{
db.DeleteEmployee(id);
return RedirectToAction("Index");
}
catch
{
return View();
}
}
}
Here are my Views code.
Index View code:
@model IEnumerable<EntityFrameworkDemo.Models.Employee>
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table border="1" width="50%">
<tr>
<th width="40%"></th>
<th width="20%">
Name
</th>
<th width="20%">
Address
</th>
<th width="20%">
DOB
</th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.Id }) |
@Html.ActionLink("Delete", "Delete", new { id=item.Id })
</td>
<td>
@item.Name
</td>
<td>
@item.Address
</td>
<td>
@item.DOB
</td>
</tr>
}
</table>
Edit View code:
@model EntityFrameworkDemo.Models.Employee
@{
ViewBag.Title = "Edit";
}
<h2>Edit</h2>
<script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script>
@using (Html.BeginForm()) {
@Html.ValidationSummary(true)
<fieldset>
<legend>Employee</legend>
@Html.HiddenFor(model => model.Id)
<div class="editor-label">
@Html.LabelFor(model => model.Name)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Name)
@Html.ValidationMessageFor(model => model.Name)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Address)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Address)
@Html.ValidationMessageFor(model => model.Address)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.DOB)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.DOB)
@Html.ValidationMessageFor(model => model.DOB)
</div>
<p>
<input type="submit" value="Save" />
</p>
</fieldset>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
Delete View code:
@model EntityFrameworkDemo.Models.Employee
@{
ViewBag.Title = "Delete";
}
<h2>Delete</h2>
<h3>Are you sure you want to delete this?</h3>
<fieldset>
<legend>Employee</legend>
<div class="display-label">Name</div>
<div class="display-field">@Model.Name</div>
<div class="display-label">Address</div>
<div class="display-field">@Model.Address</div>
</fieldset>
@using (Html.BeginForm()) {
<p>
<input type="submit" value="Delete" /> |
@Html.ActionLink("Back to List", "Index")
</p>
}
Create View code:
@model EntityFrameworkDemo.Models.Employee
@{
ViewBag.Title = "Create";
}
<h2>Create</h2>
<script src="~/Scripts/jquery-1.4.1.min.js"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script>
@using (Html.BeginForm()) {
@Html.ValidationSummary(true)
<fieldset>
<legend>Employee</legend>
<div class="editor-label">
@Html.LabelFor(model => model.Name)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Name)
@Html.ValidationMessageFor(model => model.Name)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Address)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Address)
@Html.ValidationMessageFor(model => model.Address)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.DOB)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.DOB)
@Html.ValidationMessageFor(model => model.DOB)
</div>
<p>
<input type="submit" value="Create" />
</p>
</fieldset>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
Build & run the solution, the output will be looking like:
Edit View output:
Delete View output:
Create New View output:
Happy Coding
Tarun Kumar Chatterjee
Leave a comment