Create A Blazor App With Google ☁️Cloud SQL Using MySQL Engine

Introduction

Cloud SQL is a fully managed database service that makes it simple to line up, maintain, manage, and administer your relational PostgreSQL and MySQL databases within the cloud. Cloud SQL offers high performance, scalability, and convenience. Hosted on Google Cloud platform, Cloud SQL provides a data infrastructure for applications running at any place.

We will see all the steps to create a Google Cloud SQL instance. We will create a project in the Google Cloud console and then, we will create a MySQL instance and will connect this instance from MySQL Workbench 8.0 Community Edition. We will create a database and table. Later, we will connect this table and database from our Blazor application.

 

About Blazor Framework

Blazor is a .NET web framework from Microsoft using C#/Razor and HTML that runs in the browser with Web Assembly. Blazor provides all the benefits of a client-side web UI framework using .NET on the client and optionally, on the server.

I have already written many articles on Blazor on C# Corner. If you are new to it, please refer to the below articles to get started with Blazor.

• Single Page Application With Blazor And CosmosDB
• Blazor - CRUD Using PostgreSQL And Entity Framework Core
• Blazor - Connect With Amazon DynamoDB Using AWS SDK
• Blazor - Work With Cassandra API In Cosmos DB
• Localization In Blazor App Using Microsoft.JSInterop
• Blazor - Create SPA With Azure Database For MariaDB Server
• Blazor - Connect With Oracle Database In Amazon RDS
• Get C# Corner RSS Feeds In Blazor Project
• C# Corner RSS Feeds In Blazor With Pagination
• Deploy Blazor Application On AWS Cloud Using Elastic Beanstalk
• Azure Redis Cache With Azure SQL In Blazor Project
• Single Page Application In Blazor With Azure Table Storage
• Create A Simple Chart By Date For The Latest C# Corner Article Count
• Remotely Debug Blazor App On Azure From Visual Studio

Create Google Cloud SQL instance with MySQL engine

You must create a Google Cloud account before starting it. Currently, Google provides a one-year free membership with $300 credits.

Log into Google Cloud console with your Google credentials.

Please select a project if you have any. Otherwise, you can click the “New Project” button to create a new project.

 

Choose a unique name for your project and click the “Create” button to start project creation.

 

 

 

Your project will be ready in a few minutes. You can choose the project and it will list the project details on the dashboard.

 

 

 

We can create the Cloud SQL instance now. Please choose the SQL tab from the left side menu.

 

 

 

Click the “Create Instance” button to proceed.

 

 

 

Currently, Google Cloud SQL supports MySQL and PostgreSQL engines. Here, we have selected the MySQL engine.

 

 

 

We can give a valid instance name and give a password to the “root” user. Please note this is a default user. We can later change the password if needed. We can also create multiple users on a later stage. Here, I have chosen “asia-south1” region. You can choose your convenient region.

 

 

 

It will take some time to create the instance. Before connecting this instance with MySQL workbench, you must add your local IP address to the Cloud SQL authorized networks. You can open the "Connections" tab to add your local IP address.

 

 

Add your local IP address and "Save".

 

 

Now, we can connect the instance from MySQL client. Here, we are using MySQL Workbench 8.0 Community Edition. It is a very good and free SQL Editor. We can give the instance details and test the connection.

 

 

 

Our connection is successful now.

 

 

 

Open the SQL editor and create a database and table in the Cloud SQL instance.

 

 

CREATE DATABASE sarathcloudsql;
USE sarathcloudsql;
DROP TABLE IF EXISTS Book;
CREATE TABLE Book
(Id VARCHAR(50) PRIMARY KEY,
Name VARCHAR(50),
ISBN VARCHAR(50),
Author VARCHAR(50),
Price DECIMAL(18,8));

Create a Blazor project in Visual Studio 2017

In this article, we will create a Book Data Entry single page application. I am using free Visual Studio 2017 Community edition to create the Blazor application.

Choose .NET Core -> ASP.NET Core Web Application template. Currently, there are three types of Blazor templates available. We choose Blazor (ASP.NET Core hosted) template.

 

 

 

