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

Download and activate dotConnect for SQLite

30-day free trial version

Download and install dotConnect for SQLite directly on your machine, or install the Devart.Data.SQLite 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.

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=**********;");
         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.

Commenting out the 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=**********;");
     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.

Insert New Actor

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

INSERT SQLite data

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<Actor> GetActors()
{
    List<Actor> actors = new List<Actor>();
    SQLiteConnection conn = new SQLiteConnection($"DataSource={filePath};LicenseKey=**********;");
    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<Actor> 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:

View SQLite 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=**********;");
     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:

New UPDATE button

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

New UPDATE button in action

Clicking the Go button will update our record.

Data UPDATE successfull

DELETE SQLite data

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

Data DELETE

1. In DataBaseConnectionClass, add this method called DeleteActor:

public static void DeleteActor(int actorId)
 {
     SQLiteConnection conn = new SQLiteConnection($"DataSource={filePath};LicenseKey=**********;");
     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.

Data DELETE

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

Data removal successfull

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

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.

What else can dotConnect for SQLite do?

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

dotConnect for SQLite

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

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