Lesson 17: Local Storage with SQLite

🏗️ Architecture & Data

Local Storage with SQLite

sqlite-net-pcl setup, CRUD operations, async queries.

SQLite Setup

bash

dotnet add package sqlite-net-pcl
dotnet add package SQLitePCLRaw.bundle_green

csharp

public class TodoItem
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public string Title { get; set; }
    public bool Done { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
}

Database Service

csharp

public class DatabaseService
{
    SQLiteAsyncConnection? _db;

    async Task InitAsync()
    {
        if (_db is not null) return;
        var path = Path.Combine(FileSystem.AppDataDirectory, "todos.db");
        _db = new SQLiteAsyncConnection(path);
        await _db.CreateTableAsync<TodoItem>();
    }

    public async Task<List<TodoItem>> GetAllAsync()
    {
        await InitAsync();
        return await _db!.Table<TodoItem>().ToListAsync();
    }

    public async Task<int> SaveAsync(TodoItem item) =>
        item.Id == 0 ? await _db!.InsertAsync(item)
                       : await _db!.UpdateAsync(item);

    public async Task<int> DeleteAsync(TodoItem item) =>
        await _db!.DeleteAsync(item);
}

Key Takeaways

sqlite-net-pcl provides a simple async SQLite API for MAUI

[PrimaryKey, AutoIncrement] defines the SQLite schema via attributes

CreateTableAsync creates the table only if it does not already exist

Initialize lazily on first access using a null check and async Task init