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

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.

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

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

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.

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.

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.

Step 5.2. Add this DTO to DbCtx.

Step 6. Now, let's create a service to work with this 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();

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.

In the next step, we will store the game reviews in the local database.