Connecting to Postgres with C-sharp

In this tutorial, we have provided a thorough, step-by-step guide for creating a connection to a PostgreSQL database using C#. We will utilize the Npgsql package for establishing a connection to the Postgres database.

Let's gets started

Step 1 - Install the Npgsql library in your project. You can install it via .NET command line-interface by running this command in the terminal

dotnet add package Npgsql

Step 2 - Create a connection string that includes all the necessary information required to set up a connection with a Postgres database.

For security best practices, avoid hardcoding credentials directly in your source code. Instead, store sensitive information like database credentials in environment variables or a secure configuration system.

Here's how to create a connection string using environment variables:

// Load connection parameters from environment variables
string host = Environment.GetEnvironmentVariable("POSTGRES_HOST") ?? "localhost";
string port = Environment.GetEnvironmentVariable("POSTGRES_PORT") ?? "5432";
string database = Environment.GetEnvironmentVariable("POSTGRES_DB") ?? "postgres";
string username = Environment.GetEnvironmentVariable("POSTGRES_USER") ?? "postgres";
string password = Environment.GetEnvironmentVariable("POSTGRES_PASSWORD") ?? "";

// Build the connection string
string connectionString = $"Host={host};Port={port};Database={database};User Id={username};Password={password};";

Alternatively, for .NET applications, you can use the built-in configuration system:

// In your appsettings.json
// {
//   "ConnectionStrings": {
//     "PostgresConnection": "Host=my_host;Port=5432;Database=my_db;User Id=my_user;Password=my_password;"
//   }
// }

// Then in your code:
string connectionString = Configuration.GetConnectionString("PostgresConnection");

Remember to secure your configuration files and never commit them to source control with real credentials.

Step 3 - Initialize the NpgsqlConnection class in your project to setup a connection to the Postgres database. You will also need to use the connection string you have created.

try 
{
    using NpgsqlConnection connection = new NpgsqlConnection(connectionString);
    connection.Open();
    
    // Database operations go here
}
catch (Npgsql.NpgsqlException ex)
{
    Console.WriteLine($"Database connection error: {ex.Message}");
}

You can also use the using block as a wrapper to make sure that the connection is properly closed when the program exits the using block.

Step 4 - Execute the desired SQL query using the NpgsqlCommand class.

try 
{
    using NpgsqlConnection connection = new NpgsqlConnection(connectionString);
    connection.Open();
    
    using NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM customers", connection);
    using NpgsqlDataReader reader = cmd.ExecuteReader();
    
    while (reader.Read())
    {
        // Use the fetched results
        Console.WriteLine(reader["column_name"]);
    }
}
catch (Npgsql.NpgsqlException ex)
{
    Console.WriteLine($"Database error: {ex.Message}");
}

Here's the complete code to connect to a Postgres database with C# using environment variables for secure credential management:

using Npgsql;
using System;

class Program
{
    static void Main()
    {
        try
        {
            // Load connection parameters from environment variables
            string host = Environment.GetEnvironmentVariable("POSTGRES_HOST") ?? "localhost";
            string port = Environment.GetEnvironmentVariable("POSTGRES_PORT") ?? "5432";
            string database = Environment.GetEnvironmentVariable("POSTGRES_DB") ?? "postgres";
            string username = Environment.GetEnvironmentVariable("POSTGRES_USER") ?? "postgres";
            string password = Environment.GetEnvironmentVariable("POSTGRES_PASSWORD") ?? "";

            // Build the connection string
            string connectionString = $"Host={host};Port={port};Database={database};User Id={username};Password={password};";

            using NpgsqlConnection connection = new NpgsqlConnection(connectionString);
            connection.Open();
            Console.WriteLine("Successfully connected to database");

            using NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM customers", connection);
            using NpgsqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine(reader["column_name"]);
                // Use the fetched results
            }
        }
        catch (Npgsql.NpgsqlException ex)
        {
            Console.WriteLine($"Database error: {ex.Message}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"General error: {ex.Message}");
        }
    }
}

In this example, we have demonstrated how you can read the data, but you can also perform other operations - DML statements like Insert, Update, and DDL statements like Create Table.

Insert

try
{
    using NpgsqlConnection connection = new NpgsqlConnection(connectionString);
    connection.Open();
    
    using NpgsqlCommand cmd = new NpgsqlCommand("INSERT INTO table_name (column1, column2) VALUES (@value1, @value2)", connection);
    cmd.Parameters.AddWithValue("@value1", "xyz");
    cmd.Parameters.AddWithValue("@value2", 123);

    int rowsAffected = cmd.ExecuteNonQuery();
    Console.WriteLine($"{rowsAffected} row(s) inserted");
}
catch (Npgsql.NpgsqlException ex)
{
    Console.WriteLine($"Error inserting data: {ex.Message}");
}
catch (Exception ex)
{
    Console.WriteLine($"General error during insert operation: {ex.Message}");
}

Update

try
{
    using NpgsqlConnection connection = new NpgsqlConnection(connectionString);
    connection.Open();
    
    using NpgsqlCommand cmd = new NpgsqlCommand("UPDATE table_name SET column1 = @new_value WHERE column2 = @condition", connection);
    cmd.Parameters.AddWithValue("@new_value", "new_data");
    cmd.Parameters.AddWithValue("@condition", "some_condition");

    int rowsAffected = cmd.ExecuteNonQuery();
    Console.WriteLine($"{rowsAffected} row(s) updated");
}
catch (Npgsql.NpgsqlException ex)
{
    Console.WriteLine($"Error updating data: {ex.Message}");
}
catch (Exception ex)
{
    Console.WriteLine($"General error during update operation: {ex.Message}");
}

Create Table

try
{
    using NpgsqlConnection connection = new NpgsqlConnection(connectionString);
    connection.Open();
    
    using NpgsqlCommand cmd = new NpgsqlCommand(
        "CREATE TABLE table_name (column1 data_type, column2 data_type, column3 data_type)", 
        connection
    );

    cmd.ExecuteNonQuery();
    Console.WriteLine("Table created successfully");
}
catch (Npgsql.NpgsqlException ex)
{
    Console.WriteLine($"Error creating table: {ex.Message}");
}
catch (Exception ex)
{
    Console.WriteLine($"General error during table creation: {ex.Message}");
}

You can simply copy and paste this code snippet to integrate in your project. Make sure to set the appropriate environment variables for your database connection or customize the default values as needed.

Conclusion

In this guide, we discussed the step-by-step process to connect to the Postgres database with C#.

If you are working on a Python project, you can check out other guide to know how you can connect a Postgres database with Python.

Also, check out our new extensions pgmq and pg_later.