Data Modification
Now we have a way of retrieving data, let's create a way of adding/updating it without having to use SQL Server Management Studio. This is typically done in an “Admin” controller, we'll do this shortly but first we'll create a view model for the admin product.
- Create a folder called “Admin” within the “ViewModels” folder.
- Next create a file called “ProductViewModel.cs” within this folder (as we did previously) with the following content:
using System;
using DemoShop.Shop.Models;
namespace DemoShop.Shop.ViewModels.Admin
public class ProductViewModel {
public ProductViewModel(Product product, TimeZoneInfo timeZone) {
Id = product.Id;
Name = product.Name;
Price = product.Price;
DateAdded = TimeZoneInfo.ConvertTime(product.DateAdded, timeZone);
}
public int Id { get; }
public string Name { get; }
public decimal Price { get; }
public DateTimeOffset DateAdded { get; }
}Note: Unlike the “Home” controller's view model we have exposed the “Id” and “DateAdded” properties to our admin view. Also we convert the date added to the user's time zone (fed in to the constructor).
Now we are ready to create our “Admin” controller. To do this we will add a new file called "AdminController.cs" underneath the "Controllers" folder with the following content:
using System.ComponentModel;
using System.Linq;
using System.Threading.Tasks;
using DemoShop.Shop.Models;
using DemoShop.Shop.ViewModels.Admin;
using Kit.Data;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
namespace DemoShop.Shop.Controllers;
[Area("DemoShop.Shop"), Route("admin")]
public class AdminController : Controller {
private readonly IDataContext _dataContext;
public AdminController(IDataContext dataContext) {
_dataContext = dataContext;
}
[HttpGet("products")]
public async Task<IActionResult> Products(int page = 1, int pageSize = 10, string? sortBy = null, ListSortDirection? sortDirection = null) {
var timeZone = await HttpContext.GetTimeZoneAsync();
return View((await _dataContext.Repository<Product>().All().ToSortedPagedListAsync(page, pageSize, sortBy ?? "Name", sortDirection ?? ListSortDirection.Ascending)).Convert(p => new ProductViewModel(p, timeZone)));
}
}Note: We are using a SortedPagedList instead of a PagedList as we did previously for the "HomeController".
Now add a "Admin" folder underneath the "Views" folder and inside that place a view called "Products.cshtml" with the following content:
@model ISortedPagedList<DemoShop.Shop.ViewModels.Admin.ProductViewModel>
<div class="card shadow">
<div class="card-body">
@if (Model.TotalCount > 0) {
<div class="table-responsive">
<table class="table table-striped">
<thead>
<tr>
<th class="sorting@(Model.SortBy == "Name" ? (Model.SortDirection == ListSortDirection.Ascending ? " sorting-asc" : " sorting-desc") : "")"><a asp-action="Products" asp-all-route-data="@Context.Request.GetQueryValues().AddRange(new { sortBy = "Name", sortDirection = Model.SortDirection == ListSortDirection.Ascending && Model.SortBy == "Name" ? ListSortDirection.Descending : ListSortDirection.Ascending }).ToStringDictionary()">Name</a></th>
<th class="sorting@(Model.SortBy == "Price" ? (Model.SortDirection == ListSortDirection.Ascending ? " sorting-asc" : " sorting-desc") : "")"><a asp-action="Products" asp-all-route-data="@Context.Request.GetQueryValues().AddRange(new { sortBy = "Price", sortDirection = Model.SortDirection == ListSortDirection.Ascending && Model.SortBy == "Price" ? ListSortDirection.Descending : ListSortDirection.Ascending }).ToStringDictionary()">Price</a></th>
<th class="sorting@(Model.SortBy == "DateAdded" ? (Model.SortDirection == ListSortDirection.Ascending ? " sorting-asc" : " sorting-desc") : "")"><a asp-action="Products" asp-all-route-data="@Context.Request.GetQueryValues().AddRange(new { sortBy = "DateAdded", sortDirection = Model.SortDirection == ListSortDirection.Ascending && Model.SortBy == "DateAdded" ? ListSortDirection.Descending : ListSortDirection.Ascending }).ToStringDictionary()">Date Added</a></th>
</tr>
</thead>
<tbody>
@foreach (var product in Model) {
<tr>
<td>@Format(product.Name)</td>
<td>@Format(product.Price.ToString("c"))</td>
<td>@Format(product.DateAdded.ToFormattedString(true))</td>
</tr>
}
</tbody>
</table>
</div>
} else {
<text>No products found.</text>
}
</div>
@if (Model.TotalCount > 0) {
<div class="card-footer">
<pager model="@Model" />
</div>
}
</div>Now we just need to add a migration to add a site map node for our action. Add the following code called "Migration3" beneath the "Migrations" folder:
using FluentMigrator;
namespace DemoShop.Shop.Migrations;
[Migration(201908141812)]
public class Migration3 : AutoReversingMigration {
public override void Up() {
Insert.IntoTable("SiteMapNodes").Row(new { ParentId = RawSql.Insert("(SELECT [Id] FROM [dbo].[SiteMapNodes] WHERE [Action] = 'Content' AND [Controller] = 'Admin' AND [Area] = 'Admin')"), Name = "Products", Action = "Products", Controller = "Admin", Area = "DemoShop.Shop", Order = 1, ShowInMenu = true, ShowInMenuIfHasNoChildren = true, IsLocked = false });
}
}Now navigate to https://localhost:5001/admin/products to see the changes. Clicking on the header will allow you to sort the data.
Inserting Data
Next we'll add the ability to add some products.
Let's start by adding a view model. Add a file called “ProductFormViewModel.cs” under the “ViewModels” → “Admin” folder with the following content:
using DemoShop.Shop.Models;
namespace DemoShop.Shop.ViewModels.Admin;
public class ProductFormViewModel {
public ProductFormViewModel() { }
public ProductFormViewModel(Product product) : this() {
Name = product.Name;
Price = product.Price;
Initialize(product);
}
public int Id { get; set; }
public string Name { get; set; } = default!;
public decimal Price { get; set; }
public ProductFormViewModel Initialize(Product product) {
Id = product.Id;
return this;
}
public Product ToModel(Product? product = null) {
if (product == null)
product = new Product();
product.Name = Name;
product.Price = Price;
return product;
}
}There's a few things going on here, so let's break it down. First notice we haven't added a property called “DateAdded”, this is because we don't need to set this in the form. Instead it will automatically be set since we set an initial value in the model.
Next notice the return of “default!” set against the “Name” property. This is because we have added a parameterless constructor. If you remove “= default!” then the constructor will display a warning. This is because if we created an instance of this class by calling the parameterless constructor then the “Name” property would be null and once again our compiler trick is needed. The parameterless constructor is a requirement when we post the form to insert the product back it will model bind the form data to the view model, more on this later.
The next thing to note is the “Initialize” method, this sets any properties which are not automatically sent from the form.
Finally the “ToModel” method is responsible for converting our view model back to the model.
Now we have our view model we'll return to the "AdminController.cs" file and add a "NewProduct" action method with the following:
[HttpGet("products/new")]
public IActionResult NewProduct() {
return View(new ProductFormViewModel(new Product()));
}Now we'll add our view "NewProduct.cshtml" under the modules "Views" → "Admin" folder with the following content:
@model DemoShop.Shop.ViewModels.Admin.ProductFormViewModel
<form method="post" asp-action="NewProduct">
<nav id="icons">
<ul class="nav-responsive">
<li class="save"><button name="Command" value="Save">Save</button></li>
<li class="cancel"><a asp-action="Products">Cancel</a></li>
</ul>
</nav>
<div asp-validation-summary="All"></div>
<div class="card shadow">
<div class="card-header"><h2>Details</h2></div>
<div class="card-body">
<div class="form-group">
<label asp-for="Name" class="form-label"></label>
<input asp-for="Name" class="form-control" />
</div>
<div class="form-group">
<label asp-for="Price" class="form-label"></label>
<input asp-for="Price" class="form-control" />
</div>
</div>
</div>
</form>Next we need to add an action method to handle the form once it's submitted. Return to the admin controller and add the following action under the "NewProduct" action:
[HttpPost("products/new"), ValidateAntiForgeryToken]
public async Task<IActionResult> NewProduct(ProductFormViewModel model) {
// Validate the form.
if (!ModelState.IsValid)
return View(model.Initialize(new Product()));
// Insert the product.
await _dataContext.Repository<Product>().InsertOrUpdateAsync(model.ToModel());
await _dataContext.CommitAsync();
return RedirectToAction(nameof(Products));
}There's quite abit going on here so once again let's break into down into smaller pieces. First notice the HttpPost and ValidateAntiForgeryToken attributes have been applied to our method. This make sure that our method is only called from a HTTP "POST" request (once the form is submitted) and also validates the anti-forgery token (which automatically sent as a hidden field from the form) to prevent Cross-Site Request Forgery (CSRF) attacks.
Next you'll notice how the method takes a ProductFormViewModel instance as it's argument. The values are automatically set to the data that's submitted from the form. This is done using a technique known as model binding, click here for more information. If the model validation fails we will return the view.
Next we insert the product and then commit the changes to the database. The data context will store any changes in memory until they are pushed to the database. Click here for more information.
Finally we redirect back to the "Products" action. Using nameof(Products) keeps this strongly typed, therefore if we renamed the method in the future then it wouldn't compile.
Now we just need to add a migration to add a site map node for our action. Add the following code called "Migration4" beneath the "Migrations" folder:
using FluentMigrator;
namespace DemoShop.Shop.Migrations
[Migration(201908141814)]
public class Migration4 : AutoReversingMigration {
public override void Up() {
Insert.IntoTable("SiteMapNodes").Row(new { ParentId = RawSql.Insert("(SELECT [Id] FROM [dbo].[SiteMapNodes] WHERE [Action] = 'Products' AND [Controller] = 'Admin' AND [Area] = 'DemoShop.Shop')"), Name = "New Product", Action = "NewProduct", Controller = "Admin", Area = "DemoShop.Shop", Order = 1, ShowInMenu = false, IsLocked = false });
}
}Now if you go to https://localhost:5001/admin/products/new and submit the form without entering any data you will receive an error. This is because our database expects the product's name to have a value. Therefore we need to add validation, we do this by adding data annotation attributes to our form's model. In this case we passed our "ProductFormViewModel" view model as the form's model, so return to this class and update it with the following:
using System.ComponentModel.DataAnnotations;
using DemoShop.Shop.Models;
namespace DemoShop.Shop.ViewModels.Admin;
public class ProductFormViewModel {
public ProductFormViewModel() { }
public ProductFormViewModel(Product product) : this() {
Name = product.Name;
Price = product.Price;
Initialize(product);
}
public int Id { get; set; }
[Required, StringLength(100)]
public string Name { get; set; } = default!;
[Required]
public decimal Price { get; set; }
public ProductFormViewModel Initialize(Product product) {
Id = product.Id;
return this;
}
public Product ToModel(Product? product = null) {
if (product == null)
product = new Product();
product.Name = Name;
product.Price = Price;
return product;
}
}Note: We have added attributes to our properties to specify that they are required and the “Name" has a maximum length of 100 characters (the same as the max length of the field in the database).
Now if you refresh the page you'll get an validation message displayed if you try to add a product without entering a name. You should also be able to successfully add your products by entering the required data.
Finally we will add a link to the new product page from the products list page. Open the "Products.cshtml" view and add the following after the first line:
<nav id="icons">
<ul class="nav-responsive">
<li class="new"><a asp-action="NewProduct">New</a></li>
</ul>
</nav>Updating Data
Updating data is very similar to inserting, you'll find this is the same pattern when using forms throughout a KIT application.
First open the "AdminController.cs" file and add the following:
[HttpGet("products/{id:int}/edit")]
public async Task<IActionResult> EditProduct(int id) {
return View(new ProductFormViewModel((await _dataContext.Repository<Product>().GetAsync(id))!));
}
[HttpPost("products/{id:int}/edit"), ValidateAntiForgeryToken]
public async Task<IActionResult> EditProduct(int id, ProductFormViewModel model, string? returnUrl = null) {
var product = (await _dataContext.Repository<Product>().GetAsync(id))!;
// Validate the form.
if (!ModelState.IsValid)
return View(model.Initialize(product));
// Update the product.
model.ToModel(product);
await _dataContext.CommitAsync();
if (Url.IsLocalUrl(returnUrl))
return Redirect(returnUrl);
else
return RedirectToAction(nameof(Products));
}This is almost identitical to the "NewProduct" action methods except now we retrieve the product we wish to edit from the data context instead of creating a new instance. We have also added a "returnUrl" argument, if this is provided and it is a local url (prevents open redirection attacks) then we redirect to it, otherwise we redirect back to the "Products" action.
Note: We don't have to explicitly call to update the product against the data context. This is because our product was retrieved from the data context and any changes made to the product are tracked. Once we call commit the changes are pushed to the database and are persisted.
Now we'll add our "EditProduct" view by creating a file called "EditProduct.cshtml" in the "Views" → "Admin" folder with the following content:
@model DemoShop.Shop.ViewModels.Admin.ProductFormViewModel
<form method="post" asp-action="EditProduct" asp-route-id="@Model.Id" asp-route-returnurl="@Context.Request.Query.Get("ReturnUrl")">
<nav id="icons">
<ul class="nav-responsive">
<li class="save"><button name="Command" value="Save">Save</button></li>
<li class="cancel"><a href="@Format(Context.Request.Query.Get("ReturnUrl") ?? Url.Action("Products"))">Cancel</a></li>
</ul>
</nav>
<div asp-validation-summary="All"></div>
<div class="card shadow">
<div class="card-header"><h2>Details</h2></div>
<div class="card-body">
<div class="form-group">
<label asp-for="Name" class="form-label"></label>
<input asp-for="Name" class="form-control" />
</div>
<div class="form-group">
<label asp-for="Price" class="form-label"></label>
<input asp-for="Price" class="form-control" />
</div>
</div>
</div>
</form>Note: Notice this is almost exactly the same as the "NewProduct" view, aparty from the form parameters which makes sure the correct action URL is set against the form.
Now we need to add a link to the edit product page from the products list page. We will do this in two stages:
First update the "Products.cshtml" file with the following:
@model ISortedPagedList<DemoShop.Shop.ViewModels.Admin.ProductViewModel>
<form method="post" asp-action="Products" asp-all-route-data="@Context.Request.GetQueryValues().ToStringDictionary()">
<nav id="icons">
<ul class="nav-responsive">
<li class="new"><a asp-action="NewProduct">New</a></li>
@if (Model.TotalCount > 0) {
<li class="divide"></li>
<li class="edit"><button name="Command" value="Edit">Edit</button></li>
}
</ul>
</nav>
<div class="card shadow">
<div class="card-body">
@if (Model.TotalCount > 0) {
<div class="table-responsive">
<table class="table table-striped">
<colgroup>
<col style="width: 35px" />
</colgroup>
<thead>
<tr>
<th></th>
<th class="sorting@(Model.SortBy == "Name" ? (Model.SortDirection == ListSortDirection.Ascending ? " sorting-asc" : " sorting-desc") : "")"><a asp-action="Products" asp-all-route-data="@Context.Request.GetQueryValues().AddRange(new { sortBy = "Name", sortDirection = Model.SortDirection == ListSortDirection.Ascending && Model.SortBy == "Name" ? ListSortDirection.Descending : ListSortDirection.Ascending }).ToStringDictionary()">Name</a></th>
<th class="sorting@(Model.SortBy == "Price" ? (Model.SortDirection == ListSortDirection.Ascending ? " sorting-asc" : " sorting-desc") : "")"><a asp-action="Products" asp-all-route-data="@Context.Request.GetQueryValues().AddRange(new { sortBy = "Price", sortDirection = Model.SortDirection == ListSortDirection.Ascending && Model.SortBy == "Price" ? ListSortDirection.Descending : ListSortDirection.Ascending }).ToStringDictionary()">Price</a></th>
<th class="sorting@(Model.SortBy == "DateAdded" ? (Model.SortDirection == ListSortDirection.Ascending ? " sorting-asc" : " sorting-desc") : "")"><a asp-action="Products" asp-all-route-data="@Context.Request.GetQueryValues().AddRange(new { sortBy = "DateAdded", sortDirection = Model.SortDirection == ListSortDirection.Ascending && Model.SortBy == "DateAdded" ? ListSortDirection.Descending : ListSortDirection.Ascending }).ToStringDictionary()">Date Added</a></th>
</tr>
</thead>
<tbody>
@for (var i = 0; i < Model.Count; i++) {
<tr>
<td class="text-center"><input type="radio" name="Id" value="@Model[i].Id"@(i == 0 ? " checked=\"checked\"" : "") class="form-check-input" /></td>
<td>@Format(Model[i].Name)</td>
<td>@Format(Model[i].Price.ToString("c"))</td>
<td>@Format(Model[i].DateAdded.ToFormattedString(true))</td>
</tr>
}
</tbody>
</table>
</div>
} else {
<text>No products found.</text>
}
</div>
@if (Model.TotalCount > 0) {
<div class="card-footer">
<pager model="@Model" />
</div>
}
</div>
</form>We have wrapped a form around our content and added a radio button against each product. Notice that we have also switched out the foreach statement to a for statement which gives us a counter we can use to make sure the first radio button in the list is selected. The final modification is that we have added an "Edit" button which will submit the form with the value from the selected radio button.
Since we now have a form, we have to handle the form submit event. Open the "AdminController.cs" file and add the following method after the existing "Products" method:
[HttpPost("products"), ValidateAntiForgeryToken]
public IActionResult Products(string command, int? id, int page = 1, int pageSize = 10, string? sortBy = null, ListSortDirection? sortDirection = null) {
if (id.HasValue) {
switch (command) {
case "Edit":
return RedirectToAction(nameof(EditProduct), new { id, returnUrl = Url.Action(nameof(Products), new { page, pageSize, sortBy, sortDirection }) });
}
}
return RedirectToAction(nameof(Products), new { page = 1, pageSize, sortBy, sortDirection });
}The method will take the value from the selected radio button as an argument and providing one was selected it will redirect to the "EditProduct" action passing along the id of the product we wish to edit. It also passes a "returnUrl" which means if you edit a product on a particular page then it will redirect back to the same page once the product has been edited.
Note: Although a switch statement is not really necessary, in the future we will add additional commands to handle.
Now we just need to add a migration to add a site map node for our action. Add the following code called "Migration5" beneath the "Migrations" folder:
using FluentMigrator;
namespace DemoShop.Shop.Migrations;
[Migration(201908141816)]
public class Migration5 : AutoReversingMigration {
public override void Up() {
Insert.IntoTable("SiteMapNodes").Row(new { ParentId = RawSql.Insert("(SELECT [Id] FROM [dbo].[SiteMapNodes] WHERE [Action] = 'Products' AND [Controller] = 'Admin' AND [Area] = 'DemoShop.Shop')"), Name = "Edit Product", Action = "EditProduct", Controller = "Admin", Area = "DemoShop.Shop", Order = 1, ShowInMenu = false, IsLocked = false });
}
}Now you can navigate to http://localhost/admin/shop/products to test it working as expected.
Deleting Data
No CRUD (create, read, update and delete) example is complete unless we have the ability to delete our data. This builds on the principles we have learnt above.
We'll start by adding a button to the "Products" view to pass the command "Delete". Open the "Products.cshtml" file and add the following after the edit option:
<li class="delete"><button type="button" data-bs-toggle="modal" data-bs-target="#confirm-delete-modal">Delete</button></li>Also before the closing form tag add the following:
@if (Model.TotalCount > 0) {
<div id="confirm-delete-modal" class="modal fade">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">Confirm Delete</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal"></button>
</div>
<div class="modal-body">
Are you sure you want to delete the selected product?
</div>
<div class="modal-footer">
<button name="Command" value="Delete" class="btn btn-danger" data-bs-dismiss="modal">Delete</button>
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
</div>
</div>
</div>
</div>
}Note: This will display a confirmation message when the user clicks the “Delete” button.
Now we need to handle the "Delete" command in the "Products" post action method. Open the "AdminController.cs" file and add the following case to our switch statement:
case "Delete":
try {
// Try to delete the product.
await _dataContext.Repository<Product>().DeleteAsync((await _dataContext.Repository<Product>().GetAsync(id))!);
await _dataContext.CommitAsync();
} catch {
// Add the error.
ModelState.AddModelError("Error", "Product cannot be deleted.");
// Rollback the changes.
await _dataContext.RollbackAsync();
return await Products(page, pageSize, sortBy, sortDirection);
}
break;This is wrapped in a try/catch statement since it's possible deleting data can throw an exception, for example if the product had a foreign key relationship to another table which requires the product to exist. Also we make sure to rollback any changes if an exception, this prevents any potential issues when we retrieve products in the "Products" action.
Finally you'll need to change the method which contains the code above to an async method, since we now call await when trying to delete the product.
Once more refresh your page to verify you can successfuly delete products.
You may have noticed that all of this open for anyone to tamper with. In the next step we will secure it by adding permissions.