Foreign keys in local databases with SQLite-net and .NET MAUI

Luis Beltran - Dec 5 '22 - - Dev Community

This publication is part of the .NET MAUI Advent Calendar 2022, an initiative led by Héctor Pérez and Luis Beltrán. Check this link for more interesting .NET MAUI articles posted by community members.

A common misconception about SQLite-net library is that it can't handle Foreign Keys in a local SQLite database. Actually, they are supported! Maybe not in the way you'd expect it, but it works.

Let's see how to develop a .NET MAUI app that stores information in a local database with 2 tables referenced by a foreign key! In our application, we can perform CRUD operations over two tables: Departments and Employees. An employee belongs to one department, and the department includes many employees (so there is a 1-N relationship).

Source code available here.

Step 1. Create the following project structure:

Folder structure of the project

Step 2. Add the following Nuget packages:

  • sqlite-net-pcl
  • SQLitePCLRaw.bundle_green
  • SQLitePCLRaw.core
  • SQLitePCLRaw.provider.dynamic_cdecl
  • SQLitePCLRaw.provider.sqlite3
  • CommunityToolkit.Mvvm (optional, but recommended)

Nuget Packages included in the project

Step 3. In Helpers folder, create a Constants class with the following code that includes public constants for:

  • the local database filename
  • the two table names
  • two Create Table statements with the table specification. You can observe that Employees include a foreign key restriction.
  • two Select queries that return data from the tables. The second query is a JOIN to demonstrate that the relationship actually exists.
  • two routes (for navigation using Shell)


namespace DemoFK.Helpers
{
    public static class Constants
    {
        public const string LocalDbFile = "company_v01.db";
        public const string DepartmentTable = "departments";
        public const string EmployeeTable = "employees";

        public const string CreateDepartmentTableStatement = $"CREATE TABLE IF NOT EXISTS {DepartmentTable} (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR(255));";
        public const string CreateEmployeeTableStatement = $"CREATE TABLE IF NOT EXISTS {EmployeeTable} (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR(255), DepartmentId INT, FOREIGN KEY(DepartmentId) REFERENCES departments(Id));";

        public const string AllDepartmentsQuery = $"SELECT * FROM {DepartmentTable}";
        public const string EmployeesAndDepartmentQuery = $"SELECT e.*, d.Name as DepartmentName FROM {EmployeeTable} e JOIN {DepartmentTable} d ON e.DepartmentId = d.Id";

        public const string DepartmentDetailsRoute = "DepartmentDetailsPage";
        public const string EmployeeDetailsRoute = "EmployeeDetailsPage";
    }
}
Enter fullscreen mode Exit fullscreen mode

Of course, the most important elements are the statements and queries. As you can infer, instead of using the ORM capabilities that are integrated in SQLite, you will manually execute queries! However, you can still use the ORM mapper for several operations. For example, you can create and get all items from Departments table with the CreateTable and Table methods because there is no foreign key involved. However, for Employees table, it is better to customize the data management. We will use both ways in our app.

NOTE: In this folder, there is also a FileAccessHelper class which is used to access the database file. I will not include it here, but you can take a look at the GitHub repository for the code.

Step 4. Now let's define the models. Create the following classes and structure:

Model definitions

There are two types: models that work as tables, and models that work as views. A view is a projection, a set of specific fields from a query that involves one or several tables. For example, the EmployeesAndDepartmentQuery query is a view since it includes fields from Employee (all) and Department (DepartmentName) tables. I also created a BaseTable class, which includes an Id property, and all classes inherit from this model. I believe that the SQLite attributes are not necessary since we defined our tables using the statements, but it doesn't hurt to include them (I think). This is the code of the four classes, in order of creation:



using SQLite;

namespace DemoFK.Models
{
    public class BaseTable
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
    }
}
Enter fullscreen mode Exit fullscreen mode 


using SQLite;
using DemoFK.Helpers;

namespace DemoFK.Models
{
    [Table(Constants.DepartmentTable)]
    public class Department : BaseTable
    {
        public string Name { get; set; }
    }
}
Enter fullscreen mode Exit fullscreen mode 


using SQLite;
using DemoFK.Helpers;
using DemoFK.Models.Views;

