SIRIS

Blazor WebAssembly : Call Web APIs to perform CRUD Operations

Creating Blazor WebAssembly App

In your Visual Studio create a new Blazor WebAssembly APP as shown by the below image.

blazor webassembly template

Next, select the latest version of DOT NET. We have selected the .NET version 7.0 for this Blazor WebAssembly app. Check below image.

Blazor WebAssembly App .NET 7.0

Your app will be created with basic Blazor Configuration and a few pages. Run the app in Visual studio and you can see it runs perfectly. It’s time to start adding the CRUD feature in the app.

Note that I have named my app as BlazorWA so all the namespaces will be made with respect to this name. If you have chosen a different name the make sure the namespaces are correct for the codes to run correctly.

Web API and CRUD operations

The Web API that I will be calling here is made in ASP.NET Core MVC application.

This API has the following methods that I will be calling from my Blazor WebAssembly app.

1. HTTP GET method that provides all the reservations in JSON format. Another method also of type HTTP GET accepts a reservation id and returns it’s information.
2. HTTP POST method that accepts a reservation that will be inserted to the database. It returns the newly inserted reservation back to the client in JSON.
3. HTTP PUT method that updates a previously created reservation record. It receives the new values of a reservation and returns the updated reservation record back to the client.
4. HTTP DELETE method that deletes a reservation, whose id is sent by the client.

Start by creating Models folder on the root of the app. Inside it, create a new class and call it Reservation.cs. This class will hold the reservation object and it’s code is given below:

using System.ComponentModel.DataAnnotations;
 
namespace BlazorWA.Models
{
    public class Reservation
    {
        public int Id { get; set; }
 
        [Required]
        public string Name { get; set; }
 
        [Required]
        public string StartLocation { get; set; }
 
        [Required]
        public string EndLocation { get; set; }
    }
}

Next, create a CSS called validation.css inside the wwwroot folder with the following code. The work of this css will be to style the validation errors in red & orange colors.

.validation-errors {
    background-color: #ff6a00;
    padding: 8px;
    font-size: 16px;
    font-weight: 500;
}
 
li.validation-message {
    color: #FFFFFF;
    font-weight: 500
}
 
div.validation-message {
    color: #ff6a00;
    font-weight: 500
}
 
modified.valid {
    border: solid 3px #ffd800;
}
 
.invalid {
    border: solid 3px #ff6a00;
}

Next, go to the _Imports.razor file (residing on the root folder of the app) and import the highlighted namespaces as shown below.

@using System.Net.Http
@using System.Net.Http.Json
@using Microsoft.AspNetCore.Components.Forms
@using Microsoft.AspNetCore.Components.Routing
@using Microsoft.AspNetCore.Components.Web
@using Microsoft.AspNetCore.Components.Web.Virtualization
@using Microsoft.AspNetCore.Components.WebAssembly.Http
@using Microsoft.JSInterop
@using BlazorWA
@using BlazorWA.Shared
@using BlazorWA.Models
@using Newtonsoft.Json
@using System.Text

Make sure to install the package Newtonsoft.Json to the app.

The C# classes belonging to these namespaces will be used when performing CRUD operations.

Now you need to add links on the sidebar so that you can navigate to the razor components that I will be creating shortly. The sidebar is formed by the NavMenu.razor component that resides inside the Shared folder.

The added links are shown in highlighted way in the below code.

<div class="top-row pl-4 navbar navbar-dark">
    <a class="navbar-brand" href="">BlazorWA</a>
    <button class="navbar-toggler" @onclick="ToggleNavMenu">
        <span class="navbar-toggler-icon"></span>
    </button>
</div>
 
<div class="@NavMenuCssClass" @onclick="ToggleNavMenu">
    <ul class="nav flex-column">
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="" Match="NavLinkMatch.All">
                <span class="oi oi-home" aria-hidden="true"></span> Home
            </NavLink>
        </li>
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="Read">
                <span class="oi oi-list-rich" aria-hidden="true"></span> Read Reservation
            </NavLink>
        </li>
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="Create">
                <span class="oi oi-plus" aria-hidden="true"></span> Create Reservation
            </NavLink>
        </li>
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="Get">
                <span class="oi oi-list-rich" aria-hidden="true"></span> Get Reservation by Id
            </NavLink>
        </li>
    </ul>
</div>
 
@code {
    private bool collapseNavMenu = true;
 
    private string NavMenuCssClass => collapseNavMenu ? "collapse" : null;
 
    private void ToggleNavMenu()
    {
        collapseNavMenu = !collapseNavMenu;
    }
}

Create Reservation

The Web API has a HTTP POST method that inserts a reservation in the database. It’s skeleton code is given below:

[HttpPost]
public Reservation Post([FromBody] Reservation res) =>
repository.AddReservation(new Reservation
{
    Name = res.Name,
    StartLocation = res.StartLocation,
    EndLocation = res.EndLocation
});

Here I will call this API method in order to create a new reservation. So, create a new Razor Component file called Create.razor. It will contain a reservation form as shown by the below image:

create reservation form Blazor WebAssembly

User can add a new reservation to the database by filling and submitting this form. The full code of this component is given below.

@page "/Create"
<link href="/validation.css" rel="stylesheet" />

<h1 class="bg-info text-white">Create Reservation</h1>
<h2 class="text-success p-2">@FormSubmitMessage</h2>

