CRUD Operations in MVC using Business Layer and Scaffolding

Description – In this article, we will learn how to perform Create, Read, Update and Delete operations in MVC architecture and using scaffolding template to generate views. We will also use some ADO.NET code to connect to our database to perform these operations. All we will do here, is through Business Layer that contains our logic. Also, Bootstrapping is implemented in this example.

Download Source code here..

Step 1: Create an empty ASP.NET web application with MVC.

1

2

Step 2: Add a class library project for creating business layer to the solution.

3

4

Step 3: Add two Class files to this Class Library Project. One class will be used to declare entities and other is used to implement logic.

5

6

7

Step 4: Open SQL Server Management Studio and run this script.

CREATE TABLE [dbo].[Employee] (
    [EmployeeID]          INT           IDENTITY (1, 1) NOT NULL,
    [EmployeeName]        NVARCHAR (50) NULL,
    [EmployeeGender]      NVARCHAR (10) NULL,
    [EmployeeDesignation] NVARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([EmployeeID] ASC)
);

INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (1, N'Anna', N'Female', N'Software Engineer')
INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (2, N'Barace', N'Male', N'Software Engineer')
INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (3, N'Cathy', N'Female', N'Software Engineer')
INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (4, N'Downey', N'Male', N'Senior Software Engineer')
INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (5, N'Eric', N'Male', N'Senior Software Engineer')
INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (6, N'Foster', N'Male', N'Senior Software Engineer')
INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (7, N'Genee', N'Female', N'Senior Software Engineer')
INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (8, N'Howard', N'Male', N'Senior Software Engineer')
INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (9, N'Instana', N'Female', N'Project Manager')
INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (10, N'Joe', N'Male', N'Project Manager')
INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (11, N'Kristen', N'Male', N'Senior Manager')

Select * from Employee

CREATE PROCEDURE spGetAllEmployees
AS
    BEGIN
        SELECT emp.*
        FROM dbo.Employee emp;
    END;

CREATE PROCEDURE spInsertEmployeeDetails
       @EmployeeName        NVARCHAR(50),
       @EmployeeGender      NVARCHAR(10),
       @EmployeeDesignation NVARCHAR(50)
AS
    BEGIN
INSERT dbo.Employee
  (
 --EmployeeID - this column value is auto-generated
 EmployeeName,
 EmployeeGender,
 EmployeeDesignation
  )
  VALUES
  (
 -- EmployeeID - int
 N'', -- EmployeeName - nvarchar
 N'', -- EmployeeGender - nvarchar
 N'' -- EmployeeDesignation - nvarchar
  )
    END;

CREATE PROCEDURE spUpdateEmployee
@EmployeeID int,
@EmployeeName nvarchar(100),
@EmployeeGender nvarchar(10),
@EmployeeDesignation nvarchar(100)
AS
BEGIN
UPDATE dbo.Employee
SET
    --EmployeeID - this column value is auto-generated
    dbo.Employee.EmployeeName = @EmployeeName, -- nvarchar
    dbo.Employee.EmployeeGender = @EmployeeGender, -- nvarchar
    dbo.Employee.EmployeeDesignation = @EmployeeDesignation-- nvarchar
WHERE 
dbo.Employee.EmployeeID = @EmployeeID
END

CREATE PROCEDURE spDeleteEmployee
       @EmployeeID INT
AS
    BEGIN
        DELETE dbo.Employee
        WHERE dbo.Employee.EmployeeID = @EmployeeID;
    END;

Explanation:

  • We have created an employee table with some sample data.
  • Four stored procedures are created for perform Read, Update, Delete and Add a new employee.
  • ID column is an identity column which will auto generate id.

Step 5: Add the following code to Employee class file that we created in Step 3.

using System.ComponentModel.DataAnnotations;
namespace BusinessLayer
{
    public class Employee
    {
        public int EmployeeID { getset; }
        [Required]
        public string EmployeeName { getset; }
        [Required]
        public string EmployeeGender { getset; }
        [Required]
        public string EmployeeDesignation { getset; }
    }
}

