Companion Video Tutorial: https://youtu.be/wDVLRg_HCE4
In a previous post, I discussed a Blazor server-side app with CRUD operations against a Web API endpoint. In this post, I will build a Blazor application with similar functionality. However, this time the Server-side Blazor app talks directly to SQL Server Express. This is a very realistic option since both blazor and the database server run on the server.
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 going to do in this Tutorial?
In this tutorial I will show you how to build a server-side Blazor application that connects directly with SQL Server Express using Entity Framework Core.Perquisites
This tutorial was written while Blazor was in preview mode. The specific preview version of .NET Core 3.0 used in this tutorial is 3.0.100-preview6-012264. I used the following site for setting up my environment: https://docs.microsoft.com/en-us/aspnet/core/blazor/get-startedThis is how I setup my development environment:
- .NET Core 3.0 Preview SDK installed from https://dotnet.microsoft.com/download/dotnet-core/3.0
- Visual Studio Code
- The latest C# for Visual Studio Code extension
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 blazorserverside -o ServerBlazorEF
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 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 SQL Server Express 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:
using System.ComponentModel.DataAnnotations; namespace ServerBlazorEF.Models { public class Student { public string 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 SQL Server, we will need to add the appropriate Entity Framework packages. 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.EntityFrameworkCore.Design dotnet add package Microsoft.EntityFrameworkCore.SqlServer dotnet add package Microsoft.EntityFrameworkCore.SqlServer.Design
"ConnectionStrings": {
"DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=CollegeDB;Trusted_Connection=True;MultipleActiveResultSets=true"
},
using System; using Microsoft.EntityFrameworkCore; namespace ServerBlazorEF.Models { public class SchoolDbContext : DbContext { public DbSet<Student> Students { get; set; } public SchoolDbContext(DbContextOptions<SchoolDbContext> options) : base(options) { } protected override void OnModelCreating(ModelBuilder builder) { base.OnModelCreating(builder); builder.Entity<Student>().HasData( new { StudentId = Guid.NewGuid().ToString(), FirstName = "Jane", LastName = "Smith", School = "Medicine" }, new { StudentId = Guid.NewGuid().ToString(), FirstName = "John", LastName = "Fisher", School = "Engineering" }, new { StudentId = Guid.NewGuid().ToString(), FirstName = "Pamela", LastName = "Baker", School = "Food Science" }, new { StudentId = Guid.NewGuid().ToString(), FirstName = "Peter", LastName = "Taylor", School = "Mining" } ); } } }
Notice the above code is adding four records of seed data into the database.
In the Startup.cs file, add the following code to the ConfigureServices() method so that our application can use SQL Server:
services.AddDbContext<SchoolDbContext>(
option => option.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
dotnet tool install --global dotnet-ef --version 3.0.0-*
dotnet-ef migrations add initial_migration
Unable to create an object of type 'SchoolDbContext'. For the different patterns supported at design time, see https://go.microsoft.com/fwlink/?linkid=851728
using Microsoft.Extensions.Configuration; namespace ServerBlazorEF.Models { public class ConfigurationHelper { public static string GetCurrentSettings(string key) { var builder = new ConfigurationBuilder() .SetBasePath(System.IO.Directory.GetCurrentDirectory()) .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true) .AddEnvironmentVariables(); IConfigurationRoot configuration = builder.Build(); return configuration.GetValue<string>(key); } } }
using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Design; namespace ServerBlazorEF.Models { public class SchoolDbContextFactory : IDesignTimeDbContextFactory<SchoolDbContext> { public SchoolDbContext CreateDbContext(string[] args) { var optionsBuilder = new DbContextOptionsBuilder<SchoolDbContext>(); var connStr = ConfigurationHelper.GetCurrentSettings("ConnectionStrings:DefaultConnection"); optionsBuilder.UseSqlServer(connStr); return new SchoolDbContext(optionsBuilder.Options); } } }
After builing your application, execute the following terminal command again:
dotnet-ef migrations add initial_migration
using Microsoft.EntityFrameworkCore.Migrations; namespace ServerBlazorEF.Migrations { public partial class initial_migration : Migration { protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.CreateTable( name: "Students", columns: table => new { StudentId = table.Column<string>(nullable: false), FirstName = table.Column<string>(nullable: false), LastName = table.Column<string>(nullable: false), School = table.Column<string>(nullable: false) }, constraints: table => { table.PrimaryKey("PK_Students", x => x.StudentId); }); migrationBuilder.InsertData( table: "Students", columns: new[] { "StudentId", "FirstName", "LastName", "School" }, values: new object[,] { { "22ced2e2-ff02-4fdb-b690-292df3eecef7", "Jane", "Smith", "Medicine" }, { "733548c4-aa07-40e7-bb79-737fb91a32fd", "John", "Fisher", "Engineering" }, { "48c713d5-0609-47ea-a0f8-7a962ffc0232", "Pamela", "Baker", "Food Science" }, { "48eb4c23-c8af-420c-950d-f74be1a43b98", "Peter", "Taylor", "Mining" } }); } protected override void Down(MigrationBuilder migrationBuilder) { migrationBuilder.DropTable( name: "Students"); } } }
The next step is to create the CollegeDB database in SQL Server. This is done by running the following command from inside a terminal window at the root folder of the application.
dotnet-ef database update
using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using Microsoft.EntityFrameworkCore; namespace ServerBlazorEF.Models { public class StudentService { SchoolDbContext _context; public StudentService(SchoolDbContext context) { _context = context; } public async Task<List<Student>> GetStudentsAsync() { return await _context.Students.ToListAsync(); } public async Task<Student> GetStudentByIdAsync(string id) { return await _context.Students.FindAsync(id); } public async Task<Student> InsertStudentAsync(Student student) { _context.Students.Add(student); await _context.SaveChangesAsync(); return student; } public async Task<Student> UpdateStudentAsync(string 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(string 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(string id) { return _context.Students.Any(e => e.StudentId == id); } } }
Add the following statement to the ConfigureServices() method in Startup.cs:
services.AddScoped<StudentService>();
@page "/students" @using ServerBlazorEF.Models @inject StudentService studentService <h1>Students</h1> <p>This component demonstrates managing students data.</p> @if (students == null) { <p><em>Loading...</em></p> } else { <table class='table table-hover'> <thead> <tr> <th>ID</th> <th>First Name</th> <th>Last 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> } @functions { List<Student> students; protected override async Task OnInitAsync() { await load(); } protected async Task load() { students = await studentService.GetStudentsAsync(); } }
<li class="nav-item px-3"> <NavLink class="nav-link" href="students"> <span class="oi oi-list-rich" aria-hidden="true"></span> Students </NavLink> </li>
You must be eager to test out the server-side Blazor project. To run your app, hit CTRL F5 to run the application without debugging. If you select the Students link on the left-side 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. Place the following instance variables just above the OnInitAsyc() method:
string studentId; string firstName; string lastName; string school;
@if (students != null) // Insert form { <input placeholder="First Name" @bind="@firstName" /><br /> <input placeholder="Last Name" @bind="@lastName" /><br /> <input placeholder="School" @bind="@school" /><br /> <button @onclick="@Insert" class="btn btn-warning">Insert</button> }
protected async Task Insert() { Student s = new Student() { StudentId = Guid.NewGuid().ToString(), FirstName = firstName, LastName = lastName, School = school }; await studentService.InsertStudentAsync(s); ClearFields(); await load(); }
Add the following ClearFields() method:
protected void ClearFields() { studentId = string.Empty; firstName = string.Empty; lastName = string.Empty; school = string.Empty; }
I entered Harry, Green and Agriculture for data and when I clicked on the Insert button I got the following data inserted into the database:
Updating & Deleting data
To distinguish between INSERT and EDIT/DELETE mode, we shall add an enum declaration to our code. Add the following to the list of instance variables:
private enum MODE { None, Add, EditDelete }; MODE mode = MODE.None; Student student;
<button @onclick="@Add" class="btn btn-success">Add</button>
Here is the Add() method that is called when the above button is clicked:
protected void Add() { ClearFields(); mode = MODE.Add; }
Around line 37, change the @if (students != null) statement to:
@if (students != null && mode==MODE.Add) // Insert form
mode = MODE.None;
@if (students != null && mode==MODE.EditDelete) // Update & Delete form
{
<input type="hidden" @bind="@studentId" /><br />
<input placeholder="First Name" @bind="@firstName" /><br />
<input placeholder="Last Name" @bind="@lastName" /><br />
<input placeholder="School" @bind="@school" /><br />
<button @onclick="@Update" class="btn btn-primary">Update</button>
<span> </span>
<button @onclick="@Delete" class="btn btn-danger">Delete</button>
}
protected async Task Update() { Student s = new Student() { StudentId = studentId, FirstName = firstName, LastName = lastName, School = school }; await studentService.UpdateStudentAsync(studentId, s); ClearFields(); await load(); mode = MODE.None; } protected async Task Delete() { await studentService.DeleteStudentAsync(studentId); ClearFields(); await load(); mode = MODE.None; }
<tr @onclick="@(() => Show(item.StudentId))">
protected async Task Show(string id) { student = await studentService.GetStudentByIdAsync(id); studentId = student.StudentId; firstName = student.FirstName; lastName = student.LastName; school = student.School; mode = MODE.EditDelete; }
Click on the Add button to insert data.
Enter data then click on the Insert button. The data should get added to the database:
To update the data, click on a row. The selected data should display in the update/delete form.
I selected James Bond and changed Bond to Gardner.
After I clicked on Update, last name was successfully changed to Gardner.
Lastly, let us delete data. I clicked on the Harry Green row. Data was displayed in the Update/Delete form.
When I clicked on Delete, Harry Green got removed.
I hope you learned something new in this tutorial and trust that you will build much more sophisticated Blazor apps than this.
Good Luck!
