L17 — .NET MAUI pt.5: Full CRUD (Practice)

Goals

Add Insert, Update, Delete methods to StudentRepository
Wire each operation into the ViewModel as a command
Build a UI that lets the user add a new student, edit an existing one, and delete one
Keep the on-screen list in sync with the database after every change

We start from the L16 project (StudentsApp with a working connection + read-only list). If you don't have it, catch up to the end of L16 Task 2 first.

Warm-up

Open your L16 project and run it. Confirm:

The app lists the 3 students from the students table
Adding a row manually via psql and re-running the app shows the new row

If any of that's broken, fix it before continuing — nothing below will work with a broken Read step.

Steps

Step 1 — Repository: `Insert`

Open StudentRepository.cs and add:

public int Insert(string name, double gpa)
{
    using var conn = new NpgsqlConnection(_connectionString);
    conn.Open();

    using var cmd = new NpgsqlCommand(
        "INSERT INTO students (name, gpa) VALUES (@name, @gpa) RETURNING id",
        conn);

    cmd.Parameters.AddWithValue("name", name);
    cmd.Parameters.AddWithValue("gpa", gpa);

    return (int)cmd.ExecuteScalar()!;
}

The RETURNING id clause is PostgreSQL-specific — it asks the server to return the auto-generated primary key so the ViewModel immediately knows the new row's ID.

Expected behaviour: calling repo.Insert("Dave", 3.4) inserts a row and returns the new ID as an int.

💡 Hint

The ! after ExecuteScalar() is the null-forgiving operator. ExecuteScalar is typed as object?; we promise it won't be null here because RETURNING id always produces a row.

Step 2 — Repository: `Update`

public int Update(int id, string name, double gpa)
{
    using var conn = new NpgsqlConnection(_connectionString);
    conn.Open();

    using var cmd = new NpgsqlCommand(
        "UPDATE students SET name = @name, gpa = @gpa WHERE id = @id", conn);

    cmd.Parameters.AddWithValue("id",   id);
    cmd.Parameters.AddWithValue("name", name);
    cmd.Parameters.AddWithValue("gpa",  gpa);

    return cmd.ExecuteNonQuery();   // rows affected: 0 or 1
}

Expected behaviour: Update(2, "Bob Jr.", 3.3) returns 1. Update(9999, ...) returns 0 because no row matched.

Step 3 — Repository: `Delete`

public int Delete(int id)
{
    using var conn = new NpgsqlConnection(_connectionString);
    conn.Open();

    using var cmd = new NpgsqlCommand("DELETE FROM students WHERE id = @id", conn);
    cmd.Parameters.AddWithValue("id", id);
    return cmd.ExecuteNonQuery();
}

Step 4 — ViewModel: expose Add / Update / Delete commands

Open StudentsViewModel.cs. Add three commands and three form properties:

public class StudentsViewModel : BaseViewModel
{
    private readonly StudentRepository _repo;

    public ObservableCollection<Student> Students { get; } = new();

    private Student? _selected;
    public Student? Selected
    {
        get => _selected;
        set
        {
            if (SetField(ref _selected, value) && value != null)
            {
                NewName = value.Name;
                NewGpa  = value.Gpa.ToString("F2");
            }
        }
    }

    private string _newName = "";
    public string NewName { get => _newName; set => SetField(ref _newName, value); }

    private string _newGpa = "";
    public string NewGpa  { get => _newGpa;  set => SetField(ref _newGpa,  value); }

    private string _status = "";
    public string Status  { get => _status;  set => SetField(ref _status,  value); }

    public Command AddCommand    { get; }
    public Command UpdateCommand { get; }
    public Command DeleteCommand { get; }
    public Command ClearCommand  { get; }

    public StudentsViewModel(StudentRepository repo)
    {
        _repo = repo;
        Reload();

        AddCommand    = new Command(OnAdd);
        UpdateCommand = new Command(OnUpdate, () => Selected != null);
        DeleteCommand = new Command(OnDelete, () => Selected != null);
        ClearCommand  = new Command(OnClear);
    }

    private void Reload()
    {
        Students.Clear();
        foreach (var s in _repo.GetAll())
            Students.Add(s);
    }

    private void OnAdd()
    {
        if (!TryParseGpa(out double gpa)) return;
        if (string.IsNullOrWhiteSpace(NewName)) { Status = "Name required"; return; }

        int id = _repo.Insert(NewName, gpa);
        Students.Add(new Student { Id = id, Name = NewName, Gpa = gpa });
        Status = $"Added '{NewName}' (#{id})";
        OnClear();
    }

    private void OnUpdate()
    {
        if (Selected is null) return;
        if (!TryParseGpa(out double gpa)) return;

        int rows = _repo.Update(Selected.Id, NewName, gpa);
        if (rows > 0)
        {
            Selected.Name = NewName;
            Selected.Gpa  = gpa;
            Reload();
            Status = $"Updated #{Selected.Id}";
        }
    }