<EditForm Model="ReservationData" OnValidSubmit="HandleValidSubmit" OnInvalidSubmit="HandleInvalidSubmit">
    <DataAnnotationsValidator/>
    <ValidationSummary />

    <div class="form-group">
        <label>Name</label>
        <ValidationMessage For="@(() => ReservationData.Name)" />
        <InputText class="form-control" @bind-Value="ReservationData.Name" />
    </div>
    <div class="form-group">
        <label>Start Location</label>
        <ValidationMessage For="@(() => ReservationData.StartLocation)" />
        <InputText class="form-control" @bind-Value="ReservationData.StartLocation" />
    </div>
    <div class="form-group">
        <label>End Location</label>
        <ValidationMessage For="@(() => ReservationData.EndLocation)" />
        <InputText class="form-control" @bind-Value="ReservationData.EndLocation" />
    </div>
    <div class="form-group">
        <button type="submit" class="btn btn-primary">Click</button>
    </div>
</EditForm>

@if (ReceivedReservation != null)
{
    <h3 class="text-warning p-2">Reservation</h3>
    <table class="table table-sm table-striped table-bordered m-2">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Start Location</th>
                <th>End Location</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>@ReceivedReservation.Id</td>
                <td>@ReceivedReservation.Name</td>
                <td>@ReceivedReservation.StartLocation</td>
                <td>@ReceivedReservation.EndLocation</td>
            </tr>
        </tbody>
    </table>
}

@code {
    public Reservation ReservationData = new Reservation();
    Reservation ReceivedReservation;
    public string FormSubmitMessage { get; set; } = "Reservation Not Created";

    public async Task HandleValidSubmit()
    {
        ReceivedReservation = new Reservation();
        using (var httpClient = new HttpClient())
        {
            StringContent content = new StringContent(JsonConvert.SerializeObject(ReservationData), Encoding.UTF8, "application/json");

            using (var response = await httpClient.PostAsync("https://localhost:44324/api/Reservation", content))
            {
                string apiResponse = await response.Content.ReadAsStringAsync();
                ReceivedReservation = JsonConvert.DeserializeObject<Reservation>(apiResponse);
            }
        }
        FormSubmitMessage = "Reservation Created";
    }

    public void HandleInvalidSubmit() => FormSubmitMessage = "Invalid Reservation";
}

When the form is submitted the method HandleValidSubmit is called. In this method the Web API call is made and the reservation data is sent to the API for inserting into the database.

I made a new object of the HttpClient class that is used for sending HTTP requests to the APIs and also for receiving HTTP responses from the APIs.

Then I use this object for sending the reservation information in the form to the API.

using (var httpClient = new HttpClient())
{
    StringContent content = new StringContent(JsonConvert.SerializeObject(ReservationData), Encoding.UTF8, "application/json");

    using (var response = await httpClient.PostAsync("https://localhost:44324/api/Reservation", content))
    {
        string apiResponse = await response.Content.ReadAsStringAsync();
        ReceivedReservation = JsonConvert.DeserializeObject<Reservation>(apiResponse);
    }
}

Note – The PostAsync() method of the HttpClient class is used to make HTTP POST request to the API.

The reservation information is added to the HTTP request body and this is done by the StringContent class:

StringContent content = new StringContent(JsonConvert.SerializeObject(ReservationData), Encoding.UTF8, "application/json");
…
var response = await httpClient.PostAsync("https://localhost:44324/api/Reservation", content)

The API method is called at the URL – https://localhost:44324/api/Reservation and the response is received in the variable called “ReceivedReservation”. This variable is used to create a html table that shows the newly created reservation data.

<table class="table table-sm table-striped table-bordered m-2">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Start Location</th>
            <th>End Location</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>@ReceivedReservation.Id</td>
            <td>@ReceivedReservation.Name</td>
            <td>@ReceivedReservation.StartLocation</td>
            <td>@ReceivedReservation.EndLocation</td>
        </tr>
    </tbody>
</table>

Testing

Note that our Web API is running on the port 44324. You can simply open the Web API project in another instance of Visual Studio and run it. Running will make the Web API accessible to the Blazor WebAssembly app.

Next run the Blazor WebAssembly app and navigate to the Create Reservation link. In the form presented to you, fill and submit a new reservation.

I have shown the complete procedure of creating a new reservation and the new reservation receiving back from the API in the below video.

Read All Reservation

The Web API has a HTTP GET method that returns all the reservations in JSON. It’s skeleton is shown below.

[HttpGet]
public IEnumerable<Reservation> Get() => repository.Reservations;

Now I will call this HTTP GET method to read all the reservations. So, create a new Razor component file called Read.razor inside the Pages folder of your app. The full code to be added to this component is given below:

@page "/Read"
 
<h1 class="bg-info text-white">Read Reservations</h1>
 
<NavLink class="btn btn-primary" href="Create">Create Reservation</NavLink>
<NavLink class="btn btn-secondary" href="Get">Get Reservation</NavLink>
 
<table class="table table-sm table-striped table-bordered m-2">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Start Location</th>
            <th>End Location</th>
        </tr>
    </thead>
    <tbody>
        @foreach (Reservation r in Reservations)
        {
            <tr>
                <td>@r.Id</td>
                <td>@r.Name</td>
                <td>@r.StartLocation</td>
                <td>@r.EndLocation</td>
            </tr>
        }
    </tbody>
</table>
 
@code {
    [Inject]
    public NavigationManager NV { get; set; }
 
    public IEnumerable<Reservation> Reservations { get; set; } = Enumerable.Empty<Reservation>();
 
    protected async override Task OnInitializedAsync()
    {
        await CallAPI();
    }
 
    async Task CallAPI()
    {
        using (var httpClient = new HttpClient())
        {
            using (var response = await httpClient.GetAsync("https://localhost:44324/api/Reservation"))
            {
                string apiResponse = await response.Content.ReadAsStringAsync();
                Reservations = JsonConvert.DeserializeObject<List<Reservation>>(apiResponse);
            }
        }
    }
}

