Source code for this tutorial can be found at: https://github.com/medhatelmasry/ServerBlazorEF.git
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-started

This 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

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

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

"ConnectionStrings": {
  "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=CollegeDB;Trusted_Connection=True;MultipleActiveResultSets=true"
},

We will be using the Entity Framework Code First approach. 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:

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")));

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 has changed in .NET Core 3.0 and is installed globally on your computer by running the following command in a terminal window:

dotnet tool install --global dotnet-ef --version 3.0.0-*

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

dotnet-ef migrations add initial_migration

I experienced the following error:

Unable to create an object of type 'SchoolDbContext'. For the different patterns 
supported at design time, see https://go.microsoft.com/fwlink/?linkid=851728

I followed the suggested link which obliged me to create DbContextFactory class. This class needs to read the connection string from appsettings.json without dependency injection. For this purpose, I added this helper class in the Models folder that helps read configuration settings from appsettings.json:

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);
    }
  }
}

Next, create another class file named SchoolDbContextFactory.cs in the Data folder. This file extends from IDesignTimeDbContextFactory<T> and will be used to create the database context:

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

You should get no errors and this results in the creation of a migration file ending with the name ....initial_migration.cs in the Migrations folder. In my case, this file looked like this:

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");
    }
  }
}

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

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

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. Replace the class with the following code:

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);
      }
  }
}

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

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 ConfigureServices() method in Startup.cs:

services.AddScoped<StudentService>();

Make a duplicate copy of the FetchData.razor file in the Pages node and name the new file Students.razor. Replace its contents with the following code:

@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();
  }
}

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

Let's add a menu item to the left-side navigation of our application. Open Shared/NavMenu.razor in the editor and add the following <li> to the <ul> block (around line 24):

<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;

We need an HTML form to add data. Add the following RAZOR code just before @functions:

@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>
}

When the Insert button is clicked, an Insert() method is called. Add the following Insert() method inside the @functions() block:

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();
}

After data is inserted, the above code clears the fields then loads the data again into an HTML table.

Add the following ClearFields() method:

protected void ClearFields() {
  studentId = string.Empty;
  firstName = string.Empty;
  lastName = string.Empty;
  school = string.Empty;
}

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

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;

We will add a button at the top of our table for adding data. Add the following markup just above the opening <table> tag:

<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

Run the server-side Blazor project. The form for inserting data only displays when the Add button is clicked:

After we successfully insert data, we want the insert form to disappear. Add the following code to the bottom of the Insert() method:

mode = MODE.None;

Let us now add a form that only appears when we wish to update or delete data. Add the following just before @functions:

@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>&nbsp;&nbsp;&nbsp;&nbsp;</span>
  <button @onclick="@Delete" class="btn btn-danger">Delete</button>
}

Add these Update() and Delete() methods as shown below:

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;
}

We want to be able to select a row of data and update or delete it. We will add an onclick handler to a row. In the HTML table, replace the opening <tr> tag, under the @foreach statement, with this:

<tr @onclick="@(() => Show(item.StudentId))">

The above would pass the appropriate studentId to a method named Show() whenever a row is clicked. Add the following Show() method that retrieves a student from the service and displays it in the Update/Delete form:

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;
}

Let us test our app by adding, updating and deleting data. Run the application: