SQLite CRUD Operations in .NET MAUI

.NET MAUI (Multi-platform App UI) is a cross-platform framework for building desktop and mobile applications using C# and XAML. It allows developers to create applications running on Windows, macOS, iOS, and Android, all from one codebase.

SQLite is a small, fast, self-contained database engine. It is the most popular SQL engine out there due to its serverless nature and is used on all smartphones and most computers. Database connectivity is important because no application or solution is complete without a data store, so you can insert and retrieve your information as you see fit.

In this tutorial, we will use the Code-First approach to integrate SQLite with an MAUI application, allowing you to design and manage your database directly from your C# code.

Why dotConnect for SQLite?

dotConnect for SQLite offers many other cool features, such as advanced integration with ADO.NET through Visual Studio and enhanced ORM support. You will find a more comprehensive list of features on our website.

Requirements

What you will need for this tutorial:

Visual Studio 2022: our IDE of choice. If you do not have it on your machine, go to the official website to download and install it. We will be using the Community Edition, so you can get it as well.
.NET MAUI: a .NET workload installable with the .NET CLI tool. It is available within the Visual Studio 2022 installer.
dotConnect for SQLite: a feature-rich ADO.NET provider with Entity Framework, NHibernate, and LinqConnect support.

Download and activate dotConnect for SQLite

Visit the dotConnect for SQLite download page. You have the option to select either the free trial or the full version of dotConnect for SQLite. The free trial version gives you 30 days to explore all features, but it cannot be used for deploying commercial projects and does not require a license key for activation.

Download and install the trial version

For the trial version, simply download and install dotConnect for SQLite on your machine. No license key is required for the trial, and you can start exploring the product immediately.

Purchase and access the full version

After purchasing the full version, go to your profile's Licenses page. You'll find a list of your licenses, including your dotConnect for SQLite purchase. Click the Details button next to your dotConnect for SQLite license. Here, you'll find your license details, including the Activation Key.

Install dotConnect for SQLite - The location of the Details button

Download the full version

Click the Download Product Versions button to access the list of available product versions. Select the most recent version and download the installer.

Install dotConnect for SQLite - Selecting product version

Execute the downloaded file and follow the on-screen instructions to install dotConnect for SQLite on your machine and activate all features associated with your license key.

Create a .NET MAUI project

1. To create a .NET MAUI project, you need to open the previously installed Visual Studio and click Create a new project.

2. In the Search Template field, search for .NET MAUI App and select it.

Install dotConnect for SQLite - MAUI template

3. Give your project a name, for example, SQLite_MAUI. Then, select .NET 8 as the target framework and click Create.

4. Next, install dotConnect for SQLite for your project via the NuGet Package manager in Visual Studio. In the taskbar, click Tools > NuGet package manager > Manage NuGet packages to open the console.

5. In the NuGet Package Manager that opens, click Browse and search for Devart.Data.SQLite. Then, select your project and click Install.

6. When you press F5, the automatically generated application will launch and appear on your screen.

7. Set up a SQLite test database by renaming the sakila.db file to devartmaui.db.

8. Create a folder named devart_maui_db within your project and move the devartmaui.db file into this newly created folder.

9. Create a new page where you'll do most of your work. For this, right-click the SQLiteMAUI solution, click Add New Item, and select the .NET MAUI ContentPage(XAML) template. Name this page ActorPage.xaml, and click Add.

10. In the Solution Explorer, click the MainPage.xaml file and modify it so it looks like this:

<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 Actor Page"
        Clicked="OnNavigateButtonClicked"
        VerticalOptions="Center"
        HorizontalOptions="Center" />
    </VerticalStackLayout>
</ScrollView>

There is a new button called Go to Actor Page, which will navigate to the page you just created. The final step is to open the Code-behind MainPage.xaml.cs and add the OnNavigateButtonClicked method.

11. Right-click MainPage.xaml from the Solution Explorer and select View Code.

12. Add this piece of code to the MainPage Class:

private async void OnNavigateButtonClicked(object sender, EventArgs e)
{
    await Navigation.PushAsync(new ActorPage());
}

