Technical note

Accessing Postgres from a C# console application

31 Mar 2024 Axel-Cleris Gailloty 3 min

In this article I will show you how to run basic SQL queries such as SELECT against a PostgreSQL table from a C# console application.

In a previous article I showed how to install PostgreSQL and PgAdmin with Docker for local use. In this article I will show you how to run basic SQL queries such as SELECT against a PostgreSQL table from a C# console application.

Creating a C# console project

To demonstrate this feature, we will create a simple .NET console application. To do so, navigate to the directory of your choice in your file explorer and type the following command:

dotnet new console -o console-postgres

With this command line, the .NET SDK generated a console project that prints “Hello world” to the console when we run it. We want to display something more interesting on the console.

Configuring the database connection

The first step is to configure the connection to your PostgreSQL database. For this, you need to install the Npgsql driver, which is the .NET data provider for PostgreSQL. It comes as a NuGet package that you can install by typing the following command:

dotnet add package Npgsql

Then, in the Program.cs file generated by the .NET SDK, we will write this code.

using Npgsql;

namespace PostgresData;
class Program
{
    private const string connectionString = "Host=localhost;Port=5432;Username=postgres;Password=$ecureP@ssword;";
    public static void DisplayTableData(string tableName) 
    {
        using var connection = new NpgsqlConnection(connectionString);
        connection.Open();

        string sqlForTableColumns = $@"SELECT column_name FROM information_schema.columns 
                                WHERE table_name='{tableName}' ORDER BY column_name";
        
        using var command = new NpgsqlCommand(sqlForTableColumns, connection);

        List<string> tableColumns = new();
        using (var reader = command.ExecuteReader()) 
        {
            while (reader.Read())
            {
                tableColumns.Add(reader.GetString(0));
            }
        }
        
        string sqlColumns = string.Join(" , ", tableColumns);

        using var sqlCommand = new NpgsqlCommand($"SELECT {sqlColumns} FROM {tableName}", connection);

        var results = new List<List<string>>();

        using (var reader = sqlCommand.ExecuteReader()) 
        {
            while (reader.Read())
            {
                List<string> rowResult = new();
                for (int i = 0; i < tableColumns.Count; i++) 
                {
                    string currentResult;
                    var currentData = reader.GetValue(i);
                    if (currentData is DBNull) 
                    {
                        currentResult = "null";
                    }
                    else 
                    {
                        currentResult = currentData.ToString();
                    }
                    rowResult.Add(currentResult);
                }
                results.Add(rowResult);
            }
        }

        Console.WriteLine(sqlColumns);

        foreach (var row in results) 
        {
            foreach (var data in row) 
            {
                Console.Write(data);
                Console.Write(", ");
            }
            Console.WriteLine("");
        }

    }
    static void Main()
    {
        DisplayTableData("pg_tables");
    }
}

Code walkthrough

This C# code connects to a local PostgreSQL database, retrieves the column names and the data of a specific table, then prints that data to the console.

Here is a detailed explanation of each part of the code:

Namespace and using directives:
```
using Npgsql;
```
This imports the Npgsql namespace, a .NET library for connecting to PostgreSQL databases.
Database connection string:
```
private const string connectionString = "Host=localhost;Port=5432;Username=postgres;Password=$ecureP@ssword;";
```
This constant defines the connection string to the PostgreSQL database. You will need to adjust these values to match your setup.

Opening the connection:

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

Retrieving the column names:

string sqlForTableColumns = $@"SELECT column_name FROM information_schema.columns 
                            WHERE table_name='{tableName}' ORDER BY column_name";

Printing the data to the console:

Console.WriteLine(sqlColumns);

foreach (var row in results) 
{
    foreach (var data in row) 
    {
        Console.Write(data);
        Console.Write(", ");
    }
    Console.WriteLine("");
}

Query result

When we run this code, the C# console opens and displays the following data coming from the pg_tables table.

hasindexes , hasrules , hastriggers , rowsecurity , schemaname , tablename , tableowner , tablespace
True, False, False, False, pg_catalog, pg_statistic, postgres, null, 
True, False, False, False, pg_catalog, pg_type, postgres, null,
True, False, False, False, pg_catalog, pg_foreign_table, postgres, null,
True, False, False, False, pg_catalog, pg_authid, postgres, pg_global,
True, False, False, False, pg_catalog, pg_statistic_ext_data, postgres, null,
True, False, False, False, pg_catalog, pg_user_mapping, postgres, null,
True, False, False, False, pg_catalog, pg_subscription, postgres, pg_global,
True, False, False, False, pg_catalog, pg_attribute, postgres, null,