In my previous article
Models in ASP.NET MVC5, I have hardcoded the Model EmployeeInfo.cs properties in EmployeeinfoController by creating the instance of the EmployeeInfo Model class.
In this article, I am using Entity Framework to retrieve the EmployeeInfo properties data from the database table and render the data to the view. Before reading this article I suggest you go through my article
Models in ASP.NET MVC5 for reference.
First of all, I am creating a table in SQL Server database with the name as "[dbo].[EmployeeDetails_Department]" as shown below.
- GO
-
- /****** Object: Table [dbo].[EmployeeDetails_Department] Script Date: 2019-08-17 3:30:47 PM ******/
- SET ANSI_NULLS ON
- GO
-
- SET QUOTED_IDENTIFIER ON
- GO
-
- CREATE TABLE [dbo].[EmployeeDetails_Department](
- [EmployeeId] [int] NULL,
- [EmployeeName] [varchar](50) NULL,
- [EmployeeDesignation] [varchar](50) NULL,
- [DateOfJoining] [varchar](50) NULL,
- [EmployeeAddress] [varchar](50) NULL,
- [EmployeePassport] [varchar](50) NULL,
- [EmployeePhone] [int] NULL,
- [EmployeeAge] [int] NULL,
- [EmployeeGender] [varchar](10) NULL,
- [City] [varchar](50) NULL,
- [Project] [varchar](50) NULL,
- [EmployeeBloodGroup] [varchar](10) NULL,
- [CompanyName] [varchar](50) NULL,
- [PinCode] [int] NULL,
- [DepartmentId] [int] NULL
- ) ON [PRIMARY]
- GO
After creation of the above table, I have inserted a few records into the table in order to render the data to the HTML table.
Let's create an ASP.NET MVC5 Web Application.
Open Visual Studio 2017 or any version of your choice. In my case, I am using Visual Studio 2017.
Click on File --> New --> Project. Select "Web" from the left window. From the right window, select "ASP.Net Web Application (.NET Framework)". Give some name for the application & click OK. Select the MVC template and click OK.
Once the application is ready, create a Model class with the name as "EmployeeInfo" by adding the properties same as the table's
column names. If any of the property is missing or the property name changes with respect to the table column then the application throws an error with the invalid column name. For the creation of a project or adding Models, Controller class please refer my previous article for reference.
Suppose we use 12 columns in a table, then we need to use the same 12 properties with the same name as table's column names.
In order to create a Model class right-click on Models folder, hover on Add button, and click on the class. From "Add New Item" Window select class template and give the Name as "EmployeeInfo.cs" and click on Add button, with this EmployeeInfo.cs Model class will be created.
- using System;
- using System.Collections.Generic;
- using System.ComponentModel.DataAnnotations;
- using System.ComponentModel.DataAnnotations.Schema;
- using System.Linq;
- using System.Web;
-
- namespace ModelsWithEntityFramework.Models
- {
- [Table("EmployeeDetails_Department")]
- public class EmployeeInfo
- {
- [Key]
- public int EmployeeId { get; set; }
- public string EmployeeName { get; set; }
- public string EmployeeDesignation { get; set; }
- public string DateOfJoining { get; set; }
- public string EmployeeAddress { get; set; }
- public string EmployeePassport { get; set; }
- public int EmployeePhone { get; set; }
- public int EmployeeAge { get; set; }
- public string EmployeeGender { get; set; }
- public string City { get; set; }
- public string Project { get; set; }
- public string EmployeeBloodGroup { get; set; }
- public string CompanyName { get; set; }
- public int PinCode { get; set; }
- public int DepartmentId{ get; set; }
- }
- }
![Models In ASP.NET MVC 5 Using Entity Framework]()
From the above EmployeeInfo Model class, we use Table attribute which is placed on EmployeeInfo class. Here we need to give the database table name as the parameter for the Table attribute with which database table we need to interact.
To add an Entity Framework reference, right-click on References click on Manage Nuget Packages and browse for Entity Framework,
select Entity Framework and click on Install.
With this, the reference will be added to the project's solution.
Right click on Models folder to add EmployeeContext.cs class.
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Web;
- using System.Data.Entity;
-
- namespace ModelsWithEntityFramework.Models
- {
- public class EmployeeContext : DbContext
- {
- public DbSet<EmployeeInfo> dataContext { get; set; }
- }
- }
Here we don't need any ADO.Net code for interacting with the database and retrieving the data from the database table.
Entity Framework internally does it by interacting with the database by using DbSet.
DbSet<EmployeeInfo> maps with the database table column. If any of the EmployeeInfo properties are missing or properties don't
match with the database table columns then it throws an error with the invalid column name.
In web.config use the below code.
- <connectionStrings>
- <add name="EmployeeContext" connectionString="Data Source=KHAJAMOIZ\SSMS17;Initial Catalog=PracticeDB;Integrated Security=True" providerName="System.Data.SqlClient"/>
- </connectionStrings>
In the above ConnectionString we need to define the name of the Context class, in our case we need to define it as EmployeeContext.
In this article, I am using three different scenarios for binding the data to the HTML table.
In the first scenario, I am retrieving all the Employees by interacting with the database.
In the second scenario, I am retrieving the employees who belong to the particular department & In the third scenario, I am retrieving the employee with their employeeId.
In order to do this, I am creating a Controller by right-clicking on Controller's folder, hover on Add and click on Controller,
select MVC5 Controller - Empty template. In Add Controller's window give the Controller Name as EmployeeController and click on Add.
With this, a new EmployeeController is added to the Controller's folder.
Write the below code in EmployeeController.cs,
- using ModelsWithEntityFramework.Models;
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Web;
- using System.Web.Mvc;
-
- namespace ModelsWithEntityFramework.Controllers
- {
- public class EmployeeController : Controller
- {
- private readonly EmployeeContext empContext = new EmployeeContext();
-
- public ActionResult GetEmployeeInfo()
- {
- List<EmployeeInfo> employeeInfos = empContext.dataContext.ToList();
- return View(employeeInfos);
- }
-
- public ActionResult GetEmployeeInfoByDeptId(int id)
- {
- List<EmployeeInfo> employeeByDept = empContext.dataContext.Where(x=>x.DepartmentId== id).ToList();
- return View(employeeByDept);
- }
-
- public ActionResult GetEmployeeDetails(int id)
- {
- EmployeeInfo employeeInfos = empContext.dataContext.SingleOrDefault(x=>x.EmployeeId == id);
- return View(employeeInfos);
- }
- }
- }
In the above code, I am creating an instance of EmployeeContext as private readonly at once.
In the above Controller, I have used three different ActionMethods.
- GetEmployeeInfo: To Retrieve all the Employee records.
- GetEmployeeInfoByDeptId: To retrieve Employees who belong to the particular department.
- GetEmployeeDetails: To retrieve Employee based on EmployeeId.
Based on the above-mentioned scenarios, I am using 3 different ActionMethods. In order to add view, right-click on GetEmployeeInfo Action method, click on Add View and give the names as shown in below image.
With this GetEmployeeInfo.cshtml file will be added under Views folder. Open the GetEmployeeInfo.cshtml file and place the below code.
To add a view, right click on GetEmployeeInfoByDeptId and click on Add View.. and give the names as shown below.
With this GetEmployeeInfoByDepartmentId.cshtml file is added under Views folder. Open the GetEmployeeInfoByDepartmentId.cshtml file and place the following code.
Similarly, to add a view, right click on GetEmployeeDetails Action method and click on Add View.. button and give the names as
shown below.
With this GetEmployeeDetails.cshtml file is added in the Views Folder.
Open the GetEmployeeDetails.cshtml file and place the following code.
Now execute the application by navigating to the Url as http://localhost:54816/Employee/GetEmployeeInfo, the first Action method is
executed and the output is shown below.
For executing GetEmployeeInfoByDeptId Action method navigate the url as localhost:54816/Employee/GetEmployeeInfoByDeptId/1001,
The above url gets all the employee records who belong to the department 1001.The output for the above request is shown below.
Similarly to execute GetEmployeeDetails Action method navigate the URL as localhost:54816/Employee/GetEmployeeDetails/1001.
The above URL gets the employee details with employeeId as 1001 and the output for the above request is shown below.
Thanks for reading this article, hope it helps you.