MySQL CRUD Operations in .NET MAUI
.NET MAUI (Multi-platform App UI) is a cross-platform framework for building desktop and mobile applications with C# and XAML. With its help, developers can create applications running on Windows, Mac, iOS, and Andriod Operating systems, all from one Codebase.
MySQL is one of the most popular databases for building web applications. It is also open-source, allowing everyone to establish the database instance and connect to it. Database connectivity is essential because the application requires a reliable data source. Therefore, developers must ensure a quick and smooth connection to the database, as well as efficient data retrieval and management within the application.
In this tutorial, we will explore the Code First approach used to integrate MySQL with a MAUI application, allowing us to design and manage the database directly from the C# code.
Why dotConnect for MySQL?
dotConnect for MySQL is the data provider that suits ideally for all MySQL and MariaDB-related operations. Its numerous features enhance functionality, performance, and ease of development for .NET developers working with MySQL databases.
Download and activate dotConnect for MySQL
Visit the dotConnect for MySQL download page where you can download the solution. You can choose either the free trial or the full version of dotConnect for MySQL. The free trial version gives you 30 days to explore the full functionality, but it cannot be used for deploying commercial projects.
To use the Trial version, simply download and install dotConnect for MySQL on your machine and choose to use the Trial during the installation process. You can start exploring the product immediately.
If you already have the full version, you can download it at any time, as your license is saved in your account. Go to the Licenses page at the Customer Portal at the Devart website. You'll find the list of all the Devart product licenses you purchased, including the dotConnect for MySQL purchase. Click Details next to it and find all your license details, including the Activation Key.
Click Download Product Versions to access the list of available options and select the most recent version to download it and install on your computer.
Follow the on-screen instructions to install dotConnect for MySQL on your machine and activate its features.
The next step is creating a .NET MAUI project.
Create a .NET MAUI project
Launch your Visual Studio, and choose Create a new project, then search for .NET MAUI App and click it.
Give your project and name (in our tutorial, it is MySQLMAUI), then select .NET 8 as the target framework and click Create.
Install Packages
We install dotConnect for MySQL in our project via the NuGet Package manager in Visual Studio. In the taskbar, click Tools > NuGet package manager > Manage NuGet packages for console.
The NuGet Package Manager page opens. Click Browse and search for Devart.Data.MySQL. Then select your project, and click Install.
When you run the application by clicking F5, the scaffolded application will appear.
Set up the project
Create a new page: right-click the MySQLMAUI solution, select Add New Item, then choose the template .NET MAUI ContentPage(XAML). Name this page MySQLDemo.xaml, and click Add.
Now, open the Solution Explorer and click the MainPage.xaml file. Modify it as shown below:
<ScrollView>
<VerticalStackLayout
Padding="30,0"
Spacing="25">
<Image
Source="dotnet_bot.png"
HeightRequest="185"
Aspect="AspectFit"
SemanticProperties.Description="dot net bot in a race car number eight" />
<Label
Text="Hello, World!"
Style="{StaticResource Headline}"
SemanticProperties.HeadingLevel="Level1" />
<Label
Text="Welcome to
.NET Multi-platform App UI"
Style="{StaticResource SubHeadline}"
SemanticProperties.HeadingLevel="Level2"
SemanticProperties.Description="Welcome to dot net Multi platform App U I" />
<Button
x:Name="CounterBtn"
Text="Click me"
SemanticProperties.Hint="Counts the number of times you click"
Clicked="OnCounterClicked"
HorizontalOptions="Fill" />
<Button Text="Go to Demo Page"
Clicked="OnNavigateButtonClicked"
VerticalOptions="Center"
HorizontalOptions="Center" />
</VerticalStackLayout>
</ScrollView>
We have added a new button called Go to Demo Page - it should navigate to the page we have just created.
The final step is to open Code-behind MainPage.xaml.cs and add the OnNavigateButtonClicked method.
Right-click MainPage.xaml from the Solution Explorer and select View Code. Add the following code to the MainPage class:
private async void OnNavigateButtonClicked(object sender, EventArgs e)
{
await Navigation.PushAsync(new MySQLDemo());
}
So, your MainPage.xaml.cs should look like this:
When you run the application and click Go to Demo page, you should see the page we just created.
Video tutorial: How to Connect a .NET MAUI Application to a MySQL Database
Create and manage MySQL tables
In the DatabaseConnectionClass, add the following code:
public static class DatabaseConnectionClass
{
public static string databasePath = "" +
"Server=127.0.0.1;" +
"UserId=TestUser;" +
"Password=TestPassword;" +
"Port=3306;" +
"Database=devart_test";
public static void CreateActorTable()
{
MySqlConnection conn = new MySqlConnection($"{databasePath};LicenseKey={licenseKey.yourLicensekey};");
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = @"CREATE TABLE actor (
actor_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);";
cmd.Connection = conn;
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine($"An error occured; {ex.Message}");
}
}
}
Here, we create a method that creates a table called actor. Refer to the MySQL connection strings tutorial for the details on specifying the database location and the dotConnect for the MySQL activation key (defined in another class).
Next, in MySQLDemo.xaml.cs, add the following code to call the method when the page is opened:
public partial class MySQLDemo : ContentPage
{
public MySQLDemo()
{
InitializeComponent();
DatabaseConnectionClass.CreateActorTable();
}
}
Run the application and click Go to Demo page. The method is called, and the table is created.
Now that we have created our table, we can comment out the CreateActorTable method.
When you run the application and click Go to Demo page, you should see the page we just created.
Perform CRUD operations
In addition to establishing connections, dotConnect for MySQL also enables us to deal with essential database operations such as CREATE, READ, UPDATE, and DELETE directly. Let's take a closer look at how these operations can be performed.
INSERT MySQL data
To insert data into an empty table in the MySQL database, we first need to create a class based on the table we have just created. Right-click your solution and add a class named Actor.cs. Inside that class, copy the below piece:
public class Actor
{
public int ActorId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime LastUpdate { get; set; }
}
Next, in DatabaseConnectionClass, add a new method:
public static void AddActor(Actor actor)
{
MySqlConnection conn = new MySqlConnection($"{databasePath};LicenseKey={licenseKey.yourLicensekey};");
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO actor (first_name, last_name, last_update) VALUES (@first_name, @last_name, @last_update);";
cmd.Parameters.Add(new MySqlParameter("@first_name", actor.FirstName));
cmd.Parameters.Add(new MySqlParameter("@last_name", actor.LastName));
cmd.Parameters.Add(new MySqlParameter("@last_update", actor.LastUpdate));
cmd.Connection = conn;
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine($"An error occured; {ex.Message}");
}
}
Now, let's configure our application view. In MySQLDemo.xaml, edit it so it looks like this:
<ContentPage xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
x:Class="MySQLMAUI.MySQLDemo"
Title="MySQLDemo">
<StackLayout>
<StackLayout x:Name="InsertSection" IsVisible="False" Padding="10">
<Label Text="First Name:" />
<Entry x:Name="InsertFirstNameEntry" Placeholder="Enter first name" />
<Label Text="Second Name:" />
<Entry x:Name="InsertSecondNameEntry" Placeholder="Enter second name" />
<Label x:Name="InsertErrorLabel" TextColor="Red" IsVisible="False" />
<Button Text="Insert" Clicked="OnInsertButtonClicked" WidthRequest="80" />
</StackLayout>
<Button x:Name="InsertActor" Text="Insert New Actor" Clicked="OnShowInsertSectionClicked" WidthRequest="150" />
</StackLayout>
</ContentPage>
In MySQLDemo.xaml.cs Code-behind, add two methods inside the MySQLDemo class:
private void OnShowInsertSectionClicked(object sender, EventArgs e)
{
InsertFirstNameEntry.Text = string.Empty;
InsertSecondNameEntry.Text = string.Empty;
// Show the insert section and hide the Insert actor button
InsertSection.IsVisible = true;
InsertActor.IsVisible = false;
}
private void OnInsertButtonClicked(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(InsertFirstNameEntry.Text) ||
string.IsNullOrWhiteSpace(InsertSecondNameEntry.Text))
{
// Show error message
InsertErrorLabel.Text = "Both fields are required.";
InsertErrorLabel.IsVisible = true;
return;
}
// Create a new actor object with the inserted values
var newActor = new Actor
{
FirstName = InsertFirstNameEntry.Text,
LastName = InsertSecondNameEntry.Text,
LastUpdate = DateTime.Now
};
// Add the new actor to the database
DatabaseConnectionClass.AddActor(newActor);
// Refresh the CollectionView
BindingContext = null;
BindingContext = this;
// Hide the insert section
InsertSection.IsVisible = false;
}
Run the code and click Go to Demo page:
Click Insert new actor, enter the actor's first name and last name, and click Insert.
READ MySQL data
Now that we have inserted a record into the table, we need a way to see what was added.
In DataBaseConnectionClass, add the below new method to get a list of all actors:
public static ListGetActors() { List actors = new List (); MySqlConnection conn = new MySqlConnection($"{databasePath};LicenseKey={licenseKey.yourLicensekey};"); MySqlCommand cmd = new (); cmd.CommandText = "SELECT * FROM actor;"; cmd.Connection = conn; conn.Open(); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Actor actor = new Actor { ActorId = Convert.ToInt32(reader["actor_id"]), FirstName = reader["first_name"].ToString(), LastName = reader["last_name"].ToString(), LastUpdate = Convert.ToDateTime(reader["last_update"]) }; actors.Add(actor); } return actors; }
Now, in MySQLDemo.xaml, edit the XAML file so it looks as follows:
<ContentPage xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
x:Class="MySQLMAUI.MySQLDemo"
Title="MySQLDemo">
<StackLayout>
<CollectionView ItemsSource="{Binding Actors}">
<CollectionView.ItemTemplate>
<DataTemplate>
<Grid Padding="2" RowDefinitions="Auto">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="50" />
<ColumnDefinition Width="100" />
<ColumnDefinition Width="100" />
<ColumnDefinition Width="100" />
</Grid.ColumnDefinitions>
<Label Text="{Binding ActorId}"
Grid.Column="0"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
<Label Text="{Binding FirstName}"
Grid.Column="1"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
<Label Text="{Binding LastName}"
Grid.Column="2"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
<Label Text="{Binding LastUpdate}"
Grid.Column="3"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
</Grid>
</DataTemplate>
</CollectionView.ItemTemplate>
</CollectionView>
<StackLayout x:Name="InsertSection" IsVisible="False" Padding="10">
<Label Text="First Name:" />
<Entry x:Name="InsertFirstNameEntry" Placeholder="Enter first name" />
<Label Text="Second Name:" />
<Entry x:Name="InsertSecondNameEntry" Placeholder="Enter second name" />
<Label x:Name="InsertErrorLabel" TextColor="Red" IsVisible="False" />
<Button Text="Insert" Clicked="OnInsertButtonClicked" WidthRequest="80" />
</StackLayout>
<Button x:Name="InsertActor" Text="Insert New Actor" Clicked="OnShowInsertSectionClicked" WidthRequest="150" />
</StackLayout>
</ContentPage>
In MySQLDemo.xaml.cs file, add a new property for a list of actors, initialize it in the class constructor, and set Binding in the following way:
public partial class MySQLDemo : ContentPage
{
public List Actors { get; set; }
public MySQLDemo()
{
InitializeComponent();
//DatabaseConnectionClass.CreateActorTable();
Actors = DatabaseConnectionClass.GetActors();
// Set the BindingContext
BindingContext = this;
}
....
}
Now, when you run the application and navigate to MySQLDemo, you can see the data.
UPDATE MySQL Data
Assume we want to change the actor's last name. In DataBaseConnectionClass, add a new method called UpdateActor:
public static void UpdateActor(Actor actor)
{
MySqlConnection conn = new MySqlConnection($"{databasePath};LicenseKey={licenseKey.yourLicensekey};");
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "UPDATE actor SET first_name = @first_name, last_name = @last_name, last_update = @last_update WHERE actor_id = @actor_id;";
cmd.Parameters.Add(new MySqlParameter("@first_name", actor.FirstName));
cmd.Parameters.Add(new MySqlParameter("@last_name", actor.LastName));
cmd.Parameters.Add(new MySqlParameter("@last_update", actor.LastUpdate));
cmd.Parameters.Add(new MySqlParameter("@actor_id", actor.ActorId));
cmd.Connection = conn;
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine($"An error occured; {ex.Message}");
}
}
Next, in the MySQLDemo.xaml file, replace the code with the one below:
<ContentPage xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
x:Class="MySQLMAUI.MySQLDemo"
Title="MySQLDemo">
<StackLayout>
<CollectionView ItemsSource="{Binding Actors}">
<CollectionView.ItemTemplate>
<DataTemplate>
<Grid Padding="2" RowDefinitions="Auto">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="50" />
<ColumnDefinition Width="100" />
<ColumnDefinition Width="100" />
<ColumnDefinition Width="100" />
<ColumnDefinition Width="Auto" />
</Grid.ColumnDefinitions>
<Label Text="{Binding ActorId}"
Grid.Column="0"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
<Label Text="{Binding FirstName}"
Grid.Column="1"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
<Label Text="{Binding LastName}"
Grid.Column="2"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
<Label Text="{Binding LastUpdate}"
Grid.Column="3"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
<Button Text="Update"
Grid.Column="4"
VerticalOptions="Center"
HorizontalOptions="Center"
Clicked="OnUpdateButtonClicked"
CommandParameter="{Binding ActorId}" />
</Grid>
</DataTemplate>
</CollectionView.ItemTemplate>
</CollectionView>
<StackLayout x:Name="UpdateSection" IsVisible="False" Padding="10">
<Label Text="Update First Name:" />
<Entry x:Name="UpdateFirstNameEntry" Placeholder="Enter new first name" />
<Label Text="Update Second Name:" />
<Entry x:Name="UpdateSecondNameEntry" Placeholder="Enter new Last name" />
<Label x:Name="UpdateErrorLabel" TextColor="Red" IsVisible="False" />
<Button Text="Go" Clicked="OnGoButtonClicked" />
</StackLayout>
<StackLayout x:Name="InsertSection" IsVisible="False" Padding="10">
<Label Text="First Name:" />
<Entry x:Name="InsertFirstNameEntry" Placeholder="Enter first name" />
<Label Text="Second Name:" />
<Entry x:Name="InsertSecondNameEntry" Placeholder="Enter second name" />
<Label x:Name="InsertErrorLabel" TextColor="Red" IsVisible="False" />
<Button Text="Insert" Clicked="OnInsertButtonClicked" WidthRequest="80" />
</StackLayout>
<Button x:Name="InsertActor" Text="Insert New Actor" Clicked="OnShowInsertSectionClicked" WidthRequest="150" />
</StackLayout>
</ContentPage>
We have added a new button called Update and a section for inputting the updated values.
In the MySQLDemo.xaml.cs Code-behind, we need to add a new property:
private Actor? _selectedActor;
Also, we need to add two new methods: OnUpdateButtonClicked and OnGoButtonClicked.
private void OnUpdateButtonClicked(object sender, EventArgs e)
{
var button = sender as Button;
var actorId = (int)button.CommandParameter;
// Find the actor in the list by ID
_selectedActor = Actors.FirstOrDefault(p => p.ActorId == actorId);
if (_selectedActor != null)
{
// Populate Entry fields with existing values
UpdateFirstNameEntry.Text = _selectedActor.FirstName;
UpdateSecondNameEntry.Text = _selectedActor.LastName;
// Show the update section
UpdateSection.IsVisible = true;
}
}
private void OnGoButtonClicked(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(UpdateFirstNameEntry.Text) ||
string.IsNullOrWhiteSpace(UpdateSecondNameEntry.Text))
{
// Show error message
UpdateErrorLabel.Text = "Both fields are required.";
UpdateErrorLabel.IsVisible = true;
return;
}
if (_selectedActor != null)
{
// Create a new actor object with the updated values
var updatedActor = new Actor
{
ActorId = _selectedActor.ActorId,
FirstName = UpdateFirstNameEntry.Text,
LastName = UpdateSecondNameEntry.Text,
LastUpdate = DateTime.Now
};
// Update the actor in the database
DatabaseConnectionClass.UpdateActor(updatedActor);
// Hide the update section
UpdateSection.IsVisible = false;
}
}
When the code is run, a new Update button is displayed:
Clicking this button adds the insert modal to insert new values.
Click Go, and the record gets updated:
DELETE MySQL Data
Let's try to remove an actor from our database. Our test table includes the Johnny Blaze record that we need to remove:
In DataBaseConnectionClass, add a method called DeleteActor:
public static void DeleteActor(int actorId)
{
MySqlConnection conn = new MySqlConnection($"{databasePath};LicenseKey={licenseKey.yourLicensekey};");
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "DELETE FROM actor WHERE actor_id = @actor_id;";
cmd.Parameters.Add(new MySqlParameter("@actor_id", actorId));
cmd.Connection = conn;
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine($"An error occured; {ex.Message}");
}
}
Then, in MySQLDemo.xaml, add a new button under the Update button.
<Button Text="Delete"
Grid.Column="5"
VerticalOptions="Center"
HorizontalOptions="Center"
Clicked="OnDeleteButtonClicked"
CommandParameter="{Binding ActorId}" />
Add a new column definition in Grid.ColumnDefinitions.
<ColumnDefinition Width="Auto" />
Finally, add the method for the OnDeleteButtonClicked in MySQLDemo.xaml.cs Code-behind:
private void OnDeleteButtonClicked(object sender, EventArgs e)
{
var button = sender as Button;
var actorId = (int)button.CommandParameter;
// Find the actor in the database by ID
DatabaseConnectionClass.DeleteActor(actorId);
this.Actors = DatabaseConnectionClass.GetActors();
BindingContext = null;
BindingContext = this;
}
When you run the application, you can see a new Delete button:
Clicking this butting removes the record:
