L16 — .NET MAUI pt.4: Connecting to PostgreSQL

🎯Learning Objectives

• Install and reference the Npgsql NuGet package in a MAUI project
• Build a PostgreSQL connection string and understand each part of it
• Open and close a connection safely using using
• Run a SELECT query and read results with NpgsqlCommand + NpgsqlDataReader
• Move the database code into a repository class used by a ViewModel

📖Theory

1. Why a Database?

Until now our app forgot everything the moment we closed it. For anything real — a student roster, a to-do list, a product catalogue — data must live outside the process. A database is software designed for exactly this: structured, persistent, concurrent storage.

We use PostgreSQL, the same engine you learned in the PostgreSQL track. Our MAUI app will connect to a PostgreSQL server running on the same machine (or on the college LAN) and read/write rows over the network.

2. The Bridge — Npgsql

C# doesn't know how to speak PostgreSQL out of the box. We need a driver. For PostgreSQL the standard driver is Npgsql, a free open-source library.

Install it through NuGet:

1. In Visual Studio: right-click the project → Manage NuGet Packages
2. Switch to Browse, search for Npgsql
3. Install the latest stable version

This adds a line to your .csproj:

<ItemGroup>
    <PackageReference Include="Npgsql" Version="8.0.*" />
</ItemGroup>

Npgsql follows the standard ADO.NET pattern — the same four types you'll use with any database:

• NpgsqlConnection — a network link to the server
• NpgsqlCommand — a SQL statement ready to execute
• NpgsqlDataReader — a forward-only stream of result rows
• NpgsqlParameter — a typed, safe way to inject values into SQL

3. Connection Strings

A connection string is one line that tells Npgsql where the server is and who you are:

4. Opening a Connection — using

A connection is a scarce resource. You must close it when you're done. The using statement handles that automatically — the connection closes when the block ends, even if an exception is thrown.

using Npgsql;

string connectionString =
    "Host=localhost;Port=5432;Database=school_app;Username=postgres;Password=pass";

using var conn = new NpgsqlConnection(connectionString);
conn.Open();

// ... do database work here ...

// Automatically closed at the end of the block

The modern using var conn = ...; syntax (without braces) scopes the using to the rest of the method. It's shorter and very common.

5. Running a Query

Here's reading the current count of students:

using var conn = new NpgsqlConnection(connectionString);
conn.Open();

using var cmd = new NpgsqlCommand("SELECT COUNT(*) FROM students", conn);
long count = (long)cmd.ExecuteScalar()!;

Console.WriteLine($"There are {count} students.");

• ExecuteScalar — run the query and return the first column of the first row.
• ExecuteNonQuery — run INSERT / UPDATE / DELETE; returns rows affected.
• ExecuteReader — run a SELECT; returns rows you loop through.

6. Reading Multiple Rows

To read many rows back as C# objects, loop through an NpgsqlDataReader:

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public double Gpa { get; set; }
}

List<Student> GetAllStudents()
{
    var list = new List<Student>();

    using var conn = new NpgsqlConnection(connectionString);
    conn.Open();

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

    using var reader = cmd.ExecuteReader();

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

    return list;
}

The reader is forward-only: once you've read a row, you can't go back. Each reader.Read() advances to the next row and returns false when there are no more.

7. Parameters — Never Concatenate SQL

To pass user input into SQL, always use parameters, never string concatenation:

// ❌ DANGEROUS — SQL injection waiting to happen
string badSql = $"SELECT * FROM students WHERE name = '{userInput}'";

// ✅ Safe
using var cmd = new NpgsqlCommand(
    "SELECT id, name FROM students WHERE name = @name", conn);
cmd.Parameters.AddWithValue("name", userInput);

Parameters are separate from the SQL text, so a malicious user typing '; DROP TABLE students;-- ends up as a literal search string, not code.

8. Putting It Behind a Repository

Don't let your ViewModel call Npgsql directly. Wrap the database code in a repository class — a narrow C# API that the rest of the app talks to.

public class StudentRepository
{
    private readonly string _connectionString;

    public StudentRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public List<Student> GetAll()
    {
        var list = new List<Student>();

        using var conn = new NpgsqlConnection(_connectionString);
        conn.Open();

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

        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            list.Add(new Student
            {
                Id   = reader.GetInt32(0),
                Name = reader.GetString(1),
                Gpa  = reader.GetDouble(2)
            });
        }
        return list;
    }

    public long Count()
    {
        using var conn = new NpgsqlConnection(_connectionString);
        conn.Open();
        using var cmd = new NpgsqlCommand("SELECT COUNT(*) FROM students", conn);
        return (long)cmd.ExecuteScalar()!;
    }
}

The ViewModel becomes clean again:

