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.

console application

Empty website

Step 2

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

add new project

class liberary

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.

add new class

add class

class

Step 4

Open SQL Server Management Studio and run this script.

  1. CREATE TABLE [dbo].[Employee] (
  2. [EmployeeID] INT IDENTITY (1, 1) NOT NULL,
  3. [EmployeeName] NVARCHAR (50) NULL,
  4. [EmployeeGender] NVARCHAR (10) NULL,
  5. [EmployeeDesignation] NVARCHAR (50) NULL,
  6. PRIMARY KEY CLUSTERED ([EmployeeID] ASC)
  7. );
  9. INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (1, N'Anna', N'Female', N'Software Engineer')
  10. INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (2, N'Barace', N'Male', N'Software Engineer')
  11. INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (3, N'Cathy', N'Female', N'Software Engineer')
  12. INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (4, N'Downey', N'Male', N'Senior Software Engineer')
  13. INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (5, N'Eric', N'Male', N'Senior Software Engineer')
  14. INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (6, N'Foster', N'Male', N'Senior Software Engineer')
  15. INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (7, N'Genee', N'Female', N'Senior Software Engineer')
  16. INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (8, N'Howard', N'Male', N'Senior Software Engineer')
  17. INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (9, N'Instana', N'Female', N'Project Manager')
  18. INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (10, N'Joe', N'Male', N'Project Manager')
  19. INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName], [EmployeeGender], [EmployeeDesignation]) VALUES (11, N'Kristen', N'Male', N'Senior Manager')
  21. Select * from Employee
  23. CREATE PROCEDURE spGetAllEmployees
  24. AS
  25. BEGIN
  26. SELECT emp.*
  27. FROM dbo.Employee emp;
  28. END;
  32. CREATE PROCEDURE spInsertEmployeeDetails
  33. @EmployeeName NVARCHAR(50),
  34. @EmployeeGender NVARCHAR(10),
  35. @EmployeeDesignation NVARCHAR(50)
  36. AS
  37. BEGIN
  38. INSERT dbo.Employee
  39. (
  40. --EmployeeID - this column value is auto-generated
  41. EmployeeName,
  42. EmployeeGender,
  43. EmployeeDesignation
  44. )
  45. VALUES
  46. (
  47. -- EmployeeID - int
  48. N'', -- EmployeeName - nvarchar
  49. N'', -- EmployeeGender - nvarchar
  50. N'' -- EmployeeDesignation - nvarchar
  51. )
  52. END;
  56. CREATE PROCEDURE spUpdateEmployee
  57. @EmployeeID int,
  58. @EmployeeName nvarchar(100),
  59. @EmployeeGender nvarchar(10),
  60. @EmployeeDesignation nvarchar(100)
  61. AS
  62. BEGIN
  63. UPDATE dbo.Employee
  64. SET
  65. --EmployeeID - this column value is auto-generated
  66. dbo.Employee.EmployeeName = @EmployeeName, -- nvarchar
  67. dbo.Employee.EmployeeGender = @EmployeeGender, -- nvarchar
  68. dbo.Employee.EmployeeDesignation = @EmployeeDesignation-- nvarchar
  69. WHERE
  70. dbo.Employee.EmployeeID = @EmployeeID
  71. END
  75. CREATE PROCEDURE spDeleteEmployee
  76. @EmployeeID INT
  77. AS
  78. BEGIN
  79. DELETE dbo.Employee
  80. WHERE dbo.Employee.EmployeeID = @EmployeeID;
  81. END;
Explanation:

