Medhat Elmasry: Server-side Blazor 7.0 APP with CRUD Operations and SQLite

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

Source Code: https://github.com/medhatelmasry/ServerBlazorEF7

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 endpoint 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.

What are we doing in this tutorial?

In this tutorial I will show you how to build a server-side Blazor application that connects directly with SQLite database using Entity Framework Core.

Let's start coding

1) In a terminal window, go to your working directory. Enter the following command to create a Server-Side Blazor application inside a directory called ServerBlazorEF:

dotnet new blazorserver -f net7.0 -o ServerBlazorEF

2) Open the ServerBlazorEF folder in Visual Studio Code.

3) For a Blazor server-side project, the IDE requests that you add assets to build and debug the project. Select Yes.

4) Hit Ctrl F5 (or dotnet watch in a terminal window) to run the application. Your default browser will load a page that looks like this:

Our objective is to 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 in a folder named Models with the following content:

public class Student {
public int StudentId { get; set; }
[Required]
public string? FirstName { get; set; }
[Required]
public string? LastName { get; set; }
[Required]
public string? School { get; set; }
}

Since we will be using SQLite, we will need to add the appropriate packages. Therefore, from within a terminal window at the root of your ServerBlazorEF project, run the following commands that will 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

dotnet add package CsvHelper

We need to add a connection string for the 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. Copy the following data and save it in a text file named students.csv in the wwwroot folder:

