PostgreSQL CRUD Operations in .NET MAUI

.NET MAUI (Multi-platform App UI) is a cross-platform framework for building desktop and mobile applications using C# and XAML. It allows developers to create applications running on Windows, macOS, and Android operating systems, all from one Codebase.

PostgreSQL is a powerful, open-source object-relational database system that uses and extends the SQL language. Being highly flexible and extensible, it is a good choice for data storage and management.

In this tutorial, we'll use the Code-First approach to integrate PostgreSQL with an MAUI application to design and manage a database directly from C# code.

Why dotConnect for PostgreSQL?

dotConnect for PostgreSQL is the ideal data provider for all PostgreSQL-related operations. It offers on-the-fly connector creation and flexible configuration, seamless integration into Visual Studio, and enhanced ORM support.

Download Now dotConnect for PostgreSQL

Requirements

You'll need for this tutorial:

  • Visual Studio 2022: This is our IDE of choice. If you don't have it on your machine, you can visit the official website to download and install the free Community Edition.
  • .NET Multi-platform App UI: A framework used to build native, cross-platform desktop and mobile apps from a single C# codebase for Android, iOS, Mac, and Windows.
  • dotConnect for PostgreSQL: A high-performance data provider for PostgreSQL with enhanced ORM support and database connectivity features.
  • DvdRental sample database: The sample database from the official PostgreSQL documentation.

Set up a PostgreSQL database

To set up a PostgreSQL database, run the following command in the command line: 

psql -U postgres

Enter the password, which you've set while configuring the PostgreSQL server, and create the database by executing this code:

CREATE DATABASE devart_test;

Download and activate dotConnect for PostgreSQL

Download the desired version of dotConnect for PostgreSQL: you can select the free trial or the full version. The first one gives you 30 days to explore all features, but it cannot be used for deploying commercial projects and does not require a license key for activation. If you're ready to purchase, click Buy Now to add the full version to your cart.

Download and install the trial version

Just download and install dotConnect for PostgreSQL on your machine. No license key is required and you can start exploring the product immediately.

Purchase and access the full version

After buying the full version, go to your profile's Licenses page. You'll find a list of your licenses, including your dotConnect for PostgreSQL purchase. Click Details next to your dotConnect for PostgreSQL license. Here, you'll find your license details with the Activation Key.

Purchase and access the full version

Download the full version

To access the list of available product versions, click Download Product Versions. Select the most recent version and download the installer. Execute the downloaded file and follow the on-screen instructions to install dotConnect for PostgreSQL on your machine. This process will install the software and activate all features associated with your license key.

Download the full version

Create a .NET MAUI project

To create the project, in Visual Studio, click Create a new project and, in the Search Template field, find .NET MAUI App, and click it.

Create a .NET MAUI project

Name your project, for example, PostgreSQLMAUI, then select .NET 8 as the target framework, and click Create.

Install packages

Next, we'll install dotConnect for PostgreSQL to the project via the NuGet Package manager in Visual Studio. In the taskbar, navigate to Tools > NuGet Package Manager and click Manage NuGet Packages for Solution.

Package Manager

The NuGet Package Manager page opens. Click Browse and search Devart.Data.PostgreSQL. Then select your project and
click Install.

Slect ypur project

When you run the application by pressing F5, the scaffolded application will appear.

Set up the project

Next, create a new page where we'll do most of our work. For this, right-click the solution PostgreSQLMAUI and select Add New Item. After that click the template .NET MAUI ContentPage(XAML), name this page PostgreSQLDemo.xaml,
and click Add.

In the Solution Explorer, click the MainPage.xaml file and modify it so it looks like this.

<ScrollView>
        <VerticalStackLayout
            Padding="30,0"
            Spacing="25">
            <Image
                Source="dotnet_bot.png"
                HeightRequest="185"
                Aspect="AspectFit"
                SemanticProperties.Description="dot net bot in a race car number eight" />

            <Label
                Text="Hello, World!"
                Style="{StaticResource Headline}"
                SemanticProperties.HeadingLevel="Level1" />

            <Label
  Text="Welcome to 
.NET Multi-platform App UI"
                Style="{StaticResource SubHeadline}"
                SemanticProperties.HeadingLevel="Level2"
                SemanticProperties.Description="Welcome to dot net Multi platform App U I" />

            <Button
                x:Name="CounterBtn"
                Text="Click me"
                SemanticProperties.Hint="Counts the number of times you click"
                Clicked="OnCounterClicked"
                HorizontalOptions="Fill" />

            <Button Text="Go to Actor Page"
            Clicked="OnNavigateButtonClicked"
            VerticalOptions="Center"
            HorizontalOptions="Center" />
        </VerticalStackLayout>
    </ScrollView>