On the OnInitializedAsync Lifecyle method of the razor component, I have called a C# method CallAPI which makes an API call to the HTTP GET method. The HttpClient class is once again used to make this call, see the below code.

Using (var httpClient = new HttpClient())
{
    using (var response = await httpClient.GetAsync("https://localhost:44324/api/Reservation"))
    {
        string apiResponse = await response.Content.ReadAsStringAsync();
        Reservations = JsonConvert.DeserializeObject<List<Reservation>>(apiResponse);
    }
}

The reservations received in JSON are deserialized to the Reservation class object and is shown inside the HTML table.

Reservations = JsonConvert.DeserializeObject<List<Reservation>>(apiResponse);

Note – The GetAsync() method of the HttpClient class is used to make HTTP GET request to the API.

Testing

Run the APP and navigate to the Read Reservation URL where you will be shown all the reservations that the API has sent to the Blazor wasm app. Check below image which shows the reservations.

read reservation blazor wasm

Read a Reservation by it’s “Id”

I can also read a particular reservation record by calling the below shown method of the Web API. Note – here I have to sent the “id” of the reservation to the URL of the API.

[HttpGet("{id}")]
public Reservation Get(int id) => repository[id];

So, create a new razor component file called Get.razor and add the following code to it.

@page "/Get"
<link href="/validation.css" rel="stylesheet" />

<h1 class="bg-info text-white">Get Reservation by Id</h1>

<div class="form-group">
    <label>Id</label>
    <input class="form-control" type="text" @bind="Id" />
</div>
<div class="form-group">
    <button class="btn btn-primary" @onclick="GetReservation">Click</button>
</div>

@if (ReceivedReservation != null)
{
    <h3 class="text-warning p-2">Reservation</h3>
    <table class="table table-sm table-striped table-bordered m-2">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Start Location</th>
                <th>End Location</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>@ReceivedReservation.Id</td>
                <td>@ReceivedReservation.Name</td>
                <td>@ReceivedReservation.StartLocation</td>
                <td>@ReceivedReservation.EndLocation</td>
            </tr>
        </tbody>
    </table>
}

@code {
    public int Id { get; set; }
    public Reservation ReceivedReservation;

    public async Task GetReservation()
    {
        ReceivedReservation = new Reservation();
        using (var httpClient = new HttpClient())
        {
            using (var response = await httpClient.GetAsync("https://localhost:44324/api/Reservation/" + Id))
            {
                string apiResponse = await response.Content.ReadAsStringAsync();
                ReceivedReservation = JsonConvert.DeserializeObject<Reservation>(apiResponse);
            }
        }
    }
}

This component presents a text box to the user where he needs to enter the Id of the reservation to be read. The API call is made by the below code:

using (var httpClient = new HttpClient())
{
    using (var response = await httpClient.GetAsync("https://localhost:44324/api/Reservation/" + Id))
    {
        string apiResponse = await response.Content.ReadAsStringAsync();
        ReceivedReservation = JsonConvert.DeserializeObject<Reservation>(apiResponse);
    }
}

Notice the Id of the reservation is added to the API URL as:

using (var response = await httpClient.GetAsync("https://localhost:44324/api/Reservation/" + Id))

The received reservation is shown in the HTML Table given on the component.

Testing

Run your Blazor WebAssembly App and navigate to Get Reservation by Id link. Here enter 3 on the text box and click the button. You will receive the 3rd reservation and it will be shown on the component. See the below image:

read reservation by Id blazor wasm

Update a Reservation

Now I will update a reservation record by calling the HTTP PUT method of the API which is shown below:

[HttpPut]
public Reservation Put([FromForm] Reservation res) => repository.UpdateReservation(res);

So, create a new razor component called Update.razor inside the Pages folder of the Blazor WebAssembly App.

The component’s work is to fetch the reservation (whose id is provided to it) by calling a method of the Web API and showing it on a form. The user can update the reservation fields and submit the form, and that will update the reservation by calling another method of the Web API.

Next, add the following code to the component.

@page "/Update/{id:int}"
<link href="/validation.css" rel="stylesheet" />
 
<h1 class="bg-info text-white">Update Reservation</h1>
<h2 class="text-success p-2">@FormSubmitMessage</h2>
 
<EditForm Model="ReservationData" OnValidSubmit="HandleValidSubmit" OnInvalidSubmit="HandleInvalidSubmit">
    <DataAnnotationsValidator />
    <ValidationSummary />
 
    <div class="form-group">
        <label>Id</label>
        <ValidationMessage For="@(() => ReservationData.Id)" />
        <InputNumber disabled class="form-control" @bind-Value="ReservationData.Id" />
    </div>
    <div class="form-group">
        <label>Name</label>
        <ValidationMessage For="@(() => ReservationData.Name)" />
        <InputText class="form-control" @bind-Value="ReservationData.Name" />
    </div>
    <div class="form-group">
        <label>Start Location</label>
        <ValidationMessage For="@(() => ReservationData.StartLocation)" />
        <InputText class="form-control" @bind-Value="ReservationData.StartLocation" />
    </div>
    <div class="form-group">
        <label>End Location</label>
        <ValidationMessage For="@(() => ReservationData.EndLocation)" />
        <InputText class="form-control" @bind-Value="ReservationData.EndLocation" />
    </div>
    <div class="form-group">
        <button type="submit" class="btn btn-primary">Click</button>
    </div>
</EditForm>
 
