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

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