public class StudentsViewModel : BaseViewModel
{
    private readonly StudentRepository _repo;
    public ObservableCollection<Student> Students { get; } = new();

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

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

Next lesson (L17) we'll add Insert/Update/Delete to the repository and build a full CRUD screen around it.

💻Code Examples

Example A — Connection test page

SQL to run once in psql or pgAdmin:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    gpa DOUBLE PRECISION NOT NULL CHECK (gpa BETWEEN 0 AND 4)
);

INSERT INTO students (name, gpa) VALUES
    ('Alice', 3.8),
    ('Bob',   3.2),
    ('Carol', 3.9);

MainPage.xaml:

<VerticalStackLayout Padding="20" Spacing="10">
    <Button Text="Test connection" Clicked="OnTestClicked" />
    <Label x:Name="ResultLabel" FontSize="16" />
</VerticalStackLayout>

MainPage.xaml.cs:

private const string ConnectionString =
    "Host=localhost;Port=5432;Database=school_app;Username=postgres;Password=pass";

private void OnTestClicked(object sender, EventArgs e)
{
    try
    {
        using var conn = new NpgsqlConnection(ConnectionString);
        conn.Open();

        using var cmd = new NpgsqlCommand("SELECT COUNT(*) FROM students", conn);
        long count = (long)cmd.ExecuteScalar()!;

        ResultLabel.Text = $"✅ Connected. {count} students in the database.";
    }
    catch (Exception ex)
    {
        ResultLabel.Text = $"❌ {ex.Message}";
    }
}

Example B — List students via repository + VM

StudentsPage.xaml:

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

    <VerticalStackLayout Padding="20" Spacing="10">
        <Label Text="{Binding Title}" FontSize="22" />
        <CollectionView ItemsSource="{Binding Students}">
            <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>
    </VerticalStackLayout>
</ContentPage>

StudentsPage.xaml.cs:

public partial class StudentsPage : ContentPage
{
    public StudentsPage()
    {
        InitializeComponent();

        string cs = "Host=localhost;Port=5432;Database=school_app;Username=postgres;Password=pass";
        BindingContext = new StudentsViewModel(new StudentRepository(cs));
    }
}

✏️Practice Tasks

⚠️Common Mistakes

Hard-coding credentials and committing them

The moment you push a password to GitHub, it's public. For homework it's fine; for anything serious, use dotnet user-secrets or environment variables.

Forgetting using on connections

A leaked NpgsqlConnection keeps the socket open until the garbage collector runs. After a few dozen leaks the server refuses new connections with "too many clients". Always wrap connections in using.

Concatenating user input into SQL

This is the #1 real-world security bug for beginners. Always pass values through Parameters.AddWithValue (or Parameters.Add(...) for precise typing).

Mis-typing column names or orders in GetInt32(0)

If the column at index 0 is actually name, you'll get InvalidCastException. Either keep SELECT id, name, gpa FROM ... and reference by position, or use reader.GetOrdinal("id") to look up by name and survive SELECT *.

Treating a DataReader like a list

The reader is a live cursor over the server. Don't save references to it, don't skip reader.Read(), and always close it (the using handles that). Read everything into a List<T> before leaving the method.

🎓Instructor Notes

⚡ How to run this lesson (~80 min)

• [5 min] Why a database. Close the L15 counter app. Reopen it. State is gone. Point at the problem.
• [10 min] Install Npgsql live. Show the NuGet UI on the projector. Pause to let students install theirs — common pitfall is selecting the Preview version; stick to Stable.
• [5 min] Connection string anatomy. Board diagram; emphasise Host, Port, Database, credentials.
• [15 min] Live code: using + ExecuteScalar (connection test). This is the first time students see C# talking to a real server. Expect awe + broken passwords.
• [20 min] Live code: reader loop + repository class. Build StudentRepository.GetAll. Then plug it into a ViewModel + CollectionView. Show the round trip: database row → C# object → screen.
• [15 min] Parameters + SQL injection demo. Demonstrate a concatenated query that "works". Then demonstrate the same query with a malicious input that breaks it. Then fix with AddWithValue. This demo is memorable and they'll remember it for the rest of their career.
• [20 min] Task 1 in class. Mostly environmental issues: wrong password, service not running, wrong database name. Keep a troubleshooting list on the projector.
• [5 min] Wrap up + assign homework. Preview L17: we make the list editable (add, update, delete). Assign Tasks 2 and 3.

💬 Discussion questions

• "Why do we wrap the database code in a repository instead of calling Npgsql from the ViewModel directly?"
• "What's the actual mechanism that makes a parameterised query safe from SQL injection?"
• "If you had 10,000 students in the table and called GetAll(), what would happen? How would you fix it?"