@if (ReceivedReservation != null)
{
   <h3 class="text-warning p-2">Reservation</h3>
    <table class="table table-sm table-striped table-bordered m-2">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Start Location</th>
                <th>End Location</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>@ReceivedReservation.Id</td>
                <td>@ReceivedReservation.Name</td>
                <td>@ReceivedReservation.StartLocation</td>
                <td>@ReceivedReservation.EndLocation</td>
            </tr>
        </tbody>
    </table>
}
 
@code {
    public Reservation ReservationData;
    public Reservation ReceivedReservation;
 
    public string FormSubmitMessage { get; set; } = "Reservation Not Updated";
 
    [Parameter]
    public int Id { get; set; }
 
    public async Task HandleValidSubmit()
    {
        ReceivedReservation = new Reservation();
        using (var httpClient = new HttpClient())
        {
            var content = new MultipartFormDataContent();
            content.Add(new StringContent(ReservationData.Id.ToString()), "Id");
            content.Add(new StringContent(ReservationData.Name), "Name");
            content.Add(new StringContent(ReservationData.StartLocation), "StartLocation");
            content.Add(new StringContent(ReservationData.EndLocation), "EndLocation");
 
            using (var response = await httpClient.PutAsync("https://localhost:44324/api/Reservation", content))
            {
                string apiResponse = await response.Content.ReadAsStringAsync();
                ReceivedReservation = JsonConvert.DeserializeObject<Reservation>(apiResponse);
            }
        }
        FormSubmitMessage = "Reservation Updated";
    }
 
    protected async override Task OnParametersSetAsync()
    {
        ReservationData = new Reservation();
        using (var httpClient = new HttpClient())
        {
            using (var response = await httpClient.GetAsync("https://localhost:44324/api/Reservation/" + Id))
            {
                string apiResponse = await response.Content.ReadAsStringAsync();
                ReservationData = JsonConvert.DeserializeObject<Reservation>(apiResponse);
            }
        }
    }
 
    public void HandleInvalidSubmit() => FormSubmitMessage = "Invalid Reservation";
}

The component receives the id of the reservation, which is to be fetched from the API, in it’s route parameter id. It is specified in the @page directive.

@page "/Update/{id:int}"

This id value is received by it’s component parameter shown below:

[Parameter]
public int Id { get; set; }

Then on the OnParametersSetAsync lifecycle method, I make a call to the API to fetch the corresponding reservation (whose Id is received in the route parameter). This code is shown below:

protected async override Task OnParametersSetAsync()
{
    ReservationData = new Reservation();
    using (var httpClient = new HttpClient())
    {
        using (var response = await httpClient.GetAsync("https://localhost:44324/api/Reservation/" + Id))
        {
            string apiResponse = await response.Content.ReadAsStringAsync();
            ReservationData = JsonConvert.DeserializeObject<Reservation>(apiResponse);
        }
    }
}

Next the ReservationData variable which receives this reservation (as it is deserialized to Reservation class object), is used to show the reservation on the form.

<EditForm Model="ReservationData" OnValidSubmit="HandleValidSubmit" OnInvalidSubmit="HandleInvalidSubmit">
…
</EditForm>

The user can update the reservation by adding new values and submitting the form. This will call the HandleValidSubmit method that calls the Web API. The following code does this work:

using (var httpClient = new HttpClient())
{
    …    

    using (var response = await httpClient.PutAsync("https://localhost:44324/api/Reservation", content))
    {
        string apiResponse = await response.Content.ReadAsStringAsync();
        ReceivedReservation = JsonConvert.DeserializeObject<Reservation>(apiResponse);
    }
}

Note – The PutAsync() method of the HttpClient class is used to make HTTP PUT request to the API.

Since the HTTP PUT method asks for the updated values of the reservation on the Form-Data of the HTTP request therefore I have used the MultipartFormDataContent class to do this task. This code is shown below.

var content = new MultipartFormDataContent();
content.Add(new StringContent(ReservationData.Id.ToString()), "Id");
content.Add(new StringContent(ReservationData.Name), "Name"); 
content.Add(new StringContent(ReservationData.StartLocation), "StartLocation");
content.Add(new StringContent(ReservationData.EndLocation), "EndLocation");

Once the reservation is updated, the updated reservation is send back as a response, and it is shown in the HTML table.

<table class="table table-sm table-striped table-bordered m-2">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Start Location</th>
            <th>End Location</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>@ReceivedReservation.Id</td>
            <td>@ReceivedReservation.Name</td>
            <td>@ReceivedReservation.StartLocation</td>
            <td>@ReceivedReservation.EndLocation</td>
        </tr>
    </tbody>
</table>

Testing

Link the newly created Update.razor component from the html table that shows all the reservations. This html table is given on the Read.razor component. Add a new Update column and add a link to the Update.razor and pass the Id of the reservation in it’s url. I have shown this in the highlighted code below.

See the below code.

<table class="table table-sm table-striped table-bordered m-2">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Start Location</th>
            <th>End Location</th>
            <th>Update</th>
        </tr>
    </thead>
    <tbody>
        @foreach (Reservation r in Reservations)
        {
            <tr>
                <td>@r.Id</td>
                <td>@r.Name</td>
                <td>@r.StartLocation</td>
                <td>@r.EndLocation</td>
                <td>
                    <a href="Update/@r.Id">
                        <img src="/icon/edit.png" />
                    </a>
                </td>
            </tr>
        }
    </tbody>
</table>

Now run the Blazor wasm app and check the Update feature. I have shown this in the below given video.

Delete a Reservation

The last of the CRUD operation is of delete reservation. Here I will need to call HTTP DELETE method of the Web API to do the deletion of a reservation. The skeleton of this method is given below.

[HttpDelete("{id}")]
public void Delete(int id) => repository.DeleteReservation(id);

