Using MySQL Database for Blazor Server Web App
Today we will see how we can connect a blazor server web app to a MySQL database, both running on a Ubuntu virtual machine.
About how to run a blazor server web app on Linux (Ubuntu) you can read my following post.
Install the MySQL Employees Sample Database
To show how we connect a blazor server web app to a MySQL database and how to bind data from, I will use the sample employees database from GitHub.
The Employees database is available from Employees DB on GitHub. You can download a prepackaged archive of the data, or access the information through Git.
So I will first download and unpack the Zip archive package by executing the following command.
$ sudo wget https://github.com/datacharmer/test_db/archive/refs/heads/master.zip
$ sudo unzip master.zip
The Employees database is compatible with several different storage engines, with the InnoDB engine enabled by default.
To determine the default storage engine on your MySQL server you can execute the following commands.
mysql> SHOW ENGINES;
or
mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES;
Edit the employees.sql file and adjust the comments to choose a different storage engine:
set storage_engine = InnoDB; -- set storage_engine = MyISAM; -- set storage_engine = Falcon; -- set storage_engine = PBXT; -- set storage_engine = Maria;
To import the data into your MySQL instance, load the data through the mysql command-line tool:
To test that the data you have loaded matches the expected results, run the test suite.
You can validate the Employee data using two methods, md5 and sha. Two SQL scripts are provided for this purpose, test_employees_sha.sql and test_employees_md5.sql.
Source: https://dev.mysql.com/doc/employee/en/employees-validation.html
To run the tests, use mysql:
$ time mysql -t < test_employees_sha.sql
The time command in Linux and Unix-like operating systems lets you determine how long a specific command will take to run. Usually, it’s used to measure the performance of scripts or commands.
$ time mysql -t < test_employees_md5.sql
Source: https://dev.mysql.com/doc/employee/en/employees-installation.html
Set up the connection from Blazor Server to MySQL
First I will install in the Blazor Server project Oracle’s MySql.Data NuGet package.
https://dev.mysql.com/doc/connector-net/en/connector-net-installation-binary-nuget.html
MySql.Data
This package contains the core functionality of Connector/NET, including using MySQL as a document store (with Connector/NET 8.0 only). It implements the required ADO.NET interfaces and integrates with ADO.NET-aware tools. In addition, the packages provides access to multiple versions of MySQL server and encapsulates database-specific protocols.
Next I will create a new C# class named Database.cs in the Data folder which will establish the connection with the database and doing all the handling like accessing and storing data.
In order to create the connection string in Blazor Server for the sample employees database, we will first need to create a dedicated user on the MySQL server which we will using to access the database.
mysql> CREATE USER ‘usrEmployees’@’localhost’ IDENTIFIED BY ‘password’;
mysql> GRANT ALL PRIVILEGES ON employees.* TO ‘usrEmployees’@’localhost’;
Next for developing purpose, my MySQL server is running as mentioned on a Ubuntu virtual machine which is external to my developer machine with Visual Studio, I will change the network binding (TCP/IP Socket) from my MySQL server in order to listen also on the public IP address for incoming connection requests. By default and for security concerns, the MySQL server just listens on the localhost socket as shown below.
Alternatively you can of course also install a developer MySQL database on your developing machine to avoid exposing your production MySQL server to the internet. In my case frankly, I don’t care about as this is just a lab machine and exposing is temporarily.
In order to change the socket our MySQL server is listening on, we need to edit the /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu.
Here we need to change the bind-address value from 127.0.0.1 into 0.0.0.0, which means the server from now on is listening on all assigned IP addresses.
In order to take effect we need to restart the MySQL service.
$ sudo systemctl restart mysql
A quick check shows me that the server from now on is listening on all IP addresses (TCP/IP socket including the corresponding TCP port) for incoming connection requests. I still be used to use the good old netstat command which is now actually deprecated and should be replaced with the ss (socket state) command.
Finally to access the database from external I also have to open the mysql server tcp port 3306 on Ubuntu’s built-in firewall by using the ufw command as follows.
$ sudo ufw allow 3306/tcp
More about Ubuntu’s built-in firewall you can read in my following post.
Actually Ubuntu’s built-in firewall named Ufw is just an utility which makes it easier to configure the complex IP packet filter rules for the Linux kernel firewall which is since Linux kernel 2.4.x and later Netfilter.
Another quick check if I can access the database from external by using the telnet command from my Windows machine, which will just perform a TCP 3-way handshake with the destination IP address and port, will fail with the message.
> telnet <FQDN or IP> <tcp port>
VHost is not allowed to connect to this MySQL server
So first I need to add a user which is allowed to connect from anywhere (the percentage wildcard) or to filter just the shown FQDN, for testing purpose I will allow from all hosts (%) by executing the following command on the mysql command line.
mysql> CREATE USER ‘usrEmployees’@’%’ IDENTIFIED BY ‘password’;
mysql> GRANT ALL PRIVILEGES ON employees.* TO ‘usrEmployees’@’%’;
After that I could connect from remote to the MySQL server by using telnet.
Telnet is actually also deprecated, but for this purpose still great. On Windows you can also use here the PowerShell cmdlet Test-NetConnection as shown below.
PS C:> Test-NetConnection -ComputerName <FQDN or IP> -Port 3306
So from now on I should be able to connect to the database directly from my developer machine when debugging the blazor server web app in Visual Studio.
Therefore I will change the production connection string to work from remote which is so far just to connect to the localhost, as the web app and database later is running on the same server.
using MySql.Data;
using MySql.Data.MySqlClient;
namespace BlazorAppMySQL.Data
{
public class Database
{
private readonly string MySQLConnectionString;
public Database()
{
//MySQLConnectionString = "Server=127.0.0.1; Database=employees; Uid=usrEmployees; Pwd=password;";
MySQLConnectionString = "Server=<public IP MySQL server>; Database=employees; Uid=usrEmployees; Pwd=password;";
}
}
}Request and Process Data from MySQL
Next I will add to the Database.cs class a public method which will request some data from the employee database and table. The method will fill a DataTable with the requested data and will then return this DataTable to the calling method.
In order to use the DataTable class I first need to import the namespace System.Data.
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
namespace BlazorAppMySQL.Data
{
public class Database
{
private readonly string MySQLConnectionString;
public Database()
{
//MySQLConnectionString = "Server=127.0.0.1; Database=employees; Uid=usrEmployees; Pwd=password;";
MySQLConnectionString = "Server=<public IP MySQL server>; Database=employees; Uid=usrEmployees; Pwd=password;";
}
public DataTable MySQLConnection_Datatable()
{
DataTable dtDaten = new DataTable();
// Best practice is to scope the MySqlConnection to a "using" block
using (MySqlConnection conn = new MySqlConnection(MySQLConnectionString))
{
// Connect to the database
conn.Open();
// The MySqlCommand class represents a SQL statement to execute against a MySQL database
// Read rows - Limit for testing purpose to 15 records
MySqlCommand selectCommand = new MySqlCommand("SELECT * FROM employees LIMIT 15", conn);
// execute the reader To query the database. Results are usually returned in a MySqlDataReader object, created by ExecuteReader.
using (MySqlDataReader rdr = selectCommand.ExecuteReader())
{
dtDaten.Load(rdr);
}
conn.Close();
}
return dtDaten;
}
}
}
Introduction to MySQL Connector/NET
https://dev.mysql.com/doc/connector-net/en/connector-net-introduction.htmlCreating a Connector/NET Connection String
https://dev.mysql.com/doc/connector-net/en/connector-net-connections-string.htmlThe MySqlCommand Object
https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-sql-command.html
In order to use the new Database.cs class in Blazor Server, we first need to register it in the program.cs file as shown below.
builder.Services.AddScoped<Database>();
More about the service lifetime (Singleton, Scoped or Transient) you will find in my following post in the introduction section.
https://blog.matrixpost.net/blazor-server-basics-part-6-query-the-on-premise-active-directory/
Finally I will call the public method to fill and return the DataTable including the employees records from the index.razor page as shown below.
We also need to import the MySQL namespaces and System.Data for the DataTable class as previously for the Database.cs class.
Further also to use the Database.cs class we need to import the Data namespace which will include all classes from the Data folder in our Blazor Server project. Finally we need to inject the Database.cs class into the index.razor page.
@page "/"
@using MySql.Data;
@using MySql.Data.MySqlClient;
@using System.Data;
@using Data;
@inject Database DBConnect
<div class="container-fluid">
<div class="d-flex flex-row">
<table class="table table-striped table-light">
<thead>
<tr>
<th scope="col">emp_no</th>
<th scope="col">birth_date</th>
<th scope="col">first_name</th>
<th scope="col">last_name</th>
<th scope="col">gender</th>
<th scope="col">hire_date</th>
</tr>
</thead>
<tbody>
@foreach (DataRow row in dtEmployees.Rows)
{
string emp_no = row["emp_no"].ToString();
string birth_date = row["birth_date"].ToString();
string first_name = row["first_name"].ToString();
string last_name = row["last_name"].ToString();
string gender = row["gender"].ToString();
string hire_date = row["hire_date"].ToString();
<tr>
<th scope="row">@emp_no</th>
<td>@birth_date</td>
<td>@first_name</td>
<td>@last_name</td>
<td>@gender</td>
<td>@hire_date</td>
</tr>
}
</tbody>
</table>
</div>
</div>
@code {
DataTable dtEmployees = new DataTable();
protected override async Task OnInitializedAsync()
{
await SomeStartupTask();
dtEmployees = DBConnect.MySQLConnection_Datatable();
}
Task SomeStartupTask()
{
//// Do some task based work
return Task.CompletedTask;
}
}In order to get the structure of the employees sample database, you can use the DESCRIBE <table> command. The structure is also shown in detail on the following page https://dev.mysql.com/doc/employee/en/sakila-structure.html
One final test debugging the blazor server web app, looks good.
Delete a Record from MySQL
Below I will list on the index.razor page all employee records from the employee table and further adding a Delete button to each row in order to delete the selected record from the database resp. employee table.
Here you can see the HTML and C# code within the razor page.
@page "/"
@using MySql.Data;
@using MySql.Data.MySqlClient;
@using System.Data;
@using Data;
@inject Database DBConnect
<div class="container-fluid">
<div class="d-flex flex-row Matrixpost_headline">
<div class="col-md-1">
<div class="p-2">
<p> </p>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<p>emp_no</p>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<p>birth_date</p>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<p>first_name</p>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<p>last_name</p>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<p>gender</p>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<p>hire_date</p>
</div>
</div>
</div>
@foreach (DataRow row in dtEmployees.Rows)
{
string emp_no = row["emp_no"].ToString();
string birth_date = row["birth_date"].ToString();
string first_name = row["first_name"].ToString();
string last_name = row["last_name"].ToString();
string gender = row["gender"].ToString();
string hire_date = row["hire_date"].ToString();
int RowIndex = dtEmployees.Rows.IndexOf(row);
<div class="d-flex flex-row">
<div class="col-md-1">
<div class="p-2">
<button type="button" @onclick="() => RemoveRecord(row)">Delete</button>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<input class="Matrixpost input" type="text" id="emp_no" name="EmpNo" value="@emp_no" disabled>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<input class="Matrixpost input" type="text" id="birth_date" name="BirthDate" value="@birth_date" disabled>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<input class="Matrixpost input" type="text" id="first_name" name="FirstName" value="@first_name" disabled>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<input class="Matrixpost input" type="text" id="last_name" name="LastName" value="@last_name" disabled>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<input class="Matrixpost input" type="text" id="gender" name="Gender" value="@gender" disabled>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<input class="Matrixpost input" type="text" id="hire_date" name="HireDate" value="@hire_date" disabled>
</div>
</div>
</div>
}
</div>
@code {
DataTable dtEmployees = new DataTable();
protected override async Task OnInitializedAsync()
{
await SomeStartupTask();
dtEmployees = DBConnect.GetEmployees();
}
Task SomeStartupTask()
{
//// Do some task based work
return Task.CompletedTask;
}
private void RemoveRecord(DataRow row)
{
// Determine the selected row ID.
string sEmployeeID = row["emp_no"].ToString();
// Remove the row from the Data Table
dtEmployees.Rows.Remove(row);
// Calling the RemoveRecord() method in the Datbase.cs class which will finally remove the selected record from the employee table
DBConnect.RemoveRecord(sEmployeeID);
}
}Here you can see the source code from the RemoveRecord() method in the Database.cs class.
public int RemoveRecord(string EmployeeID)
{
int records = 0;
using (MySqlConnection conn = new MySqlConnection(MySQLConnectionString))
{
// Connect to the database
conn.Open();
//The MySqlCommand class represents a SQL statement to execute against a MySQL database
MySqlCommand selectCommand = new MySqlCommand("DELETE FROM employees WHERE emp_no = '" + EmployeeID + "'", conn);
// Executes a Transact-SQL statement against the connection and returns the number of rows affected.
// to insert, update, and delete data.
records = selectCommand.ExecuteNonQuery();
conn.Close();
}
return records;
Insert/Update a Record to/from MySQL
Below I will show on the index.razor page all employee records from the employee table and further adding a Button to insert a new record to the database resp. employee table.
Here you can see the HTML and C# code within the razor page, the insert code here I simplified because I just wanted to show how to insert and update a new record from Blazor Server into a MySQL database.
More about Data Binding in Blazor Server you will find in my following post
@page "/"
@using MySql.Data;
@using MySql.Data.MySqlClient;
@using System.Data;
@using Data;
@inject Database DBConnect
<div class="container-fluid">
<div class="d-flex flex-row Matrixpost_headline">
<div class="col-md-3">
<div class="p-2">
<button type="button" @onclick="() => AddEmployee()">Add Employee</button>
</div>
</div>
</div>
<div class="d-flex flex-row Matrixpost_headline">
<div class="col-md-1">
<div class="p-2">
<p> </p>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<p>emp_no</p>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<p>birth_date</p>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<p>first_name</p>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<p>last_name</p>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<p>gender</p>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<p>hire_date</p>
</div>
</div>
</div>
@foreach (DataRow row in dtEmployees.Rows)
{
string emp_no = row["emp_no"].ToString();
string birth_date = row["birth_date"].ToString();
string first_name = row["first_name"].ToString();
string last_name = row["last_name"].ToString();
string gender = row["gender"].ToString();
string hire_date = row["hire_date"].ToString();
int RowIndex = dtEmployees.Rows.IndexOf(row);
<div class="d-flex flex-row">
<div class="col-md-1">
<div class="p-2">
<button type="button" @onclick="() => RemoveRecord(row)">Delete</button>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<input class="Matrixpost input" type="text" id="emp_no" name="EmpNo" value="@emp_no" disabled>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<input class="Matrixpost input" type="text" id="birth_date" name="BirthDate" value="@birth_date" disabled>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<input class="Matrixpost input" type="text" id="first_name" name="FirstName" value="@first_name" disabled>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<input class="Matrixpost input" type="text" id="last_name" name="LastName" value="@last_name" disabled>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<input class="Matrixpost input" type="text" id="gender" name="Gender" value="@gender" disabled>
</div>
</div>
<div class="col-md-2">
<div class="p-2">
<input class="Matrixpost input" type="text" id="hire_date" name="HireDate" value="@hire_date" disabled>
</div>
</div>
</div>
}
</div>
@code {
DataTable dtEmployees = new DataTable();
protected override async Task OnInitializedAsync()
{
await SomeStartupTask();
dtEmployees = DBConnect.GetEmployees();
}
Task SomeStartupTask()
{
//// Do some task based work
return Task.CompletedTask;
}
private void RemoveRecord(DataRow row)
{
string sEmployeeID = row["emp_no"].ToString();
dtEmployees.Rows.Remove(row);
DBConnect.RemoveRecord(sEmployeeID);
}
// Adding a Data Row and finally calling the AddEmployee() method in the Database.cs class
private void AddEmployee()
{
DataRow row;
row = dtEmployees.NewRow();
int emp_no = 123456789;
DateTime birth_date = DateTime.Now.Date;
string first_name = "New";
string last_name = "User";
string gender = "M";
DateTime hire_date = DateTime.Now.Date;
row["emp_no"] = emp_no;
row["birth_date"] = birth_date;
row["first_name"] = first_name;
row["last_name"] = last_name;
row["gender"] = gender;
row["hire_date"] = hire_date;
dtEmployees.Rows.InsertAt(row, 0);
DBConnect.AddEmployee(emp_no,birth_date,first_name,last_name,gender,hire_date);
}
}Here you can see the source code from the AddEmployee() method in the Database.cs class.
In order to update a record, you just need to adjust the TSQLString with the UPDATE statement. Executing the Transact-SQL statement, you can also by using the ExecuteNonQuery() method as shown below.
This method can be used for insert, update and delete statements.
public int AddEmployee(int EmployeeID,DateTime BirthDate, string First_Name, string Last_Name, string Gender, DateTime HireDate)
{
int records = 0;
// Convert DateTime to MySQL Date format
string mysql_birth_date = BirthDate.ToString("yyyy-MM-dd HH:mm:ss");
string mysql_hire_date = HireDate.ToString("yyyy-MM-dd HH:mm:ss");
string TSQLString = "INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (" + EmployeeID
+ ", '" + mysql_birth_date + "', '" + First_Name + "', '" + Last_Name + "', '" + Gender + "', '" + mysql_hire_date + "')";
using (MySqlConnection conn = new MySqlConnection(MySQLConnectionString))
{
// Connect to the database
conn.Open();
//The MySqlCommand class represents a SQL statement to execute against a MySQL database
MySqlCommand selectCommand = new MySqlCommand(TSQLString, conn);
// Executes a Transact-SQL statement against the connection and returns the number of rows affected.
// to insert, update, and delete data.
records = selectCommand.ExecuteNonQuery();
conn.Close();
}
return records;
}As already mentioned, about Data Binding and writing data back to a database, you will find more in my following post which will cover accessing a MS SQL Server.
Links
Installing Connector/NET Using NuGet
https://dev.mysql.com/doc/connector-net/en/connector-net-installation-binary-nuget.htmlEmployees Sample Database
https://dev.mysql.com/doc/employee/en/DataTable Class
https://learn.microsoft.com/en-us/dotnet/api/system.data.datatable?view=net-7.0Introduction to MySQL Connector/NET
https://dev.mysql.com/doc/connector-net/en/connector-net-introduction.htmlCreating a Connector/NET Connection String
https://dev.mysql.com/doc/connector-net/en/connector-net-connections-string.htmlThe MySqlCommand Object
https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-sql-command.htmlClass MySqlDataReader
https://dev.mysql.com/doc/dev/connector-net/latest/api/data_api/MySql.Data.MySqlClient.MySqlDataReader.html
