How to retrieve records from database table in MVC

In this article i will show you, How to retrieve record from database table in MVC. This things we have already learned in ASP.NET WEB FORMS, Now the same things we can do in MVC. Before doing this task first to install Entity Framework in the application. Create a Empty MVC project. Add a new database also create table by using this video tutorial. We have a database table with their following fields:

CREATE TABLE [dbo].[Student_Table] (

    [Id]   INT           IDENTITY (1, 1) NOT NULL,

    [Name] NVARCHAR (50) NULL,

    [City] NVARCHAR (50) NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC)

);

Now, Following these steps to retrieve items from table:

Step-1 :  Create a "Student" model class in model folder. Like :

using System.ComponentModel.DataAnnotations.Schema;

namespace WebApplication19.Models

{

    [Table("Student_Table")]

    public class Student

    {

        public int Id { get; set; }

        public string Name { get; set; }

        public string City { get; set; }

    }

}

By using the Table class we can mapped our class to database table. 

Step-2 : Create a "StudentContext" class in model folder. Like

using System.Data.Entity;

namespace WebApplication19.Models
{
    public class StudentContext : DbContext
    {
        public DbSet<Student> students { get; set; }
    }
}

By using "students" property we can communicate with the table.

Step-3 : Create ConnectionString in the Web.Config file, in this way :

<connectionStrings>

    <add name="StudentContext" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
 
here we have a connection name is same as Context Class name.

Step-4 : Now, add a controller in the Controller folder, copy this code under controller action method

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

namespace WebApplication19.Controllers
{
    public class HomeController : Controller
    {
        // GET: Home
        public ActionResult Index()
        {
            StudentContext sc = new StudentContext();
            Student singlestu = sc.students.Single(stu => stu.Id == 2);
            return View(singlestu);
        }
    }
}

By using the LINQ Query we have to retrieving student details from table who's id is 2. Now, Build the project. Add a View By Right click on Index Action method.Now, appear a screen look like this.

Fill all mentioned fields, according to mentioned snaps. Now, last
Step-5 : Design to view for values.

@model WebApplication19.Models.Student

@{
    ViewBag.Title = "Student";
}

<h2>Single Student Record</h2>
<div>
    Student Id : @Model.Id <br/>
    Student Name : @Model.Name <br/>
    Student City : @Model.City

</div>

Code Generates the following output: