CRUD

CPAN 369 - CRUD (Create, Read, Update, Delete) in MVC

Overview of CRUD

• CRUD stands for Create, Read, Update, Delete. It is the basic set of operations for managing persistent data.

• Implementing CRUD operations within an MVC (Model-View-Controller) framework allows for a structured approach.

Database Structure

Employee Database and Table

• Table Definition: EmployeeTbl

• Database Object: dbo.EmployeeTbl

• Update Script File: dbo.EmployeeTbl.sql

Table Columns

• Id: int, IDENTITY (1, 1), NOT NULL, Primary Key

• Name: varchar(50), NULL

• Gender: varchar(50), NULL

• City: varchar(50), NULL

SQL Table Creation Example

CREATE TABLE [dbo].[Employee Tbl] (
    [Id] INT IDENTITY(1,1) NOT NULL,
    [Name] VARCHAR(50) NULL,
    [Gender] VARCHAR(50) NULL,
    [City] VARCHAR(50) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Employee Class and Context

Employee Class Definition

• Namespace: EmployeeMVC.Models

• Structure:

  using System;
  using System.Collections.Generic;
  using System.ComponentModel.DataAnnotations.Schema;
  using System.Linq;
  using System.Web;

  [Table("Employee Tbl")]
  public class Employee
  {
      public int ID { get; set; }
      public string Name { get; set; }
      public string Gender { get; set; }
      public string City { get; set; }
  }

EmployeeContext Class Definition

• Inherits from DbContext.

• Provides a DbSet for managing Employee entities.

• Structure:

  public class EmployeeContext : DbContext
  {
      public DbSet<Employee> Employees { get; set; }
  }

Controller Implementation

Creating Controller

• Create an empty controller called EmployeeController to handle CRUD operations.

Index Action Method and View (List)

Index Method Implementation

• Purpose: Retrieve Employees and display in a list format.

• Code:

  public ActionResult Index()
  {
      using (EmployeeContext employeeContext = new EmployeeContext())
      {
          List<Employee> employees = employeeContext.Employees.ToList();
          return View(employees);
      }
  }

Index View - Index.cshtml

• Generated using List Template (Scaffold Template).

• Structure:

  @model IEnumerable<EmployeeMVC.Models.Employee>
  @{ ViewBag.Title = "Index"; }
  <h2>Index</h2>
  <p>@Html.ActionLink("Create New", "Create")</p>
  <table class="table">
      <tr>
          <th>@Html.DisplayNameFor(model => model.Name)</th>
          <th>@Html.DisplayNameFor(model => model.Gender)</th>
          <th>@Html.DisplayNameFor(model => model.City)</th>
          <th></th>
      </tr>
      @foreach (var item in Model)
      {
          <tr>
              <td>@Html.DisplayFor(model => item.Name)</td>
              <td>@Html.DisplayFor(model => item.Gender)</td>
              <td>@Html.DisplayFor(model => item.City)</td>
              <td>
                  @Html.ActionLink("Edit", "Edit", new { id = item.ID }) |
                  @Html.ActionLink("Details", "Details", new { id = item.ID }) |
                  @Html.ActionLink("Delete", "Delete", new { id = item.ID })
              </td>
          </tr>
      }
  </table>

Create Action Method and View

Create Action Method (HttpGet)

• Gets the Create view:

   [HttpGet]
   public ActionResult Create()
   {
       return View();
   }

Create View - Create.cshtml

• Structure:

  @model EmployeeMVC.Models.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.Name, htmlAttributes: new { @class = "control-label col-md-2" })
              <div class="col-md-10">
                  @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" }})
                  @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
              </div>
          </div>
          <!-- Other fields similar to Name -->
          <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>
  }

DropDown List for Gender in Create View

• Use the DropDownList helper to create a dropdown for Gender selection:

  @Html.DropDownList("Gender", new List<SelectListItem>{
      new SelectListItem { Text="Male", Value="Male" },
      new SelectListItem { Text="Female", Value="Female" }
  }, "Select Gender")

Create Action Method (HttpPost) Implementation

• Using FormCollection

  [HttpPost]
  public ActionResult Create(FormCollection formCollection)
  {
      Employee employee = new Employee();
      employee.Name = formCollection["Name"];
      employee.Gender = formCollection["Gender"];
      employee.City = formCollection["City"];
      using (EmployeeContext employeeContext = new EmployeeContext())
      {
          employeeContext.Employees.Add(employee);
          employeeContext.SaveChanges();
      }
      return RedirectToAction("Index");
  }

• Using Explicit Parameters

  [HttpPost]
  public ActionResult Create(string name, string gender, string city)
  {
      Employee employee = new Employee();
      employee.Name = name;
      employee.Gender = gender;
      employee.City = city;
      using (EmployeeContext employeeContext = new EmployeeContext())
      {
          employeeContext.Employees.Add(employee);
          employeeContext.SaveChanges();
      }
      return RedirectToAction("Index");
  }