Step 5

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

  1. using System.ComponentModel.DataAnnotations;
  2. namespace BusinessLayer
  3. {
  4. public class Employee
  5. {
  6. public int EmployeeID { get; set; }
  7. [Required]
  8. public string EmployeeName { get; set; }
  9. [Required]
  10. public string EmployeeGender { get; set; }
  11. [Required]
  12. public string EmployeeDesignation { get; set; }
  13. }
  14. }
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.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Configuration;
  4. using System.Data;
  5. using System.Data.SqlClient;
  7. namespace BusinessLayer
  8. {
  9. public class EmployeeBusinessLayer
  10. {
  11. public IEnumerable<Employee> Employees
  12. {
  13. get
  14. {
  15. string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
  16. List<Employee> employees = new List<Employee>();
  17. using (SqlConnection con = new SqlConnection(CS))
  18. {
  19. con.Open();
  20. SqlCommand cmd = new SqlCommand("spGetAllEmployees", con);
  21. cmd.CommandType = CommandType.StoredProcedure;
  22. SqlDataReader dr = cmd.ExecuteReader();
  23. while (dr.Read())
  24. {
  25. Employee employee = new Employee();
  26. employee.EmployeeID = Convert.ToInt32(dr["EmployeeID"]);
  27. employee.EmployeeName = dr["EmployeeName"].ToString();
  28. employee.EmployeeGender = dr["EmployeeGender"].ToString();
  29. employee.EmployeeDesignation = dr["EmployeeDesignation"].ToString();
  30. employees.Add(employee);
  31. }
  32. }
  33. return employees;
  34. }
  35. }
  36. public void AddEmployee(Employee employee)
  37. {
  38. string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
  39. using (SqlConnection con = new SqlConnection(CS))
  40. {
  41. con.Open();
  42. SqlCommand cmd = new SqlCommand("spInsertEmployeeDetails", con);
  43. cmd.CommandType = CommandType.StoredProcedure;
  44. cmd.Parameters.AddWithValue("@EmployeeName", employee.EmployeeName);
  45. cmd.Parameters.AddWithValue("@EmployeeGender", employee.EmployeeGender);
  46. cmd.Parameters.AddWithValue("@EmployeeDesignation", employee.EmployeeDesignation);
  48. cmd.ExecuteNonQuery();
  49. }
  50. }
  51. public void UpdateEmployee(Employee employee)
  52. {
  53. string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
  54. using (SqlConnection con = new SqlConnection(CS))
  55. {
  56. con.Open();
  57. SqlCommand cmd = new SqlCommand("spUpdateEmployee", con);
  58. cmd.CommandType = CommandType.StoredProcedure;
  59. cmd.Parameters.AddWithValue("@EmployeeID", employee.EmployeeID);
  60. cmd.Parameters.AddWithValue("@EmployeeName", employee.EmployeeName);
  61. cmd.Parameters.AddWithValue("@EmployeeGender", employee.EmployeeGender);
  62. cmd.Parameters.AddWithValue("@EmployeeDesignation", employee.EmployeeDesignation);
  64. cmd.ExecuteNonQuery();
  65. }
  66. }
  67. public void DeleteEmployee(int id)
  68. {
  69. string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
  70. using (SqlConnection con = new SqlConnection(CS))
  71. {
  72. con.Open();
  73. SqlCommand cmd = new SqlCommand("spDeleteEmployee", con);
  74. cmd.CommandType = CommandType.StoredProcedure;
  75. cmd.Parameters.AddWithValue("@EmployeeID", id);
  76. cmd.ExecuteNonQuery();
  77. }
  78. }
  79. }
  80. }
Explanation:

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.

add reference

Solution

Step 8

Add a controller class to the controller folder.

add controller

MVC

controller name

Step 9

Add the following code to this file.

  1. using BusinessLayer;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web.Mvc;
  6. namespace MVCDataAccessByLayers.Controllers
  7. {
  8. public class EmployeeController : Controller
  9. {
  10. public ActionResult Index()
  11. {
  12. EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
  13. List<Employee> employees = employeeBusinessLayer.Employees.ToList();
  14. return View(employees);
  15. }
  16. [HttpGet]
  17. public ActionResult Create()
  18. {
  19. return View();
  20. }
  21. [HttpPost]
  22. public ActionResult Create(Employee employee)
  23. {
  24. if (ModelState.IsValid)
  25. {
  26. EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
  27. employeeBusinessLayer.AddEmployee(employee);
  28. return RedirectToAction("Index", "Employee");
  29. }
  30. return View();
  31. }
  32. [HttpGet]
  33. public ActionResult Edit(int id)
  34. {
  35. EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
  36. Employee employee = employeeBusinessLayer.Employees.Single(emp => emp.EmployeeID == id);
  37. return View(employee);
  38. }
  39. [HttpPost]
  40. public ActionResult Edit(Employee employee)
  41. {
  42. if (ModelState.IsValid)
  43. {
  44. EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
  45. employeeBusinessLayer.UpdateEmployee(employee);
  46. return RedirectToAction("Index", "Employee");
  47. }
  48. return View();
  49. }
  50. [HttpGet]
  51. public ActionResult Delete(int id)
  52. {
  53. EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
  54. employeeBusinessLayer.DeleteEmployee(id);
  55. return RedirectToAction("Index", "Employee");
  56. }
  57. public ActionResult Details(int id)
  58. {
  59. EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
  60. Employee employee = employeeBusinessLayer.Employees.Single(emp => emp.EmployeeID == id);
  61. return View(employee);
  62. }
  63. }
  64. }
Explanation:

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.

click add view

Model class

Add view

Click ADD

type view name

Step 11

Replace with the following code for View files created in the preceding step.

