Data Access

Migrations

We will start by creating a database table to store our products. We can either do this using either SQL Server Management Studio or we can create a migration file to do this for us. Creating a migration has the advantage that once we upload our changes or copy our module to an existing site then the database changes will automatically be applied once the application next runs.

For this series we will be using migrations. Migrations are written in C# which is translated to SQL before it is executed. This has a number of benefits, most notably it gives us intellisense and compile time checking.

We use a third party library called Fluent Migrator for our migrations. Click here for more information and further examples.

First you'll to add a package reference to the data abstractions in your project file. Open the “DemoShop.Shop.csproj” file and add the following:

<PackageReference Include="Kit.Data.Abstractions" Version="1.*" />

Now we can add our migration.

  1. Add folder called "Migrations" to your module.
  2. Now create a file called "Migration1.cs" beneath this folder with the following content:
using FluentMigrator;

namespace DemoShop.Shop.Migrations;

[Migration(201908141652)]
public class Migration1 : AutoReversingMigration {
    public override void Up() {
        Create.Table("Products")
            .WithColumn("Id").AsInt32().NotNullable().PrimaryKey("PK_Products").Identity()
            .WithColumn("Name").AsString(100).NotNullable()
            .WithColumn("Price").AsDecimal(18, 5).NotNullable()
            .WithColumn("DateAdded").AsDateTimeOffset().NotNullable();
    }
}

Note: The version "201908141652" is the date and time we created our migration and consists of YYYYMMDDHHMM. We log the version inside a table called "Migrations" within the database to make sure the migrations are only run once.

Now you can run your application to apply your migration. You can use SQL Server Management Studio to verify the "Products" table has been created successfully.

Now we understand the concept of migrations, we will create a migration to add a site map node for our home controller's index action method. Every action should have a site map node. The site map node allows you to set the page layout, title and meta information for each action.

Add a file called "Migration2.cs" within the "Migrations" folder with the following content:

using FluentMigrator;

namespace DemoShop.Shop.Migrations;

[Migration(201908141658)]
public class Migration2 : AutoReversingMigration {
    public override void Up() {
        Insert.IntoTable("SiteMapNodes").Row(new { ParentId = RawSql.Insert("(SELECT [Id] FROM [dbo].[SiteMapNodes] WHERE [Action] = 'Index' AND [Controller] = 'Home' AND [Area] = 'Pages')"), Name = "Shop", Action = "Index", Controller = "Home", Area = "DemoShop.Shop", Order = 1, ShowInMenu = true, ShowInMenuIfHasNoChildren = true, IsLocked = false });
    }
}

Now we have site map node for our action method we can simplify the "Home" controller's "Index" view to the following:

Hello World (View)

Finally refresh the site to see the changes. You will see that the view is now hosted within the theme's layout/master page.

Models

We will now create a model for our products. A model is a C# class where each property represents a field within our database table. A model which is mapped to a database table is also known as an entity. This also completes the final part of our MVC pattern.

  1. First add a new folder within your module called "Models". This will be where we will place all of our future models for this module.
  2. Next we will create a file called "Product.cs" beneath this folder with the following content:
using System;
using Kit.Entities;

namespace DemoShop.Shop.Models;

public class Product : Entity<int> {
    public virtual string Name { get; set; } = default!;
    public virtual decimal Price { get; set; }
    public virtual DateTimeOffset DateAdded { get; set; } = DateTimeOffset.UtcNow;
}

Note: The “DateAdded” property is of type “DateTimeOffset”. Click here for more information about date/times.

Each property is marked as virtual since this is a requirement of NHibernate (see below) to support lazy loading.

By inherting from the "Entity" class it will automatically inherit an "Id" property which represents the primary key for our entity/model.

The final thing is we set the “Name” to “default!”, this is because we have enabled nullable reference types (in the project file) and we have specified that the “Name” property cannot be null. Since this class doesn't have a constructor then if we created an instance of it, “Name" would automatically set to null, therefore we trick the compiler to remove the warning (try removing = default!).

Mapping

Now we need to create a mapping file to map this entity to our database table. This is done using a third party library called NHibernate. NHibernate is an ORM (Object-Relational Mapping) that makes working with the database a lot simpler.

Add a file called "ProductMapping.cs" underneath the "Models" folder with the following content:

using NHibernate.Mapping.ByCode;
using NHibernate.Mapping.ByCode.Conformist;

namespace DemoShop.Shop.Models;

public class ProductMapping : ClassMapping<Product> {
    public ProductMapping() {
        Table("Products");
        Id(x => x.Id, m => m.Generator(Generators.Identity));
        Property(x => x.Name);
        Property(x => x.Price);
        Property(x => x.DateAdded);
        Cache(x => x.Usage(CacheUsage.ReadWrite));
    }
}

