John Ojo

Posted on Oct 13, 2020 • Updated on Oct 17, 2020

Develop a Simple CRUD App with ASP.NET Core Blazor WebAssembly and CockroachDB

#dotnet #csharp #blazor #cockroachdb

In this post, I will walk you through how to get started with ASP.NET Core Blazor WebAssembly app by developing an app that performs basic CRUD. We will be making use of CockroachDB as the database, Entity Framework Core as the ORM.

The basic CRUD app will be a bank account with just name and balance where we can Create, Read, Update, and Delete accounts.

The source code on GitHub : BlazorAppWithCockroachDB

Prerequisites

Introduction

Blazor is an open source and cross-platform web UI framework for building single-page apps using .NET and C# instead of JavaScript. Blazor is based on a powerful and flexible component model for building rich interactive web UI. You implement Blazor UI components using a combination of .NET code and Razor syntax: an elegant melding of HTML and C#.

Cockroach Labs is the company behind CockroachDB, the cloud-native, distributed SQL database that provides next-level consistency, ultra-resilience, data locality, and massive scale to modern cloud applications.

Create the Solution and Projects

We will be making use of three projects:

BlazorAppWithCockroachDB- A Blazor WebAssembly project. After opening VS, click the Create a new project link. Next type Blazor in the search box and choose the first option that comes up (Blazor App):

Give the project and solution a name e.g. BlazorAppWithCockroachDB then click the Create button.

Next select the Blazor WebAssembly App option and click the Create button this will create the project and solution for you. This link will help you understand a Blazor WebAssembly project structure

BlazorAppWithCockroachDB.Api- A Web API project, which is used to get data across to the Blazor app. Right click the Solution in the Solution Explorer and select Add -> New Project, search for Web Application and select the ASP.NET Core Web Application option:

click Next, give the project a name BlazorAppWithCockroachDB.Api, then click Create, select the API option:

and finally click Create.

CockroachDbLib- A class Library which holds the database objects and data access codes. Right click the Solution in the Solution Explorer and select Add -> New Project, search for Class Library then select the Class Library (.NET Standard) option click Next:

Next give the library a name e.g. CockroachDbLib and click Create.

Install Dependencies

We need to install the Npgsql.EntityFrameworkCore.PostgreSQL package. This package will be used to communicate with the database. To install the package, right click the solution in the solution explorer and select Manage NuGet Packages for Solution. Under the Browse section, search for Npgsql.EntityFrameworkCore.PostgreSQL and click on it, then in the preview panel, select the BlazorAppWithCockroachDB.Api and CockroachDbLib checkbox so it will be installed only into the projects BlazorAppWithCockroachDB.Api and CockroachDbLib and click the Install button:

We need Newtonsoft.Json to Serialize and Deserialize the API response. Search for Newtonsoft.Json under the browse section and click it and in the preview section, select only the BlazorAppWithCockroachDB checkbox and click the Install button:

Finally, we need to add a reference for project CockroachDbLib to BlazorAppWithCockroachDB.Api. To do this, right click the Dependencies and click Add project Reference, select the CockroachDbLib checkbox and click ok:

The Database

After starting a cluster in insecure mode (this is for the Docker version), we need to connect a SQL shell to the cluster in insecure mode. In the SQL Shell create a database (bank) and a table (accounts) with columns id:int, name:string and balance:int. The query is given below:

CREATE DATABASE bank;
CREATE TABLE bank.accounts (id INT PRIMARY KEY, name string, balance int);

Add initial data to the database:

INSERT INTO bank.accounts VALUES(1, 'John', 1000);
INSERT INTO bank.accounts VALUES(2, 'Eric', 750);

Check the data by running:

SELECT * FROM bank.accounts;

Defining the Database Entity

