PostgreSQL CRUD Tutorials in C#: A Step-by-Step Guide

PostgreSQL is a robust open-source relational database management system, and C# is a versatile programming language. In this tutorial, we will explore the basics of performing CRUD (Create, Read, Update, Delete) operations in PostgreSQL using C#. We’ll cover the following steps:

Setting Up Your Environment:
- Installing PostgreSQL.
- Setting up your C# development environment.
Connecting to PostgreSQL:
- Creating a connection to your PostgreSQL server.
Creating a Table:
- Writing C# code to create a table in your PostgreSQL database.
Inserting Data:
- Demonstrating how to insert data into the table.
Querying Data:
- Retrieving data from the table.
Updating Data:
- Modifying existing records in the table.
Deleting Data:
- Deleting records from the table.

1. Setting Up Your Environment

Installing PostgreSQL

Download and install PostgreSQL from the official PostgreSQL website.

Setting Up Your C# Development Environment

Install Visual Studio or Visual Studio Code, and ensure you have the .NET SDK installed.

2. Connecting to PostgreSQL

To connect to your PostgreSQL server from a C# application, you can use the Npgsql library (Npgsql). Install it using NuGet Package Manager or the .NET CLI:

dotnet add package Npgsql

Now, let’s create a connection to your PostgreSQL server:

using System;
using Npgsql;

class Program
{
    static void Main()
    {
        string connectionString = "Host=localhost;Port=5432;Username=myuser;Password=mypassword;Database=mydatabase;";
        NpgsqlConnection connection = new NpgsqlConnection(connectionString);

        try
        {
            connection.Open();
            Console.WriteLine("Connected to PostgreSQL!");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        finally
        {
            connection.Close();
        }
    }
}

Replace localhost:5432, myuser, mypassword, and mydatabase with your PostgreSQL server details.

3. Creating a Table

Let’s create a simple users table in your PostgreSQL database:

string createTableSql = "CREATE TABLE IF NOT EXISTS users (" +
    "id serial PRIMARY KEY," +
    "name VARCHAR(255) NOT NULL," +
    "email VARCHAR(255) NOT NULL)";
NpgsqlCommand createTableCommand = new NpgsqlCommand(createTableSql, connection);

try
{
    connection.Open();
    createTableCommand.ExecuteNonQuery();
    Console.WriteLine("Table created!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code creates a table named users with columns id, name, and email.

4. Inserting Data

Now, let’s insert a user into the users table:

string insertSql = "INSERT INTO users (name, email) VALUES (@name, @email)";
NpgsqlCommand insertCommand = new NpgsqlCommand(insertSql, connection);

// Parameters
insertCommand.Parameters.AddWithValue("@name", "John Doe");
insertCommand.Parameters.AddWithValue("@email", "[email protected]");

try
{
    connection.Open();
    int rowsAffected = insertCommand.ExecuteNonQuery();
    Console.WriteLine($"Inserted {rowsAffected} row(s)!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code inserts a user with the name “John Doe” and email “[email protected]” into the users table.

5. Querying Data

Let’s retrieve data from the users table:

string query = "SELECT * FROM users";
NpgsqlCommand queryCommand = new NpgsqlCommand(query, connection);

try
{
    connection.Open();
    NpgsqlDataReader reader = queryCommand.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine($"ID: {reader["id"]}, Name: {reader["name"]}, Email: {reader["email"]}");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code queries and displays all records in the users table.

6. Updating Data

Let’s update a user’s email address:

string updateSql = "UPDATE users SET email = @newEmail WHERE name = @name";
NpgsqlCommand updateCommand = new NpgsqlCommand(updateSql, connection);

// Parameters
updateCommand.Parameters.AddWithValue("@newEmail", "[email protected]");
updateCommand.Parameters.AddWithValue("@name", "John Doe");

try
{
    connection.Open();
    int rowsAffected = updateCommand.ExecuteNonQuery();
    Console.WriteLine($"Updated {rowsAffected} row(s)!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code updates the email address of the user with the name “John Doe” in the users table.

7. Deleting Data

Let’s delete a user from the users table:

string deleteSql = "DELETE FROM users WHERE name = @name";
NpgsqlCommand deleteCommand = new NpgsqlCommand(deleteSql, connection);

// Parameter
deleteCommand.Parameters.AddWithValue("@name", "John Doe");

try
{
    connection.Open();
    int rowsAffected = deleteCommand.ExecuteNonQuery();
    Console.WriteLine($"Deleted {rowsAffected} row(s)!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code deletes the user with the name “John Doe” from the users table.

With these CRUD operations, you have a solid foundation for working with PostgreSQL in your C# applications. Feel free to extend and adapt these examples to meet the requirements of your specific project. PostgreSQL’s reliability and extensibility make it an excellent choice for various application scenarios.

If you want to learn more about MySQL, please use our PostgreSQL tutorials and PostgreSQL Reference.