Create An ASP.NET Core MVC CRUD Website Using RepoDB ORM And SQL Server
Hello All!
4. Add Nuget package RepoDB.SqlServer for communicating with SQL Server database.
5. Install font-awesome 5.x version via libman.
6. Next is to add the connection string inside appsettings.json. Be sure to replace the server and database name.
3. Inside your UnitOfWork folder, create an IUnitOfWork interface with an Athletes repository of type IRepository.
4. Then add a UnitOfWork class that implements the IUnitOfWork interface by creating an AthletesRepository property of which it's value is set in the contructor.
5. In Startup.cs file, initialize the SqlServerBootstrap class of RepoDB and map the interface with it's corresponding implementation using the AddTransient() method.
2. Index.cshtml - This page has a table that will be populated with records retrieved from the datasource. Each row has a delete and edit button that redirects to delete and edit pages respectively. The page also has a new button that opens up the Add New Athlete information.
3. Create.cshtml - This view is responsible for adding new athletes to the database.
4. Edit.cshtml - This page is responsible for editing existing athletes from the database. The form layout is similar to the Create page except that it has a hidden field for the athlete ID. Since the ID is an identity column in our database, we don't need to edit that.
5. Delete.cshtml - This page will show the record to be deleted. When the form is submitted, it will call the Delete method in the controller and remove that record from the database using the player's ID.
6. Athletes.css - This file has the custom css styles used by the project's views.
When running the project, the Index view will show on screen.
This blog post illustrates on how to create an ASP.NET Core 5.0 MVC CRUD Web Application using RepoDB ORM and SQL Server. RepoDB is a hybrid-ORM library for .NET which is an alternative ORM to both Dapper and EntityFramework. This framework was created by Michael Camara Pendon. This project also integrates Bootstrap 4 for styling and Unit Of Work with Repository Design Pattern as the data mechanism. I also installed the Font Awesome 5 via Libman for showing icons to the button controls. So to get started, perform the following steps below.
I. Project Setup
1. Create an Athletes table in your database using the script provided. This table will serve as the dataset for this project.
2. Create a new ASP.NET Core 5.0 MVC web application.
3. Add a new folder called Data Access Layer with two subfolders specifically Repository and UnitOfWork. For this project, I did not add class libraries that separates the infrastructure and models since this only has minimal coding involved.
USE [your_database] GO /****** Object: Table [dbo].[Athletes] Script Date: 5/5/2021 4:13:47 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Athletes]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](100) NULL, [Age] [int] NULL, [Country] [varchar](50) NULL, [Sport] [varchar](50) NULL, [Allowance] [decimal](18, 2) NULL, [Weight] [decimal](18, 2) NULL, [Height] [decimal](18, 2) NULL, [Gender] [varchar](50) NULL, CONSTRAINT [PK_Athletes] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
3. Add a new folder called Data Access Layer with two subfolders specifically Repository and UnitOfWork. For this project, I did not add class libraries that separates the infrastructure and models since this only has minimal coding involved.
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"ASPCoreRepoDB": "Server=YourServer;Database=YourDB;Integrated Security=True"
}
}
II. Coding The Model classes
1. Inside your Models folder, add a class that contains information that describes an athlete. This model does not have validation attributes per se. You may opt to add them if you want to.[Serializable] public class Athletes { public int ID { get; set; } public string Name { get; set; } public int Age { get; set; } public string Country { get; set; } public string Sport { get; set; } public decimal Allowance { get; set; } public decimal Weight { get; set; } public decimal Height { get; set; } public string Gender { get; set; } public Athletes() { ID = 0; Name = string.Empty; Age = 0; Country = string.Empty; Sport = string.Empty; Allowance = 0; Weight = 0; Height = 0; Gender = string.Empty; } }
III. Coding The Data Access Classes And Interfaces (Repository and Unit Of Work)
In this section, we will create inferaces and classes using the Repository Design Pattern and Unit Of Work. For further explanation, please refer to this page Implementing the Repository and Unit of Work Patterns in an ASP.NET MVC Application provided by Microsoft.
1. Add a IRepository interface with common function definitions for CRUD operations such as FindByID(), GetAll(), Add(), Delete() and Update().
public interface IRepository<T> where T: class{ T FindByID(int ID); IEnumerable<T> GetAll(); int Add(T Entity); int Delete(T Entity); int Update(T Entity); }
2. Create a class called GenericRepository that implements the IRepository interface. This class has two variables which is an IConfiguration that retrieve the connection string value from appsettings.json and a connection string. In the constructor method, assign value for the IConfiguration variable using constructor injection. Once initialized, use the GettConnectionString() method to retrieve the connection string value and store it to the class level variable. This class also implements the methods defined in IRepository interface utilizing RepoDB's ORM pre-defined set of functions for manipulating database information.
public class GenericRepository<T> : IRepository<T> where T : class{ private readonly IConfiguration _configuration; private readonly string connectionString; public GenericRepository(IConfiguration configuration) { _configuration = configuration; connectionString = _configuration.GetConnectionString("ASPCoreRepoDB"); } public int Add(T Entity) { int id = 0; try { using (var connection = new SqlConnection(this.connectionString)) { id = (int)connection.Insert<T>(Entity); } } catch (Exception ex) { return -1; } return id; } public int Update(T Entity) { int affectedRows = 0; try { using (var connection = new SqlConnection(this.connectionString)) { affectedRows = (int)connection.Update<T>(Entity); } } catch (Exception ex) { return -1; } return affectedRows; } public int Delete(T Entity) { int affectedRows = 0; try { using (var connection = new SqlConnection(this.connectionString)) { affectedRows = (int)connection.Delete<T>(Entity); } } catch (Exception ex) { return -1; } return affectedRows; } public T FindByID(int ID) { T result; try { using (var connection = new SqlConnection(this.connectionString)) { result = connection.Query<T>(ID).FirstOrDefault(); } } catch (Exception ex) { return null; } return result; } public IEnumerable<T> GetAll() { List<T> lstAthletes; lstAthletes = new List<T>(); try { using (var connection = new SqlConnection(this.connectionString)) { connection.Open(); lstAthletes = connection.QueryAll<T>().ToList(); } } catch (Exception ex) { return null; } return lstAthletes; } }
public interface IUnitOfWork { IRepository<Athletes> AthletesRepository { get; } }
public class UnitOfWork : IUnitOfWork { public IRepository<Athletes> AthletesRepository { get; private set; } public UnitOfWork(IRepository<Athletes> repository) { this.AthletesRepository = repository; } }
public void ConfigureServices(IServiceCollection services) { RepoDb.SqlServerBootstrap.Initialize(); services.AddTransient(typeof(IRepository<>), typeof(GenericRepository<>)); services.AddTransient<IUnitOfWork, UnitOfWork>(); services.AddControllersWithViews(); }
IV. Coding The Controller class
In the HomeController.cs class, create a class level Unit Of Work variable which will then be assigned a value through the HomeController constructor. For this to work, you already have mapped the service type and it's implementation in Startup.cs file as mentioned above. And then create ActionResult methods that will show all athletes to the view, add a new athlete to the database, update it's value and delete it from the database. Each ActionResult method defined will call the CRUD methods of the repository class that utilzed RepoDB's awesome functions through the Unit Of Work variable.
public class HomeController : Controller { private readonly ILogger<HomeController> _logger; private IUnitOfWork _unitOfWork; public HomeController(ILogger<HomeController> logger, IUnitOfWork unitOfWork) { _logger = logger; _unitOfWork = unitOfWork; } public IActionResult Index() { List<Athletes> model; model = new List<Athletes>(); model = _unitOfWork.AthletesRepository.GetAll().ToList(); return View(model); } [HttpGet] public IActionResult Create() { return View(); } [HttpPost] [ValidateAntiForgeryToken] public IActionResult Create(Athletes athlete) { if (ModelState.IsValid) { _unitOfWork.AthletesRepository.Add(athlete); return RedirectToAction("Index"); } return View("Index"); } [HttpGet] public IActionResult Edit(int? id) { Athletes model; model = new Athletes(); if (id == null) return NotFound(); model = _unitOfWork.AthletesRepository.FindByID(Convert.ToInt32(id)); if (model == null) return NotFound(); return View(model); } [HttpPost] public ActionResult Edit(Athletes athlete) { try { _unitOfWork.AthletesRepository.Update(athlete); return RedirectToAction("Index"); } catch { return View(); } } [HttpGet] public IActionResult Delete(int? id) { Athletes model; model = new Athletes(); if (id == null) return NotFound(); model = _unitOfWork.AthletesRepository.FindByID(Convert.ToInt32(id)); if (model == null) return NotFound(); return View(model); } [HttpPost] [ValidateAntiForgeryToken] public ActionResult Delete(Athletes athlete) { try { _unitOfWork.AthletesRepository.Delete(athlete); return RedirectToAction("Index"); } catch { return View(); } } 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 fontawesome css files namely 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"] - Superstar Athletes</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<ASPCoreMVCRepoDB.Models.Athletes>
@{
ViewData["Title"] = "Index";
}
<div id="divIndexHeader">
<h3>Athletes Record Monitoring</h3>
</div>
<div id="divIndexCreate" class="row">
<a asp-action="Create" class="btn btn-primary">
<i class="fas fa-plus"><span> Add Athlete</span></i>
</a>
</div>
<div class="row">
<table id="tblRecords" class="table table-bordered table-striped">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.ID)
</th>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.Age)
</th>
<th>
@Html.DisplayNameFor(model => model.Country)
</th>
<th>
@Html.DisplayNameFor(model => model.Sport)
</th>
<th>
@Html.DisplayNameFor(model => model.Allowance)
</th>
<th>
@Html.DisplayNameFor(model => model.Weight)
</th>
<th>
@Html.DisplayNameFor(model => model.Height)
</th>
<th>
@Html.DisplayNameFor(model => model.Gender)
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.ID)
</td>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.Age)
</td>
<td>
@Html.DisplayFor(modelItem => item.Country)
</td>
<td>
@Html.DisplayFor(modelItem => item.Sport)
</td>
<td>
@Html.DisplayFor(modelItem => item.Allowance)
</td>
<td>
@Html.DisplayFor(modelItem => item.Weight)
</td>
<td>
@Html.DisplayFor(modelItem => item.Height)
</td>
<td>
@Html.DisplayFor(modelItem => item.Gender)
</td>
<td>
<a asp-action="Edit" title="Edit Employee" asp-route-id="@item.ID">
<i class="fas fa-edit"></i>
</a>
<a asp-action="Delete" title="Delete Employee" asp-route-id="@item.ID">
<i class="fas fa-trash"></i>
</a>
</td>
</tr>
}
</tbody>
</table>
</div>
@section scripts{
<link href="~/css/Athletes.css" rel="stylesheet" />
}
@model ASPCoreMVCRepoDB.Models.Athletes
@{
ViewData["Title"] = "Create";
}
<div>
<h3>Add New Athlete</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="Name" class="control-label col-4"></label>
<div class="col-8">
<input asp-for="Name" class="form-control" />
<span asp-validation-for="Name" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Age" class="control-label col-4"></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="Country" class="control-label col-4"></label>
<div class="col-8">
<input asp-for="Country" class="form-control" />
<span asp-validation-for="Country" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Sport" class="control-label col-4"></label>
<div class="col-8">
<input asp-for="Sport" class="form-control" />
<span asp-validation-for="Sport" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Allowance" class="control-label col-4"></label>
<div class="col-8">
<input asp-for="Allowance" class="form-control" />
<span asp-validation-for="Allowance" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Weight" class="control-label col-4"></label>
<div class="col-8">
<input asp-for="Weight" class="form-control" />
<span asp-validation-for="Weight" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Height" class="control-label col-4"></label>
<div class="col-8">
<input asp-for="Height" class="form-control" />
<span asp-validation-for="Height" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Gender" class="control-label col-4"></label>
<div class="col-8">
<input asp-for="Gender" class="form-control" />
<span asp-validation-for="Gender" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<div class="col-4"></div>
<div class="col-8">
<input type="submit" value="Create" class="btn btn-primary" />
<a asp-action="Index" class="btn btn-info">Back to List</a>
</div>
</div>
</form>
</div>
</div>
@model ASPCoreMVCRepoDB.Models.Athletes
@{
ViewData["Title"] = "Edit";
}
<div>
<h3>Edit Athlete Record</h3>
</div>
<hr />
<div class="row">
<div class="col-6">
<form asp-action="Edit">
<div asp-validation-summary="ModelOnly" class="text-danger"></div>
<input type="hidden" asp-for="ID" />
<div class="form-group row">
<label asp-for="Name" class="control-label col-4"></label>
<div class="col-8">
<input asp-for="Name" class="form-control" />
<span asp-validation-for="Name" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Age" class="control-label col-4"></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="Country" class="control-label col-4"></label>
<div class="col-8">
<input asp-for="Country" class="form-control" />
<span asp-validation-for="Country" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Sport" class="control-label col-4"></label>
<div class="col-8">
<input asp-for="Sport" class="form-control" />
<span asp-validation-for="Sport" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Allowance" class="control-labe col-4"></label>
<div class="col-8">
<input asp-for="Allowance" class="form-control" />
<span asp-validation-for="Allowance" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Weight" class="control-label col-4"></label>
<div class="col-8">
<input asp-for="Weight" class="form-control" />
<span asp-validation-for="Weight" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Height" class="control-label col-4"></label>
<div class="col-8">
<input asp-for="Height" class="form-control" />
<span asp-validation-for="Height" class="text-danger"></span>
</div>
</div>
<div class="form-group row">
<label asp-for="Gender" class="control-label col-4"></label>
<div class="col-8">
<input asp-for="Gender" class="form-control" />
<span asp-validation-for="Gender" class="text-danger"></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>
@model ASPCoreMVCRepoDB.Models.Athletes
@{
ViewData["Title"] = "Delete";
}
<div>
<h3>Are you sure you want to delete this record?</h3>
</div>
<hr />
<div>
<dl class="row">
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.Name)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Name)
</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.Country)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Country)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.Sport)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Sport)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.Allowance)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Allowance)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.Weight)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Weight)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.Height)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Height)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.Gender)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Gender)
</dd>
</dl>
<form asp-action="Delete">
<input type="hidden" asp-for="ID" />
<input type="submit" value="Delete" class="btn btn-danger" />
<a asp-action="Index" class="btn btn-info">Back to List</a>
</form>
</div>
#divIndexCreate {
margin-top: 20px;
margin-bottom: 20px;
}
#divIndexHeader {
margin-top: 50px;
margin-bottom: 50px;
text-align:center;
}
#tblRecords{
border-radius: 5px;
}
Comments
Post a Comment