Our solution will be ready in a few minutes. Please note that there are three projects created in the solution - “Client”, “Server” and “Shared”.

 

 

By default, Blazor created some files in these three projects. We can remove all the unwanted files like “Counter.cshtml”, “FetchData.cshtml”, “SurveyPrompt.cshtml” from the Client project and “SampleDataController.cs” file from Server project and delete “WeatherForecast.cs” file from the Shared project too.

Now, let us create a “Models” folder in “Shared” project and create “Book” class inside this.

Book.cs

namespace BlazorCloudSQL.Shared.Models
{
    public class Book
    {
        public string Id
        {
            get;
            set;
        }
        public string Name
        {
            get;
            set;
        }
        public string ISBN
        {
            get;
            set;
        }
        public string Author
        {
            get;
            set;
        }
        public decimal Price
        {
            get;
            set;
        }
    }
}

Install “MySql.Data” NuGet Package in the “Server” project. This package is developed by Oracle Corporation.

 

 

Create a “DataAccess” folder in the “Server” project and create CloudSQLContext class inside the DataAccess folder. Add all the CRUD operation logic inside this class. We will call the methods in this class from our Controller class later.

 

CloudSQLContext.cs

using BlazorCloudSQL.Shared.Models;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BlazorCloudSQL.Server.DataAccess
{
    public class CloudSQLContext
    {
        public string ConnectionString { get; set; }

        public CloudSQLContext(string connectionString)
        {
            ConnectionString = connectionString;
        }

        private MySqlConnection GetConnection()
        {
            return new MySqlConnection(ConnectionString);
        }

        public async Task<List<Book>> GetAllAsync()
        {
            List<Book> list = new List<Book>();

            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"SELECT Id,Name,ISBN,Author,Price FROM Book;";
                MySqlCommand cmd = new MySqlCommand(commandText, conn);

                using (var reader = cmd.ExecuteReader())
                {
                    while (await reader.ReadAsync())
                    {
                        list.Add(new Book()
                        {
                            Id = await reader.GetFieldValueAsync<string>(0),
                            Name = await reader.GetFieldValueAsync<string>(1),
                            ISBN = await reader.GetFieldValueAsync<string>(2),
                            Author = await reader.GetFieldValueAsync<string>(3),
                            Price = await reader.GetFieldValueAsync<decimal>(4),
                        });
                    }
                }

            }
            return list;
        }

        public async Task InsertAsync(Book book)
        {
            book.Id = Guid.NewGuid().ToString();

            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"INSERT INTO Book (Id,Name,ISBN,Author,Price) VALUES (@Id, @Name, @ISBN, @Author, @Price);";

                MySqlCommand cmd = new MySqlCommand(commandText, conn);

                cmd.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "@Id",
                    DbType = DbType.String,
                    Value = book.Id,
                });

                cmd.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "@Name",
                    DbType = DbType.String,
                    Value = book.Name,
                });

                cmd.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "@ISBN",
                    DbType = DbType.String,
                    Value = book.ISBN,
                });

                cmd.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "@Author",
                    DbType = DbType.String,
                    Value = book.Author,
                });

                cmd.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "@Price",
                    DbType = DbType.Decimal,
                    Value = book.Price,
                });

                await cmd.ExecuteNonQueryAsync();

            }
        }

        public async Task UpdateAsync(Book book)
        {
            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"UPDATE Book SET Name=@Name, ISBN=@ISBN, Author=@Author, Price=@Price  Where Id=@Id;";

                MySqlCommand cmd = new MySqlCommand(commandText, conn);

                cmd.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "@Id",
                    DbType = DbType.String,
                    Value = book.Id,
                });

                cmd.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "@Name",
                    DbType = DbType.String,
                    Value = book.Name,
                });

                cmd.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "@ISBN",
                    DbType = DbType.String,
                    Value = book.ISBN,
                });

                cmd.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "@Author",
                    DbType = DbType.String,
                    Value = book.Author,
                });

                cmd.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "@Price",
                    DbType = DbType.Decimal,
                    Value = book.Price,
                });

                await cmd.ExecuteNonQueryAsync();
            }

        }

        public async Task DeleteAsync(string id)
        {
            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"DELETE FROM Book Where Id=@Id;";

                MySqlCommand cmd = new MySqlCommand(commandText, conn);

                cmd.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "@Id",
                    DbType = DbType.String,
                    Value = id,
                });

                await cmd.ExecuteNonQueryAsync();
            }

        }

        public async Task<Book> FindOneAsync(string id)
        {
            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"SELECT Name,ISBN,Author,Price FROM Book Where Id=@Id;";
                MySqlCommand cmd = new MySqlCommand(commandText, conn);
                cmd.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "@Id",
                    DbType = DbType.String,
                    Value = id,
                });

                using (var reader = cmd.ExecuteReader())
                {
                    if (await reader.ReadAsync())
                    {
                        return new Book()
                        {
                            Id = id,
                            Name = await reader.GetFieldValueAsync<string>(0),
                            ISBN = await reader.GetFieldValueAsync<string>(1),
                            Author = await reader.GetFieldValueAsync<string>(2),
                            Price = await reader.GetFieldValueAsync<decimal>(3),
                        };
                    }
                    else
                    {
                        return null;
                    }

                }
            }
        }
    }
}