As a result, your MainPage.xaml.cs should look like this:

When you run the application and click the Go to Actor Page button, you should see the page you just created.

In order to create a connection instance, right-click the solution, and select Add New Item, and then add a new class. Call it DatabaseConnectionClass.cs.

Create a SQLite table

1. Now you need to add some code to the DatabaseConnectionClass in order to create the table you will work with:

public static class DatabaseConnectionClass
 {
     public static string filePath = "\\devartmaui.db";

     public static void CreateActorTable()
     {

         SQLiteConnection conn = new SQLiteConnection($"" +
            $"DataSource={filePath};" +
            $"FailIfMissing=False;" +
            $"LicenseKey={licenseKey.yourLicensekey};");
         SQLiteCommand cmd = new SQLiteCommand();
         cmd.CommandText = "CREATE TABLE actor (\r\n  actor_id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "\r\n  first_name VARCHAR(45) NOT NULL," +
            "\r\n  last_name VARCHAR(45) NOT NULL," +
            "\r\n  last_update TIMESTAMP NOT NULL\r\n);";
         cmd.Connection = conn;
         conn.Open();
         try
         {
             cmd.ExecuteNonQuery();
         }
         catch (Exception ex)
         {
             Console.WriteLine($"An error occured; {ex.Message}");
         }
     }
 }

Here, we introduce a method to create a table named actor. The connection string specifies the database location and includes the dotConnect for SQLite activation key, which is defined in a separate class. FilePath is the full path where your SQLite database is located.

2. In ActorPage.xaml.cs, simply add the following code so that the method is triggered when the page opens:

public partial class ActorPage : ContentPage
{
	public ActorPage()
	{
		InitializeComponent();
        DatabaseConnectionClass.CreateActorTable();
    }
}

3. Run the application and click Go to Actor Page. The method is now named, and the table is created. You can comment out the CreateActorTable method.

INSERT SQLite data

Let's insert some data from the application into our table.

1. Add a class based on the table we just created. Right-click your solution, then add a class named Actor.cs. Copy the following code and paste it there:

public class Actor
{
    public int ActorId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime LastUpdate { get; set; }
}

2. Add a new method in DatabaseConnectionClass:

public static void AddActor(Actor actor)
 {
     SQLiteConnection conn = new SQLiteConnection($"DataSource={filePath};LicenseKey={licenseKey.yourLicensekey};");
     SQLiteCommand cmd = new SQLiteCommand();
     cmd.CommandText = "INSERT INTO actor (first_name, last_name, last_update) VALUES (@first_name, @last_name, @last_update);";
     cmd.Parameters.Add(new SQLiteParameter("@first_name", actor.FirstName));
     cmd.Parameters.Add(new SQLiteParameter("@last_name", actor.LastName));
     cmd.Parameters.Add(new SQLiteParameter("@last_update", actor.LastUpdate));
     cmd.Connection = conn;
     conn.Open();
     try
     {
         cmd.ExecuteNonQuery();
     }
     catch (Exception ex)
     {
         Console.WriteLine($"An error occured; {ex.Message}");
     }
 }

3. Edit ActorPage.xaml so it looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             x:Class="SQLiteMAUI.ActorPage"
             Title="ActorPage">
    <StackLayout>
        <StackLayout x:Name="InsertSection" IsVisible="False" Padding="10">
            <Label Text="First Name:" />
            <Entry x:Name="InsertFirstNameEntry" Placeholder="Enter first name" />
            <Label Text="Actor Name:" />
            <Entry x:Name="InsertActorNameEntry" Placeholder="Enter Actor 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>

4. Find the ActorPage class in ActorPage.xaml.cs and add these two methods there:

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);

    this.Actors = DatabaseConnectionClass.GetActors();

    // Refresh the CollectionView
    BindingContext = null;
    BindingContext = this;

    // Hide the insert section
    InsertSection.IsVisible = false;
}

5. Run the code and click the Go to Actor Page button.

6. Click Insert New Actor, enter the actor's first and last names, and then click Insert.

READ SQLite data

Now that we have inserted a record into the table, we need a way to see what was added.

