ASP.NET Core MVC CRUD Using Entity Framework Core, Bootstrap 4 and SQL Server
Hello and Good Day!
3. Add NuGet packages needed for the Entity Framework Core ORM specifically EntityFrameworkCore, EntityFrameworkCore.SqlServer and EntityFrameworkCore.Tools. All of them pointing to the 5.0.4 version.
4. As for the libraries, I installed the font-awesome 5.x version along with jQuery and jQuery validation files.
5. Next is to create a table in your SQL Server database since this tutorial implements the Database First approach.
2. Next is to add an EmployeeRecord class with properties identical to the Employee class which serves as the View Model. The properties of this class have been decorated with DataAnnotations attribute and will be used for databinding with the Html Razor controls. In this way, I don't have to manipulate or apply changes to the class produced by the Scaffhold command.
3. Next is to remove the connection string from the DBContext class and transfer that inside the appsettings.json file.
2. Add another repository interface called IEmployeeRepository that inherits the IRepository interface. This interface may contain method definitions which is unique for an employee entity.
3. Create a generic repository class that implements the methods of IRepository interface. This lass communicates with the database via the DBContext class and performs the actual CRUD operations using Entity Framework Core built-in methods.
4. Define a specific repository class for the Employee entity that inherits both the generic repository class and IEmployeeRepository interface.
5. Create an interface called IUnitOfWork that inherits the IDisposable interface and has an IEmployeeRepository property with a get accessor only and Complete method declaration that returns int.
6. For the Unit Of Work pattern to work, create a class that implements the IUnitOfWork interface and injects the DBContext to it's constructor.
7. In Startup.cs ConfigureServices() method, a dependency injection happens through AddTransient() method by directly associating the service type (interface) and it's implementation (class). This method also assigns the the connection string for the DBContext class.
The Data Access structure resembles with screenshot provided below.
2. Index.cshtml - This will load all the records and show them to the table. Each table row has a delete and edit button which redirects to separate pages. The page also has a new button that redirects to a page that will add a new Employee record.
3. Create.cshtml - This view is responsible for adding new records to the database. The model class used in this view is the EmployeeRecord with DataAnnotations attribute used in validating user input.
4. Edit.cshtml - This view is responsible for editing existing records from the database. The form layout is similar with the Create page except that we have a hidden field for the EmployeeID.
5. Delete.cshtml - This view shows the record to be deleted. When submitted, it will call the Delete method in the controller and remove that record from the database using EmployeeID.
6. Employee.css - Custom CSS styles used in the views.
This article demonstrates on how to create an ASP.NET Core MVC application using Entity Framework Core Database First, Bootstrap 4, SQL Server, Unit Of Work And Repository Design Pattern. The IDE for this project is Visual Studio 2019 version 16.9.2. I also installed the Font Awesome icons since glyphicons are not supported in Bootstrap 4 and applied the Model Validation technique for checking of input data during submission of a form.
I. Project Setup
1. Create an ASP.NET Core MVC application targetting .NET Core 3.1 or perhaps .NET 5.0 if you have it installed in your machine.
2. The project structure is composed of several folders such as Controllers, Data Access, Models and Views. For Data Access, add two more subfolders called Repository and UnitOfWork. We will need to segregate the interface and classes for the Data Access Layer. For the purpose of this tutorial, I did not add several class libraries in order keep this simple and straightforward.
2. The project structure is composed of several folders such as Controllers, Data Access, Models and Views. For Data Access, add two more subfolders called Repository and UnitOfWork. We will need to segregate the interface and classes for the Data Access Layer. For the purpose of this tutorial, I did not add several class libraries in order keep this simple and straightforward.
USE [YourDatabase] GO /****** Object: Table [dbo].[Employee] Script Date: 4/7/2021 11:11:47 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Employee]( [EmployeeID] [int] IDENTITY(1,1) NOT NULL, [EmployeeGovtID] [varchar](50) NULL, [EmployeeName] [varchar](200) NULL, [Age] [int] NULL, [Address] [nvarchar](max) NULL, [Salary] [decimal](18, 2) NULL, [Designation] [varchar](10) NULL, [HasDependents] [bit] NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [EmployeeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
II. Coding The Model Classes
1. Run the Scaffold-DBContext in the Package Manager Conosle of your project to connect to the database schema you just created earlier. This will reverse engineer the database table and scaffold entity type classes and DbContext class. After running the command below, this will create Employee and DBContext class inside the Models folder.Scaffold-DbContext "Server=Your_Server;Database=Your_Database;Integrated Security=True" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
public class EmployeeRecord { public int EmployeeId { get; set; } [Display(Name = "Government ID")] [Required(ErrorMessage = "Government ID is required")] public string EmployeeGovtId { get; set; } [Display(Name = "Employee Name")] [Required(ErrorMessage = "Name is required")] public string EmployeeName { get; set; } [Required(ErrorMessage = "Age is required")] [Range(0, int.MaxValue, ErrorMessage = "Please enter integer number")] public int? Age { get; set; } [Required(ErrorMessage = "Address is required")] public string Address { get; set; } [Required] [Range(1000, 99999.99, ErrorMessage = "Please enter value from 1000 to 99999.99")] public decimal? Salary { get; set; } [Required(ErrorMessage = "Designation is required")] [MinLength(2, ErrorMessage = "Min length of designation is 2 characters")] public string Designation { get; set; } [Display(Name = "Has Dependents")] public bool HasDependents { get; set; } public string DisplayForDependents { get { if (HasDependents) return "Yes"; return "No"; } } public EmployeeRecord() { EmployeeId = 0; Address = string.Empty; Salary = 0; Age = 0; Address = string.Empty; EmployeeName = string.Empty; Designation = string.Empty; EmployeeGovtId = string.Empty; HasDependents = false; } }
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"ASPCoreDB": "Server=Your_Machine;Database=Your_Database;Integrated Security=True"
}
}
III. Coding The Data Access Classes And Interfaces (Repository and Unit Of Work)
1. Create an interface called IRepository that contains method declarations of common CRUD (Create, Update and Delete) operations. public interface IRepository<T> where T: class
{
T GetByID(int Id);
IEnumerable<T> GetAll();
IEnumerable<T> Find(Expression<Func<T, bool>> expression);
void Add(T Entity);
void Delete(T Delete);
void Update(T Entity);
}
public interface IEmployeeRepository : IRepository<Employee> { //you may add other methods here specific to employees. }
public class Repository<T> : IRepository<T> where T : class{ private ASPCoreTestDBContext _context; public Repository(ASPCoreTestDBContext context) { this._context = context; } public void Add(T Entity) { _context.Set<T>().Add(Entity); } public void Delete(T Entity) { _context.Set<T>().Remove(Entity); } public IEnumerable<T> Find(Expression<Func<T, bool>> expression) { return _context.Set<T>().Where(expression); } public IEnumerable<T> GetAll() { return _context.Set<T>().ToList(); } public T GetByID(int Id) { return _context.Set<T>().Find(Id); } public void Update(T Entity) { _context.Set<T>().Update(Entity); } }
public class EmployeeRepository : Repository<Employee>, IEmployeeRepository { public EmployeeRepository(ASPCoreTestDBContext context) : base(context) { } }
public interface IUnitOfWork : IDisposable { IEmployeeRepository EmployeeRepository { get; } int Complete(); }
public class UnitOfWork : IUnitOfWork { private readonly ASPCoreTestDBContext _context; public IEmployeeRepository EmployeeRepository { get; private set; } public UnitOfWork(ASPCoreTestDBContext context) { _context = context; EmployeeRepository = new EmployeeRepository(_context); } public int Complete() { return _context.SaveChanges(); } public void Dispose() { _context.Dispose(); } }
public void ConfigureServices(IServiceCollection services) { services.AddTransient(typeof(IRepository<>), typeof(Repository<>)); services.AddTransient<IEmployeeRepository, EmployeeRepository>(); services.AddTransient<IUnitOfWork, UnitOfWork>(); var connectionString = Configuration.GetConnectionString("ASPCoreDB"); services.AddDbContext<YourDBContext>(x => x.UseSqlServer(connectionString)); services.AddControllersWithViews(); }
IV. Coding The Controller
1. In your HomeController.cs class, create private fields for Unit Of Work and DBContext objects. These variables will initialized in the HomeController constructor. Next is to add ActionResult methods for the CRUD operations and Index which will show the entire employee records. The Unit Of Work object glues everything together by calling the CRUD methods defined in the Repository class using EFCore's built-in functions.
public class HomeController : Controller { private readonly ILogger<HomeController> _logger; private IUnitOfWork _unitOfWork; private readonly ASPCoreTestDBContext _context; public HomeController(ILogger<HomeController> logger, IUnitOfWork unitOfWork, ASPCoreTestDBContext context) { _logger = logger; _unitOfWork = unitOfWork; _context = context; } public IActionResult Index() { List<EmployeeRecord> model; model = new List<EmployeeRecord>(); var result = _unitOfWork.EmployeeRepository.GetAll(); if (result.Count() > 0) { foreach (var item in result) { model.Add(new EmployeeRecord() { EmployeeId = item.EmployeeId, Address = item.Address, Age = item.Age, Designation = item.Designation, EmployeeGovtId = item.EmployeeGovtId, EmployeeName = item.EmployeeName, HasDependents = (bool)item.HasDependents, Salary = item.Salary, }); } } return View(model); } [HttpGet] public IActionResult Create() { return View(); } [HttpPost] [ValidateAntiForgeryToken] public IActionResult Create(EmployeeRecord employee) { Employee empObject; empObject = new Employee(); if (ModelState.IsValid) { empObject.Address = employee.Address; empObject.Age = employee.Age; empObject.Designation = employee.Designation; empObject.EmployeeGovtId = employee.EmployeeGovtId; empObject.EmployeeName = employee.EmployeeName; empObject.HasDependents = employee.HasDependents; empObject.Salary = employee.Salary; _unitOfWork.EmployeeRepository.Add(empObject); _unitOfWork.Complete(); return RedirectToAction("Index"); } return View("Index"); } [HttpGet] public IActionResult Edit(int? id) { EmployeeRecord model; model = new EmployeeRecord(); if (id == null) { return NotFound(); } var employee = _unitOfWork.EmployeeRepository.GetByID(Convert.ToInt32(id)); if (employee == null) { return NotFound(); } model.EmployeeId = employee.EmployeeId; model.Address = employee.Address; model.Age = employee.Age; model.Designation = employee.Designation; model.EmployeeGovtId = employee.EmployeeGovtId; model.EmployeeName = employee.EmployeeName; model.HasDependents = (bool)employee.HasDependents; model.Salary = employee.Salary; return View(model); } [HttpPost] [ValidateAntiForgeryToken] public IActionResult Edit(int id, EmployeeRecord employee) { Employee empObject; empObject = new Employee(); if (id != employee.EmployeeId) { return NotFound(); } if (ModelState.IsValid) { try { empObject.EmployeeId = employee.EmployeeId; empObject.Address = employee.Address; empObject.Age = employee.Age; empObject.Designation = employee.Designation; empObject.EmployeeGovtId = employee.EmployeeGovtId; empObject.EmployeeName = employee.EmployeeName; empObject.HasDependents = employee.HasDependents; empObject.Salary = employee.Salary; _unitOfWork.EmployeeRepository.Update(empObject); _unitOfWork.Complete(); } catch (DbUpdateConcurrencyException) { if (!EmployeeExists(employee.EmployeeId)) { return NotFound(); } else { throw; } } return RedirectToAction(nameof(Index)); } return View(employee); } [HttpGet] public IActionResult Delete(int? id) { EmployeeRecord model; model = new EmployeeRecord(); if (id == null) { return NotFound(); } var employee = _unitOfWork.EmployeeRepository.GetByID(Convert.ToInt32(id)); if (employee == null) { return NotFound(); } model.EmployeeId = employee.EmployeeId; model.Address = employee.Address; model.Age = employee.Age; model.Designation = employee.Designation; model.EmployeeGovtId = employee.EmployeeGovtId; model.EmployeeName = employee.EmployeeName; model.HasDependents = (bool)employee.HasDependents; model.Salary = employee.Salary; return View(model); } [HttpPost, ActionName("Delete")] [ValidateAntiForgeryToken] public IActionResult DeleteConfirmed(int id) { var employee = _unitOfWork.EmployeeRepository.GetByID(id); if (employee != null) { _unitOfWork.EmployeeRepository.Delete(employee); _unitOfWork.Complete(); } return RedirectToAction(nameof(Index)); } private bool EmployeeExists(int id) { return _unitOfWork.EmployeeRepository.GetByID(id) != null ? true : false; } public IActionResult Privacy() { return View(); } [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)] public IActionResult Error() { return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier }); } }
V. Coding The Views
1. Layout.cshtml - Reference the necessary JavaScript and CSS files like jQuery.min.js, jquery.validate.unobtrusive.min.js, jquery.validate.min.js, bootstrap.min.css, fontawesome.css, regular.min.css and solid.min.css<!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <title>@ViewData["Title"] - ABC Inc.</title> <link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.min.css" /> <link rel="stylesheet" href="~/css/site.css" /> </head> <body> @* header, container and footer elements here *@ <script src="~/lib/jquery/dist/jquery.min.js"></script> <script src="~/lib/bootstrap/dist/js/bootstrap.bundle.min.js"></script> <script src="~/js/site.js" asp-append-version="true"></script> <script src="https://ajax.aspnetcdn.com/ajax/jquery.validate/1.14.0/jquery.validate.min.js"></script> <script src="https://ajax.aspnetcdn.com/ajax/mvc/5.2.3/jquery.validate.unobtrusive.min.js"></script> <link rel="stylesheet" href="~/lib/font-awesome/css/fontawesome.css" /> <link rel="stylesheet" href="~/lib/Font-Awesome/css/regular.min.css"> <link rel="stylesheet" href="~/lib/Font-Awesome/css/solid.min.css"> @RenderSection("Scripts", required: false) </body> </html>
@model IEnumerable<ASPCoreMVCCrud.Models.EmployeeRecord>
@{
ViewData["Title"] = "Index";
}
<div class="divCompanyHeader">
<h3>ABC Manpower Services Inc. Employee MasterList</h3>
</div>
<div class="row" id="divCreateLink">
<a asp-action="Create" class="btn btn-info">
<i class="fas fa-plus"><span>Add Employee</span></i>
</a>
</div>
<div class="row">
<table class="table table-bordered table-striped">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.EmployeeGovtId)
</th>
<th>
@Html.DisplayNameFor(model => model.EmployeeName)
</th>
<th>
@Html.DisplayNameFor(model => model.Age)
</th>
<th>
@Html.DisplayNameFor(model => model.Address)
</th>
<th>
@Html.DisplayNameFor(model => model.Salary)
</th>
<th>
@Html.DisplayNameFor(model => model.Designation)
</th>
<th>
@Html.DisplayNameFor(model => model.HasDependents)
</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@if (Model != null)
{
foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.EmployeeGovtId)
</td>
<td>
@Html.DisplayFor(modelItem => item.EmployeeName)
</td>
<td>
@Html.DisplayFor(modelItem => item.Age)
</td>
<td>
@Html.DisplayFor(modelItem => item.Address)
</td>
<td>
@Html.DisplayFor(modelItem => item.Salary)
</td>
<td>
@Html.DisplayFor(modelItem => item.Designation)
</td>
<td>
@Html.DisplayFor(modelItem => item.DisplayForDependents)
</td>
<td>
<a asp-action="Edit" title="Edit Employee" asp-route-id="@item.EmployeeId">
<i class="fas fa-edit"></i>
</a>
<a asp-action="Delete" title="Delete Employee" asp-route-id="@item.EmployeeId">
<i class="fas fa-trash"></i>
</a>
</td>
</tr>
}
}
</tbody>
</table>
</div>
@section scripts{
<link href="~/css/Employee.css" rel="stylesheet" />
}
@model ASPCoreMVCCrud.Models.EmployeeRecord
@{
ViewData["Title"] = "Create";
}
<div class="divCompanyHeader">
<h3>Add New Employee Record</h3>
</div>
<hr />
<div class="row">
<div class="col-6">
<form asp-action="Create">
<div asp-validation-summary="ModelOnly" class="text-danger"></div>
<div class="form-group row">
<label asp-for="EmployeeGovtId" class="col-4 control-label"></label>
<div class="col-8">
<input asp-for="EmployeeGovtId" class="form-control" />
<span asp-validation-for="EmployeeGovtId" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="EmployeeName" class="col-4 control-label"></label>
<div class="col-8">
<input asp-for="EmployeeName" class="form-control" />
<span asp-validation-for="EmployeeName" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Age" class="col-4 control-label"></label>
<div class="col-8">
<input asp-for="Age" class="form-control" />
<span asp-validation-for="Age" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Address" class="col-4 control-label"></label>
<div class="col-8">
<input asp-for="Address" class="form-control" />
<span asp-validation-for="Address" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Salary" class="col-4 control-label"></label>
<div class="col-8">
<input asp-for="Salary" class="form-control" />
<span asp-validation-for="Salary" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Designation" class="col-4 control-label"></label>
<div class="col-8">
<input asp-for="Designation" class="form-control" />
<span asp-validation-for="Designation" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<div class="col-4">
</div>
<div class="col-8">
<input id="chkDependents" class="form-check-input" type="checkbox" asp-for="HasDependents" />
<span id="spnDependents">Dependents?</span>
</div>
</div>
<div class="form-group row">
<div class="col-4">
</div>
<div class="col-8">
<input type="submit" value="Save" class="btn btn-primary" />
<a asp-action="Index" class="btn btn-info">Back to List</a>
</div>
</div>
</form>
</div>
</div>
@section scripts{
<link href="~/css/Employee.css" rel="stylesheet" />
}
@model ASPCoreMVCCrud.Models.EmployeeRecord
@{
ViewData["Title"] = "Edit";
}
<div class="divCompanyHeader">
<h3>Update Employee Record</h3>
</div>
<hr />
<div class="row">
<div class="col-md-6">
<form asp-action="Edit">
<div asp-validation-summary="ModelOnly" class="text-danger"></div>
<input type="hidden" asp-for="EmployeeId" />
<div class="form-group row">
<label asp-for="EmployeeGovtId" class="col-4 control-label"></label>
<div class="col-8">
<input asp-for="EmployeeGovtId" class="form-control" />
<span asp-validation-for="EmployeeGovtId" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="EmployeeName" class="col-4 control-label"></label>
<div class="col-8">
<input asp-for="EmployeeName" class="form-control" />
<span asp-validation-for="EmployeeName" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Age" class="col-4 control-label"></label>
<div class="col-8">
<input asp-for="Age" class="form-control" />
<span asp-validation-for="Age" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Address" class="col-4 control-label"></label>
<div class="col-8">
<input asp-for="Address" class="form-control" />
<span asp-validation-for="Address" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Salary" class="col-4 control-label"></label>
<div class="col-8">
<input asp-for="Salary" class="form-control" />
<span asp-validation-for="Salary" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Designation" class="col-4 control-label"></label>
<div class="col-8">
<input asp-for="Designation" class="form-control" />
<span asp-validation-for="Designation" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<div class="col-4">
</div>
<div class="col-8">
<input asp-for="HasDependents" id="chkDependents" class="form-check-input" type="checkbox" />
<span id="spnDependents">Dependents?</span>
</div>
</div>
<div class="form-group row">
<div class="col-4">
</div>
<div class="col-8">
<input type="submit" value="Update" class="btn btn-primary" />
<a asp-action="Index" class="btn btn-info">Back to List</a>
</div>
</div>
</form>
</div>
</div>
@section scripts{
<link href="~/css/Employee.css" rel="stylesheet" />
}
@model ASPCoreMVCCrud.Models.EmployeeRecord
@{
ViewData["Title"] = "Delete";
}
<div class="divCompanyHeader">
<h3>Delete Employee Record?</h3>
</div>
<hr />
<div>
<dl class="row">
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.EmployeeGovtId)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.EmployeeGovtId)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.EmployeeName)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.EmployeeName)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.Age)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Age)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.Address)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Address)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.Salary)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Salary)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.Designation)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Designation)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.HasDependents)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.HasDependents)
</dd>
</dl>
<form asp-action="Delete">
<input type="hidden" asp-for="EmployeeId" />
<input type="submit" value="Delete" class="btn btn-danger" />
<a asp-action="Index" class="btn btn-info">Back to List</a>
</form>
</div>
@section scripts{
<link href="~/css/Employee.css" rel="stylesheet" />
}
#divCreateLink {
margin-top: 40px;
margin-bottom: 10px;
color: white !important;
}
#divCreateLink span {
margin-left: 5px;
}
.divCompanyHeader{
margin-top: 50px;
text-align: center;
}
#chkDependents {
margin-left: 0px !important;
}
#spnDependents {
margin-left: 20px;
}
table i{
color: black;
}
VI. Wrapping Up
In this post, we learned how to create an ASP.NET Core MVC CRUD website using Entity Framework Core 5, SQL Server and Bootstrap 4. This tutorial also embraced the font awesome icons instead of glyphicons. All we need to do when following this tutorial is to make sure to update the connection string value with your database and server. In addition, you need to replace the DBContext and namespace name of the files that you've created.
Comments
Post a Comment