We have created each method for CRUD actions inside this class. Please note we are using “MySqlConnection” class from “MySql.Data” library to connect MySQL database.

Now, let us inject the CloudSQLContextservice in the Startup class.

 

Startup.cs

using BlazorCloudSQL.Server.DataAccess;
using Microsoft.AspNetCore.Blazor.Server;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.ResponseCompression;
using Microsoft.Extensions.DependencyInjection;
using System.Linq;
using System.Net.Mime;

namespace BlazorCloudSQL.Server
{
    public class Startup
    {
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();

            services.AddResponseCompression(options =>
            {
                options.MimeTypes = ResponseCompressionDefaults.MimeTypes.Concat(new[]
                {
                    MediaTypeNames.Application.Octet,
                    WasmMediaTypeNames.Application.Wasm,
                });
            });
            var connStr = "Server=<Your Server IP>; Port=3306; Database=sarathcloudsql; Uid=root; Pwd=<Your Password>; SslMode=Preferred;";
            services.Add(new ServiceDescriptor(typeof(CloudSQLContext), new CloudSQLContext(connStr)));
        }

        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            app.UseResponseCompression();

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

            app.UseMvc(routes =>
            {
                routes.MapRoute(name: "default", template: "{controller}/{action}/{id?}");
            });

            app.UseBlazor<Client.Program>();
        }
    }
}

For simplicity, I have hard-corded the MySQL connection details inside Startup class. You can even keep these connection details in a separate configuration file if needed.

We can create Books Controller now. Please add the below code to this Controller class.

BooksController.cs

using BlazorCloudSQL.Server.DataAccess;
using BlazorCloudSQL.Shared.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace BlazorCloudSQL.Server.Controllers
{
    [Route("api/[controller]")]
    public class BooksController : Controller
    {
        [HttpGet]
        public async Task<IEnumerable<Book>> GetAsync()
        {
            CloudSQLContext context = HttpContext.RequestServices.GetService(typeof(CloudSQLContext)) as CloudSQLContext;
            return await context.GetAllAsync();
        }

        [HttpGet("{id}")]
        public async Task<Book> Get(string id)
        {
            CloudSQLContext context = HttpContext.RequestServices.GetService(typeof(CloudSQLContext)) as CloudSQLContext;
            return await context.FindOneAsync(id);
        }

        [HttpPost]
        public async Task Post([FromBody] Book book)
        {
            if (ModelState.IsValid)
            {
                CloudSQLContext context = HttpContext.RequestServices.GetService(typeof(CloudSQLContext)) as CloudSQLContext;
                await context.InsertAsync(book);
            }
        }

        [HttpPut]
        public async Task Put([FromBody] Book book)
        {
            if (ModelState.IsValid)
            {
                CloudSQLContext context = HttpContext.RequestServices.GetService(typeof(CloudSQLContext)) as CloudSQLContext;
                await context.UpdateAsync(book);
            }
        }

        [HttpDelete("{id}")]
        public async Task Delete(string id)
        {
            CloudSQLContext context = HttpContext.RequestServices.GetService(typeof(CloudSQLContext)) as CloudSQLContext;
            await context.DeleteAsync(id);
        }
    }
}