We have just declared the properties corresponding to columns in the database table. All these properties are auto implemented and are wrapped inside Employee class. We will use this employee class everywhere in the project.

Step 6: Add the following code to Employee Business Layer Class that also created in Step 3.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace BusinessLayer
{
    public class EmployeeBusinessLayer
    {
        public IEnumerable<Employee> Employees
        {
            get
            {
                string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                List<Employee> employees = new List<Employee>();
                using (SqlConnection con = new SqlConnection(CS))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("spGetAllEmployees", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Employee employee = new Employee();
                        employee.EmployeeID = Convert.ToInt32(dr["EmployeeID"]);
                        employee.EmployeeName = dr["EmployeeName"].ToString();
                        employee.EmployeeGender = dr["EmployeeGender"].ToString();
                        employee.EmployeeDesignation = dr["EmployeeDesignation"].ToString();
                        employees.Add(employee);
                    }
                }
                return employees;
            }
        }
        public void AddEmployee(Employee employee)
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("spInsertEmployeeDetails", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@EmployeeName", employee.EmployeeName);
                cmd.Parameters.AddWithValue("@EmployeeGender", employee.EmployeeGender);
                cmd.Parameters.AddWithValue("@EmployeeDesignation", employee.EmployeeDesignation);

                cmd.ExecuteNonQuery();
            }
        }
        public void UpdateEmployee(Employee employee)
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("spUpdateEmployee", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@EmployeeID", employee.EmployeeID);
                cmd.Parameters.AddWithValue("@EmployeeName", employee.EmployeeName);
                cmd.Parameters.AddWithValue("@EmployeeGender", employee.EmployeeGender);
                cmd.Parameters.AddWithValue("@EmployeeDesignation", employee.EmployeeDesignation);

                cmd.ExecuteNonQuery();
            }
        }
        public void DeleteEmployee(int id)
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("spDeleteEmployee", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@EmployeeID", id);
                cmd.ExecuteNonQuery();
            }
        }
    }
}

Explanation:

  • The first method will get us all list of employees. That is why we have created it as IEnumerable. Its object name is Employees which gives us all the employees in the database table. Simple ADO.NET connectivity is used.
  • Add employee method will add the new employee to database and employee class is passed as an object parameter. Here we have used our InsertEmployee stored procedure.
  • Update employee method will update the details of employee. Update Employee Stored procedure is used.
  • Delete employee method will delete the employee and expects just the id of employee. DeleteEmployee Stored procedure is used.
  • All of these methods will be used in our MVC controller.

So, till now we have created the model of our application. Now Controller and Views are left. Let’s implement that also.

Step 7: Build the solution by pressing Ctrl + Shift + B. Now add reference of Business Layer in your MVC Project.

11

12

Step 8: Add a controller class to the controller folder.

8

9

10

Step 9: Add the following code to this file.

using BusinessLayer;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;

namespace MVCDataAccessByLayers.Controllers
{
    public class EmployeeController : Controller
    {
        public ActionResult Index()
        {
            EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
            List<Employee> employees = employeeBusinessLayer.Employees.ToList();
            return View(employees);
        }
        [HttpGet]
        public ActionResult Create()
        {
            return View();
        }
        [HttpPost]
        public ActionResult Create(Employee employee)
        {
            if (ModelState.IsValid)
            {
                EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
                employeeBusinessLayer.AddEmployee(employee);
                return RedirectToAction("Index""Employee");
            }
            return View();
        }
        [HttpGet]
        public ActionResult Edit(int id)
        {
            EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
            Employee employee = employeeBusinessLayer.Employees.Single(emp => emp.EmployeeID == id);
            return View(employee);
        }
        [HttpPost]
        public ActionResult Edit(Employee employee)
        {
            if (ModelState.IsValid)
            {
                EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
                employeeBusinessLayer.UpdateEmployee(employee);
                return RedirectToAction("Index""Employee");
            }
            return View();
        }
        [HttpGet]
        public ActionResult Delete(int id)
        {
            EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
            employeeBusinessLayer.DeleteEmployee(id);
            return RedirectToAction("Index""Employee");
        }
        public ActionResult Details(int id)
        {
            EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
            Employee employee = employeeBusinessLayer.Employees.Single(emp => emp.EmployeeID == id);
            return View(employee);
        }
    }
}