1. To get a list of all actors, add this new method to DataBaseConnectionClass:

public static List GetActors()
{
    List actors = new List();
    SQLiteConnection conn = new SQLiteConnection($"DataSource={filePath};LicenseKey={licenseKey.yourLicensekey};");
    SQLiteCommand cmd = new SQLiteCommand();
    cmd.CommandText = "SELECT * FROM actor;";
    cmd.Connection = conn;
    conn.Open();
    SQLiteDataReader 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;
}

2. Edit ActorPage.xaml so it looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             x:Class="SQLiteMAUI.ActorPage"
             Title="ActorPage">
    <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>

3. In Actor.xaml.cs file, add a new property for the list of Actors, have it initialized in the class constructor, and set binding to it.

public partial class ActorPage : ContentPage
{
    public List Actors { get; set; }
    public ActorPage()
	{
		InitializeComponent();
        //DatabaseConnectionClass.CreateActorTable();
        Actors = DatabaseConnectionClass.GetActors();
        // Set the BindingContext
        BindingContext = this;
    }
....
}

Now, when you run the application and navigate to the ActorPage, you can see the data:

UPDATE SQLite data

Let's see how to change the last name of an actor, Seth. This case will most probably come in handy for your application.

1. In DataBaseConnectionClass, add a new method called UpdateActor:

public static void UpdateActor(Actor actor)
 {
     SQLiteConnection conn = new SQLiteConnection($"DataSource={filePath};LicenseKey={licenseKey.yourLicensekey};");
     SQLiteCommand cmd = new SQLiteCommand();
     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 SQLiteParameter("@first_name", actor.FirstName));
     cmd.Parameters.Add(new SQLiteParameter("@last_name", actor.LastName));
     cmd.Parameters.Add(new SQLiteParameter("@last_update", actor.LastUpdate));
     cmd.Parameters.Add(new SQLiteParameter("@actor_id", actor.ActorId));
     cmd.Connection = conn;
     conn.Open();
     try
     {
         cmd.ExecuteNonQuery();
     }
     catch (Exception ex)
     {
         Console.WriteLine($"An error occured; {ex.Message}");
     }
 }

2. In the ActorPage.xaml file, replace the code with the below:

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             x:Class="SQLiteMAUI.ActorPage"
             Title="ActorPage">
    <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.

3. In the ActorPage.xaml.cs code-behind, add the private Actor? _selectedActor; property.

4. Then, add two new methods:

OnUpdateButtonClicked;
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 you run the code, you can see a new update button:

Clicking the Update button will bring up the insert modal to insert new values:

Clicking the Go button will update our record.

DELETE SQLite data

Let's see how to remove an actor from the database. In our case, it will be Johnny Blaze.

1. In DataBaseConnectionClass, add this method called DeleteActor:

public static void DeleteActor(int actorId)
 {
     SQLiteConnection conn = new SQLiteConnection($"DataSource={filePath};LicenseKey={licenseKey.yourLicensekey};");
     SQLiteCommand cmd = new SQLiteCommand();
     cmd.CommandText = "DELETE FROM actor WHERE actor_id = @actor_id;";
     cmd.Parameters.Add(new SQLiteParameter("@actor_id", actorId));
     cmd.Connection = conn;
     conn.Open();
     try
     {
         cmd.ExecuteNonQuery();
     }
     catch (Exception ex)
     {
         Console.WriteLine($"An error occured; {ex.Message}");
     }
 }

2. Add a new button after Button Text="Update" in ActorPage.xaml:

<Button Text="Delete" 
 Grid.Column="5" 
 VerticalOptions="Center"
 HorizontalOptions="Center"
 Clicked="OnDeleteButtonClicked" 
 CommandParameter="{Binding ActorId}" />

3. In the ActorPage.xaml.cs code-behind, add the method for the OnDeleteButtonClicked.

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, we see our new Delete button.

Clicking the Delete button next to Johnny Blaze's record will delete it.

Conclusion

Now you can see how easy it is to connect a .NET MAUI application to a SQLite database, create tables, and manage data. You can try it all yourself by dotConnect for SQLite for a free trial.