MVC Data Access Using Entity Framework and HTML Helpers

Look at the following procedure.

Step 1

Create a database with some sample data using the following SQL script.

CREATE DATABASE MVC;

USE MVC;

CREATE TABLE dbo.Students(ID int, Name varchar(50), Gender varchar(6), Fees int);

INSERT INTO dbo.Students
VALUES(1, 'Harry', 'Male', 2500);

INSERT INTO dbo.Students
VALUES(2, 'Jane', 'Female', 2400);

INSERT INTO dbo.Students
VALUES(3, 'Emma', 'Female', 2100);

INSERT INTO dbo.Students
VALUES(4, 'Roster', 'Male', 2500);

INSERT INTO dbo.Students
VALUES(5, 'Chris', 'Male', 2900);

INSERT INTO dbo.Students
VALUES(6, 'Evan', 'Male', 2200);

INSERT INTO dbo.Students
VALUES(7, 'Cathlie', 'Female', 2550);

INSERT INTO dbo.Students
VALUES(8, 'Jack', 'Male', 2500);

INSERT INTO dbo.Students
VALUES(9, 'Jone', 'Male', 2900);

INSERT INTO dbo.Students
VALUES(10, 'Videra', 'Female', 2550);

INSERT INTO dbo.Students
VALUES(11, 'Sara', 'Female', 2900);

INSERT INTO dbo.Students
VALUES(12, 'Mak', 'Male', 2500);

INSERT INTO dbo.Students
VALUES(13, 'Max', 'Male', 2550);

INSERT INTO dbo.Students
VALUES(14, 'Brock', 'Male', 2900);

INSERT INTO dbo.Students
VALUES(15, 'Eddie', 'Male', 2500);

INSERT INTO dbo.Students
VALUES(16, 'Edna', 'Female', 2500);

Step 2

Create a new MVC Project in Visual Studio and name it "MVCDataAccessByEntityFrame".





Step 3

Install Entity Framework using Nuget package manager from Solution Explorer into your project.





Step 4

Go to the Models folder in the Solution Explorer and add two class files.



Step 5

Copy the following code to the preceding created class files.

Students.cs

using System.ComponentModel.DataAnnotations.Schema;

namespace MVCDataAccessByEntityFrame.Models
{
    [Table("Students")]
    public class Students
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public int Fees { get; set; }
    }
}

StudentsContext.cs

using System.Data.Entity;
namespace MVCDataAccessByEntityFrame.Models
{
    public class StudentsContext : DbContext
    {
        public DbSet<Students> Students { get; set; }
    }
}

Step 6

Go to the Controllers folder and add a controller to it.



Step 7

Add the following code to the controller.

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

namespace MVCDataAccessByEntityFrame.Controllers
{
    public class StudentsController : Controller
    {
        public ActionResult Index()
        {
            StudentsContext studentsContext = new StudentsContext();
            List<Students> students = studentsContext.Students.ToList();
            return View(students);
        }
        public ActionResult Details(int id)
        {
            StudentsContext studentsContext = new StudentsContext();
            Students students = studentsContext.Students.Single(stu => stu.ID == id);
            return View(students);
        }

    }
}

Step 8

Press Ctrl + Shift + B and then right-click on the index method and add a view.



Step 9

Add the following code to the Index.cshtml.

@model IEnumerable<MVCDataAccessByEntityFrame.Models.Students>
@using MVCDataAccessByEntityFrame.Models;
@{
    ViewBag.Title = "Students List";
}

<h2>Students List</h2>
<ol start="1">
    @foreach (Students students in @Model)
    {
        <li id="item">
            @Html.ActionLink(students.Name, "Details", new { id = students.ID })
        </li>
    }
</ol>

Step 10

Right-click on the Details method and add a view again.



Step 11

Add the following code to details.cshtml.

@model MVCDataAccessByEntityFrame.Models.Students

@{
    ViewBag.Title = "Students Details";
}

<table border="1">
    <tr>
        <td><b>ID:</b></td>
        <td>
            @Model.ID
        </td>
    </tr>
    <tr>
        <td><b>Name:</b></td>
        <td>@Model.Name</td>
    </tr>
    <tr>
        <td><b>Gender:</b></td>
        <td>@Model.Gender</td>
    </tr>
    <tr>
        <td><b>Fees:</b></td>
        <td>@Model.Fees</td>
    </tr>
</table>
<br />
@Html.ActionLink("Back to Students List", "Index")

Step 12

Go to the web.config file in the Views folder in Solution Explorer and add the following code.

<connectionStrings>
  <add name="StudentsContext" connectionString="server=ANKITBANSALPC; database = MVC; integrated security = SSPI" providerName="System.Data.SqlClient"/>
</connectionStrings>

Step 13

Go to the Route.config file in the App_Start folder and paste in the following code.

using System.Web.Mvc;
using System.Web.Routing;

namespace MVCDataAccessByEntityFrame
{
    public class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "Students", action = "Index", id = UrlParameter.Optional }
            );
        }
    }
}

Step 14

Save all the changes and Press F5 to run the project and you will see a list of Students and on clicking a specific student name, you will see the details of that student.





Explanation

Students.cs

• Add some public properties that correspond to database columns.
  
  
• Sometimes the table name in the database and the class file name are not same, so to explicitly declare the table name we use the Table attribute at the top because MVC by default thinks that the name of the class file is the name of the table of the database.

StudentsContext.cs

• We first inherit the DBContext class that is a part of Entity Framework. This class doed all the database connectivity without having to write the ADO.NET code.
  
  
• We are storing the data of the students table in the Dbset collection since it stores all the retrieved data from the table.
  
  
• This file finds the Students class that we created earlier and matches all the properties defined there.
  
  
• Hence the mapping of the table and the class properties is done.

StudentsController.cs

• Now we create two methods, Index and Details. Index shows the list of data and Details shows the data of a specific student based on its ID that we pass as a parameter to the Details method.
  
  
• For the index method, we create the instance of the StudentsContext class and use its property Students that contains the list of students and saved this data in an instance of List<Students> interface.
  
  Finally, we pass this instance to the view for rendering.
  
  
• For the details method, we again created an instance of the StudentContext class and use its property Students that contains the list of students and using a LINQ query Single function, we fetch the data of the student by passing its id as a parameter. Since this returns a Students class, we created the instance of the Students class and pass its instance to the view for rendering.

Index.cshtml

• At first, we make the namespace as IEnumerable since it can store a list of data.
  
  
• We iterate through each student using the foreach loop and then added each student item to the <li> tag. This will populate the list of students for us.

Details.cshtml

• We simply used the properties of model to populate the data in our table. Simple HTML is used. Web.config.
  
  
• We add a connection string to connect to the database. Notice that the name of the connection string should be the same as that of the StudentsContext file since by default, MVC searches connection strings by the same name.

Route.config

• Just change the name of controller to Students from Home so that when our page loads, it directly calls the Index action method.

Download Source code here.

See this article in my own blog: debugsolutions and technewsinform.