We added a new button called Go to Actor Page, which will navigate to the created page. The final step is to open Code-behind MainPage.xaml.cs and add the OnNavigateButtonClicked method. Right-click MainPage.xaml in the Solution Explorer and select View Code.

Add this piece of code to the MainPage class.

private async void OnNavigateButtonClicked(object sender, EventArgs e)
        {
            await Navigation.PushAsync(new PostgreSQLDemo());
        }

As a result, your MainPage.xaml.cs should look as follows.

MainPage file

When you run the application and click Go to Actor page, you'll see the newly created page.

Create a connection instance

To create the table, which we're going to work with, add this code to DatabaseConnectionClass.

public static class DatabaseConnectionClass
{
    public static string databasePath = "" +
    "Server=127.0.0.1;" +
    "UserId=postgres;" +
    "Password=test;" +
    "Port=5432;" +
    "Database=devart_test;" +
    "Schema=public;";

    public static void CreateActorTable()
    {

        PgSqlConnection conn = new PgSqlConnection($"{databasePath};LicenseKey={licenseKey.yourLicensekey};");
        PgSqlCommand cmd = new PgSqlCommand();
        cmd.CommandText = @"CREATE TABLE actor (
        actor_id SERIAL PRIMARY KEY,
        first_name VARCHAR(45) NOT NULL,
        last_name VARCHAR(45) NOT NULL,
        last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );";
        cmd.Connection = conn;
        conn.Open();
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine($"An error occured; {ex.Message}");
        }
    }
}

Thus, we added the method required to create the Actor table. In the PostgreSQL connection string, we specified the database location and the dotConnect for the PostgreSQL activation key (defined in another class).

Next, in PostgreSQLDemo.xaml.cs, simply add the following code for the method to be called when the page opens. 

public partial class PostgreSQLDemo : ContentPage
{
	public PostgreSQLDemo()
	{
		InitializeComponent();
        DatabaseConnectionClass.CreateActorTable();
    }
}

Run the application and click Go to Actor page. The method will be called and the table will be created. Since the table is ready, you can comment out the CreateActorTable method.

Insert PostgreSQL data

Let's transfer some data from the application into the table. First, create a class based on the created table. Right-click your solution and add the Actor.cs class.

Paste this code inside the class. 

 public class Actor
    {
        public int ActorId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime LastUpdate { get; set; }
    }

Next, in DatabaseConnectionClass, add a new method.

public static void AddActor(Actor actor)
{
    PgSqlConnection conn = new PgSqlConnection($"{databasePath};LicenseKey={licenseKey.yourLicensekey};");
    PgSqlCommand cmd = new PgSqlCommand();
    cmd.CommandText = "INSERT INTO actor (first_name, last_name, last_update) VALUES (@first_name, @last_name, @last_update);";
    cmd.Parameters.Add(new PgSqlParameter("@first_name", actor.FirstName));
    cmd.Parameters.Add(new PgSqlParameter("@last_name", actor.LastName));
    cmd.Parameters.Add(new PgSqlParameter("@last_update", actor.LastUpdate));
    cmd.Connection = conn;
    conn.Open();
    try
    {
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
      Console.WriteLine($"An error occured; {ex.Message}");
    }
}

Let's configure our applications view. For this, edit PostgreSQLDemo.xaml so it looks like this. 

<ContentPage xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             x:Class="PostgreSQLMAUI.PostgreSQLDemo"
             Title="PostgreSQLDemo">
    <StackLayout>
    <StackLayout x:Name="InsertSection" IsVisible="False" Padding="10">
        <Label Text="First Name:" />
        <Entry x:Name="InsertFirstNameEntry" Placeholder="Enter first name" />
        <Label Text="Second Name:" />
        <Entry x:Name="InsertSecondNameEntry" Placeholder="Enter second name" />
        <Label x:Name="InsertErrorLabel" TextColor="Red" IsVisible="False" />
        <Button Text="Insert" Clicked="OnInsertButtonClicked" WidthRequest="80" />
    </StackLayout>
    <Button x:Name="InsertActor" Text="Insert New Actor" Clicked="OnShowInsertSectionClicked" WidthRequest="150" />
    </StackLayout>
</ContentPage>

Then, in PostgreSQLDemo.xaml.cs, insert these two methods inside the PostgreSQLDemo class. 

