Connect to Azure Database for PostgreSQL Using C#

Azure Database for PostgreSQL is a fully managed enterprise-grade PostgreSQL service that ensures advanced performance, security, and scalability for your applications. Whether you're building REST APIs, microservices, or data-heavy apps, PostgreSQL on Azure is a solid choice.

This guide will explain how to connect .NET applications to Azure Database for PostgreSQL and perform essential database operations, such as reading, inserting, updating, and deleting data, using C#.

Why dotConnect for PostgreSQL?

dotConnect for PostgreSQL is a powerful ADO.NET data provider that allows .NET developers to connect directly to PostgreSQL databases in the Azure cloud and interact with the data efficiently. It supports dynamic (on-the-fly) connector creation, flexible configuration options, and integrates with Visual Studio.

dotConnect for PostgreSQL is ideal for .NET specialists who work with Azure Database for PostgreSQL, as it enhances functionality, performance, security, and ease of development.

Prerequisites

The following prerequisites are necessary to follow this guide:

Visual Studio 2022: An integrated development environment. If you don't have it on your machine, you can visit the official website to download and install the free Community Edition.
dotConnect for PostgreSQL: A high-performance data provider for PostgreSQL with enhanced ORM support and database connectivity features.
Azure Database for PostgreSQL: A sample Azure cloud database for PostgreSQL provided for learning and testing.

Download and activate dotConnect for PostgreSQL

Depending on your conditions, you may choose a fully-functional free trial or a full version.

30-day free trial version

You can start using dotConnect for PostgreSQL immediately with a 30-day free trial. Choose one of the following options.

Installer: Download and install dotConnect for PostgreSQL directly on your machine.
NuGet: Add the Devart.Data.PostgreSQL package to your project via NuGet.

No license key is necessary for the trial version — just install it and explore.

Full version

After you've purchased the full version, follow these steps:

1. Go to the Products section in your Devart profile.

2. Click the product name, or hover over it and click Details. The License details page opens. Find the Activation Key there:

License details and the activation key

3. To activate dotConnect for PostgreSQL in your application, copy the Activation Key and paste it into your connection string as the License Key parameter.

Create a .NET project

To illustrate our tutorial, we need a demo application that will connect to the PostgreSQL database hosted in the Azure cloud and perform the necessary data operations. Let us create a simple console application in Visual Studio and install the required NuGet packages for dotConnect.

1. Launch Visual Studio and click Create a new project.

2. Choose Console App (.NET Core) and click Next.

3. Give this project a name (in our tutorial, we name it AzurePostgreSQLTest), specify the path to its folder, and click Create.

4. In the Solution Explorer, right-click the created project and select Manage NuGet Packages.

5. Go to the Browse tab, find and install the Devart.Data.PostgreSql package.

Optionally, you can run the following command in the terminal:

dotnet add package Devart.Data.PostgreSql

Connect to Azure Database for PostgreSQL with Data Explorer

Let us connect to PostgreSQL in Azure with the help of the built-in Data Explorer. Click Tools, then select Connect to Database, and choose PostgreSQL as the data source.

Connect to a PostgreSQL database in the Azure cloud using the Data Explorer

Enter your server details and credentials. Click Connect.

Successful connection to a PostgreSQL database in Azure

Once connected, you can browse tables, execute queries, and manage data directly within the Data Explorer.

View the PostgreSQL data Azure in the Data Explorer

Connect and retrieve data from the database

Let us establish a connection with the Azure Database for PostgreSQL and then run a simple SELECT query to pull records from the Actor table. It confirms the connection is working correctly and the data is accessible.

Create the DatabaseConnection.cs class (connection strings)

The DatabaseConnection.cs class keeps connection details separate for better maintainability.

using Devart.Data.PostgreSql;

public static class DatabaseConnection
{
    public static PgSqlConnection CreateConnection()
    {
        string connectionString = "" +
            "Host=test.postgres.database.azure.com;" +
            "User Id=TestUser;" +
            "Password=TestPassword;" +
            "Port=5432;" +
            "Database=postgres;" +
            "Schema=public;" +
            "License key=**********";
        return new PgSqlConnection(connectionString);
    }
}

Update the connection string

Replace the placeholders in the connection string with your actual PostgreSQL database credentials. If you own a paid license of dotConnect for PostgreSQL, include the license key in the connection strings.

Add the below code to your Program.cs file.

using System;
using Devart.Data.PostgreSql;

