Skip to main content

C# to PostgreSQL

Introduction​

This learning material will guide you through the process of connecting a C# application to a PostgreSQL database.

caution

In this material, we assume that you have a PostgreSQL database installed on your machine. If you don't, consider reading the PostgreSQL learning material.

Prerequisites​

  • A code editor (Visual Studio Code, NeoVim, etc.)
  • .NET 6.0 or later

Step 1: Set up the project​

We will start by creating a new C# basic console application.

dotnet new console -n MyPostgresApp
cd MyPostgresApp

Step 2: Add the Npgsql package​

We will use the Npgsql package to connect to the PostgreSQL database.

dotnet add package Npgsql

Step 3: Create the database content​

(Skip this step if you already have a database created with an init file for example.)

We start by entering via the terminal the PostgreSQL database. Consider replacing postgres with the username you used to create the database.

psql -h localhost -U postgres

Then, create a new database and a table.

CREATE DATABASE mydatabase;

Move to the new database.

\c mydatabase

Create a new table.

CREATE TABLE customer (id SERIAL PRIMARY KEY, name VARCHAR(50));

Step 4: Connect to the database​

Open the file named Program.cs and add the following code.

using System;
using Npgsql;

class Program
{
    static void Main()
    {
        var cs = "Host=localhost;Username=postgres;Password=your_password;Database=mydatabase";
        using var con = new NpgsqlConnection(cs);
        con.Open();

        using var cmd = new NpgsqlCommand("SELECT version()", con);
        var version = cmd.ExecuteScalar().ToString();
        Console.WriteLine($"PostgreSQL version: {version}");
    }
}
caution

Replace your_password with the password you used to create the database.

Step 5: Run the application​

Run the application.

dotnet run

You should see the PostgreSQL version displayed in the console.

Step 8: Add a customer​

Add the following code to the Main method.

using var cmd = new NpgsqlCommand("INSERT INTO customer (name) VALUES ('John Doe')", con);
cmd.ExecuteNonQuery();

Run the application again.

dotnet run

Step 7: See the table content​

Add the following code to the Main method.

using var cmd = new NpgsqlCommand("SELECT * FROM customer", con);
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine($"{reader.GetInt32(0)} {reader.GetString(1)}");
}

Run the application again.

dotnet run

You should see "1 John Doe" displayed in the console.

To go further​

Author: Yann M. Vidamment