Database objects (tables) are defined as Entities using Entity Framework Core. The database object, in this case, the accounts table will be represented as an entity (a C# class file). The naming of the class doesn't really matter here, when hooking up the class/entity in the DbContext, the name given to the variable/property used to link the entity to the DbContext must match exactly with the name of the table in the database.

Create a folder called Models in the CockroachDbLib project, and in that folder add a new class file called Account.cs. The code is:

Each column is mapped to a property as shown in the figure above. The name and data type of the property must match exactly with that of the column in the database table.

Defining the DbContext

Create a new class file CockroachDbContext.cs also in the Models folder. The code for the file:

Line 14: is used to link the Entity to the Dbcontext hence to the database table accounts: if the property name doesn't match the table name exactly, an error will occur.

The Repository

This is where all the data access code will be written. We will be making use of dependency injection (using interface) when calling the access code.
Create a new folder Repository in the CockroachDbLib project and in this folder add a new Interface file called IAccountRepository.cs The code for the file:

This interface defines all the methods needed to perform the CRUD.

Finally, create the AccountRepository.cs class file in the Repository folder also. This class implements the IAccountRepository.cs hence providing implementations to the methods defined in the interface:

With this, we are done with the CockroachDbLib.cs project

Creating the API Endpoints

Adding Configuration

The BlazorAppWithCockroachDB.Api project will be used to create all the required endpoints to get and store data.

We need to add some configurations.

First we need to enable CORS on the API which can be done easily in the Startup.cs file. Create a new field in the Startup.cs file:

readonly string AllowedOrigin = "allowedOrigin";

Next, add the CORS service to your app by adding these lines of code to the ConfigureServices(IServiceCollection services) method:

 services.AddCors(option => {
            option.AddPolicy("allowedOrigin",
                 builder => builder.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader()
                    );
            });

Finally, add the CORS middleware to the Configure(IApplicationBuilder app, IWebHostEnvironment env) method:

app.UseCors(AllowedOrigin);

Ensure that this middleware is added as the first middleware in the pipeline that is right after the:

if (env.IsDevelopment())
       {
              app.UseDeveloperExceptionPage()
       }

The next is to configure the DbContext which can be done by adding this line of code in the ConfigureServices(IServiceCollection services) method of the Startup.cs file:

services.AddDbContext<CockroachDbContext>(options =>
                options.UseNpgsql(Configuration.GetConnectionString("CockroachDb")));

This line requires the ConnectionString which will be added to the appsettings.json file as:

"ConnectionStrings": {
    "CockroachDb": "Host=localhost;Database=bank;Username=John;Port=26257"
  }

The Username should be the user you created and assigned to the database from the SQL shell or omitted if you didn't add any user. The Port must be the port number you used to start up the local cluster for CockroachDb.

The last step is to inject the IAccountRepository dependency which can be done by adding:

 services.AddTransient<IAccountRepository, AccountRepository>();

in the ConfigureServices(IServiceCollection services) method of the Startup.cs file.
Ensure to import all the required namespace in case you are getting an error with the added lines.

Defining the Endpoints

Create a new API Controller in the Controllers folder of project BlazorAppWithCockroachDB.Api and name it AccountController.cs. This controller is going to contain all the endpoints we need. The code for the AccountController.cs file:

The list of endpoints we have from the AccountController.cs are:

https://localhost:44368/api/Account/GetAll - to return a List containing all the Accounts in the bank
https://localhost:44368/api/Account/Get/{id} - to get an Account by the id.
https://localhost:44368/api/Account/GetLastAccountId - to get the id of the last Account.
https://localhost:44368/api/Account/Create - to create a new Account
https://localhost:44368/api/Account/Update - to update the details of an account.
https://localhost:44368/api/Account/Delete/{id} - to delete an account.

The 44368 will be replaced with the port VS uses for yours, this can be gotten from the launchSettings.json file in the Properties folder.

Now that the API is ready it's time to move to the last part which is the Blazor App (project BlazorAppWithCockroachDB).

Blazor App

The first thing to do is to set the base Uri of the API endpoints. This can be done in the Program.cs file by replacing

builder.Services.AddScoped(sp => new HttpClient { BaseAddress = new Uri(builder.HostEnvironment.BaseAddress) });

with

 builder.Services.AddScoped(sp => new HttpClient { BaseAddress = new Uri("https://localhost:44368/api/") });

Model Objects

The API returns data in JSON format so we would need to map the response data to an object which would be displayed to the user. Create a new folder called Model.
Create a class called Account.cs; this will be similar to the Account.cs in the CockroachDbLib.Models namespace. The code for the new Account.cs is:

The second Model object will be used when the user wants to create or edit an account. Create another class file AccountModel.cs in the Model folder. The code for this file is:

Blazor Pages

Blazor pages ends with .razor and they are called Razor component.
Create a new folder called Bank and in this folder create a Razor Component and name it Accounts.razor This component will be used to display all the Accounts and also contain other links to other components. The code is:

The accounts are displayed in a tabular form - line 17 to 35. The DeleteAccount function - line 50 to 75 defines the process called when the Delete button is clicked against an account - line 31.
Line 47 calls the /GetaAll endpoint and line 55 calls the /Delete/{id} passing the id of the account selected.

Next create another Razor component CreateAccount.razor in the Bank folder. This component will be used to add a new account. The code for this component:

Line 39 to 64 contains the code to add a new account. Notice I got the last account's id, added 1 to it and used it as the new id of the new account; you might not want to do this in a real application. A better alternative is to create the id as a Guid type and create a new Guid for every account.

Next, we need the component to edit an account. Add a new Razor component called EditAccount.razor. The code for this is:

Finally, we need a component to view the details of an account. Add a Razor component called SingleAccount.razor. The code is:

Navigation

Replace the code in the NavMenu.razor component in the Shared folder with:

Testing the application

At this point, we are done with coding. Save all the changes made to the files, build project, make sure there is no error in any file.
Run the BlazorAppWithCockroachDB.Api project first and test one of the endpoints using your browser or Postman, then run the BlazorAppWithCockroachDB project. You should get the: