Blazor - Create SPA With Azure Database For MariaDB Server

Overview

In this article, we will create a MariaDB database service in Azure and connect MariaDB to a single-page Page Blazor application.

Azure Database for MariaDB is a relational database service in the Microsoft cloud. This service is currently in public preview. Azure Database for MariaDB is based on the MariaDB community edition database engine.

MariaDB Server is one of the most popular database servers in the world. MariaDB turns data into structured information in a wide array of applications, ranging from banking to websites. It is an enhanced, drop-in replacement for MySQL. MariaDB is used because it is fast, scalable, and robust, with a rich ecosystem of storage engines, plugins, and many other tools that make it very versatile for a wide variety of use cases. It’s made by the original developers of MySQL and guaranteed to stay open source.

Please refer to this URL to get more details of MariaDB.

I have already written many articles on Blazor in C# Corner. If you are new to the Blazor framework, please refer to the below articles to get a basic idea of 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

Create Azure database for MariaDB server

Create new resource -> Databases -> Azure Database for MariaDB (Currently it is in preview)

We can give a name to the MariaDB server. Also, choose the resource group name. If you do not have any resource group, you can create a new one. You must give the server admin login name and password.

You can choose the pricing tier. I chose the Basic tier.

After selecting the pricing tier, you can click the “Create” button to create the DB server. It will take some moments to create the server.

We must add the IP address in Connection security to access MariaDB from outside Azure. You can choose “the Connection security” tab and add the starting IP and Ending IP address. Here I added minimum IP address and maximum IP address. Because my system IP address often changes. But in production, we can give the exact IP address.

Create a database and table in MariaDB

I am using MySQL Workbench to connect MariaDB for creating a Database and Table. Most of the MySQL admin tools also support MariaDB. You can download a free MySQL community edition from this URL.

Please give the connection name, hostname, and username in the workbench. You can get the hostname and username from the Azure portal. You can also store the password as Vault. It is fully secured.

We can create a new database and table using Query eEditor Here I am creating an Employee table with five columns.

CREATE DATABASE sarathmariadb;
USE sarathmariadb;
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee (
    Id VARCHAR(50) PRIMARY KEY,
    Name VARCHAR(50),
    Department VARCHAR(50),
    Designation VARCHAR(50),
    Gender VARCHAR(10)
);

We have successfully created a MariaDB server and created one database and table.

Create a new Blazor application

In this article, we will create an Employee data entry single-page application. I am using the free Visual Studio 2017 Community edition for creating a Blazor application.

Choose .NET Core -> ASP.NET Core Web Application template

Currently, there are three types of Blazor templates available. We chose the Blazor (ASP.NET Core hosted) template.

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

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

We can create a Models folder in the “Shared” project and create an Employee class inside this Models folder.

Employee.cs

namespace BlazorMariaDB.Shared.Models
{
    public class Employee
    {
        public string Id
        {
            get;
            set;
        }       
        public string Name
        {
            get;
            set;
        }
        public string Department
        {
            get;
            set;
        }        
        public string Designation
        {
            get;
            set;
        }       
        public string Gender
        {
            get;
            set;
        }
    }
}

As I mentioned earlier MariaDB is developed from (forked) MySQL. We can use MySQL providers to connect MariaDB. NET.

We can install “MySql.Data” NuGet Package in the “Server” project. This package is developed by Oracle corporation

We create a “DataAccess” folder in the “Server” project create Ma ariaDBContext class inside the DataAccess folder and add all the CRUD operations logic inside this class.

We will call the methods in this class from our Controller class later.

MariaDBContext.cs

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

namespace BlazorMariaDB.Server.DataAccess
{
    public class MariaDBContext
    {
        public string ConnectionString { get; set; }

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

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

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

            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"SELECT Id,Name,Department,Designation,Gender FROM Employee;";
                MySqlCommand cmd = new MySqlCommand(commandText, conn);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        list.Add(new Employee()
                        {
                            Id = await reader.GetFieldValueAsync<string>(0),
                            Name = await reader.GetFieldValueAsync<string>(1),
                            Department = await reader.GetFieldValueAsync<string>(2),
                            Designation = await reader.GetFieldValueAsync<string>(3),
                            Gender = await reader.GetFieldValueAsync<string>(4),
                        });
                    }
                }

            }
            return list;
        }

        public async Task InsertAsync(Employee employee)
        {
            employee.Id = Guid.NewGuid().ToString();

            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"INSERT INTO Employee (Id,Name,Department,Designation,Gender) VALUES (@Id, @Name, @Department, @Designation, @Gender);";
                MySqlCommand cmd = new MySqlCommand(commandText, conn);

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

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

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

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

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

                await cmd.ExecuteNonQueryAsync();

            }
        }

        public async Task UpdateAsync(Employee employee)
        {
            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"UPDATE Employee SET Name=@Name, Department=@Department, Designation=@Designation, Gender=@Gender  Where Id=@Id;";
                MySqlCommand cmd = new MySqlCommand(commandText, conn);

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

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

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

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

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

                await cmd.ExecuteNonQueryAsync();
            }

        }

        public async Task DeleteAsync(string id)
        {
            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"DELETE FROM Employee 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<Employee> FindOneAsync(string id)
        {
            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                var commandText = @"SELECT Name,Department,Designation,Gender FROM Employee Where Id=@Id;";
                MySqlCommand cmd = new MySqlCommand(commandText, conn);
                cmd.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "@Id",
                    DbType = DbType.String,
                    Value = id,
                });

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        return new Employee()
                        {
                            Id = id,
                            Name = await reader.GetFieldValueAsync<string>(0),
                            Department = await reader.GetFieldValueAsync<string>(1),
                            Designation = await reader.GetFieldValueAsync<string>(2),
                            Gender = await reader.GetFieldValueAsync<string>(3),
                        };
                    }
                    else
                    {
                        return null;
                    }

                }
            }
        }
    }
}

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

We can inject the MariaDBContext service in the Startup class.

Startup.cs