StudentId,FirstName,LastName,School
1,Tom,Max,Nursing
2,Ann,Fay,Mining
3,Joe,Sun,Nursing
4,Sue,Fox,Computing
5,Ben,Ray,Mining
6,Zoe,Cox,Business
7,Sam,Ray,Mining
8,Dan,Ash,Medicine
9,Pat,Lee,Computing
10,Kim,Day,Nursing
11,Tim,Rex,Computing
12,Rob,Ram,Business
13,Jan,Fry,Mining
14,Jim,Tex,Nursing
15,Ben,Kid,Business
16,Mia,Chu,Medicine
17,Ted,Tao,Computing
18,Amy,Day,Business
19,Ian,Roy,Nursing
20,Liz,Kit,Nursing
21,Mat,Tan,Medicine
22,Deb,Roy,Medicine
23,Ana,Ray,Mining
24,Lyn,Poe,Computing
25,Amy,Raj,Nursing
26,Kim,Ash,Mining
27,Bec,Kid,Nursing
28,Eva,Fry,Computing
29,Eli,Lap,Business
30,Sam,Yim,Nursing
31,Joe,Hui,Mining
32,Liz,Jin,Nursing
33,Ric,Kuo,Business
34,Pam,Mak,Computing
35,Cat,Yao,Medicine
36,Lou,Zhu,Mining
37,Tom,Dag,Business
38,Stu,Day,Business
39,Tom,Gad,Mining
40,Bob,Bee,Business
41,Jim,Ots,Business
42,Tom,Mag,Business
43,Hal,Doe,Mining
44,Roy,Kim,Mining
45,Vis,Cox,Nursing
46,Kay,Aga,Nursing
47,Reo,Hui,Nursing
48,Bob,Roe,Mining
49,Jay,Eff,Computing
50,Eva,Chu,Business
51,Lex,Rae,Nursing
52,Lin,Dex,Mining
53,Tom,Dag,Business
54,Ben,Shy,Computing
55,Rob,Bos,Nursing
56,Ali,Mac,Business
57,Ken,Sim,Medicine

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 class 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() {

string[] p = { Directory.GetCurrentDirectory(), "wwwroot", "students.csv" };

var csvFilePath = Path.Combine(p);

var config = new CsvConfiguration(CultureInfo.InvariantCulture) {

PrepareHeaderForMatch = args => args.Header.ToLower(),

};

var data = new List<Student>().AsEnumerable();

using (var reader = new StreamReader(csvFilePath)) {

using (var csvReader = new CsvReader(reader, config)) {

data = csvReader.GetRecords<Student>().ToList();

}

return data;

}

Notice the above code is adding the contents of the wwwroot/students.csv file as seed 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 some sample 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

Remember to build your entire solution before proceeding. Then, from within a terminal window inside the ServerBlazorEF root directory, run the following command to create migrations:

dotnet ef migrations add M1 -o Data/Migrations

You should get no errors and this results in the creation of a migration file ending with the name ....M1.cs in the Migrations folder which contains commands for inserting sample data.

The next step is to create the SQLite college.db database file. This is done by running the following command from inside a terminal window at the root folder of the application.

dotnet ef database update

If no errors are encountered, you can assume that the database was created and properly seeded with data.

Add a class file named StudentService.cs in the Data folder with following code:

public class StudentService {

private SchoolDbContext _context;

public StudentService(SchoolDbContext context) {

_context = context;

}

public async Task<List<Student>> GetStudentsAsync() {

return await _context.Students.ToListAsync();

}

public async Task<Student?> GetStudentByIdAsync(int id) {

return await _context.Students.FindAsync(id) ?? null;

}

public async Task<Student?> InsertStudentAsync(Student student) {

_context.Students.Add(student);

await _context!.SaveChangesAsync();

return student;

}

public async Task<Student> UpdateStudentAsync(int id, Student s) {

var student = await _context.Students!.FindAsync(id);

if (student == null)

return null!;

student.FirstName = s.FirstName;

student.LastName = s.LastName;

student.School = s.School;

_context.Students.Update(student);

await _context.SaveChangesAsync();

return student!;

}

public async Task<Student> DeleteStudentAsync(int id) {

var student = await _context.Students!.FindAsync(id);

if (student == null)

return null!;

_context.Students.Remove(student);

await _context.SaveChangesAsync();

return student!;

}

private bool StudentExists(int id) {

return _context.Students!.Any(e => e.StudentId == id);

}

The above StudentService class provides all the necessary methods for CRUD operations involving data retrieval, insertion, update and deletion.

We need to configure the StudentService class as a scoped service so that we can use dependency injection. Scoped lifetime services are created once per client request (connection). Add the following statement to the Program.cs just before ‘var app = builder.Build()’:

builder.Services.AddScoped<StudentService>();

Close all the files in your editor. Rename FetchData.razor file in the Pages folder to Students.razor. Replace its contents with the following code:

@page "/students"

@using ServerBlazorEF.Data

@using ServerBlazorEF.Models

@inject StudentService studentService

<h1>Students</h1>

@if (students == null) {

<p><em>Loading...</em></p>

} else {

<thead>

<tr>

<th>First Name</th>

<th>School</th>

</tr>

</thead>

<tbody>

@foreach (var item in students)

{

<tr>

<td>@item.StudentId</td>

<td>@item.FirstName</td>

<td>@item.LastName</td>

<td>@item.School</td>

</tr>

}

</tbody>

</table>

}

@code {

List<Student>? students;

protected override async Task OnInitializedAsync() {

students = await studentService.GetStudentsAsync();

}

Since we will be using the Student class in multiple razor pages, move the @using ServerBlazorEF.Models statement on line 3 in the above code to _Imports.razor.

Let us focus on the @code block. The OnInitAsyns() method is called when the page gets loaded. It makes a call to the student service which loads a list of students from the database. The remaining HTML/Razor code simply displays the data in a table.

Let's modify the menu item on the left navigation of our application. Open Shared/NavMenu.razor in the editor and change the link for “Fetch data” so it looks like this:

<span class="oi oi-list-rich" aria-hidden="true"></span> Get Students

</NavLink>

</div>

You must be eager to test out the server-side Blazor project. Run your app and select the “Get Students” link on the left navigation, this is what the output will look like:

Adding data

Our Blazor app is not complete without add, edit and delete functionality. We shall start with adding data.

Let us re-purpose Counter.razor so that it becomes our page for adding data. Rename Counter.razor to AddStudent.razor.

Replace AddStudent.razor with the following code:

@page "/addstudent"

@using ServerBlazorEF.Models

@inject ServerBlazorEF.Data.StudentService studentService

@inject NavigationManager NavManager

<PageTitle>Add Student</PageTitle>

<h1>Add Student</h1>

<label for="FirstName">First Name:</label>

</div>

</div>

<label for="School">School:</label>

</div>

<button type="submit" class="btn btn-primary">Submit</button>

</EditForm>

@code {

private Student student = new Student();

private async Task HandleValidSubmit() {

await studentService.InsertStudentAsync(student);

NavManager.NavigateTo("/students");

}

Open Shared/NavMenu.razor in the editor and change the link for “Counter” so it looks like this:

<span class="oi oi-list-rich" aria-hidden="true"></span> Add Student

</NavLink>

</div>

Run the Blazor server-side project and select Add Student on the left navigation menu. This is what it should look like:

I entered Bob, Smith and Travel for data and when I clicked on the Submit button I got the following data inserted into the database:

Update & Delete data using PUT & DELETE methods

We want to be able to select a row of data and update or delete it. Add the following additional cells to the table row in Students.razor:

The above would pass the appropriate studentId and mode parameters to another page with route /updel.

Create a text file named UpdateDelete.razor in the Pages folder with the following content:

@page "/updel/{id}/{mode}"
@using ServerBlazorEF.Models

@inject ServerBlazorEF.Data.StudentService studentService

@inject NavigationManager NavManager

<style>

fieldset {

border: 2px solid #000;

padding-left: 20px;

margin-bottom: 20px;

}

</style>

<PageTitle>Update/Delete Student</PageTitle>

@if (student != null && Mode == "edit") // Update

{

<p>Update Student with ID == @Id</p>

<label for="FirstName">First Name:</label>

</div>

</div>

<label for="School">School:</label>

</div>

<button type="submit" class="btn btn-primary">Update</button>

</EditForm>

@code {

private async Task HandleValidSubmit()

{

await studentService.UpdateStudentAsync(student!.StudentId, student);

NavManager.NavigateTo("/students");

}

else if (student != null && Mode == "del")

{ // Delete

// display student details

<legend>Student Information</legend>

<p>Student ID: @Id</p>

<p>First Name: @student.FirstName</p>

<p>Last Name: @student.LastName</p>

<p>School: @student.School</p>

</fieldset>

<p>Delete Student with ID == @Id</p>

<button type="button" class="btn btn-danger" @onclick="HandleDeleteStudent">Delete</button>

@code {

private async Task HandleDeleteStudent()

{

await studentService.DeleteStudentAsync(student!.StudentId);

NavManager.NavigateTo("/students");

}

else

{

<p>Student with ID == @Id not found</p>

}

@code {

[Parameter]

public string? Id { get; set; }

[Parameter]

public string? Mode { get; set; }

private Student? student = new Student();

protected override async Task OnInitializedAsync()

{

int intId = Convert.ToInt32(Id);

student = await studentService.GetStudentByIdAsync(intId);

}

Note how parameters are passed from one page to another.

1) {id}/{mode} are defined in the route

2) In the @code section, the following parameters are defined:

[Parameter]
public string? Id { get; set; }
[Parameter]
public string? Mode { get; set; }

CRUD Experience

Component CSS

In the UpdateDeleter.razor page, notice that we have some CSS in the <style> . . . </style> block. We can move this into a CSS file that only serves the UpdateDelete.razor component.

In the Pages foldr, create a text file name UpdateDelete.razor.css and add to it the following CSS:

fieldset {
border: 2px solid #000;
padding-left: 20px;
margin-bottom: 20px;
}

Meantime, delete the entire <style> . . . . </style> block in UpdateDeleter.razor. The page should behave just like it did before when you view the delete page.

SignalR

Server-side Blazor uses SignalR to keep a copy of the DOM on the server and to only update changes on the client. Open your Chrome browser development settings and look at the blazor line in the Network tab. This gives you an indication that websockets are used to transmit data between clent and server.

Also, look at the negotiate line in the Network tab.

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

Medhat Elmasry

Monday, November 6, 2023

Server-side Blazor 7.0 APP with CRUD Operations and SQLite

Overview

Architecture

What are we doing in this tutorial?

Let's start coding

Adding data

Update & Delete data using PUT & DELETE methods

CRUD Experience

Component CSS

SignalR

No comments:

Post a Comment

About Me

Blog Archive