CRUD operations using Blazor Web App, Entity Framework and SQL Server

#dotnet #blazor #csharp #sqlserver

In this tutorial, we’ll explore how to do simple CRUD (Create, Read, Update, and Delete) Operations in a Blazor web App.

What you'll need for this tutorial;

Visual Studio community edition. You can download it here
SQL Server (Express edition). Can be downloaded here
SQL Server Management Studio (Optionally). Can be downloaded here

Create a Blazor project

Open up Visual Studio and click Create a new Project
In the Search Template field, search for Blazor Web App and click on it.

Give your project and name, I have called mine BlazorWebApp, then Select .NET 8 as the target framework and leave all the other options as the default values then click Create.
Next, we’re going to install the necessary packages into our project, so open up the package manager console and type in the following commands;

Install-Package Microsoft.EntityFrameworkCore

Install-Package Microsoft.EntityFrameworkCore.SqlServer

Install-Package Microsoft.EntityFrameworkCore.Tools

Install-Package Microsoft.EntityFrameworkCore.Design

Scafollding DbContext

Now, we're going to create a model/entity that most of our buisness logic will be based on;

Create a folder called Models and add a new class called Club
The Club class will have the structure as below;

    public class Club
    {
        public int Id { get; set; }
        public string ClubName { get; set; }
        public string YearFounded { get; set; }
        public string Country { get; set; }
    }

Next, still in the Models folder, create a new class called ClubDbContext and copy this code into it;

    public class ClubDbContext : DbContext
    {
        public ClubDbContext(DbContextOptions<ClubDbContext> options) : base(options)
        {
        }
        public DbSet<Club> Clubs { get; set; }
    }

We can now create both our Database and Table.
Add this to your appsettings.json file;

  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=BlazorDB;Trusted_Connection=True;"
  }

This contains the connection string for our database.

Now, create a class called DbContextFactory and add the following code;

    public class DbContextFactory : IDesignTimeDbContextFactory<ClubDbContext>
    {
        public ClubDbContext CreateDbContext(string[] args)
        {
            var optionsBuilder = new DbContextOptionsBuilder<ClubDbContext>();

            // Get the connection string from appsettings.json
            var configuration = new ConfigurationBuilder()
                .AddJsonFile("appsettings.json")
                .Build();
            optionsBuilder.UseSqlServer(configuration.GetConnectionString("DefaultConnection"));
            return new ClubDbContext(optionsBuilder.Options);
        }
    }

The DbContextFactory class is implementing the IDesignTimeDbContextFactory<ClubDbContext> interface. This interface is used to create instances of our DbContext (ClubDbContext) at design time.

So far, your project should look like this.

Next, in Program.cs add this line of code so your project knows the correct Database String to reference;

builder.Services.AddDbContext<ClubDbContext>(c =>
    c.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

Running Migrations

Now that all that has been setup, lets create the first migration
Open up the Package Manager Console and run this command;

Add-Migration FirstMigration -Context ClubDbContext

This command creates a Migrations folder in the project based on the current state of our ClubDbContext.

In other to finish this process and create the Clubs table, we are going to have to update the database. So go ahead, and run the below command in the Package Manager console as well;

Update-Database

If all goes well, you should get a response that the build Succeeded and a response saying Applying migration '20240911173037_FirstMigration', which means our migration has been applied and our Clubs table has been created.

Before we move on, let us create a Services folder that will act as the hub for where most of the service implementation will reside.
In the services folder create an Interface called IClubService and add the below code;

    public interface IClubService
    {
        Task<List<Club>> GetClubs();
        Task<Club> GetClub(int id);
        Task AddClub(Club club);
        Task UpdateClub(Club club);
        Task DeleteClub(int id);
    }

Then, still in the Services folder, create a new class that will implement that Interface called ClubService and copy the below code into it;

public class ClubService : IClubService
{
    private readonly ClubDbContext _context;

    public ClubService(ClubDbContext context)
    {
        _context = context;
    }

    public async Task<List<Club>> GetClubs()
    {
        return await _context.Clubs.ToListAsync();
    }

    public async Task<Club> GetClub(int id)
    {
        var club = await _context.Clubs.FindAsync(id);
        if (club == null)
        {
            throw new Exception("club not found");
        }
        return club;
    }

    public async Task AddClub(Club club)
    {
        _context.Clubs.Add(club);
        await _context.SaveChangesAsync();
    }

    public async Task UpdateClub(Club club)
    {
        _context.Entry(club).State = EntityState.Modified;
        await _context.SaveChangesAsync();
    }

    public async Task DeleteClub(int id)
    {
        var club = await _context.Clubs.FindAsync(id);
        if (club != null)
        {
            _context.Clubs.Remove(club);
            await _context.SaveChangesAsync();
        }
    }
}

Make sure to register both the Interface and its implementation by adding the below code to Program.cs;

builder.Services.AddScoped<IClubService, ClubService>();

At this point, your project should look like this;

Implementing CRUD Operations

Let’s insert some data into our Club table. From your project, navigate to the Components folder and then to the Pages folder, and then create a Razor Component and call this Club.razor
In Club.razor page, add this piece of code;

@page "/club"
@rendermode InteractiveServer
@inject NavigationManager Navigation
@inject BlazorWebApp.Services.IClubService ClubService

<h3>Club Table</h3>

@if (clubs == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <table class="table">
        <thead>
            <tr>
                <th>ID</th>
                <th>Club Name</th>
                <th>Year Founded</th>
                <th>Country</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var club in clubs)
            {
                <tr>
                    <td>@club.Id</td>
                    <td>@club.ClubName</td>
                    <td>@club.YearFounded</td>
                    <td>@club.Country</td>
                    <td>
                        <button class="btn btn-warning btn-sm" @onclick="() => NavigateToUpdate(club.Id)">Update</button>
                        <button class="btn btn-danger btn-sm" @onclick="() => DeleteClub(club.Id)">Delete</button>
                    </td>
                </tr>
            }
        </tbody>
    </table>

}

