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();
}