using BlazorMariaDB.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 BlazorMariaDB.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=sarathlal.mariadb.database.azure.com; Port=3306; Database=sarathmariadb; Uid =sarathlal@sarathlal; Pwd=<user pasword>; SslMode=Preferred;";
            services.Add(new ServiceDescriptor(typeof(MariaDBContext), new MariaDBContext(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 connected the connection details inside the Startup class. You can even keep these connection details in a separate configuration file.

We can create an employee controller now.

We can add the below code in this controller class.

EmployeesController.cs

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

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

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

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

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

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

We have added all the CRUD operations to this controller. We are calling methods from the MariaDBContext class in this controller.

We can go to the “Client” project and modify “NavMenu.cshtml” Razor view.

NavMenu.cshtml

<div class="top-row pl-4 navbar navbar-dark">
    <a class="navbar-brand" href="">Blazor MariaDB 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="/listemployees">
                <span class="oi oi-plus" aria-hidden="true"></span> Employee Details
            </NavLink>
        </li>
    </ul>
</div>

@functions {
    bool collapseNavMenu = true;

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

We can create a new Razor view for listing employee details.

You can add the below code in this Razor view

ListEmployees.cshtml

@using BlazorMariaDB.Shared.Models

@page "/listemployees"
@inject HttpClient Http

<h1>Employee Details</h1>
<p>
    <a href="/addemployee">Create New Employee</a>
</p>
@if (employees == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <table class='table'>
        <thead>
            <tr>
                <th>Name</th>
                <th>Department</th>
                <th>Designation</th>
                <th>Gender</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var employee in employees)
            {
                <tr>
                    <td>@employee.Name</td>
                    <td>@employee.Department</td>
                    <td>@employee.Designation</td>
                    <td>@employee.Gender</td>
                    <td>
                        <a href='/editemployee/@employee.Id'>Edit</a>
                        <a href='/deleteemployee/@employee.Id'>Delete</a>
                    </td>
                </tr>
            }
        </tbody>
    </table>
}

@functions {
    Employee[] employees;

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

Add three more Razor view files and add the below codes

AddEmployee.cshtml

@using BlazorMariaDB.Shared.Models

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

<h2>Create Employee</h2>
<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="@employee.Name" />
            </div>
            <div class="form-group">
                <label for="Department" class="control-label">Department</label>
                <input for="Department" class="form-control" bind="@employee.Department" />
            </div>
            <div class="form-group">
                <label for="Designation" class="control-label">Designation</label>
                <input for="Designation" class="form-control" bind="@employee.Designation" />
            </div>
            <div class="form-group">
                <label for="Gender" class="control-label">Gender</label>
                <select for="Gender" class="form-control" bind="@employee.Gender">
                    <option value="">-- Select Gender --</option>
                    <option value="Male">Male</option>
                    <option value="Female">Female</option>
                </select>
            </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 CreateEmployee())" value="Save" />
                <input type="button" class="btn" onclick="@Cancel" value="Cancel" />
            </div>
        </div>
    </div>
</form>

@functions {
    Employee employee = new Employee();

    protected async Task CreateEmployee()
    {
        await Http.SendJsonAsync(HttpMethod.Post, "/api/employees", employee);
        UriHelper.NavigateTo("/listemployees");
    }

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

EditEmployee.cshtml

@using BlazorMariaDB.Shared.Models

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

<h2>Edit</h2>
<h4>Employee</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="@employee.Name" />
            </div>
            <div class="form-group">
                <label for="Department" class="control-label">Department</label>
                <input for="Department" class="form-control" bind="@employee.Department" />
            </div>
            <div class="form-group">
                <label for="Designation" class="control-label">Designation</label>
                <input for="Designation" class="form-control" bind="@employee.Designation" />
            </div>
            <div class="form-group">
                <label for="Gender" class="control-label">Gender</label>
                <select for="Gender" class="form-control" bind="@employee.Gender">
                    <option value="">-- Select Gender --</option>
                    <option value="Male">Male</option>
                    <option value="Female">Female</option>
                </select>
            </div>
        </div>
    </div>
    <div class="row">
        <div class="form-group">
            <input type="button" class="btn btn-default" onclick="@(async () => await UpdateEmployee())" value="Save" />
            <input type="button" class="btn" onclick="@Cancel" value="Cancel" />
        </div>
    </div>
</form>

@functions {
    [Parameter]
    string id { get; set; }

    Employee employee = new Employee();

    protected override async Task OnInitAsync()
    {
        employee = await Http.GetJsonAsync<Employee>("/api/employees/" + id);
    }

    protected async Task UpdateEmployee()
    {
        await Http.SendJsonAsync(HttpMethod.Put, "api/employees", employee);
        UriHelper.NavigateTo("/listemployees");
    }

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

DeleteEmployee.cshtml

@using BlazorMariaDB.Shared.Models

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

<h2>Delete</h2>
<p>Are you sure you want to delete this Employee with Id: <b>@id</b></p>
<br />

<div class="col-md-4">
    <table class="table">
        <tr>
            <td>Name</td>
            <td>@employee.Name</td>
        </tr>
        <tr>
            <td>Department</td>
            <td>@employee.Department</td>
        </tr>
        <tr>
            <td>Designation</td>
            <td>@employee.Designation</td>
        </tr>
        <tr>
            <td>Gender</td>
            <td>@employee.Gender</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; }

    Employee employee = new Employee();

    protected override async Task OnInitAsync()
    {
        employee = await Http.GetJsonAsync<Employee>("/api/employees/" + id);
    }

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

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

We have completed all the coding parts. Now we can run the application.

We can click the Employee Details link in the menu bar. It will open the Employee List page. There is a Create Employee Link. You can click that link. It will open the Add Employee page and here you can enter the employee details and the “Save” button to save the information.

We can add one more employee in the same way. Now we have two employee records

We can modify the employee data by clicking the “Edit” link

I have modified the employee's name. Now we can delete one employee record. You can click the “Delete” link

It will display the selected employee details for confirmation and if you click “the Delete” button employee data will be deleted from the table.

We have seen all the CRUD operations with employee data.

Conclusion

In this article, we have created an Azure Database for the MariaDB server and we have also created a firewall rule to access this MariaDB server from outside Azure. Later we created a new database and table in MariaDB using MySQL workbench (we used the wWorkbenchfree community edition). We have created a Blazor application using the Blazor (ASP.NET Core hosted) template. We used MySQL.Data NuGet package to provide data access from Blazor to the MariaDB server. We have successfully created two employee records and have seen how to modify and delete this employee data.

We can see more Blazor features in upcoming articles.