We have added all the CRUD operations in this Controller. We will call methods from CloudSQLContext class here.

Go to the “Client” project and modify “NavMenu.cshtml” Razor View inside the “Shared” folder.

NavMenu.cshtml

<div class="top-row pl-4 navbar navbar-dark">
    <a class="navbar-brand" href="">Blazor Cloud SQL App</a>
    <button class="navbar-toggler" onclick=@ToggleNavMenu>
        <span class="navbar-toggler-icon"></span>
    </button>
</div>

<div class=@(collapseNavMenu ? "collapse" : null) 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="/listbooks">
                <span class="oi oi-plus" aria-hidden="true"></span> Book Details
            </NavLink>
        </li>
    </ul>
</div>

@functions {
bool collapseNavMenu = true;

void ToggleNavMenu()
{
    collapseNavMenu = !collapseNavMenu;
}
}

We can create a new Razor View inside “Pages” folder for listing all the book details. Add the below code to the Razor View.

ListBooks.cshtml

@using BlazorCloudSQL.Shared.Models

@page "/listbooks"
@inject HttpClient Http

<h4>Book Details</h4>
<p>
    <a href="/addbook">Create New Book</a>
</p>
@if (books == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <table class='table'>
        <thead>
            <tr>
                <th>Name</th>
                <th>ISBN</th>
                <th>Author</th>
                <th>Price</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var book in books)
            {
                <tr>
                    <td>@book.Name</td>
                    <td>@book.ISBN</td>
                    <td>@book.Author</td>
                    <td>@book.Price</td>
                    <td>
                        <a href='/editbook/@book.Id'>Edit</a>
                        <a href='/deletebook/@book.Id'>Delete</a>
                    </td>
                </tr>
            }
        </tbody>
    </table>
}
@functions {
Book[] books;

protected override async Task OnInitAsync()
{
    books = await Http.GetJsonAsync<Book[]>("/api/books");
}
}

Let's add three more Razor View files and add the below codes.

AddBook.cshtml

@using BlazorCloudSQL.Shared.Models

@page "/addbook"
@inject HttpClient Http
@inject Microsoft.AspNetCore.Blazor.Services.IUriHelper UriHelper

<h4>Create Book</h4>
<hr />
<form>
    <div class="row">
        <div class="col-md-8">
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" bind="@book.Name" />
            </div>
            <div class="form-group">
                <label for="ISBN" class="control-label">ISBN</label>
                <input for="ISBN" class="form-control" bind="@book.ISBN" />
            </div>
            <div class="form-group">
                <label for="Author" class="control-label">Author</label>
                <input for="Author" class="form-control" bind="@book.Author" />
            </div>
            <div class="form-group">
                <label for="Price" class="control-label">Price</label>
                <input for="Price" class="form-control" bind="@book.Price" />
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col-md-4">
            <div class="form-group">
                <input type="button" class="btn btn-default" onclick="@(async () => await CreateBook())" value="Save" />
                <input type="button" class="btn" onclick="@Cancel" value="Cancel" />
            </div>
        </div>
    </div>
</form>

@functions {

Book book = new Book();

protected async Task CreateBook()
{
    await Http.SendJsonAsync(HttpMethod.Post, "/api/books", book);
    UriHelper.NavigateTo("/listbooks");
}

void Cancel()
{
    UriHelper.NavigateTo("/listbooks");
}
}

EditBook.cshtml

@using BlazorCloudSQL.Shared.Models

@page "/editbook/{id}"
@inject HttpClient Http
@inject Microsoft.AspNetCore.Blazor.Services.IUriHelper UriHelper

