Setting Up and Using a Local SQLite Database in MAUI .NET 9 [GamesCatalog]

Previous part: Creating a Rating Bar in MAUI C# .NET 9 [GamesCatalog] - Part 8

Step 1. Now that we have a functional registration screen, we can focus on creating a local database to persist our user reviews.

Let's create a class library project called Repo, which will be responsible for functions related to the local database.

Class library project

Step 2. We will install the necessary NuGet packages to use EntityFramework in Repo.

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.Tools
Microsoft.EntityFrameworkCore.Sqlite

EntityFramework

Using the latest version available at the time of writing this article, 9.0.3.

Step 2.1. Also, add Microsoft.EntityFrameworkCore to the GamesCatalogMobile and Services projects.

Step 3. Let's create the DbCtx class.

DbCtx class

Code

using Microsoft.EntityFrameworkCore;

namespace Repo;

public class DbCtx(DbContextOptions<DbCtx> options) : DbContext(options)
{
}

Step 4. Now, let's add its configuration to Dependency Injection (DI) in CreateMauiApp.

Dependency Injection

Code

builder.Services.AddDbContextFactory<DbCtx>(options =>
    options.UseSqlite($"Filename={Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "GamesCatalog.db")}")
           .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

Step 5. Now that we have our local database, let's create a table to track its version locally.

Step 5.1. Create a Dto folder to handle the models related to the local database. Then, create a model called VersionDbTables to manage the database versioning.

Dto folder

Step 5.2. Add this DTO to DbCtx.

DTO

Step 6. Now, let's create a service to work with this model.

Model

Code

using Microsoft.EntityFrameworkCore;
using Models.DTOs;
using Repo;

namespace Services
{
    public class BuildDbService(IDbContextFactory<DbCtx> DbCtx) : IBuildDbService
    {
        public void Init()
        {
            using var context = DbCtx.CreateDbContext();
            context.Database.EnsureCreated();

            VersionDbTables? actualVesionDbTables = context.VersionDbTables.FirstOrDefault();

            VersionDbTables newVersionDbTables = new() { Id = 0, Version = 1 };

            if (actualVesionDbTables != null)
            {
                if (actualVesionDbTables.Version != newVersionDbTables.Version)
                {
                    context.Database.EnsureDeleted();
                    context.Database.EnsureCreated();

                    actualVesionDbTables.Version = newVersionDbTables.Version;

                    context.VersionDbTables.Add(actualVesionDbTables);

                    context.SaveChanges();
                }
            }
            else
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                context.VersionDbTables.Add(newVersionDbTables);

                context.SaveChanges();
            }
        }
    }
}

If you want to update the database structure, simply update the database version in the Version variable of newVersionDbTables.

Step 6.1. Using the shortcut [Ctrl + .] or by right-clicking on the class name, extract the interface into a new file.

Step 7. Returning to MauiProgram.cs, create a class that will add dependency injections for the business logic layer.

  public static IServiceCollection Services(this IServiceCollection services)
  {
      services.AddScoped<IBuildDbService, BuildDbService>();
      return services;
  }

Step 7.1. In the CreateMauiApp function, add the call to this function.

builder.Services.Services();

CreateMauiApp

Step 8. Declare and use BuildDbService in App.xaml.cs.

using Services;

namespace GamesCatalog
{
    public partial class App : Application
    {
        private IBuildDbService BuildDbService { get; set; }

        public App(IBuildDbService buildDbService)
        {
            BuildDbService = buildDbService;

            InitializeComponent();
        }

        protected override Window CreateWindow(IActivationState? activationState)
        {
            BuildDbService.Init();

            return new Window(new AppShell());
        }
    }
}

This way, if necessary, we can recreate the system's local database by simply increasing the version in the code.

Next step: Adding Objects to a Local Database in MAUI .NET 9 [GamesCatalog] - Part 10