Scaffold Interactive-Server Blazor CRUD pages with SQLite

In this walkthrough, we will use the scaffolding tool to create the CRUD pages for a data driven Interactive-Server Blazor app. The app connects directly with SQLite database using Entity Framework Core.

Overview

Blazor is a framework for developing interactive client-side web applications using C# instead of JavaScript.

Architecture

The architecture of the application that we will be building will look like this: 

ASP.NET Core hosts the server-side app and sets up SignalR endpoints where clients connect. SignalR is responsible for updating the DOM on the client with any changes. The Blazor application on the server connects directly to the database using Entity Framework Core.

Blazor Render Modes

Name	Description	Render Location	Interactive
Static Server	Static server-side rendering (static SSR)	Server	❌
Interactive Server	Interactive server-side rendering (interactive SSR) using Blazor Server.	Server	✔️
Interactive WASM	Client-side rendering (CSR) using Blazor WebAssembly.	Client	✔️
Interactive Auto	Interactive SSR using Blazor Server initially and then CSR on subsequent visits after the Blazor bundle is downloaded.	Server, then client	✔️

To apply a render mode to a component, use the @rendermode directive on the component instance or on the component definition. Example:

<Dialog @rendermode="InteractiveServer" />

In this post, we will build a Server-side Blazor app talks directly to a SQLite database. This is a very realistic option since both blazor and the database server run on the server.

Let's get coding

In a terminal window, go to a work directory. Enter the following command to create a Server-Side Blazor application inside a directory named BlazorEF: 

dotnet new blazor -o BlazorEF
cd BlazorEF
mkdir Models
mkdir Data

Open the BlazorEF folder in Visual Studio Code.

When you run the app, your default browser will load a page that looks like this: 

We will extend the above application so that it talks to SQLite using Entity Framework Core. To this end, we will be dealing with a very simple Student model. Therefore, add a Student.cs class file to the Models folder with the following C# content: 

public class Student {
    public int StudentId { get; set; }

    [Required(ErrorMessage = "You must enter first name.")]
    public String? FirstName { get; set; }

    [Required(ErrorMessage = "You must enter last name.")]
    public String? LastName { get; set; }

    [Required(ErrorMessage = "You must enter school.")]
    public String? School { get; set; }

    [Required(ErrorMessage = "You must enter gender.")]
    public String? Gender { get; set; }

    public DateOnly? DateOfBirth { get; set; }
}

Since we will be using SQLite, we will need to add the appropriate Entity Framework Core packages to our project. From within a terminal window at the root of your BlazorEF project, run the following commands to add the appropriate database related packages: 

dotnet add package Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore
dotnet add package Microsoft.AspNetCore.Identity.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design

We need to add a connection string for the SQLite database. Add the following to the appsettings.json file: 

"ConnectionStrings": {
  "DefaultConnection": "DataSource=college.db;Cache=Shared;"
}

We will be using the Entity Framework Code First approach. 

Developers prefer having sample data when building data driven applications. Therefore, we will create some sample data to ensure that our application behaves as expected. Student data from this link will be used.

The starting point is to create a database context class. Add a C# class file named SchoolDbContext.cs in the Data folder with the following C# code: 

public class SchoolDbContext : DbContext {
    public DbSet<Student> Students => Set<Student>();

    public SchoolDbContext(DbContextOptions<SchoolDbContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Student>().HasData(GetStudents());
    }

    private static IEnumerable<Student> GetStudents() {
        const string studentsUrl =
            "https://gist.githubusercontent.com/medhatelmasry/2ae7a36d5392f862b0d1039b2a52b9b4/raw/45533eda6c43ce439dbc65c37a7fbdef01546c2d/students.json";

        using var httpClient = new HttpClient();

        var json = httpClient.GetStringAsync(studentsUrl).GetAwaiter().GetResult();

        return System.Text.Json.JsonSerializer.Deserialize<List<Student>>(json)
            ?? throw new InvalidOperationException("The students JSON returned no data.");
    }
}

Notice the above code will seed Student data into the database.

In the Program.cs file, just before ‘var app = builder.Build();’, add the following code so that our application can use SQLite:

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");

builder.Services.AddDbContext<SchoolDbContext>(
    options => options.UseSqlite(connectionString)
);

We are now ready to apply Entity Framework migrations, create the database and seed data. If you have not done so already, you will need to globally install the Entity Framework CLI tool. This tooling is installed globally on your computer by running the following command in a terminal window:

dotnet tool install --global dotnet-ef

To make sure nothing is broken, build your project with "dotnet build" before proceeding. Then, from within a terminal window inside the BlazorEF root directory, run the following command to create migrations: 

dotnet ef migrations add M1 -o Data/Migrations

 This results in the creation of a migration file ending with the name ....M1.cs in the Data/Migrations folder. In my case, this file looked like this: 

Note that the above code also includes commands for inserting sample data.

The next step is to create the SQLite college.db database file. To automatically apply migrations, add the following code to Program.cs, right before the final app.Run() statement:

using (var scope = app.Services.CreateScope()) {
  var services = scope.ServiceProvider;

  var context = services.GetRequiredService<SchoolDbContext>();    

  context.Database.Migrate();
}

We will scaffold the CRUD pages for students using the scaffold tool. To install the scaffold tool globally, enter this command in any terminal window:

dotnet tool install --global Microsoft.dotnet-scaffold

Thereafter, run the scaffold tool in the root folder of your Blazor project with:

dotnet scaffold 

Follow these steps:

Delete this unnecessary addition in the appsettings.json file:

"SchoolDbContext": "Data Source=SchoolDbContext.db"

You will notice these razor components have been added to your Blazor project:

Add this menu item to your Components/Layout/NavMenu.razor file.

<div class="nav-item px-3">
    <NavLink class="nav-link" href="students">
        <span class="bi bi-list-nested-nav-menu" aria-hidden="true"></span> Students
    </NavLink>
</div>

Run your application:

You will discover that Student CRUD operations work seamlessly.

I hope you learned something new in this tutorial and trust that you will build much more sophisticated Blazor apps than this.