Working with Databases in C#: PostgreSQL and MongoDB on .NET

Working with Databases in C#: PostgreSQL and MongoDB — A Complete Guide for .NET Developers

Modern development in C# (.NET) rarely goes without interacting with databases. Whether you are creating a web application, a microservice, or a desktop tool, the ability to connect and manage data is a key skill. In this article, we will explore working with two popular database management systems: the relational PostgreSQL and the document-oriented MongoDB. You will learn how to set up a connection, perform CRUD operations, and avoid common mistakes.

1. Connecting to PostgreSQL from C# (.NET) using Npgsql

PostgreSQL is a powerful open-source relational DBMS. To work with it in .NET, the Npgsql provider is used. Install it via NuGet:

dotnet add package Npgsql

After installation, a connection is created via a connection string. Example:

using Npgsql;

string connectionString = "Host=localhost;Port=5432;Database=mydb;Username=user;Password=pass";

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

Console.WriteLine("Connection to PostgreSQL established!");

Executing SQL Queries: CREATE, INSERT, SELECT

Use NpgsqlCommand to execute commands. Let's look at creating a table and inserting data:

using var cmd = new NpgsqlCommand();cmd.Connection = conn;

// Creating a tablecmd.CommandText = @"    CREATE TABLE IF NOT EXISTS users (        id SERIAL PRIMARY KEY,        name VARCHAR(100),        email VARCHAR(200) UNIQUE    )";cmd.ExecuteNonQuery();

// Inserting data with parameterscmd.CommandText = "INSERT INTO users (name, email) VALUES (@name, @email)";cmd.Parameters.AddWithValue("name", "Ivan Petrov");cmd.Parameters.AddWithValue("email", "ivan@example.com");cmd.ExecuteNonQuery();

// Reading datacmd.CommandText = "SELECT id, name, email FROM users";using var reader = cmd.ExecuteReader();while (reader.Read()){    Console.WriteLine($"{reader["id"]}: {reader["name"]} - {reader["email"]}");}

Important: always use parameterized queries to protect against SQL injection.

2. Working with PostgreSQL via Entity Framework Core

For higher-level work, Entity Framework Core (EF Core) with the Npgsql provider is recommended. Install the packages:

dotnet add package Microsoft.EntityFrameworkCoredotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

Create a context and model:

public class User{    public int Id { get; set; }    public string Name { get; set; }    public string Email { get; set; }}

public class AppDbContext : DbContext{    public DbSet<User> Users { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)        => options.UseNpgsql("Host=localhost;Database=mydb;Username=user;Password=pass");}

// Usageusing var db = new AppDbContext();db.Database.EnsureCreated();

var user = new User { Name = "Anna Smirnova", Email = "anna@example.com" };db.Users.Add(user);db.SaveChanges();

var users = db.Users.ToList();

EF Core automatically generates SQL, manages migrations, and simplifies code maintenance.

3. Connecting to MongoDB from C# (.NET) using MongoDB.Driver

MongoDB is a NoSQL database that stores data in BSON document format. For C#, use the official driver MongoDB.Driver:

dotnet add package MongoDB.Driver

Connecting to a local server:

using MongoDB.Driver;using MongoDB.Bson;

var client = new MongoClient("mongodb://localhost:27017");var database = client.GetDatabase("mydb");var collection = database.GetCollection<