The Delete feature is added to the same Read.razor component which shows all the reservations. You have to add a new Delete Column to the HTML table. In this column add a button which on clicking will delete the record.

I have shown this in highlighted manner in the below code of the Read.razor component.

@page "/Read"
 
<h1 class="bg-info text-white">Read Reservations</h1>
 
<NavLink class="btn btn-primary" href="Create">Create Reservation</NavLink>
<NavLink class="btn btn-secondary" href="Get">Get Reservation</NavLink>
 
<table class="table table-sm table-striped table-bordered m-2">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Start Location</th>
            <th>End Location</th>
            <th>Update</th>
            <th>Delete</th>
        </tr>
    </thead>
    <tbody>
        @foreach (Reservation r in Reservations)
        {
            <tr>
                <td>@r.Id</td>
                <td>@r.Name</td>
                <td>@r.StartLocation</td>
                <td>@r.EndLocation</td>
                <td>
                    <a href="Update/@r.Id">
                        <img src="/icon/edit.png" />
                    </a>
                </td>
                <td>
                    <button class="btn btn-sm btn-danger" @onclick="@(() => Delete(r))">
                        <img src="/icon/close.png" />
                    </button>
                </td>
            </tr>
        }
    </tbody>
</table>
 
@code {
    [Inject]
    public NavigationManager NV { get; set; }
 
    public IEnumerable<Reservation> Reservations { get; set; } = Enumerable.Empty<Reservation>();
 
    protected async override Task OnInitializedAsync()
    {
        await CallAPI();
    }
 
    async Task CallAPI()
    {
        using (var httpClient = new HttpClient())
        {
            using (var response = await httpClient.GetAsync("https://localhost:44324/api/Reservation"))
            {
                string apiResponse = await response.Content.ReadAsStringAsync();
                Reservations = JsonConvert.DeserializeObject<List<Reservation>>(apiResponse);
            }
        }
    }
 
    public async Task Delete(Reservation r)
    {
        using (var httpClient = new HttpClient())
        {
            using (var response = await httpClient.DeleteAsync("https://localhost:44324/api/Reservation/" + r.Id))
            {
                string apiResponse = await response.Content.ReadAsStringAsync();
            }
        }
        await CallAPI();
    }
}

The delete button call a method by the name “Delete”. In this method the call to the API is made, and that deletes our record. The deletion code is:

using (var httpClient = new HttpClient())
{
    using (var response = await httpClient.DeleteAsync("https://localhost:44324/api/Reservation/" + r.Id))
    {
        string apiResponse = await response.Content.ReadAsStringAsync();
    }
}

Note that I have used DeletAsync() method of the HttpClient class to call the HTTP DELETE type of Web API method.

Testing

Run your Blazor WebAssembly APP and perform the deletion as shown by the below video.

Upload Images to API from Blazor WASM

Now I will show how you can Upload Image files to Web API from Blazor WASM app. The Web API has an “Upload File method” that accepts IFormFile type of parameter. The skeleton of this method is given below.

[HttpPost("UploadFile")]
public async Task<string> UploadFile([FromForm] IFormFile file)
{
    string path = Path.Combine(webHostEnvironment.WebRootPath, "Images/" + file.FileName);
    using (var stream = new FileStream(path, FileMode.Create))
    {
        await file.CopyToAsync(stream);
    }
    return "https://localhost:44324/Images/" + file.FileName;
}

I will call this above method of the API from Blazor WASM app. So, create a new razor component called Upload.razor. It’s code is given below.

@page "/Upload"
<link href="/validation.css" rel="stylesheet" />

<h1 class="bg-info text-white">Upload File</h1>

<div class="form-group">
    <InputFile OnChange="@ChangeHandler" />
</div>
<div class="form-group">
    <button class="btn btn-primary" @onclick="UploadFile">Click</button>
</div>

@if (apiResponse != null)
{
    <h2>Uploaded File</h2>
    <img src="@apiResponse" />
}

@code {
    public string apiResponse { get; set; }
    public IBrowserFile F;

    private void ChangeHandler(InputFileChangeEventArgs e)
    {
        F = e.File;
    }

    public async Task UploadFile()
    {
        if (F != null)
        {
            // 968435456 is roughly equalt to 900 mb file size
            using (var ms = F.OpenReadStream(968435456))
            {
                var content = new MultipartFormDataContent();
                content.Add(new StreamContent(ms), "file", F.Name);

                using (var httpClient = new HttpClient())
                {
                    using (var response = await httpClient.PostAsync("https://localhost:44324/api/Reservation/UploadFile", content))
                    {
                        response.EnsureSuccessStatusCode();
                        apiResponse = await response.Content.ReadAsStringAsync();
                    }
                }
            }
        }
    }
}

The component has an InputFile that can be use to add a file.

<InputFile OnChange="@ChangeHandler" />

The onchange event sets the IBrowserFile variable called “F” with the supplied file to the InputFile component.

private void ChangeHandler(InputFileChangeEventArgs e)
{
    F = e.File;
}

The button given on the component calls the Web API method and uploads the file to it.

I opened a stream and allowed up to 900 mb file size to be uploaded.

// 968435456 is roughly equalt to 900 mb file size
using (var ms = F.OpenReadStream(968435456))
{
…
}

Then created a MultiPartFormDataContent variable, populate it, and send it to the HttpClient class method PostAsync.

The below code does the calling of the Web API.

using (var httpClient = new HttpClient())
{
    using (var response = await httpClient.PostAsync("https://localhost:44324/api/Reservation/UploadFile", content))
    {
        response.EnsureSuccessStatusCode();
        apiResponse = await response.Content.ReadAsStringAsync();
    }
}

