MVC Data access using Entity Framework and HTML Helpers

Download Source code here..
Description: In this tutorial, we will learn how to retrieve data from SQL Server table using Entity Framework and also to create hyperlinks using MVC provided HTML helpers.
Step 1: Create a database with some sample data from the below 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 as ‘MVCDataAccessByEntityFrame’.
Step 3: Install entity framework using nuget package manager from solution explorer to your project.
Step 4: Go to Models folder in Solution explorer and add two class files.
Step 5: Copy the following code in the above 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 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 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 web.config file in Views folder in solution explorer and add the below code.
  <connectionStrings>
    <add name=StudentsContext connectionString=server=ANKITBANSALPC; database = MVC; integrated security = SSPI providerName=System.Data.SqlClient/>
  </connectionStrings>
Step 13: Go to Route.config file in the App_Start folder and Paste 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 on particular student name, you will see the details of that student.
Explanation:
Students.cs
  • Add some public properties that corresponds to database columns.
  • Sometimes, if table name in database and class file name are not same, so to explicitly declare the table name we use Table attribute on the top because MVC by default thinks that name of class file is the name of table of database.
StudentsContext.cs
  • We first inherit DBContext class which is a part of entity framework. This class do all the database connectivity without having to write the ado.net code.
  • We are storing the data of students table in Dbset collection as it stores all the retrieved data from table.
  • This file finds the Students class which we created earlier and matches all the properties defined there.
  • Hence mapping of table and class properties is completed.
StudentsController.cs
  • Now we create two methods. One is Index and other is Details. Index is used to show the list of data and Details is used to show the data of a particular student based on its ID which we pass as a parameter to the Details method.
  • For index method, we create the instance of the StudentsContext class and use its property Students which 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 details method, we again created instance of StudentContext class and use its property Students which contains the list of students and using LINQ query Single function, we fetch the data of the student by passing its id as a parameter. As this returns us Students class, we created the instance of Students class and pass its instance to the view for rendering.
Index.cshtml
  • At first, we make the namespace as IEnumerable as it can store list of Data.
  • We iterate through each student using the foreach loop and then adding each student item in the <li> tag. This will populate the list of students for us.
Details.cshtml
  • We simply used the properties of model to populate data in our table. Simple HTML is used.
Web.config
  • We add a connection string to connect to database. Notice that the name of the connection string should be the same as that of StudentsContext file as by default, MVC search 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.
If you find the above tutorial useful, then follow me on Facebook, Twitter.
See this article on my own Blogs: technewsinform
Advertisements

2 thoughts on “MVC Data access using Entity Framework and HTML Helpers

  1. […] to MVC Data Access using Entity Framework and HTML Helpers. Please read it at technewsinform OR debugsolutions OR C-sharpcorner before proceeding with this tutorial. Description – In this tutorial, we will […]

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s