This file specifies the table our class is mapped to and identifies which of our properties we should also map. Since our properties in our Product class are the same name as the fields in the database we can simply say Property(x => x.Name) but if for example the "Name" field was called "ProductName" then we would instead say Property(x => x.Product, m => m.Column("ProductName")) to map it.

Now rebuild the application to make sure there are no errors.

Data Context

The data context is used to query the database. The data context is a wrapper around NHibernate's session object and only exposes the parts of NHibernate we are interested in. Click here for a more indepth look at the data context.

Now our model is created, we can use it as well as the data context to query the database.

First let's use SQL Server Management Studio to populate some dummy data into the "Products" table.

Now return to the "HomeController.cs" file and update it with the following:

using System.Threading.Tasks;
using DemoShop.Shop.Models;
using Kit.Data;
using Microsoft.AspNetCore.Mvc;

namespace DemoShop.Shop.Controllers;

[Area("DemoShop.Shop"), Route("shop")]
public class HomeController : Controller {
    private readonly IDataContext _dataContext;

    public HomeController(IDataContext dataContext) {
        _dataContext = dataContext;
    }

    [HttpGet]
    public async Task<IActionResult> Index() {
        return View(await _dataContext.Repository<Product>().All().ToListAsync());
    }
}

First we inject the data context (IDataContext) inside the constructor. This is known as dependency injection.

If you refer to the line where we return the view from the controller's action method, we now say await _dataContext.Repository<Product>().All().ToListAsync() within it. This will retrieve all of the products and pass it to the view. This also calls the database asynchronously, click here for more information about asynchronous programming.

Now you can see how our pieces of the MVC pattern are finally coming together.

Now our view has been passed the data/model we wish to display we can adjust our view accordingly:

@model IList<DemoShop.Shop.Models.Product>
<ul>
    @foreach (var product in Model) {
        <li>@Format(product.Name)</li>
    }
</ul>

Note: The first line tells the view the type of the model we are expecing. Now when we access the model we know it will be of this type. The call to Format makes sure we automatically encode the product's name to prevent a potential XSS attack.

Now you should be able to re-run the application and see the products listed.

View Models

Above we have passed the model directly to the view. This creates an association between the data context and the view. Ideally we should have a layer which sits between these concepts which allows us to only define the properties on the model we are interested in. View models offer many other benefits most notibly the ability to pass multiple models to our views.

To create our view model then do the following:

  1. First create a folder called “ViewModels” in the root of the project to host your view models.
  2. Within this folder we will add a folder called “Home” which matches the name of the controller our view model is for.
  3. Finally create a file called “ProductViewModel.cs" within this folder with the following content:
using DemoShop.Shop.Models;

namespace DemoShop.Shop.ViewModels.Home;

public class ProductViewModel {
    public ProductViewModel(Product product) {
        Name = product.Name;
        Price = product.Price;
    }

    public string Name { get; }
    public decimal Price { get; }
}

Note: The constructor takes the model and converts it to our view model. We only need to add the properties of our model we wish to expose to the view. We also don't need to set the “Name” property to “default!”, this is because we now have a constructor which set's it's value.

Next you need to update your home controller's “Index” action with the following body (adding any missing namespaces):

return View((await _dataContext.Repository<Product>().All().ToListAsync()).Select(p => new ProductViewModel(p)).ToList());

Finally since we now pass a different model to our view, we should update the first line of the view with the following:

@model IList<DemoShop.Shop.ViewModels.Home.ProductViewModel>

Paging & Sorting

Let's finish off by adding paging to list the products.

First change the "Index" action to the following (add a using for the “System.Linq” namespace):

public async Task<IActionResult> Index(int page = 1) {
    return View((await _dataContext.Repository<Product>().All().ToPagedListAsync(page, 10)).Convert(p => new ProductViewModel(p)));
}

Note: Our method now takes a page argument with a default value of 1, this can be overriden by passing along query string or form field values with the same name. Also the call to ToPagedListAsync takes care of automatically paging the products collection. The final thing you'll notice is we have changed "Select(…).ToList()” to “Convert(…)” this makes sure that when we convert the model to our view model it still remains as a paged list.

Now replace the view with the following content:

@model IPagedList<DemoShop.Shop.ViewModels.Home.ProductViewModel>
<ul>
    @foreach (var product in Model) {
        <li>@Format(product.Name)</li>
    }
</ul>
<div class="pager">
    <pager model="@Model" />
</div>

Note: The model is now expecting an IPagedList instead of an IList.

Now refresh to see the changes. You will also need to make sure you have populated more than 10 (the page size we fed into ToPagedListAsync) products to see the pager.

If we wanted to add the ability to sort our list of products, we would change all occurances of "PagedList" with "SortedPagedList". In the next step we show an example of this.

Data Modification »