CRUD Operations in MVC Using Business Layer and Scaffolding

We will also use some ADO.NET code to connect to our database to do the CRUD operations. All we will do here is use a Business Layer containing our logic. Also, Bootstrapping is implemented in this example.

Download the source code here.

Step 1

Create an empty ASP.NET web application with MVC.





Step 2

Add a Class Library project for the business layer to the solution.





Step 3

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







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 created an employee table with some sample data.
  
  
• Four Stored Procedures are created to Read, Update, Delete and Add employees.
  
  
• An ID column is an identity column that will auto-generate an id.

Step 5

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

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

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

Step 6

Add the following code to the Employee Business Layer Class that was 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 a list of employees. That is why it was created as an IEnumerable. Its object name is Employees that gives us all the employees in the database table. Simple ADO.NET connectivity is used.
  
  
• The Add employee method will add the new employee to the database and the employee class is passed as an object parameter. Here we have used our InsertEmployee Stored Procedure.
  
  
• The Update employee method will update the details of the employee. The Update Employee Stored Procedure is used.
  
  
• The Delete employee method will delete the employee and expects just the id of the employee. The DeleteEmployee Stored Procedure is used.
  
  
• All of these methods will be used in our MVC controller.

So, until now we have created the model of our application. Now the Controller and Views are left. Let's implement them also.

Step 7

Build the solution by pressing Ctrl + Shift + B. Now add a reference for the Business Layer into your MVC Project.





Step 8

Add a controller class to the controller folder.







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 the business layer to the controller.
  
  
• In the index action method, a list of employees is retrieved.
  
  
• The create method is decorated with HTTP Get and Post requests separately. This is because, when a get request is called, then a view will be returned and when the user clicks the create button, the request is then a post request and it will contact the model for the business logic. We also checked the model state for validity to do some validations.
  
  
• The same procedure is with the Edit method. We just pass the id of an employee to get a single employee in the get request. In the post request the same concept applies.
  
  
• The delete method is called on the get request only. No view is required for this since the employee is deleted only at the get request.
  
  
• The details method also works on the same concept.

In you have any query regarding this then please comment, I will reply to that at the earliest.

Step 10

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











Step 11

Replace with the following code for View files created in the preceding 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 the preceding views will be generated automatically. You don't need to do anything. I have made some alteration to the design, in other words why I want you to replace the auto-generated code with the preceding code. The code that is generated automatically above is called Scaffolding.

Step 12

Replace the code of the Layout.cshtml page in the view folder. It is the Master Page for the 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>© @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 preceding is the code of the Master Page for the project. It is completely bootstrapped.

Step 13

Add a connection string to the web.config file as in the following:

<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 the database.

Step 14

Replace the code of the 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 the Employee controller.

Step 15

Press F5 to run the project and you will see the screen like the following screenshots.

















I hope you liked this demo. Please provide your precious comments that encourages me to create more demos.

Please read this article on my personal blog and website.