namespace DemoFK.Models
{
    [Table(Constants.EmployeeTable)]
    public class Employee : BaseTable
    {
        public string Name { get; set; }
        public int DepartmentId { get; set; }

        public Employee()
        {

        }

        public Employee(EmployeeWithDepartment empDep)
        {
            Id = empDep.Id;
            Name = empDep.Name;
            DepartmentId = empDep.DepartmentId;
        }
    }
}
Enter fullscreen mode Exit fullscreen mode 


namespace DemoFK.Models.Views
{
    public class EmployeeWithDepartment : Employee
    {
        public string DepartmentName { get; set; }
    }
}
Enter fullscreen mode Exit fullscreen mode

EmployeeWithDepartment inherits from Employee, meaning that all fields from that table will be included in a query (Id, Name) as well as the DepartmentName field.

Step 5. In Services folder, define an interface: ILocalDatabase. There are 4 methods defined here:

  • GetItemsWithQuery returns a collection of T elements using a query string (previously defined in the Constants file).
  • GetItems does exactly the same as the previous method, except that this one uses the ORM capabilities of the library (the most well-known use case of SQLite-net)
  • ExecuteQuery is used for Insert, Update, and Delete operations
  • CountItemsWithQuery for demonstration purposes, it will be used to see how much data is in a table and see if it was created successfully


using DemoFK.Models;

namespace DemoFK.Services
{
    public interface ILocalDatabaseService
    {
        Task<IEnumerable<T>> GetItemsWithQuery<T>(string query) where T : BaseTable, new();
        Task<IEnumerable<T>> GetItems<T>() where T : BaseTable, new();
        Task<bool> ExecuteQuery(string query);
        Task<int> CountItemsWithQuery(string query);
    }
}
Enter fullscreen mode Exit fullscreen mode

Step 6. In the same folder, create the LocalDatabaseService class, which implements the ILocalDatabaseService interface. There's a lot of code here so I will break it down:

First, the namespaces and the class definition:



using SQLite;
using System.Diagnostics;

using DemoFK.Models;
using DemoFK.Helpers;

namespace DemoFK.Services
{
    public class LocalDatabaseService : ILocalDatabaseService
    {
Enter fullscreen mode Exit fullscreen mode

Two private fields are defined, the database file path and the connection to interact with SQLite:



        private string dbPath;
        private SQLiteAsyncConnection connection;
Enter fullscreen mode Exit fullscreen mode

The constructor sets the dbPath field:



        public LocalDatabaseService(string dbPath)
        {
            this.dbPath = dbPath;
        }
Enter fullscreen mode Exit fullscreen mode

The Init method:

  • checks if the connection has been set.
  • if it hasn't, it creates the SQLiteAsyncConnection instance.
  • The Tracer and Trace members from connection allow us to see the SQL commands generated each time we interact with the local database file, for debugging purposes.
  • Several methods are called, and they are defined below. The idea is that the tables are created, we count how much data there is in the database; if it is empty, we insert mock data and check if it was successfully created.