The Web API sends back a response which contains the path to the uploaded file, and I show this inside an img tag.

@if (apiResponse != null)
{
    <h2>Uploaded File</h2>
    <img src="@apiResponse" />
}

Testing

Before testing this feature make sure to add the link to this newly created component to the NavMenu.razor. Check below code.

<li class="nav-item px-3">
    <NavLink class="nav-link" href="Upload">
        <span class="oi oi-list-rich" aria-hidden="true"></span> Upload Image File
    </NavLink>
</li>

Now run the app and upload an image file. I have shown this in the below video.

Download the source codes

Download

Conclusion

In this Blazor WebAssembly tutorial I created CRUD operations by calling Web API methods. I hope you liked this video and enjoyed learning Blazor WebAssembly and it’s features.

SQL query optimization basics

12 Query optimization tips for better performance

Monitoring metrics can be used to evaluate query runtime, detect performance pitfalls, and show how they can be improved. For example, they include:

Execution plan: A SQL Server query optimizer executes the query step by step, scans indexes to retrieve data, and provides a detailed overview of metrics during query execution.
Input/Output statistics: Used to identify the number of logical and physical reading operations during the query execution that helps users detect cache/memory capacity issues.
Buffer cache: Used to reduce memory usage on the server.
Latency: Used to analyze the duration of queries or operations.
Indexes: Used to accelerate reading operations on the SQL Server.
Memory-optimized tables: Used to store table data in memory to make reading and writing operations run faster.

Now, we’ll discuss the best SQL Server performance tuning practices and tips you may apply when writing SQL queries.

Tip 1: Add missing indexes

Table indexes in databases help retrieve information faster and more efficiently.

In SQL Server, when you execute a query, the optimizer generates an execution plan. If it detects the missing index that may be created to optimize performance, the execution plan suggests this in the warning section. With this suggestion, it informs you which columns the current SQL should be indexed, and how performance can be improved upon completion.

Let’s run the Query Profiler available in dbForge Studio for SQL Server to see how it works.

Execution plan displays missing indexes in dbForge Studio for SQL Server

You can also understand which tables need indexes by analyzing graphical query plans. The thicker the arrow between operators on the query execution plan is, the more data is passed. Seeing thick arrows you need to think about adding indexes to the tables being processed to reduce the amount of data passed through the arrow.

On the execution plan, you might encounter Table Spool (Lazy Spool in our case) that builds a temporary table in the tempdb and fills it in a lazy manner. Simply put, the table is filled by reading and storing the data only when individual rows are required by the parent operator. The Index Spool operator works in a somehow similar manner ⁠— all input rows are scanned and a copy of each row is placed in a hidden spool file that is stored in the tempdb database and exists only for the lifetime of the query. After that, an index on the rows is built. Both Table Spool and Index Spool might require optimization and adding indexes on the corresponding tables.

Nested Loops might also need your attention. Nested Loops must be indexed, as they take the first value from the first table and search for a match in the second table. Without indexes, SQL Server will have to scan and process the whole table, which can be time-consuming and resource-intensive.

Keep in mind that the missing index does not 100% guarantee better performance. In SQL Server, you can use the following dynamic management views to get a deep insight in using indexes based on query execution history:

sys.dm_db_missing_index_details: Provides information about the suggested missing index, except for spatial indexes.
sys.dm_db_missing_index_columns: Returns information about the table columns that do not contain indexes.
sys.dm_db_missing_index_group_stats: Returns summary information about the missing index group, such as query cost, avg_user_impact (informs you how much performance can be improved by increasing the missing index), and some other metrics to measure effectiveness.
sys.dm_db_missing_index_groups: Provides information about missing indexes included in a specific index group.

Tip 2: Check for unused indexes

You may encounter a situation where indexes exist but are not being used. One of the reasons for that might be implicit data type conversion. Let’s consider the following query:

SELECT *
FROM TestTable
WHERE IntColumn = '1';

When executing this query, SQL Server will perform implicit data type conversion, i.e. convert int data to varchar and run the comparison only after that. In this case, indexes won’t be used. How can you avoid this? We recommend using the CAST() function that converts a value of any type into a specified datatype. Look at the query below.

SELECT *
FROM TestTable
WHERE IntColumn = CAST(@char AS INT);

Let’s study one more example.

SELECT *
FROM TestTable
WHERE DATEPART(YEAR, SomeMyDate) = '2021';

In this case, implicit data type conversion will take place too, and the indexes won’t be used. To avoid this, we can optimize the query in the following way:

SELECT *
FROM TestTable
WHERE SomeDate >= '20210101'
AND SomeDate < '20220101'

Filtered indexes can affect performance too. Suppose, we have an index on the Customer table.

CREATE UNIQUE NONCLUSTERED INDEX IX ON Customer (MembershipCode)
WHERE MembershipCode IS NOT NULL;

The index won’t work for the following query:

SELECT *
FROM Customer
WHERE MembershipCode = '258410';

To make use of the index, you’ll need to optimize the query in the following way:

SELECT *
FROM Customer
WHERE MembershipCode = '258410'
AND MembershipCode IS NOT NULL;

Tip 3: Avoid using multiple OR in the FILTER predicate

When you need to combine two or more conditions, it is recommended to eliminate the usage of the OR operator or split the query into parts separating search expressions. SQL Server can not process OR within one operation. Instead, it evaluates each component of the OR which, in turn, may lead to poor performance.

Let’s consider the following query.

SELECT *
FROM USER
WHERE Name = @P
OR login = @P;

If we split this query into two SELECT queries and combine them by using the UNION operator, SQL Server will be able to make use of the indexes, and the query will be optimized.