<button class="btn btn-primary" @onclick="OpenAddClubModal">Add New Club</button>

@code {
    private List<Models.Club> clubs;

    protected override async Task OnInitializedAsync()
    {
        // Load the club when the component is initialized
        clubs = await ClubService.GetClubs();

    }

    private void OpenAddClubModal()
    {
        Navigation.NavigateTo("/add-club");
    }

    private void NavigateToUpdate(int clubId)
    {
        Navigation.NavigateTo($"/update/{clubId}");
    }

    private async Task DeleteClub(int clubId)
    {

        await ClubService.DeleteClub(clubId);

        clubs = await ClubService.GetClubs();
    }
}

Next, Let’s Create a page where we can add a Club.
So still in Pages folder, create a razor page called Add-club.razor and add this code to it;

@page "/add-club"
@rendermode InteractiveServer
@using BlazorWebApp.Models
@inject BlazorWebApp.Services.IClubService ClubService
@inject NavigationManager Navigation

<h3>Add New Club</h3>

<div class="mb-3">
    <label for="clubName" class="form-label">Club Name</label>
    <input type="text" class="form-control" id="clubName" @bind="newClubName" />
</div>
<div class="mb-3">
    <label for="yearFounded" class="form-label">Year Founded</label>
    <input type="text" class="form-control" id="yearFounded" @bind="yearFounded" />
</div>
<div class="mb-3">
    <label for="country" class="form-label">Country</label>
    <input type="text" class="form-control" id="country" @bind="newCountry" />
</div>

<button class="btn btn-secondary" @onclick="Cancel">Cancel</button>
<button class="btn btn-primary" @onclick="InsertClub">Save</button>

@code {
    private string newClubName;
    private string yearFounded;
    private string newCountry;

    private async Task InsertClub()
    {
        // Create a new club object
        var club = new Models.Club
            {
                ClubName = newClubName,
                YearFounded = yearFounded,
                Country = newCountry
            };

        // Insert the club into the database
        await ClubService.AddClub(club);

        // Close the modal and navigate back to the club page
        Navigation.NavigateTo("/club");
        StateHasChanged();
    }

    private void Cancel()
    {
        // Close the modal and navigate back to the club page
        Navigation.NavigateTo("/club");
    }
}

Finally, we'll need to create an Update page so create a page called Update.razor
Then add this piece of code to it;

@page "/update/{clubId:int}"
@rendermode InteractiveServer
@using BlazorWebApp.Models
@inject BlazorWebApp.Services.IClubService ClubService
@inject NavigationManager Navigation

<h3>Update Club</h3>

@if (club == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <div class="mb-3">
        <label for="clubName" class="form-label">Club Name</label>
        <input type="text" class="form-control" id="clubName" @bind="club.ClubName" />
    </div>
    <div class="mb-3">
        <label for="country" class="form-label">Country</label>
        <input type="text" class="form-control" id="country" @bind="club.Country" />
    </div>
    <button class="btn btn-secondary" @onclick="Cancel">Cancel</button>
    <button class="btn btn-primary" @onclick="UpdateClub">Save</button>
}

@code {
    [Parameter]
    public int clubId { get; set; }

    private Models.Club club;

    protected override async Task OnInitializedAsync()
    {
        // Load the club to be updated
        club = await ClubService.GetClub(clubId);
    }

    private async Task UpdateClub()
    {
        // Update the club using the service
        await ClubService.UpdateClub(club);

        // Redirect back to the club list page
        Navigation.NavigateTo("/club");
    }

    private void Cancel()
    {
        // Navigate back to the club list page
        Navigation.NavigateTo("/club");
    }
}

We’ll need to add the home Club component to the Nav bar so we can see it.
Navigate to the Components folder and in the Layouts folder, select NavMenu.Razor
Replace the code in NavMenu with the below;

<div class="top-row ps-3 navbar navbar-dark">
    <div class="container-fluid">
        <a class="navbar-brand" href="">BlazorWebApp</a>
    </div>
</div>

<input type="checkbox" title="Navigation menu" class="navbar-toggler" />

<div class="nav-scrollable" onclick="document.querySelector('.navbar-toggler').click()">
    <nav class="flex-column">
        <div class="nav-item px-3">
            <NavLink class="nav-link" href="" Match="NavLinkMatch.All">
                <span class="bi bi-house-door-fill-nav-menu" aria-hidden="true"></span> Home
            </NavLink>
        </div>
        <div class="nav-item px-3">
            <NavLink class="nav-link" href="club">
                <span class="bi bi-list-nested-nav-menu" aria-hidden="true"></span> Clubs
            </NavLink>
        </div>

    </nav>
</div>