Data Access using Entity Framework in MVC

This is What we want to Achieve

Tables Used

select * from employeeMaster

select * from employeeLocationDetails

Step 1 : Create new MVC Project

Step 2 : Change the Name, Location of Project

Step 3 : Choose Empty MVC Application

Step 4 : This is going to be the structure of our Application

Step 5 : Install the entity framework

                        

Step 6 :  Create connection with underlying Database in Web.config file

  <connectionStrings>

    <add name="DataLayerBase" connectionString="server=.;database=employee;integrated security=SSPI" providerName="System.Data.SqlClient" />

  </connectionStrings>

  <entityFramework>

Step 7 : Create Employees class and Location class in Model

In Employee.cs

using System;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

using System.Linq;

using System.Web;

namespace MVCExample.Models

{

    [Table("employeeMaster")]

    public class Employee

    {

        [Key]

        public int EmpID { get; set; }

        public string EmpName { get; set; }

        public int Salary { get; set; }

        public int Locationid { get; set; }

    }

}

In Location.cs

using System;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

using System.Linq;

using System.Web;

namespace MVCExample.Models

{

    [Table("employeeLocationDetails")]

    public class Location

    {

        [Key]

        public int LocationId { get; set; }

        public string LocationName { get; set; }

    }

}

Step 8 : Create DataLayerBase class in Model folder which will be used for accessing data from database. connectionStrings name must match with this class name.

In DataLayerBase.cs put the following code

using System;

using System.Collections.Generic;

using System.Data.Entity;

using System.Linq;

using System.Web;

namespace MVCExample.Models

{

    public class DataLayerBase : DbContext

    {

        public DbSet<Employee> Employees { get; set; }

        public DbSet<Location> Location { get; set; }

    }

}

Step 9 : Create EmployeeLocation Controller

In EmployeeLocationController.cs

using MVCExample.Models;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

namespace MVCExample.Controllers

{

    public class EmployeeLocationController : Controller

    {

        //

        // GET: /EmployeeLocation/

        public ActionResult EmployeeLocation()

        {

            DataLayerBase dataLayerBase = new DataLayerBase();

            List<Location> Location = dataLayerBase.Location.ToList();

            return View(Location);

        }

       }

}

Step 10 : Create EmployeeLocation View of the EmpoyeeLocationController

In EmployeeLocation.cshtml

@model IEnumerable<MVCExample.Models.Location>

@using MVCExample.Models;

@{

    ViewBag.Title = "EmployeeLocation";

}

<h2>Employee Location</h2>

<ul>

    @foreach (Location location in @Model)

    {

        <li>

            @Html.ActionLink(location.LocationName, "EmployeeNameWithID", "Employee", new { id = location.LocationId }, null)

        </li>

    }

</ul>

Step 11 : Create Employee Controller and create EmployeeNameWithID method returning a View

In EmployeeController.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using MVCApplication.Models;

using System.Reflection;

namespace MVCApplication.Controllers

{

    public class EmployeeController : Controller

    {

        [ActionName("EmployeeNameWithID")]

        public ActionResult EmployeeName(int id)

        {

            DataLayerBase dataLayerBase = new DataLayerBase();

            List<Employee> employee = dataLayerBase.Employees.Where(emp => emp.Locationid ==    id).ToList();

            return View(employee);

        }

       }

}

Step 12 : Create EmployeeNameWithID View of Employee Controller

In EmployeeNameWithID.cshtml

@model IEnumerable<MVCExample.Models.Employee>

@using MVCExample.Models;

@{

    ViewBag.Title = "EmployeeName";

}

<h2>Employee List</h2>

<ul>

    @foreach (Employee employee in @Model)

    {

        <li>

            @Html.ActionLink(employee.EmpName, "EmployeeDetails", new { id = employee.EmpID })

        </li>

    }

</ul>

@Html.ActionLink("Back to Location List", "EmployeeLocation", "EmployeeLocation")

Step 13 : Add the following code in Employee Controller

        //

        // GET: /Employee/

        public ActionResult EmployeeDetails(int id)

        {

            DataLayerBase dataLayerBase = new DataLayerBase();

            Employee employee = dataLayerBase.Employees.Single(emp => emp.EmpID == id);

            return View(employee);

        }

Step 14 : Create EmployeeDetails View of Employee Controller

In EmployeeDetails.cshtml

@model MVCExample.Models.Employee

@{

    ViewBag.Title = "Employee Details";

}

<h2>Employee Details</h2>

<table>

    <tr>

        <td>

            <b>Employee ID :</b>

        </td>

        <td>

            @Model.EmpID

        </td>

    </tr>

    <tr>

        <td>

            <b>Employee Name :</b>

        </td>

        <td>

            @Model.EmpName

        </td>

    </tr>

    <tr>

        <td>

            <b>Salary :</b>

        </td>

        <td>

            @Model.Salary

        </td>

    </tr>

</table>

@Html.ActionLink("Back to Employee List", "EmployeeNameWithID", new { id = @Model.Locationid })

Step 15 : Complete Code of Employee Controller

using MVCExample.Models;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

namespace MVCExample.Controllers

{

    public class EmployeeController : Controller

    {

        //

        // GET: /Employee/

        public ActionResult EmployeeDetails(int id)

        {

            DataLayerBase dataLayerBase = new DataLayerBase();

            Employee employee = dataLayerBase.Employees.Single(emp => emp.EmpID == id);

            return View(employee);

        }

        public ActionResult EmployeeName()

        {

            DataLayerBase dataLayerBase = new DataLayerBase();

            List<Employee> employee = dataLayerBase.Employees.ToList();

            return View(employee);

        }

        [ActionName("EmployeeNameWithID")]

        public ActionResult EmployeeName(int id)

        {

            DataLayerBase dataLayerBase = new DataLayerBase();

            List<Employee> employee = dataLayerBase.Employees.Where(emp => emp.Locationid == id).ToList();

            return View(employee);

        }

       }

}

Step 16 : Create EmployeeName View of Employee Controller

In EmployeeName.cshtml

@model IEnumerable<MVCExample.Models.Employee>

@using MVCExample.Models;

@{

    ViewBag.Title = "EmployeeName";

}

<h2>Employee List</h2>

<ul>

    @foreach (Employee employee in @Model)

    {

        <li>

            @Html.ActionLink(employee.EmpName, "EmployeeDetails", new { id = employee.EmpID })

        </li>

    }

</ul>

Step 17 : In the global.asax.cs make the following change

using System;

using System.Collections.Generic;

using System.Data.Entity;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using System.Web.Routing;

namespace MVCExample

{

    public class MvcApplication : System.Web.HttpApplication

    {

        protected void Application_Start()

        {

            Database.SetInitializer<MVCExample.Models.DataLayerBase>(null);

            AreaRegistration.RegisterAllAreas();

            RouteConfig.RegisterRoutes(RouteTable.Routes);

        }

    }

}

  

Step 18 : Run the Application