        private async Task Init()
        {
            if (connection != null)
                return;

            try
            {
                connection = new SQLiteAsyncConnection(dbPath);

                connection.Tracer = new Action<string>(q => Debug.WriteLine(q));
                connection.Trace = true;

                await CreateTables();
                var count = await CountItems();

                if (count == 0)
                {
                    await AddInitialData();
                    await CheckData();
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
            }
        }
Enter fullscreen mode Exit fullscreen mode

Let's see the specific code of the aforementioned methods:

CreateTables: As you can see, the two statements defined in Constants are used here and passed each to ExecuteQuery method (which is defined in the interface).



        private async Task CreateTables()
        {
            var createTableStatements = new List<string>()
            {
                Constants.CreateDepartmentTableStatement,
                Constants.CreateEmployeeTableStatement
            };

            foreach (var statement in createTableStatements)
                await ExecuteQuery(statement);
        }
Enter fullscreen mode Exit fullscreen mode

How is ExecuteQuery method defined? Let's take a look:



        public async Task<bool> ExecuteQuery(string query)
        {
            await Init();

            var op = await connection.ExecuteAsync(query);
            return op > 0;
        }
Enter fullscreen mode Exit fullscreen mode

The above method checks if the connection has been set; then, it calls its ExecuteAsync method and returns the number of records that were affected by the operation.

Now, let's continue with CountItems method, which counts the number of items per table. The CountItemsWithQuery method is invoked here:



        private async Task<int> CountItems()
        {
            var tables = new string[] { Constants.DepartmentTable, Constants.EmployeeTable };
            var count = 0;

            foreach (var table in tables)
            {
                var countQuery = $"SELECT COUNT(*) FROM {table}";
                var tableCount = await CountItemsWithQuery(countQuery);
                Debug.WriteLine($"{table}: {tableCount}");
                count += tableCount;
            }

            return count;
        }
Enter fullscreen mode Exit fullscreen mode

In CountItemsWithQuery method, the ExecuteScalarAsync method is used since our query returns an integer number of rows. This method was also defined in the interface:



        public async Task<int> CountItemsWithQuery(string query)
        {
            await Init();

            return await connection.ExecuteScalarAsync<int>(query);
        }
Enter fullscreen mode Exit fullscreen mode

Next up, the AddInitialData method, which inserts information in each table:



        private async Task AddInitialData()
        {
            var commands = new List<string>()
            {
                "INSERT INTO departments VALUES (1, 'Kitchen'), (2, 'Sales'), (3, 'Accounting');",
                "INSERT INTO employees VALUES (1, 'SpongeBob SquarePants', 1), (2, 'Squidward Tentacles', 2), (3, 'Mr. Krabs', 3), (4, 'Pearl Krabs', 3);",
            };

            foreach (var command in commands)
            {
                var op = await ExecuteQuery(command);
                Debug.WriteLine(op);
            }
        }
Enter fullscreen mode Exit fullscreen mode

We can validate if the data is there with the CheckData method:



        private async Task CheckData()
        {
            var item = await connection.Table<Department>().Where(v => v.Id == 1).FirstOrDefaultAsync();
            Debug.WriteLine(item?.Name);
        }
Enter fullscreen mode Exit fullscreen mode

The final two methods are required as they are defined in the interface and return a list of items, either from a query or by using the Table method:



        public async Task<IEnumerable<T>> GetItems<T>() where T : BaseTable, new()
        {
            await Init();

            return await connection.Table<T>().ToListAsync();
        }

        public async Task<IEnumerable<T>> GetItemsWithQuery<T>(string query) where T : BaseTable, new()
        {
            await Init();

            return await connection.QueryAsync<T>(query);
        }
Enter fullscreen mode Exit fullscreen mode

And that's basically it. The next steps involve setting up and consumption, so I'll focus on the specific, most important code:

In App.xaml.cs, an ILocalDatabaseService public static object is defined to get access to the methods from anywhere in the app. It is injected in the constructor (in MauiProgram, we will register all dependencies).



public partial class App : Application
{
    public static ILocalDatabaseService LocalDb { get; private set; }

    public App(ILocalDatabaseService localDb)
    {
        ...

        LocalDb = localDb;
        ...
    }
}
Enter fullscreen mode Exit fullscreen mode

The GenericListViewModel will be used as the BindingContext of a ContentPage that displays a list of items and supports navigation to another page with the specific details of the selected item (or a new one). The important part here is the GetItemsAsync method, which invokes the GetItemsWithQuery from the database service class and uses a query parameter that defines what we want to see in the page:



    public partial class GenericListViewModel<T> : BaseViewModel where T : BaseTable, new()
    {
        ...

        [RelayCommand]
        private async Task GetItemsAsync()
        {
            if (IsBusy)
                return;

            try
            {
                IsBusy = true;

                var items = await App.LocalDb.GetItemsWithQuery<T>(query);

                // if you want to get all items from specific table (no join involved) you can also use
                // items = await App.LocalDb.GetItems<T>();

                if (Items.Count != 0)
                    Items.Clear();

                foreach (var item in items)
                    Items.Add(item);
            }
            catch (Exception ex)
            {
                await Shell.Current.DisplayAlert("Error!", ex.Message, "OK");
            }
            finally
            {
                IsBusy = false;
            }
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

For example, let's say we have two pages: one for departments (DepartmentListView) and another one for employees (EmployeeListView); in the first one we want to display the list of departments and in the second one, we show a list of Employees that also includes the department they belong to. We know we already have a query (EmployeesAndDepartmentQuery, which defines a JOIN between the two tables). We specify the argument in MauiProgram because we register the ViewModel as a dependency; since the constructor defines arguments, we use ActivatorUtilities.CreateInstance:



...
        builder.Services.AddSingleton<ILocalDatabaseService>(
            s => ActivatorUtilities.CreateInstance<LocalDatabaseService>(s, dbPath));

        builder.Services.AddTransient(
            s => ActivatorUtilities.CreateInstance<GenericListViewModel<Department>>(
                s, Constants.AllDepartmentsQuery, Constants.DepartmentDetailsRoute));

        builder.Services.AddTransient(
            s => ActivatorUtilities.CreateInstance<GenericListViewModel<EmployeeWithDepartment>>(
                s, Constants.EmployeesAndDepartmentQuery, Constants.EmployeeDetailsRoute));
...
Enter fullscreen mode Exit fullscreen mode

There you have it! Each View will require a ViewModel, which is the same (GenericListViewModel) with different generic type and arguments. Thanks to ActivatorUtilities, we can customize this definition by including the following arguments: a query and a route. The query is used by GetItemsAsync in the ViewModel, while the route is used for navigation.

For the rest of the code, please check the GitHub repository. In this post I'm not including the Views definition (because it is pretty obvious, just bindings) or the DetailsView/DetailsViewModel that I use to add, update, or delete information from the tables. It shouldn't be complicated to understand, but if you do have a question, write a comment and I'll do my best to help :-)

Reminder: Source code available here

Ok, so let's run the app. We can see some information in the Output window (in Visual Studio) as well as in the phone:

First, creating the tables:
Creating tables

Then, counting data, inserting values and retrieving the first department (Kitchen). Finally, get departments data:

Data insertion and validation

And we DO have data in our app!

Department List

What do we get when we click on the menu and access the Employees item? First, this query is executed:

Get Employees and their Department

And the app shows the information from both tables.

Employees and Departments

Well, this was a long post but hopefully, useful for you. You can check the rest of the code to see how to insert, update, and delete. In this case, I also used statements instead of the ORM capabilities (you can use both without any issues).

Remember to follow the rest of the interesting publications of the .NET MAUI Advent Calendar 2022. You can also follow the conversation on Twitter with the hashtag #dotNETMAUIAdvent.

Thanks for reading. Until next time!

Luis

Image
The Transformative Impact of AI in Finance: Key Use Cases
Image
Behind the Words: Crafting My Career Transition Story from Product Manager to QA Engineer for Quality Insights Newsletter

qa, careerdevelopment, personalgrowth, careertransition
Image
Penyiapan Simpel Untuk Buat Account Bandar Judi Online
Image
The Most Common Personal Injury Lawsuits Mistake Every Beginning Personal Injury Lawsuits User Makes
Image
Five Self Propelled Wheelchair With Suspension Projects For Any Budget
Image
kenyataan memikat mengenai slots online
Image
Are Mesothelioma Attorneys Near Me The Best There Ever Was?
Image
pahami mesin slots online untuk dapat main secara lancar
Image
Kenal Model Mesin Judi Online yang Banyak Dipakai Dalam Permainan
Image
pendaftaran simpel judi slots online sah
Image
A Hacktoberfest 2024 Journey: Insights from a First-Time Project Maintainer

devchallenge, hacktoberfestchallenge, hacktoberfest, opensource
Image
Understanding Compiler Passes: A Deep Dive into GCC Compilation

spo
Image
Auto publish NPM packages using changesets and GitHub Actions

webdev, javascript, typescript
Image
20 Great Tweets Of All Time Asbestos Lawsuit Settlement
Image
How To Outsmart Your Boss On Asbestos Cancer Lawsuit Mesothelioma Settlement
Image
My 14-Day Learning Journey with HackQuest

learnwithhq, 14daysoflearning
Image
Cat Flap Installation - How to Choose and Install a Cat Flap
Image
Tehnik Peroleh Bandar Judi Online Aman Dalam Permainan Online
Image
Spokój w Domu: Najskuteczniejsze Sposoby Wyciszania Mieszkań
. . . . . . . . . . . . . . . . . . . . . . . . . . .