    private void OnDelete()
    {
        if (Selected is null) return;
        int rows = _repo.Delete(Selected.Id);
        if (rows > 0)
        {
            Status = $"Deleted #{Selected.Id}";
            Reload();
            OnClear();
        }
    }

    private void OnClear()
    {
        Selected = null;
        NewName  = "";
        NewGpa   = "";
    }

    private bool TryParseGpa(out double gpa)
    {
        if (!double.TryParse(NewGpa, out gpa) || gpa < 0 || gpa > 4)
        {
            Status = "GPA must be a number 0.0–4.0";
            return false;
        }
        return true;
    }
}

Expected behaviour: selecting a row copies its values into the form entries; "Add" inserts a new row; "Update" changes the selected row; "Delete" removes it.

💡 Hint

canExecute functions (() => Selected != null) are evaluated only when we ask. Call UpdateCommand.ChangeCanExecute() from the Selected setter if the buttons don't grey/un-grey correctly as you select rows.

Step 5 — UI: form + list with selection

Replace StudentsPage.xaml with:

XML

<ContentPage xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             x:Class="StudentsApp.StudentsPage"
             Title="Students">

    <Grid RowDefinitions="Auto,Auto,*,Auto" Padding="20" RowSpacing="10">

        <!-- Form -->
        <Grid Grid.Row="0"
              ColumnDefinitions="*,120"
              ColumnSpacing="10">
            <Entry Text="{Binding NewName}" Placeholder="Name" Grid.Column="0"/>
            <Entry Text="{Binding NewGpa}"  Placeholder="GPA"  Grid.Column="1"
                   Keyboard="Numeric"/>
        </Grid>

        <!-- Buttons -->
        <HorizontalStackLayout Grid.Row="1" Spacing="8">
            <Button Text="Add"    Command="{Binding AddCommand}"/>
            <Button Text="Update" Command="{Binding UpdateCommand}"/>
            <Button Text="Delete" Command="{Binding DeleteCommand}"
                    BackgroundColor="IndianRed" TextColor="White"/>
            <Button Text="Clear"  Command="{Binding ClearCommand}"/>
        </HorizontalStackLayout>

        <!-- List -->
        <CollectionView Grid.Row="2"
                        ItemsSource="{Binding Students}"
                        SelectionMode="Single"
                        SelectedItem="{Binding Selected}">
            <CollectionView.ItemTemplate>
                <DataTemplate>
                    <Grid Padding="8" ColumnDefinitions="*,Auto">
                        <Label Text="{Binding Name}" Grid.Column="0"/>
                        <Label Text="{Binding Gpa, StringFormat='{0:F2}'}"
                               Grid.Column="1" />
                    </Grid>
                </DataTemplate>
            </CollectionView.ItemTemplate>
        </CollectionView>

        <!-- Status -->
        <Label Grid.Row="3" Text="{Binding Status}" FontAttributes="Italic"/>

    </Grid>
</ContentPage>

Expected behaviour: tapping a row copies its values into the two entries. Buttons update/delete it in the DB. Adding a new name + GPA inserts a row.

Step 6 — Test the full cycle

Run on Windows. Then:

Type Dave / 3.4 → Add. The row appears at the top of the list.
Tap Dave. Change GPA to 3.9. Update. The list row updates.
Tap Dave. Delete. The row disappears.
Close the app. Re-open. Confirm the changes survived (they're in PostgreSQL, not just in memory).

If any step fails, open psql and run SELECT * FROM students; to see what the database actually holds vs what the UI shows.

Bonus Tasks

Bonus 1 — Confirmation dialog on Delete

Before deleting, show a confirmation dialog. MAUI pages expose:

bool ok = await Shell.Current.DisplayAlert(
    "Delete student",
    $"Delete {Selected?.Name}?",
    "Yes", "Cancel");

if (!ok) return;

Move OnDelete to an async void method so you can await the alert. Call it from the Command: DeleteCommand = new Command(async () => await OnDeleteAsync(), ...);

Bonus 2 — Async repository

Convert one repository method (GetAll) to async:

public async Task<List<Student>> GetAllAsync()
{
    var list = new List<Student>();
    await using var conn = new NpgsqlConnection(_connectionString);
    await conn.OpenAsync();

    await using var cmd = new NpgsqlCommand(
        "SELECT id, name, gpa FROM students ORDER BY name", conn);
    await using var reader = await cmd.ExecuteReaderAsync();

    while (await reader.ReadAsync())
    {
        list.Add(new Student
        {
            Id   = reader.GetInt32(0),
            Name = reader.GetString(1),
            Gpa  = reader.GetDouble(2)
        });
    }
    return list;
}

Update the ViewModel to await it. Now the UI stays responsive when the query is slow. This matters on mobile and in the final project.

Bonus 3 — Validation feedback on the form

Highlight the bad entry in red when parsing fails. Add a bool GpaIsInvalid property in the ViewModel and bind the BackgroundColor of the GPA Entry to a converter, or flip a style. Keeps the form honest.