Add SQLite to the MAUI application

sql sqlite wallpaper
sql sqlite wallpaper

In this new post, I show you how to add SQLite to the MAUI application in order to have a small database in your mobile applications.

Add NuGet packages

First of all, we need to install NuGet packages. Here the packages we have to install for using SQLite in the MAUI applications:

<PackageReference Include="sqlite-net-pcl" Version="1.8.116" />
<PackageReference Include="SQLitePCLRaw.core" Version="2.1.0" />
<PackageReference Include="SQLitePCLRaw.bundle_green" Version="2.1.0" />
<PackageReference Include="SQLitePCLRaw.provider.dynamic_cdecl" Version="2.1.0" />
<PackageReference Include="SQLitePCLRaw.provider.sqlite3" Version="2.1.0" />

File Access Helper

Now, we have to create a helper to locate the database. This is the class

public class FileAccessHelper
{
    public static string GetLocalFilePath(string filename)
    {
        return System.IO.Path.Combine(FileSystem.AppDataDirectory, filename);
    }
}

After that, in the MauiProgram.cs, add the following (highlighted) line

public static class MauiProgram
{
	public static MauiApp CreateMauiApp()
	{
		var builder = MauiApp.CreateBuilder();
		builder
			.UseMauiApp<App>()
			.ConfigureFonts(fonts =>
			{
				fonts.AddFont("OpenSans-Regular.ttf", "OpenSansRegular");
				fonts.AddFont("OpenSans-Semibold.ttf", "OpenSansSemibold");
			});

		string dbPath = FileAccessHelper.GetLocalFilePath("my.db");
		builder.Services.AddSingleton<Repository>(
			s => ActivatorUtilities.CreateInstance<Repository>(s, dbPath));

		return builder.Build();
	}
}

Create a repository

Then create repository class as in the following code:

private readonly SQLiteAsyncConnection _database;
public string StatusMessage { get; set; }

public Repository()
{
_dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "my.db");
}

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

    _db = new SQLiteAsyncConnection(_dbPath);

    await _db.CreateTableAsync<MyEntity>();
}

public List<MyEntity> List()
{
    try
    {
        await Init();
        return await _db.Table<MyEntity>().ToListAsync();
    }
    catch (Exception ex)
    {
        StatusMessage = $"Failed to retrieve data. {ex.Message}";
    }

    return new List<MyEntity>();
}

public int Create(MyEntity entity)
{
    int result = 0;

    try
    {
        await Init();

        if (string.IsNullOrEmpty(entity.Address))
            throw new Exception("Valid address required");

        result = await _db.InsertAsync(entity);

        StatusMessage = string.Format("{0} record(s) added [Name: {1})", result, entity.Address);
    }
    catch (Exception ex)
    {
        StatusMessage = string.Format("Failed to add {0}. Error: {1}", entity.Address, ex.Message);
    }
}

public int Update(MyEntity entity)
{
    return _database.Update(entity);
}

public int Delete(MyEntity entity)
{
    return _database.Delete(entity);
}

How to use it

So far, I am following the Microsoft recommendation. The next step is to change the App.xaml.cs to inject the repository

public partial class App : Application
{
	public static Repository Repo { get; private set; }

	public App(Repository repo)
	{
		InitializeComponent();

		MainPage = new AppShell();

		Repo = repo;
	}
}

When in a page you have to use the repo, you should refer to the Repo in the App like that

await App.Repo.Create(new MyEntity() { ... });
StatusMessage.Text = App.HomeRepo.StatusMessage;

Another way is to initialize the repository in the page or viewmodel you want like in the following example:

private readonly Repository repository;

public MainPage()
{
    repository = new Repository();
    InitializeComponent();
}

protected override void OnAppearing()
{
    base.OnAppearing();
    GetEntities();
}

private void GetEntities()
{
    collectionView.ItemsSource = repository.List();
}

Important part for iOS

For iOS/MacCatalyst we need to set the SQLite provider. We can do it in AppDelegate:

protected override MauiApp CreateMauiApp()
{
    raw.SetProvider(new SQLite3Provider_sqlite3());
    return MauiProgram.CreateMauiApp();
}

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.