Setup and Use SQLite with .NET MAUI - Tutorial and Code Examples

 I was recently working on a .NET MAUI mobile app and needed some data to persist between launches. I know from experience that SQLite is a very common approach to storing data locally on mobile devices so I began implementing it. As usual, the road to success was bumpy and I hit an error immediately and wrote about fixing it fairly quickly in Using SQLite as a Local Database With .NET MAU (resolving missing SQLitePCLRaw.provider.dynamic_cdecl). This issue is a problem one would encounter when setting up the NuGet packages. This post was pretty small, so I decided to write this post on the entire process of setting up and using SQLite with .NET MAUI.

1) Install the SQLite NuGet packages

In order to use SQLite, you will need "sqlite-net-pcl" and "SQLitePCLRaw.provider.dynamic_cdecl."

I attempted to follow Microsoft's instructions and use "SQLitePCLRaw.bundle_green" in place of SQLitePCLRaw.provider.dynamic_cdecl" but I continued to get FileNotFoundExceptions. These are simply the NuGet packages that worked for me in the end and are still working.

2) Set up some simple database access methods.

I did follow Microsoft's instructions on this part. I found their example of Constants.cs to be really helpful. I found it better to use the Environment.SpecialFolder.Personal file path as the database file path. This one also seems to work quite nicely on my Android devices.

This is what I am using for mine. 

    public static class Constants
    {
        public const string DatabaseFilename = "todo.db";

        public const SQLite.SQLiteOpenFlags Flags =
            // open the database in read/write mode
            SQLite.SQLiteOpenFlags.ReadWrite |
            // create the database if it doesn't exist
            SQLite.SQLiteOpenFlags.Create |
            // enable multi-threaded database access
            SQLite.SQLiteOpenFlags.SharedCache;

        public static string DatabasePath =>
            Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), DatabaseFilename);
    }

I have another class dedicated to accessing the database's content as well. Microsoft's instructions show them initializing the database each time they want to access the data or write to the database. I have one method that is called when my application first needs to access the database and initializes it. In my case, I need the data immediately on start-up so that is when I initialize my database. If you are unsure when you will need to access your database, you can follow their lazy-initialization strategy and call your initialize method each time the database is accessed. No worries about overwriting your tables as well. The CreateTableAsync call only creates the table if it does not exist. The following is what my database initialization method looks like.

        public static async void CreateDatabase()
        {
            try
            {

                var database = new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);
                var result = await database.CreateTableAsync<Todo>();
            }
            catch (Exception ex)
            {
                // Eventually some logging, probably
            }
        }

Finally, I have some methods to read data from the SQLite database and write to it. They look like this.

        public static async Task<int> AddTodo(Todo todoItem)
        {
            try
            {
                var database = new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);
                return await database.InsertAsync(todoItem);
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public static async Task<Todo> GetLatestTodo()
        {
            try
            {
                var database = new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);
                var todos = await database.Table<Todo>().ToListAsync();
                return todos.OrderBy(b => b.CreateDate).FirstOrDefault();
            }
            catch (Exception ex)
            {
                return new Todo();
            }
        }

You can write SQL queries to access the database as well. I just prefer this method as I am fairly comfortable using LINQ to do so. We are pretty much just selecting all the data from the desired table and then sorting it afterward.

However, if you want to use SQL, here is the example Microsoft uses in their documentation:

await Database.QueryAsync<TodoItem>("SELECT * FROM [TodoItem] WHERE [Done] = 0");

This almost gets you going! What else is missing...?

3) The table classes!

In order to create the tables we will need objects to define the columns.

In my case, I wanted a table with the following columns:

    1) An auto-incrementing ID as the primary key (int)

    2) A start date (DateTime)

    3) An end date (DateTime)

    4) A create date -- The date the value is inserted into the SQLite database (DateTime)

    5) Task name (string)

    6) Task details (string)

Here is the class that got the job done.    

    public class Todo
    {
        [PrimaryKey, AutoIncrement]
        public int ID { get; set; }
        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }
        public DateTime CreateDate
        {
            get
            {
                return DateTime.Now;
            }
        }
        public string TaskName { get; set; }
        public string TaskDetails { get; set; } }
    }

If you wanted to add a calculated column to calculate something like if the current date is between the start and end dates, you could add another value like this:

        public bool WithinDataRange
        {
            get
            {
                return (DateTime.Now > StartDate) && (DateTime.Now < EndDate);
            }
        }

I went with the approach of making my database access class static. I did not like the idea of making an object each time just to call one method to insert or read data. I have buttons set up so that when they are clicked or tapped we insert the given user input into the SQLite database using RelayCommands. For example, this is what one of those RelayCommands looks like:

        [RelayCommand]
        internal async Task CreateTodo()
        {
            Todo todo = new()
            {
                StartDate = StartDate,
                EndDate = EndDate,
                TaskName = "Woah we could instead use input",
                TaskDetails = "Woah we could instead use input",
            };
            var result = await TodoDatabase.AddTodo(todo);
        }

Finally, I will show you the XAML that works with the above command. Each time the button is tapped the command executes. The XAML class had user input values that have bindings to my view model, which also contains the CreateTodo command, that way the CreateTodo command can access the user input and insert it into the database. I removed those values above, but that is how it really works.

        <Button
                x:Name="CreateBtn"
                Text="Create TODO"
                Command="{Binding CreateTodoCommand}"
                HorizontalOptions="Center" />

These are the steps and code that I am using to work with my SQLite database in .NET MAUI, specifically targeting Android. I have not run into any issues using this code and all seems to be working. It's been fun experimenting and playing around with .NET MAUI, it definitely makes mobile development fun for someone like me who is not interested in picking up Java or Kotlin right now. 

Hopefully, this makes your experience positive as well and gets you on the right track.