Explanation:

  • Add a namespace to include business layer in the controller.
  • In the index action method, a list of employee is retrieved.
  • The create method is decorated with HTTP Get and Post requests separately. This is because, when get request is called, then a view will be returned and when the user click create button, the request is now post request and it will contact model for the business logic. We also checked model state as valid or not to do some validations.
  • The same procedure is with the Edit method. Just we pass id of employee to get single employee in the get request. In the post request same concept is followed.
  • The delete method is called on the get request only. No view is required for this as the employee is deleted only at the get request.
  • The details method also works on the same concept.

In case of any query regarding this, please comment, I will reply to that at the earliest.

Step 10: Create Views for all of the Action Methods except Delete Action Method.

13 14 15 17

16

 

Step 11: Replace with the following codes for View files created in above step.

Index.cshtml

@model IEnumerable<BusinessLayer.Employee>

@{
    ViewBag.Title = "Index";
}
<div class="container">
    <div class="jumbotron text-center"><h1>Employee Details</h1></div>


    <p>
        @Html.ActionLink("Create New""Create")
    </p>
    <table class="table">
        <tr>
            <th>
                Name
            </th>
            <th>
                Gender
            </th>
            <th>
                Designation
            </th>
            <th></th>
        </tr>

        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.EmployeeName)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.EmployeeGender)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.EmployeeDesignation)
                </td>
                <td>
                    @Html.ActionLink("Edit""Edit"new { id = item.EmployeeID }) |
                    @Html.ActionLink("Details""Details"new { id = item.EmployeeID }) |
                    @Html.ActionLink("Delete""Delete"new { id = item.EmployeeID })
                </td>
            </tr>
        }

    </table>
</div>

Create.cshtml

@model BusinessLayer.Employee

@{
    ViewBag.Title = "Create";
}

<h2>Create</h2>


@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>Employee</h4>
        <hr />
        @Html.ValidationSummary(true""new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.EmployeeName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmployeeName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmployeeName, ""new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.EmployeeGender, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmployeeGender, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmployeeGender, ""new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.EmployeeDesignation, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmployeeDesignation, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmployeeDesignation, ""new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List""Index")
</div>

<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

Edit.cshtml

@model BusinessLayer.Employee

@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>


@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>Employee</h4>
        <hr />
        @Html.ValidationSummary(true""new { @class = "text-danger" })
        @Html.HiddenFor(model => model.EmployeeID)

        <div class="form-group">
            @Html.LabelFor(model => model.EmployeeName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmployeeName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmployeeName, ""new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.EmployeeGender, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmployeeGender, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmployeeGender, ""new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.EmployeeDesignation, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmployeeDesignation, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmployeeDesignation, ""new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List""Index")
</div>

<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

Details.cshtml

@model BusinessLayer.Employee

@{
    ViewBag.Title = "Details";
}

<h2>Details</h2>

<div>
    <h4>Employee</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.EmployeeName)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.EmployeeName)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.EmployeeGender)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.EmployeeGender)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.EmployeeDesignation)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.EmployeeDesignation)
        </dd>

    </dl>
</div>
<p>
    @Html.ActionLink("Edit""Edit"new { id = Model.EmployeeID }) |
    @Html.ActionLink("Back to List""Index")
</p>

Note: The code for above views will be generated automatically. You don’t need to do anything. I have made some alteration to the design i.e. why I want you to replace the auto generated code with the above code. The code which is generated automatically above is called as Scaffolding.

Step 12: Replace the code of the Layout.cshtml page in the view folder. It is the master page of project.

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>@ViewBag.Title - E.M.S</title>
    <link href="~/Content/Site.css" rel="stylesheet" type="text/css" />
    <link href="~/Content/bootstrap.min.css" rel="stylesheet" type="text/css" />
    <script src="~/Scripts/modernizr-2.6.2.js"></script>