class Program
{
    static void Main(string[] args)
    {
        try
        {
            using (PgSqlConnection connection = DatabaseConnection.CreateConnection())
            {
                connection.Open();
                Console.WriteLine("Connection successful!\n");

                string sql = "SELECT actor_id, first_name, last_name FROM actor ORDER BY actor_id LIMIT 10";
                using (PgSqlCommand command = new PgSqlCommand(sql, connection))
                using (PgSqlDataReader reader = command.ExecuteReader())
                {
                    Console.WriteLine("actor_id | first_name | last_name");
                    while (reader.Read())
                    {
                        int actorId = reader.GetInt32(0);
                        string firstName = reader.GetString(1);
                        string lastName = reader.GetString(2);
                        Console.WriteLine($"{actorId} | {firstName} | {lastName}");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("An error occurred: " + ex.Message);
        }
    }
}

Run the application

Build and run your application: select Start from the menu or click F5. It displays the data retrieved from the database.

Get data from the PostgreSQL database in Azure

Insert new records into Azure Database for PostgreSQL

Let's add a new row to the Actor table by executing an INSERT command. Use the below code.

using System;
using Devart.Data.PostgreSql;

class Program
{
    static void Main(string[] args)
    {
        try
        {
            using (PgSqlConnection connection = DatabaseConnection.CreateConnection())
            {
                connection.Open();
                Console.WriteLine("Connection successful!\n");

                // Insert a new actor
                string insertSql = "INSERT INTO actor (first_name, last_name, last_update) VALUES (@first, @last, NOW()) RETURNING actor_id, first_name, last_name";
                using (PgSqlCommand insertCmd = new PgSqlCommand(insertSql, connection))
                {
                    insertCmd.Parameters.AddWithValue("@first", "Emily");
                    insertCmd.Parameters.AddWithValue("@last", "Johnson");

                    using (PgSqlDataReader reader = insertCmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            int actorId = reader.GetInt32(0);
                            string firstName = reader.GetString(1);
                            string lastName = reader.GetString(2);
                            Console.WriteLine("New actor added:");
                            Console.WriteLine($"actor_id | first_name | last_name");
                            Console.WriteLine($"{actorId} | {firstName} | {lastName}");
                        }
                        else
                        {
                            Console.WriteLine("No actor was inserted.");
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("An error occurred: " + ex.Message);
        }
    }
}

See the results in the application:

Successfully inserted record in a PostgreSQL database in Azure

Update rows in Azure Database for PostgreSQL

Next, we want to update an existing record by changing the actor's first and last name. The following code shows how to build and run an UPDATE command from the application to modify data already stored in the database.

using System;
using Devart.Data.PostgreSql;

class Program
{
    static void Main(string[] args)
    {
        try
        {
            using (PgSqlConnection connection = DatabaseConnection.CreateConnection())
            {
                connection.Open();
                Console.WriteLine("Connection successful!\n");

                string updateSql = "UPDATE actor SET first_name = @first, last_name = @last, last_update = NOW() WHERE actor_id = @id";
                using (PgSqlCommand updateCmd = new PgSqlCommand(updateSql, connection))
                {
                    updateCmd.Parameters.AddWithValue("@first", "David");
                    updateCmd.Parameters.AddWithValue("@last", "Miller");
                    updateCmd.Parameters.AddWithValue("@id", 201);

                    int rowsAffected = updateCmd.ExecuteNonQuery();

                    if (rowsAffected > 0)
                    {
                        string selectSql = "SELECT actor_id, first_name, last_name FROM actor WHERE actor_id = @id";
                        using (PgSqlCommand selectCmd = new PgSqlCommand(selectSql, connection))
                        {
                            selectCmd.Parameters.AddWithValue("@id", 201);
                            using (PgSqlDataReader reader = selectCmd.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    int actorId = reader.GetInt32(0);
                                    string firstName = reader.GetString(1);
                                    string lastName = reader.GetString(2);
                                    Console.WriteLine("Updated actor:");
                                    Console.WriteLine("actor_id | first_name | last_name");
                                    Console.WriteLine($"{actorId} | {firstName} | {lastName}");
                                }
                                else
                                {
                                    Console.WriteLine("Actor not found after update.");
                                }
                            }
                        }
                    }
                    else
                    {
                        Console.WriteLine("No actor found with actor_id: 201");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("An error occurred: " + ex.Message);
        }
    }
}

Our application shows us the results:

Successfully updated record in a Azure Database for PostgreSQL

Delete rows from Azure Database for PostgreSQL

Finally, let us see how to delete the record. We are going to remove the actor we have just added to the database using the DELETE command with a condition.

using System;
using Devart.Data.PostgreSql;

class Program
{
    static void Main(string[] args)
    {
        try
        {
            using (PgSqlConnection connection = DatabaseConnection.CreateConnection())
            {
                connection.Open();
                Console.WriteLine("Connection successful!\n");

                string deleteSql = "DELETE FROM actor WHERE actor_id = @id";
                using (PgSqlCommand deleteCmd = new PgSqlCommand(deleteSql, connection))
                {
                    deleteCmd.Parameters.AddWithValue("@id", 201);

                    int rowsAffected = deleteCmd.ExecuteNonQuery();

                    if (rowsAffected > 0)
                    {
                        Console.WriteLine("Deleted actor with actor_id: 201");
                    }
                    else
                    {
                        Console.WriteLine("No actor found with actor_id: 201");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("An error occurred: " + ex.Message);
        }
    }
}

The output in the application proves that the operation was successful.

Successfully deleted record from the PostgreSQL database table in Azure

Conclusion

This tutorial covers the essentials of connecting to and working with databases hosted in Azure Database for PostgreSQL from a .NET application. You can connect, query, insert, update, and delete records.

With these fundamentals in place, you can advance to more complex techniques and build richer, data-driven applications that take full advantage of PostgreSQL's power and flexibility in the cloud. Using dotConnect for PostgreSQL ensures a smooth, secure, and efficient connection experience during the development process.