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 integration 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

30-day free trial version

Download and install dotConnect for MySQL directly on your machine, or install the Devart.Data.MySQL NuGet package.

To activate a 30-day trial, use the license key assigned to you after completing the registration on the Devart website.

Full version

After purchasing the full version, go to your profile's Licenses page. Choose your product and click Details. Here, you'll find the license details and the Activation Key.

License details and the activation key

To activate a connection in your application, add the License Key to your connection string.

The next step is creating a .NET MAUI project.

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:

Insert data into a table

Click Insert new actor, enter the actor's first name and last name, and click Insert.

Results of the data insertion

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 List GetActors()
{
    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.

Results of the data reading

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:

Update data in a table

Clicking this button adds the insert modal to insert new values.

Data updating process

Click Go, and the record gets updated:

Data updating results

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:

Data deletion

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:

A new Delete button is added

Clicking this butting removes the record:

Data deletion results

Video tutorial: How to connect a .NET MAUI application to a MySQL database

Conclusion

This tutorial describes how to connect a .NET MAUI application to a MySQL database using dotConnect for MySQL and demonstrates how to manage the database. We explored how to create tables, insert data, read from the tables, and update or delete records.

Using the dotConnect for MySQL data provider greatly simplifies tasks by supporting database-specific features and integrating seamlessly with Visual Studio. This allows you to leverage your preferred IDE for application development with enhanced functionality.

Download a free trial to test the solution's functionality under real-world workloads and evaluate its efficiency within your workflows.

What else can dotConnect for MySQL do?

How to connect to MySQL and MariaDB in .NET with C#
Find out direct, SSL/TLS, EF Core and other types of connections in a .NET applications.
Connecting C# to MySQL and MariaDB with EF Core
Explore how to connect to MySQL and MariaDB with EF Core using the MySQL ADO.NET data provider.
Connecting C# to MySQL and MariaDB with Dapper
Learn how to connect C# to MySQL using Dapper for fast and efficient data access.

dotConnect for MySQL

Get an enhanced ORM-enabled data provider for MySQL and develop .NET applications working with MySQL data quickly and easily!

Find out more
Try the 30-day trial of the full product. No limits. No card required Start free trial