</head>
<body>
    <div class="navbar navbar-inverse navbar-fixed-top">
        <div class="container">
            <div class="navbar-header">
                <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                </button>
                @Html.ActionLink("Employee Management System""Index""Employee"new { area = "" }, new { @class = "navbar-brand" })
            </div>
            <div class="navbar-collapse collapse">
                <ul class="nav navbar-nav">
                </ul>
            </div>
        </div>
    </div>

    <div class="container body-content">
        @RenderBody()
        <hr />
        <footer>
            <p>&copy; @DateTime.Now.Year - My ASP.NET Demo Application</p>
        </footer>
    </div>

    <script src="~/Scripts/jquery-1.10.2.min.js"></script>
    <script src="~/Scripts/bootstrap.min.js"></script>
</body>
</html>

The above is the code of Master page for the project. It is completely bootstrapped.

Step 13: Add connection string to web.config file.

<connectionStrings>
    <add connectionString="Data Source=ANKITBANSALPC;Initial Catalog=MVC;Integrated Security=True" name="DBCS" providerName="System.Data.SqlClient"/>
  </connectionStrings>

It will establish a connection to database.

Step 14: Replace the code of Route.config file with the following code.

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

namespace MVCDataAccessByLayers
{
    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 = "Employee", action = "Index", id = UrlParameter.Optional }
            );
        }
    }
}

The name of the controller is changed to Employee. No other change is being done to it. It will direct the user to the index action method of Employee controller.

Step 15: Press F5 to run the project and you will see the screen like the below screenshots.

18 25 24 23 22 19 20 21

Hope you liked this demo. Please provide your precious comments which encourages me to create more demos.

Please read this article on my personal blog.

Follow me on twitter and facebook. Like my facebook page.

Advertisements

MVC Data Access and Filter using Entity Framework and HTML Helpers

This tutorial is in continuation 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 learn how to use multiple table and their relationship to fetch data from the database. Also we will learn something more about HTML Helpers.
Download Previous Source code here..
Download new Source Code here..
Objective – We want a list of Student’s standards in which they are studying. When the initial page loads, it should show the Standards available in database table. When a standard is clicked, it should only show those students who belongs to that standard and when the name of student is clicked, it should show the details of that student. In addition to this, we have to also provide links on each page to perform back action.
Step 1: Open the previous created Project. In case you don’t have that project, just download it from the links provided above or create a new ASP .NET Web application with MVC.
Step 2: Open SQL Server Management Studio and Execute the following Script.
CREATE DATABASE MVC;
USE MVC;
CREATE TABLE dbo.Students( ID         INT,
Name       VARCHAR(50),
Gender     VARCHAR(6),
Fees       INT,
standardId INT );
INSERT INTO dbo.Students
VALUES( 1, ‘Harry’, ‘Male’, 2500, 9 );
INSERT INTO dbo.Students
VALUES( 2, ‘Jane’, ‘Female’, 2400, 9 );
INSERT INTO dbo.Students
VALUES( 3, ‘Emma’, ‘Female’, 2100, 9 );
INSERT INTO dbo.Students
VALUES( 4, ‘Roster’, ‘Male’, 2500, 9 );
INSERT INTO dbo.Students
VALUES( 5, ‘Chris’, ‘Male’, 2900, 10 );
INSERT INTO dbo.Students
VALUES( 6, ‘Evan’, ‘Male’, 2200, 10 );
INSERT INTO dbo.Students
VALUES( 7, ‘Cathlie’, ‘Female’, 2550, 10 );
INSERT INTO dbo.Students
VALUES( 8, ‘Jack’, ‘Male’, 2500, 10 );
INSERT INTO dbo.Students
VALUES( 9, ‘Jone’, ‘Male’, 2900, 11 );
INSERT INTO dbo.Students
VALUES( 10, ‘Videra’, ‘Female’, 2550, 11 );
INSERT INTO dbo.Students
VALUES( 11, ‘Sara’, ‘Female’, 2900, 11 );
INSERT INTO dbo.Students
VALUES( 12, ‘Mak’, ‘Male’, 2500, 11 );
INSERT INTO dbo.Students
VALUES( 13, ‘Max’, ‘Male’, 2550, 12 );
INSERT INTO dbo.Students
VALUES( 14, ‘Brock’, ‘Male’, 2900, 12 );
INSERT INTO dbo.Students
VALUES( 15, ‘Eddie’, ‘Male’, 2500, 12 );
INSERT INTO dbo.Students
VALUES( 16, ‘Edna’, ‘Female’, 2500, 12 );
SELECT Students.ID,
Students.Name,
Students.Gender,
Students.Fees,
Students.standardId
FROM dbo.Students;CREATE TABLE dbo.Standard( Id   INT,
Info VARCHAR(20));