SELECT * FROM USER
WHERE Name = @P
UNION
SELECT * FROM USER
WHERE login = @P;

Tip 4: Use wildcards at the end of a phrase only

Wildcards in SQL Server work as a placeholder for words and phrases and can be added at the beginning/end of them. To make data retrieval faster and more efficient, you can use wildcards in the SELECT statement at the end of a phrase. For example:

SELECT
  p.BusinessEntityID
 ,p.FirstName
 ,p.LastName
 ,p.Title
FROM Person.Person p
WHERE p.FirstName LIKE 'And%';

As a result, the query will retrieve a list of customers whose First Name matches the specified condition, i.e. their First Name starts with ‘And’.

Retrieve data using wildcards in the SELECT statement

However, you might encounter situations where you regularly need to search by the last symbols of a word, number, or phrase — for example, by the last digits of a telephone number. In this case, we recommend creating a persisted computed column and running the REVERSE() function on it for easier back-searching.

CREATE TABLE dbo.Customer (
  id INT IDENTITY PRIMARY KEY
 ,CardNo VARCHAR(128)
 ,ReversedCardNo AS REVERSE(CardNo) PERSISTED
)
GO

CREATE INDEX ByReversedCardNo ON dbo.Customer (ReversedCardNo)
GO
CREATE INDEX ByCardNo ON dbo.Customer (CardNo)
GO

INSERT INTO dbo.Customer (CardNo)
  SELECT
    NEWID()
  FROM master.dbo.spt_values sv

SELECT TOP 100
  *
FROM Customer c

--searching for CardNo that end in 510c
SELECT *
FROM dbo.Customer
WHERE CardNo LIKE '%510c'

SELECT
  *
FROM dbo.Customer
WHERE ReversedCardNo LIKE REVERSE('%510c')

Tip 5: Avoid too many JOINs

When you add multiple tables to a query and join them, you may overload the server. In addition, a large number of tables to retrieve data from may result in an inefficient execution plan. When generating a plan, the SQL query optimizer needs to identify how the tables are joined, in which order, and how and when to apply filters and aggregation.

All SQL experts are aware of the SQL JOINs importance, and understanding how to use them in queries appropriately is critical. In particular, JOIN elimination is one of the many techniques to achieve efficient query plans. You can split a single query into several separate queries which can later be joined, and thus remove unnecessary joins, subqueries, tables, etc.

Tip 6: Avoid using SELECT DISTINCT

The SQL DISTINCT operator is used to select only unique values of the column and thus eliminate duplicated values. It has the following syntax:

SELECT DISTINCT column_name FROM table_name;

However, this may require the tool to process large volumes of data and as a result, make the query run slowly. Generally, it is recommended to avoid using SELECT DISTINCT and simply execute the SELECT statement but specify columns.

Another issue is that quite often people build JOINs unnecessarily, and when the data doubles, they add DISTINCT. This happens mainly in a leader-follower relation when people do SELECT DISTINCT … FROM LEADER JOIN FOLLOWER… instead of doing the correct SELECT … FROM LEADER WHERE EXISTS (SELECT… FROM FOLLOWER).

Tip 7: Use SELECT fields instead of SELECT *

The SELECT statement is used to retrieve data from the database. In the case of large databases, it is not recommended to retrieve all data because this will take more resources on querying a huge volume of data.

If we execute the following query, we will retrieve all data from the Users table, including, for example, users’ avatar pictures. The result table will contain lots of data and will take too much memory and CPU usage.

SELECT
  *
FROM Users;

Instead, you can specify the exact columns you need to get data from, thus, saving database resources. In this case, SQL Server will retrieve only the required data, and the query will have lower cost.

For example:

SELECT
    FirstName
   ,LastName
   ,Email
   ,Login
FROM Users;

If you need to retrieve this data regularly, for example, for authentication purposes, we recommend using covering indexes, the biggest advantage of which is that they contain all the fields required by query and can significantly improve query performance and guarantee better results.

CREATE NONCLUSTERED INDEX IDX_Users_Covering ON Users
INCLUDE (FirstName, LastName, Email, Login)

Tip 8: Use TOP to sample query results

The SELECT TOP command is used to set a limit on the number of records to be returned from the database. To make sure that your query will output the required result, you can use this command to fetch several rows as a sample.

For example, take the query from the previous section and define the limit of 5 records in the result set.

SELECT TOP 5
  p.BusinessEntityID
 ,p.FirstName
 ,p.LastName
 ,p.Title
FROM Person.Person p
WHERE p.FirstName LIKE 'And%';

This query will retrieve only 5 records matching the condition:

Use LIMIT (TOP in SQL) to sample query results

Tip 9: Run the query during off-peak hours

Another SQL tuning technique is to schedule the query execution at off-peak hours, especially if you need to run multiple SELECT queries from large tables or execute complex queries with nested subqueries, looping queries, etc. If you are running a heavy query against a database, SQL Server locks the tables you are working with to prevent concurrent use of resources by different transactions. That means that other users are not able to work with those tables. Thus, executing heavy queries at peak times leads not only to server overload but also to restricting other users’ access to certain amounts of data. One of the popular mechanisms to avoid this is to use the WITH (NOLOCK) hint. It allows the user to retrieve the data without being affected by the locks. The biggest drawback of using WITH (NOLOCK) is that it may result in working with dirty data. We recommend that users should give preference to snapshot isolation which helps avoid data locking by using row versioning and guarantees that each transaction sees a consistent snapshot of the database.

Tip 10: Minimize the usage of any query hint

