Entity Framework Database first approach to Read/Insert/Update/Delete the database data in Asp.Net MVC & C#


Tarun Kumar Chatterjee
.Net – Technology Specialist
Published On :   16 May 2016
Visit Count
Today :  1    Total :   5011
Plan, Migrate, Secure, Report
SharePoint & Office 365 Tool. Simple & Easy to Use. 15-Day Trial!

SharePoint Office 365 Tool
Simple & Powerful Tool for Migration, Security & Reporting. Free Trial


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.

clip_image002

Click on Next & then new connection

Select the server name & database name where we have created all the tables & procedures.

clip_image004

Click on Ok & then Next

Select the tables and procedures you wanted to include into the model

clip_image006

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:

clip_image008

Edit View output:

clip_image010

Delete View output:

clip_image012

Create New View output:

clip_image014

Happy Coding

Tarun Kumar Chatterjee

SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Categories

Migratiin Tools for SharePoint