private void OnShowInsertSectionClicked(object sender, EventArgs e)
    {
        InsertFirstNameEntry.Text = string.Empty;
        InsertSecondNameEntry.Text = string.Empty;

        // Show the insert section and hide the Insert actor button
        InsertSection.IsVisible = true;
        InsertActor.IsVisible = false;
    }
    private void OnInsertButtonClicked(object sender, EventArgs e)
    {
        if (string.IsNullOrWhiteSpace(InsertFirstNameEntry.Text) ||
               string.IsNullOrWhiteSpace(InsertSecondNameEntry.Text))
        {
            // Show error message
            InsertErrorLabel.Text = "Both fields are required.";
            InsertErrorLabel.IsVisible = true;
            return;
        }
        // Create a new actor object with the inserted values
        var newActor = new Actor
        {
            FirstName = InsertFirstNameEntry.Text,
            LastName = InsertSecondNameEntry.Text,
            LastUpdate = DateTime.Now
        };
        // Add the new actor to the database
        DatabaseConnectionClass.AddActor(newActor);

        // Refresh the CollectionView
        BindingContext = null;
        BindingContext = this;

        // Hide the insert section
        InsertSection.IsVisible = false;
    }

Now, run the code and click Go to Actor page.

Insert button

Click Insert New Actor, enter the actor's first and last names, and click Insert.

Click Insert

Read PostgreSQL data

We need to verify the inserted record. In DataBaseConnectionClass, add this new method to get a list of all actors.

public static List GetActors()
 {
     List actors = new List();
     PgSqlConnection conn = new PgSqlConnection($"{databasePath};LicenseKey={licenseKey.yourLicensekey};");
     PgSqlCommand cmd = new();
     cmd.CommandText = "SELECT * FROM actor;";
     cmd.Connection = conn;
     conn.Open();
     PgSqlDataReader reader = cmd.ExecuteReader();
     while (reader.Read())
     {
         Actor actor = new Actor
         {
             ActorId = Convert.ToInt32(reader["actor_id"]),
             FirstName = reader["first_name"].ToString(),
             LastName = reader["last_name"].ToString(),
             LastUpdate = Convert.ToDateTime(reader["last_update"])
         };
         actors.Add(actor);
     }
     return actors;
 }

Edit PostgreSQLDemo.xaml so it looks as follows.

<ContentPage xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             x:Class="PostgreSQLMAUI.PostgreSQLDemo"
             Title="PostgreSQLDemo">
    <StackLayout>
        <CollectionView ItemsSource="{Binding Actors}">
            <CollectionView.ItemTemplate>
                <DataTemplate>
                    <Grid Padding="2" RowDefinitions="Auto">
                        <Grid.ColumnDefinitions>
                            <ColumnDefinition Width="50" />
                            <ColumnDefinition Width="100" />
                            <ColumnDefinition Width="100" />
                            <ColumnDefinition Width="100" />
                        </Grid.ColumnDefinitions>

                        <Label Text="{Binding ActorId}" 
                         Grid.Column="0"                         
                         VerticalOptions="Center"
                         HorizontalOptions="Center"
                         Padding="3" 
                         Margin="0.5" />


                        <Label Text="{Binding FirstName}" 
                         Grid.Column="1" 
                         VerticalOptions="Center"
                         HorizontalOptions="Center"
                         Padding="3" 
                         Margin="0.5" />

                        <Label Text="{Binding LastName}" 
                         Grid.Column="2" 
                         VerticalOptions="Center"
                         HorizontalOptions="Center"
                         Padding="3" 
                         Margin="0.5" />

                        <Label Text="{Binding LastUpdate}" 
                        Grid.Column="3" 
                        VerticalOptions="Center"
                        HorizontalOptions="Center"
                        Padding="3" 
                        Margin="0.5" />
                    </Grid>

                </DataTemplate>
            </CollectionView.ItemTemplate>
        </CollectionView>

        <StackLayout x:Name="InsertSection" IsVisible="False" Padding="10">
            <Label Text="First Name:" />
            <Entry x:Name="InsertFirstNameEntry" Placeholder="Enter first name" />
            <Label Text="Second Name:" />
            <Entry x:Name="InsertSecondNameEntry" Placeholder="Enter second name" />
            <Label x:Name="InsertErrorLabel" TextColor="Red" IsVisible="False" />
            <Button Text="Insert" Clicked="OnInsertButtonClicked" WidthRequest="80" />
        </StackLayout>
        <Button x:Name="InsertActor" Text="Insert New Actor" Clicked="OnShowInsertSectionClicked" WidthRequest="150" />
    </StackLayout>

</ContentPage>

Then, in the PostgreSQLDemo.xaml.cs file, add a new property to retrieve the list of actors from the database and bound to the UI.