Index.cshtml

  1. @model IEnumerable<BusinessLayer.Employee>
  3. @{
  4. ViewBag.Title = "Index";
  5. }
  6. <div class="container">
  7. <div class="jumbotron text-center"><h1>Employee Details</h1></div>
  10. <p>
  11. @Html.ActionLink("Create New", "Create")
  12. </p>
  13. <table class="table">
  14. <tr>
  15. <th>
  16. Name
  17. </th>
  18. <th>
  19. Gender
  20. </th>
  21. <th>
  22. Designation
  23. </th>
  24. <th></th>
  25. </tr>
  27. @foreach (var item in Model)
  28. {
  29. <tr>
  30. <td>
  31. @Html.DisplayFor(modelItem => item.EmployeeName)
  32. </td>
  33. <td>
  34. @Html.DisplayFor(modelItem => item.EmployeeGender)
  35. </td>
  36. <td>
  37. @Html.DisplayFor(modelItem => item.EmployeeDesignation)
  38. </td>
  39. <td>
  40. @Html.ActionLink("Edit", "Edit", new { id = item.EmployeeID }) |
  41. @Html.ActionLink("Details", "Details", new { id = item.EmployeeID }) |
  42. @Html.ActionLink("Delete", "Delete", new { id = item.EmployeeID })
  43. </td>
  44. </tr>
  45. }
  47. </table>
  48. </div>
Create.cshtml
  1. @model BusinessLayer.Employee
  3. @{
  4. ViewBag.Title = "Create";
  5. }
  7. <h2>Create</h2>
  10. @using (Html.BeginForm())
  11. {
  12. @Html.AntiForgeryToken()
  14. <div class="form-horizontal">
  15. <h4>Employee</h4>
  16. <hr />
  17. @Html.ValidationSummary(true, "", new { @class = "text-danger" })
  18. <div class="form-group">
  19. @Html.LabelFor(model => model.EmployeeName, htmlAttributes: new { @class = "control-label col-md-2" })
  20. <div class="col-md-10">
  21. @Html.EditorFor(model => model.EmployeeName, new { htmlAttributes = new { @class = "form-control" } })
  22. @Html.ValidationMessageFor(model => model.EmployeeName, "", new { @class = "text-danger" })
  23. </div>
  24. </div>
  26. <div class="form-group">
  27. @Html.LabelFor(model => model.EmployeeGender, htmlAttributes: new { @class = "control-label col-md-2" })
  28. <div class="col-md-10">
  29. @Html.EditorFor(model => model.EmployeeGender, new { htmlAttributes = new { @class = "form-control" } })
  30. @Html.ValidationMessageFor(model => model.EmployeeGender, "", new { @class = "text-danger" })
  31. </div>
  32. </div>
  34. <div class="form-group">
  35. @Html.LabelFor(model => model.EmployeeDesignation, htmlAttributes: new { @class = "control-label col-md-2" })
  36. <div class="col-md-10">
  37. @Html.EditorFor(model => model.EmployeeDesignation, new { htmlAttributes = new { @class = "form-control" } })
  38. @Html.ValidationMessageFor(model => model.EmployeeDesignation, "", new { @class = "text-danger" })
  39. </div>
  40. </div>
  42. <div class="form-group">
  43. <div class="col-md-offset-2 col-md-10">
  44. <input type="submit" value="Create" class="btn btn-default" />
  45. </div>
  46. </div>
  47. </div>
  48. }
  50. <div>
  51. @Html.ActionLink("Back to List", "Index")
  52. </div>
  54. <script src="~/Scripts/jquery-1.10.2.min.js"></script>
  55. <script src="~/Scripts/jquery.validate.min.js"></script>
  56. <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
Edit.cshtml
  1. @model BusinessLayer.Employee
  3. @{
  4. ViewBag.Title = "Edit";
  5. }
  7. <h2>Edit</h2>
  10. @using (Html.BeginForm())
  11. {
  12. @Html.AntiForgeryToken()
  14. <div class="form-horizontal">
  15. <h4>Employee</h4>
  16. <hr />
  17. @Html.ValidationSummary(true, "", new { @class = "text-danger" })
  18. @Html.HiddenFor(model => model.EmployeeID)
  20. <div class="form-group">
  21. @Html.LabelFor(model => model.EmployeeName, htmlAttributes: new { @class = "control-label col-md-2" })
  22. <div class="col-md-10">
  23. @Html.EditorFor(model => model.EmployeeName, new { htmlAttributes = new { @class = "form-control" } })
  24. @Html.ValidationMessageFor(model => model.EmployeeName, "", new { @class = "text-danger" })
  25. </div>
  26. </div>
  28. <div class="form-group">
  29. @Html.LabelFor(model => model.EmployeeGender, htmlAttributes: new { @class = "control-label col-md-2" })
  30. <div class="col-md-10">
  31. @Html.EditorFor(model => model.EmployeeGender, new { htmlAttributes = new { @class = "form-control" } })
  32. @Html.ValidationMessageFor(model => model.EmployeeGender, "", new { @class = "text-danger" })
  33. </div>
  34. </div>
  36. <div class="form-group">
  37. @Html.LabelFor(model => model.EmployeeDesignation, htmlAttributes: new { @class = "control-label col-md-2" })
  38. <div class="col-md-10">
  39. @Html.EditorFor(model => model.EmployeeDesignation, new { htmlAttributes = new { @class = "form-control" } })
  40. @Html.ValidationMessageFor(model => model.EmployeeDesignation, "", new { @class = "text-danger" })
  41. </div>
  42. </div>
  44. <div class="form-group">
  45. <div class="col-md-offset-2 col-md-10">
  46. <input type="submit" value="Save" class="btn btn-default" />
  47. </div>
  48. </div>
  49. </div>
  50. }
  52. <div>
  53. @Html.ActionLink("Back to List", "Index")
  54. </div>
  56. <script src="~/Scripts/jquery-1.10.2.min.js"></script>
  57. <script src="~/Scripts/jquery.validate.min.js"></script>
  58. <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