• Using Employee Object

  [HttpPost]
  public ActionResult Create(Employee employee)
  {
      using (EmployeeContext employeeContext = new EmployeeContext())
      {
          employeeContext.Employees.Add(employee);
          employeeContext.SaveChanges();
      }
      return RedirectToAction("Index");
  }

Edit Action Method and View

Edit Action Method (HttpGet)

• Retrieves the Employee record to edit:

  [HttpGet]
  public ActionResult Edit(int id)
  {
      using (EmployeeContext employeeContext = new EmployeeContext())
      {
          Employee employee = employeeContext.Employees.Single(emp => emp.ID == id);
          return View(employee);
      }
  }

Edit View - Edit.cshtml

• Structure similar to Create view with fields populated with existing data to allow updates:

  @model EmployeeMVC.Models.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.ID)
          <!-- Similar with fields for Name, Gender, City -->
          <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>
  }

Edit Action Method (HttpPost)

• Updates the Employee record in the database:

  [HttpPost]
  public ActionResult Edit(Employee employee)
  {
      using (EmployeeContext employeeContext = new EmployeeContext())
      {
          employeeContext.Entry(employee).State = System.Data.Entity.EntityState.Modified;
          employeeContext.SaveChanges();
      }
      return RedirectToAction("Index");
  }

Delete Action Method and View

Delete Action Method (HttpGet)

• Retrieves the Employee record intended for deletion:

  [HttpGet]
  public ActionResult Delete(int id)
  {
      using (EmployeeContext employeeContext = new EmployeeContext())
      {
          Employee employee = employeeContext.Employees.Single(emp => emp.ID == id);
          return View(employee);
      }
  }

Delete View - Delete.cshtml

• Asking for confirmation to delete:

  @model EmployeeMVC.Models.Employee
  @{ ViewBag.Title = "Delete"; }
  <h2>Delete</h2>
  <h3>Are you sure you want to delete this?</h3>
  <div>
      <h4>Employee</h4>
      <hr />
      <dl class="dl-horizontal">
          <dt>@Html.DisplayNameFor(model => model.Name)</dt>
          <dd>@Html.DisplayFor(model => model.Name)</dd>
          <dt>@Html.DisplayNameFor(model => model.Gender)</dt>
          <dd>@Html.DisplayFor(model => model.Gender)</dd>
          <dt>@Html.DisplayNameFor(model => model.City)</dt>
          <dd>@Html.DisplayFor(model => model.City)</dd>
      </dl>
      <form action="@Url.Action("DeleteConfirmed", new { id = Model.ID })" method="post">
          <input type="submit" value="Delete" class="btn btn-danger" />
      </form>
      <a href="@Url.Action("Index")">Back to List</a>
  </div>

Delete Action Method (HttpPost)

• Actual deletion of the Employee record:

  [HttpPost, ActionName("Delete")]
  public ActionResult DeleteConfirmed(int id)
  {
      using (EmployeeContext employeeContext = new EmployeeContext())
      {
          Employee employee = employeeContext.Employees.Single(emp => emp.ID == id);
          employeeContext.Employees.Remove(employee);
          employeeContext.SaveChanges();
      }
      return RedirectToAction("Index");
  }

Details Action Method and View

Details Action Method

• Retrieves detailed information of a single Employee record:

  public ActionResult Details(int id)
  {
      using (EmployeeContext employeeContext = new EmployeeContext())
      {
          Employee employee = employeeContext.Employees.Single(emp => emp.ID == id);
          return View(employee);
      }
  }

Details View - Details.cshtml

• Displays detailed information about the Employee:

  @model EmployeeMVC.Models.Employee
  @{ ViewBag.Title = "Details"; }
  <h2>Details</h2>
  <div>
      <h4>Employee</h4>
      <hr />
      <dl class="dl-horizontal">
          <dt>@Html.DisplayNameFor(model => model.Name)</dt>
          <dd>@Html.DisplayFor(model => model.Name)</dd>
          <dt>@Html.DisplayNameFor(model => model.Gender)</dt>
          <dd>@Html.DisplayFor(model => model.Gender)</dd>
          <dt>@Html.DisplayNameFor(model => model.City)</dt>
          <dd>@Html.DisplayFor(model => model.City)</dd>
      </dl>
      <a href="@Url.Action("Index")">Back to List</a>
  </div>

Summary of MVC CRUD Operations

• The Controller in ASP.NET MVC generates CRUD action methods and views through scaffolding.

• Developers can customize functionality by inserting logic within these actions while the basic structure remains intact.

• Implementing a CRUD application through MVC ensures that data handling is systematic, structured, and easy to maintain.