Fetch data from database in MVC using Entity framework and DB Context

Entity framework is the replacement for ADO.net, It will usually connect database and make all the transactions. To achieve this, we need to add entity framework reference to the solution from Nuget packages.

Let’s start an example that fetches the data from “EmpoyeeDetails” table in my database using Code first Approch

EmployeeDetails
1.      Create new project.

2.      Add Entity Framework reference from NuGet package manager.

3.      Create new class in model [that should be the table structure]


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations.Schema;

namespace ViewInGrid.Models
{
    [Table("EmployeeDetails")]
    public class Employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
    }

    public class EmployeeContext : DbContext
    {
       public DbSet<Employee> employee { get; set; }
    }
}


4.      Now Add Connection string in the web.config. connection string name should be matches with the given context [in this Example EmployeeContext ]

<connectionStrings>
    <add name="EmployeeContext" connectionString="Data Source=3.52.207.14; UID=myusername; Password=mypassword; Database=myDB" providerName="System.Data.SqlClient"/>
  </connectionStrings>


5.      Open Global.asax.cs class and add the additional namespace of Entity FrameWork and initialize the database as below.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;
using System.Data.Entity;
using ViewInGrid.Models;

namespace ViewInGrid
{
    public class MvcApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {
            Database.SetInitializer<EmployeeContext>(null);
            AreaRegistration.RegisterAllAreas();
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);
        }
    }
}


6.      Now RIghtClick on Controller folder and add new controller. And add model reference in namespace section.

a.      From controller, we are returning the view with the list of employees as below.

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

namespace ViewInGrid.Controllers
{
    public class EmployeeController : Controller
    {
        public ActionResult Index()
        {
            EmployeeContext context = new EmployeeContext();
            List<Employee> employeeList = context.employee.ToList();
            return View(employeeList);
        }
    }
}


7.     Now RightClick on the controller name and View strongly typed with Employee Model and EmployeeContext as a context.
   
     Make namespace in the view as IEnumerable for the Models as below.

@model IEnumerable<ViewInGrid.Models.Employee>

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <style>
        .tableDesign {
            border: 1px solid Black;
            padding-left: 80px;
            padding-right: 80px;
        }
    </style>
</head>

<body>
    <div>
        <table>
            <th class="tableDesign">ID</th>
            <th class="tableDesign">Name</th>
            <th class="tableDesign">Gender</th>
            <th class="tableDesign">City</th>

            @foreach (var employee in @Model)
            {
                <tr class="tableDesign">
                    <td class="tableDesign">@employee.ID</td>
                    <td class="tableDesign">@employee.Name</td>
                    <td class="tableDesign">@employee.Gender</td>
                    <td class="tableDesign">@employee.City</td>
                </tr>
            }
        </table>
    </div>
</body>
</html>


8.      Output as below