When you face performance issues, you may use query hints to optimize queries. They are specified in T-SQL statements and make the optimizer select the execution plan based on this hint. Usually, query hints include NOLOCK, Optimize For and Recompile. However, you should carefully consider their usage because sometimes they may cause more unexpected side effects, undesirable impacts, or even break business logic when trying to solve the issue. For example, you write additional code for the hints that can be inapplicable or obsolete after a while. This means that you should always monitor, manage, check, and keep hints up to date.

Tip 11: Minimize large write operations

Writing, modifying, deleting, or importing large volumes of data may impact query performance and even block the table when it requires updating and manipulating data, adding indexes or check constraints to queries, processing triggers, etc. In addition, writing a lot of data will increase the size of log files. Thus, large write operations may not be a huge performance issue, but you should be aware of their consequences and be prepared in case of unexpected behavior.

One of the best practices in optimizing SQL Server performance lies in using filegroups that allow you to spread your data across multiple physical disks. Thereby multiple write operations can be processed simultaneously and thus much faster.

Compression and data partitioning can also optimize performance and help minimize the cost of large write operations.

Tip 12: Create JOINs with INNER JOIN (not WHERE)

The SQL INNER JOIN statement returns all matching rows from joined tables, while the WHERE clause filters the resulting rows based on the specified condition. Retrieving data from multiple tables based on the WHERE keyword condition is called NON-ANSI JOINs while INNER JOIN belongs to ANSI JOINs.

It does not matter for SQL Server how you write the query – using ANSI or NON-ANSI joins – it’s just much easier to understand and analyze queries written using ANSI joins. You can clearly see where the JOIN conditions and the WHERE filters are, whether you missed any JOIN or filter predicates, whether you joined the required tables, etc.

Let’s see how to optimize a SQL query with INNER JOIN on a particular example. We are going to retrieve data from the tables HumanResources.Department and HumanResources.EmployeeDepartmentHistory where DepartmentIDs are the same. First, execute the SELECT statement with the INNER JOIN type:

SELECT
  d.DepartmentID
 ,d.Name
 ,d.GroupName
FROM HumanResources.Department d
INNER JOIN HumanResources.EmployeeDepartmentHistory edh
  ON d.DepartmentID = edh.DepartmentID

Then, use the WHERE clause instead of INNER JOIN to join the tables in the SELECT statement:

SELECT
  d.Name
 ,d.GroupName
 ,d.DepartmentID
FROM HumanResources.Department d
    ,HumanResources.EmployeeDepartmentHistory edh
WHERE d.DepartmentID = edh.DepartmentID

Both queries will output the following result:

SQL query optimization best practices

SQL Server performance tuning and SQL query optimization are some of the main aspects for database developers and administrators. They need to carefully consider the usage of specific operators, the number of tables on a query, the size of a query, its execution plan, statistics, resource allocation, and other performance metrics – all that may improve and tune query performance or make it worse.

For better query performance, we recommend using tips and techniques presented in the article, such as running queries at off-peak hours, creating indexes, retrieving data only for the specific columns, applying the correct filter, joins, and operators, as well as trying not to overload queries.

In addition, we propose some recommendations which may not directly relate to coding techniques, but they can still help you write precise and efficient SQL code.

Use uppercase for keywords

Keywords in SQL are generally case-insensitive. You can use lower case, upper case, or both mixed across all popular database management systems, including Microsoft SQL Server. However, it is recommended to use the upper case for keywords for improved code readability.

Although some developers may find it cumbersome to switch between upper and lower case while coding, modern SQL code formatting tools provide the functionality to configure case usage, text coloring, indents, and other options. These tools can automatically apply the preferable formatting while typing.

Write comments for your SQL code

Commenting on the code is optional, but it is highly recommended. Even if some code solutions seem obvious at the moment, what happens in a couple of months when you need to revisit it, especially after writing lots of other code for different modules or projects? This is especially important for your colleagues who will have to work with your code.

Another essential point is to review your existing comments whenever you make changes to your code, ensuring that they remain relevant. It may take time, but it greatly improves the readability of your code, and your efforts will pay off.

Use a professional SQL code editor

As a developer, you may apply various techniques and customize your workflows according to your preferences, but creating code manually from scratch consumes a lot of time and demands exceptional precision. A reliable and potent SQL editor makes code writing easier and enhances accuracy.

Modern SQL editors offer robust functionality for query development, such as auto-completion options, libraries of code snippets, syntax validation, and code formatting. Advanced tools for SQL development allow developers to double the coding speed twice (at least) and guarantee outstanding code quality.

Conclusion

In the article, we have covered a lot of fine-tuning techniques and tips to improve performance. We hope that they will work for you and help you avoid any performance issues that may arise.

SIRIS

Blazor WebAssembly : Call Web APIs to perform CRUD Operations

Creating Blazor WebAssembly App

Web API and CRUD operations

Create Reservation

Read All Reservation

Read a Reservation by it’s “Id”

Update a Reservation

Delete a Reservation

Upload Images to API from Blazor WASM

SQL query optimization basics

12 Query optimization tips for better performance

Tip 1: Add missing indexes

Tip 2: Check for unused indexes

Tip 3: Avoid using multiple OR in the FILTER predicate

Tip 4: Use wildcards at the end of a phrase only

Tip 5: Avoid too many JOINs

Tip 6: Avoid using SELECT DISTINCT

Tip 7: Use SELECT fields instead of SELECT *

Tip 8: Use TOP to sample query results

Tip 9: Run the query during off-peak hours

Tip 10: Minimize the usage of any query hint

Tip 11: Minimize large write operations

Tip 12: Create JOINs with INNER JOIN (not WHERE)

SQL query optimization best practices

Conclusion

Complete Tutorials

Labels