INSERT INTO dbo.Standard
VALUES( 9, ‘Ninth’ );
INSERT INTO dbo.Standard
VALUES( 10, ‘Tenth’ );
INSERT INTO dbo.Standard
VALUES( 11, ‘Eleventh’ );
INSERT INTO dbo.Standard
VALUES( 12, ‘Twelth’ );
SELECT *
FROM Students;
SELECT *
FROM Standard;

Our Database tables will look like this.



Step 3: Add a class file to Models folder of your project and name it as Standard.cs and add the following code to it.
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
namespace MVCDataAccessByEntityFrame.Models
{
[Table(“Standard”)]
public class Standard
{
public int Id { get; set; }
public string Info { get; set; }
public List<Students> Students { get; set; }
}
}
Step 4: Replace with the following code to StudentsContext.cs file already available in models folder of your project.
using System.Data.Entity;
namespace MVCDataAccessByEntityFrame.Models
{
public class StudentsContext : DbContext
{
public DbSet<Students> Students { get; set; }
public DbSet<Standard> Standard { get; set; }
}
}
Step 5: Add a controller to Controllers folder and Name it as StandardController.cs and add the following code to it.
using MVCDataAccessByEntityFrame.Models;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
namespace MVCDataAccessByEntityFrame.Controllers
{
public class StandardController : Controller
{
public ActionResult Index()
{
StudentsContext studentsContext = new StudentsContext();
List<Standard> standard = new List<Standard>();
standard = studentsContext.Standard.ToList();
return View(standard);
}
}
}
Step 6: Add a view to the index method of the preceding controller and replace with the following code.
@using MVCDataAccessByEntityFrame.Models;
@model IEnumerable<Standard>
@{
ViewBag.Title = “Student Standard”;
}
<h2>Student Standard List</h2>
<ul>
@foreach (Standard standard in @Model)
{
        <li>
@Html.ActionLink(standard.Info, “Index”, “Students”, new { standardId = standard.Id }, null)
</li>
}
</ul>
Step 7: Replace the code of the following files with their respective codes mentioned below.
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; }
public int standardId { get; set; }
}
}
StudentController.cs
using MVCDataAccessByEntityFrame.Models;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
namespace MVCDataAccessByEntityFrame.Controllers
{
public class StudentsController : Controller
{
public ActionResult Index(int standardId)
{
StudentsContext studentsContext = new StudentsContext();
List<Students> students = studentsContext.Students.Where(std => std.standardId == standardId).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);        }
}
}

Students/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>
@Html.ActionLink(“Back to Standard List”, “Index”,“Standard”)
Students/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”, new { standardId = @Model.standardId })
Step 8: Make the following changes to Route.config file present in App_Start folder.
Step 9: Make sure that you solution explorer has all the below mentioned folders and files.
Step 10: Finally, run the project by pressing F5 and you will see the following.
When you click on any one of above option, you will be redirected to Index page of Students Controller which will show the list of students filtered on the basis of ID of above selected option.
When you click on any student name, it should show the details of Student. If you select Back option, you will be redirected to first page.
When you click Back button in preceding image, you will go back to Students List.
So, finally we completed our objective.

See this article at technewsinform