public partial class PostgreSQLDemo : ContentPage
{
    public List Actors { get; set; }
    public PostgreSQLDemo()
    {
        InitializeComponent();
        //DatabaseConnectionClass.CreateActorTable();
        Actors = DatabaseConnectionClass.GetActors();
        // Set the BindingContext
        BindingContext = this;
    }

.....
}

If you run the application and navigate to PostgreSQLDemo, you'll see the data.

Check data

Update PostgreSQL data

Assume Seth wants to change his last name. The application needs to cope with the challenge. For this, in DataBaseConnectionClass, add a new method called UpdateActor.

public static void UpdateActor(Actor actor)
        {
            PgSqlConnection conn = new PgSqlConnection($"{databasePath};LicenseKey={licenseKey.yourLicensekey};");
            PgSqlCommand cmd = new PgSqlCommand();
            cmd.CommandText = "UPDATE actor SET first_name = @first_name, last_name = @last_name, last_update = @last_update WHERE actor_id = @actor_id;";
            cmd.Parameters.Add(new PgSqlParameter("@first_name", actor.FirstName));
            cmd.Parameters.Add(new PgSqlParameter("@last_name", actor.LastName));
            cmd.Parameters.Add(new PgSqlParameter("@last_update", actor.LastUpdate));
            cmd.Parameters.Add(new PgSqlParameter("@actor_id", actor.ActorId));
            cmd.Connection = conn;
            conn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occured; {ex.Message}");
            }
        }

Next, in the PostgreSQLDemo.xaml file, replace the code with this one. 

<ContentPage xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             x:Class="PostgreSQLMAUI.PostgreSQLDemo"
             Title="PostgreSQLDemo">
    <StackLayout>
        <CollectionView ItemsSource="{Binding Actors}">
            <CollectionView.ItemTemplate>
                <DataTemplate>
                    <Grid Padding="2" RowDefinitions="Auto">
                        <Grid.ColumnDefinitions>
                            <ColumnDefinition Width="50" />
                            <ColumnDefinition Width="100" />
                            <ColumnDefinition Width="100" />
                            <ColumnDefinition Width="100" />
                            <ColumnDefinition Width="Auto" />
                        </Grid.ColumnDefinitions>

                        <Label Text="{Binding ActorId}" 
                         Grid.Column="0"                         
                         VerticalOptions="Center"
                         HorizontalOptions="Center"
                         Padding="3" 
                         Margin="0.5" />

                        <Label Text="{Binding FirstName}" 
                         Grid.Column="1" 
                         VerticalOptions="Center"
                         HorizontalOptions="Center"
                         Padding="3" 
                         Margin="0.5" />

                        <Label Text="{Binding LastName}" 
                         Grid.Column="2" 
                         VerticalOptions="Center"
                         HorizontalOptions="Center"
                         Padding="3" 
                         Margin="0.5" />

                        <Label Text="{Binding LastUpdate}" 
                        Grid.Column="3" 
                        VerticalOptions="Center"
                        HorizontalOptions="Center"
                        Padding="3" 
                        Margin="0.5" />

                        <Button Text="Update" 
                        Grid.Column="4" 
                        VerticalOptions="Center"
                        HorizontalOptions="Center"
                        Clicked="OnUpdateButtonClicked" 
                        CommandParameter="{Binding ActorId}" />
                    </Grid>

                </DataTemplate>
            </CollectionView.ItemTemplate>
        </CollectionView>

        <StackLayout x:Name="UpdateSection" IsVisible="False" Padding="10">
            <Label Text="Update First Name:" />
            <Entry x:Name="UpdateFirstNameEntry" Placeholder="Enter new first name" />
            <Label Text="Update Second Name:" />
            <Entry x:Name="UpdateSecondNameEntry" Placeholder="Enter new Last name" />
            <Label x:Name="UpdateErrorLabel" TextColor="Red" IsVisible="False" />
            <Button Text="Go" Clicked="OnGoButtonClicked" />
        </StackLayout>

        <StackLayout x:Name="InsertSection" IsVisible="False" Padding="10">
            <Label Text="First Name:" />
            <Entry x:Name="InsertFirstNameEntry" Placeholder="Enter first name" />
            <Label Text="Second Name:" />
            <Entry x:Name="InsertSecondNameEntry" Placeholder="Enter second name" />
            <Label x:Name="InsertErrorLabel" TextColor="Red" IsVisible="False" />
            <Button Text="Insert" Clicked="OnInsertButtonClicked" WidthRequest="80" />
        </StackLayout>
        <Button x:Name="InsertActor" Text="Insert New Actor" Clicked="OnShowInsertSectionClicked" WidthRequest="150" />
    </StackLayout>