Details.cshtml
  1. @model BusinessLayer.Employee
  3. @{
  4. ViewBag.Title = "Details";
  5. }
  7. <h2>Details</h2>
  9. <div>
  10. <h4>Employee</h4>
  11. <hr />
  12. <dl class="dl-horizontal">
  13. <dt>
  14. @Html.DisplayNameFor(model => model.EmployeeName)
  15. </dt>
  17. <dd>
  18. @Html.DisplayFor(model => model.EmployeeName)
  19. </dd>
  21. <dt>
  22. @Html.DisplayNameFor(model => model.EmployeeGender)
  23. </dt>
  25. <dd>
  26. @Html.DisplayFor(model => model.EmployeeGender)
  27. </dd>
  29. <dt>
  30. @Html.DisplayNameFor(model => model.EmployeeDesignation)
  31. </dt>
  33. <dd>
  34. @Html.DisplayFor(model => model.EmployeeDesignation)
  35. </dd>
  37. </dl>
  38. </div>
  39. <p>
  40. @Html.ActionLink("Edit", "Edit", new { id = Model.EmployeeID }) |
  41. @Html.ActionLink("Back to List", "Index")
  42. </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.
  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <meta charset="utf-8" />
  5. <meta name="viewport" content="width=device-width, initial-scale=1.0">
  6. <title>@ViewBag.Title - E.M.S</title>
  7. <link href="~/Content/Site.css" rel="stylesheet" type="text/css" />
  8. <link href="~/Content/bootstrap.min.css" rel="stylesheet" type="text/css" />
  9. <script src="~/Scripts/modernizr-2.6.2.js"></script>
  10. </head>
  11. <body>
  12. <div class="navbar navbar-inverse navbar-fixed-top">
  13. <div class="container">
  14. <div class="navbar-header">
  15. <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
  16. <span class="icon-bar"></span>
  17. <span class="icon-bar"></span>
  18. <span class="icon-bar"></span>
  19. </button>
  20. @Html.ActionLink("Employee Management System", "Index", "Employee", new { area = "" }, new { @class = "navbar-brand" })
  21. </div>
  22. <div class="navbar-collapse collapse">
  23. <ul class="nav navbar-nav">
  24. </ul>
  25. </div>
  26. </div>
  27. </div>
  29. <div class="container body-content">
  30. @RenderBody()
  31. <hr />
  32. <footer>
  33. <p>© @DateTime.Now.Year - My ASP.NET Demo Application</p>
  34. </footer>
  35. </div>
  37. <script src="~/Scripts/jquery-1.10.2.min.js"></script>
  38. <script src="~/Scripts/bootstrap.min.js"></script>
  39. </body>
  40. </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:
  1. <connectionStrings>
  2. <add connectionString="Data Source=ANKITBANSALPC;Initial Catalog=MVC;Integrated Security=True" name="DBCS" providerName="System.Data.SqlClient"/>
  3. </connectionStrings>
It will establish a connection to the database.

Step 14

Replace the code of the Route.config file with the following code.
  1. using System.Web.Mvc;
  2. using System.Web.Routing;
  4. namespace MVCDataAccessByLayers
  5. {
  6. public class RouteConfig
  7. {
  8. public static void RegisterRoutes(RouteCollection routes)
  9. {
  10. routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
  12. routes.MapRoute(
  13. name: "Default",
  14. url: "{controller}/{action}/{id}",
  15. defaults: new { controller = "Employee", action = "Index", id = UrlParameter.Optional }
  16. );
  17. }
  18. }
  19. }
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.

see page

create detail

edit detail

see employee detail

delete

Detail image

Edit

Employee detail

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.