<h4>Edit Book</h4>
<hr />

<form>
    <div class="row">
        <div class="col-md-8">
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" bind="@book.Name" />
            </div>
            <div class="form-group">
                <label for="ISBN" class="control-label">ISBN</label>
                <input for="ISBN" class="form-control" bind="@book.ISBN" />
            </div>
            <div class="form-group">
                <label for="Author" class="control-label">Author</label>
                <input for="Author" class="form-control" bind="@book.Author" />
            </div>
            <div class="form-group">
                <label for="Price" class="control-label">Price</label>
                <input for="Price" class="form-control" bind="@book.Price" />
            </div>
        </div>
    </div>
    <div class="row">
        <div class="form-group">
            <input type="button" class="btn btn-default" onclick="@(async () => await UpdateBook())" value="Save" />
            <input type="button" class="btn" onclick="@Cancel" value="Cancel" />
        </div>
    </div>
</form>

@functions {

[Parameter]
string id { get; set; }

Book book = new Book();

protected override async Task OnInitAsync()
{
    book = await Http.GetJsonAsync<Book>("/api/books/" + id);
}

protected async Task UpdateBook()
{
    await Http.SendJsonAsync(HttpMethod.Put, "api/books", book);
    UriHelper.NavigateTo("/listbooks");

}

void Cancel()
{
    UriHelper.NavigateTo("/listbooks");
}
}

DeleteBook.cshtml

@using BlazorCloudSQL.Shared.Models

@page "/deletebook/{id}"
@inject HttpClient Http
@inject Microsoft.AspNetCore.Blazor.Services.IUriHelper UriHelper

<h4>Delete Book</h4>
<p>Are you sure you want to delete this Book with Id :<b> @id</b></p>
<hr />
<div class="col-md-4">
    <table class="table">
        <tr>
            <td>Name</td>
            <td>@book.Name</td>
        </tr>
        <tr>
            <td>ISBN</td>
            <td>@book.ISBN</td>
        </tr>
        <tr>
            <td>Author</td>
            <td>@book.Author</td>
        </tr>
        <tr>
            <td>Price</td>
            <td>@book.Price</td>
        </tr>
    </table>
    <div class="form-group">
        <input type="button" value="Delete" onclick="@(async () => await Delete())" class="btn btn-default" />
        <input type="button" value="Cancel" onclick="@Cancel" class="btn" />
    </div>
</div>

@functions {

[Parameter]
string id { get; set; }

Book book = new Book();

protected override async Task OnInitAsync()
{
    book = await Http.GetJsonAsync<Book>("/api/books/" + id);
}

protected async Task Delete()
{
    await Http.DeleteAsync("api/books/" + id);
    UriHelper.NavigateTo("/listbooks");
}

void Cancel()
{
    UriHelper.NavigateTo("/listbooks");
}
}

Let us modify the Index.cshtml as well.

Index.cshtml

@page "/"

<h4>Create a Single Page Blazor App with Google Cloud SQL </h4>

<p>
    We will see how to create a Google Cloud SQL instance using the MySQL engine and will create a single page application in Blazor.
</p>

We have completed all the coding part. Now, we can run the application in a browser.

 

Click the “Book Details” link in the left side navigation bar and add a new book.

 

 

I have added a new book. We can easily edit the book by clicking the “Edit” link in the grid.

 

 

I have just modified the author name. We can add one more book and display all the books in the grid.

 

 

Let us delete a book by clicking the Delete link in the grid. It will ask for a confirmation before deleting from the database.

 

 

We have seen all the CRUD actions in our newly created Books app.

Conclusion

In this post, we have created a new project in Google Cloud console and a Google Cloud SQL instance with MySQL engine. We, then, connected the MySQL instance with MySQL Workbench Community Edition and created a new database. We created a table also in the same database. After that, we created a Blazor application in Visual Studio 2017 Community Edition and created a Book Data Entry app. 

We shall see more exciting features of Blazor framework in upcoming posts.