</ContentPage>

Thus, we added the Update button and the section for inserting the modified values.

Now, in PostSQLDemo.xaml.cs, add a new property.

private Actor? _selectedActor;

And add two new methods: OnUpdateButtonClicked and OnGoButtonClicked. 

private void OnUpdateButtonClicked(object sender, EventArgs e)
    {
        var button = sender as Button;
        var actorId = (int)button.CommandParameter;

        // Find the actor in the list by ID
        _selectedActor = Actors.FirstOrDefault(p => p.ActorId == actorId);

        if (_selectedActor != null)
        {
            // Populate Entry fields with existing values

            UpdateFirstNameEntry.Text = _selectedActor.FirstName;
            UpdateSecondNameEntry.Text = _selectedActor.LastName;

            // Show the update section
            UpdateSection.IsVisible = true;
        }
    }
    private void OnGoButtonClicked(object sender, EventArgs e)
    {
        if (string.IsNullOrWhiteSpace(UpdateFirstNameEntry.Text) ||
               string.IsNullOrWhiteSpace(UpdateSecondNameEntry.Text))
        {
            // Show error message
            UpdateErrorLabel.Text = "Both fields are required.";
            UpdateErrorLabel.IsVisible = true;
            return;
        }

        if (_selectedActor != null)
        {
            // Create a new actor object with the updated values
            var updatedActor = new Actor
            {
                ActorId = _selectedActor.ActorId,
                FirstName = UpdateFirstNameEntry.Text,
                LastName = UpdateSecondNameEntry.Text,
                LastUpdate = DateTime.Now
            };

            // Update the actor in the database
            DatabaseConnectionClass.UpdateActor(updatedActor);

            // Hide the update section
            UpdateSection.IsVisible = false;
        }
    }

To check the button, run the code.

Check the button

If you click the button, the insert modal will be added to input new values.

Add the insert modal

The records will be updated after clicking Go.

Updated records

Delete PostgreSQL data

Let's try to remove an actor from the database. For reference, we added a new actor, Johnny Blaze, to be deleted.

New actor

In DataBaseConnectionClass, add the DeleteActor method. 

public static void DeleteActor(int actorId)
        {
            PgSqlConnection conn = new PgSqlConnection($"{databasePath};LicenseKey={licenseKey.yourLicensekey};");
            PgSqlCommand cmd = new PgSqlCommand();
            cmd.CommandText = "DELETE FROM actor WHERE actor_id = @actor_id;";
            cmd.Parameters.Add(new PgSqlParameter("@actor_id", actorId));
            cmd.Connection = conn;
            conn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occured; {ex.Message}");
            }
        }

Then, in PostgreSQLDemo.xaml, add a new button under <Button Text="Update". 

<Button Text="Delete" 
 Grid.Column="5" 
 VerticalOptions="Center"
 HorizontalOptions="Center"
 Clicked="OnDeleteButtonClicked" 
 CommandParameter="{Binding ActorId}" />

And insert a new column definition in <Grid.ColumnDefinitions>. 

<ColumnDefinition Width="Auto" />

Then, in PostgreSQLDemo.xaml.cs, add the method for OnDeleteButtonClicked.

private void OnDeleteButtonClicked(object sender, EventArgs e)
    {
        var button = sender as Button;
        var actorId = (int)button.CommandParameter;

        // Find the actor in the database by ID
        DatabaseConnectionClass.DeleteActor(actorId);

        this.Actors = DatabaseConnectionClass.GetActors();

        BindingContext = null;
        BindingContext = this;
    }

Run the application to view the button.

Check the button

To delete Johnny Blaze's record, click the corresponding button.

Delete the record

Video tutorial: How to Connect a .NET MAUI Application to a PostgreSQL Database

Conclusion

In this tutorial, we explored how to perform basic CRUD (Create, Read, Update, Delete) operations with PostgreSQL in the .NET MAUI application. As .NET MAUI continues to evolve as a versatile framework for building modern apps, it's useful to understand how to work with databases like PostgreSQL. Provided examples and code snippets can help integrate PostgreSQL into .NET MAUI projects and enable efficient data management in both local and remote environments.

dotConnect for PostgreSQL

Get enhanced ORM-enabled data provider for PostgreSQL and develop .NET applications working with PostgreSQL data quickly and easily!

Download

We use cookies to provide you with a better experience on the Devart website. You can read more about our use of cookies in our Cookies Policy.
Click OK to continue browsing the Devart site. Be aware you can disable cookies at any time.