Working with Databases in C#: PostgreSQL and MongoDB

Introduction: Why a C# Developer Should Know PostgreSQL and MongoDB

Modern C# (.NET) development rarely goes without working with databases. The choice between a relational (SQL) and document-oriented (NoSQL) DBMS depends on the project architecture. PostgreSQL is a powerful relational database with JSON support, while MongoDB is a flexible document database for rapid prototyping and Big Data. In this article, we will cover connection and basic operations with both systems from C#.

1. Working with PostgreSQL in C#

For interacting with PostgreSQL in .NET, the Npgsql provider is used. It supports Entity Framework Core and direct execution of SQL queries.

1.1. Installation and Connection

Install the NuGet package:

dotnet add package Npgsql

Example connection string:

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

1.2. CRUD Operations via Npgsql

Let's create a Users table and perform basic operations:

using Npgsql;

var connString = "Host=localhost;Database=testdb;Username=postgres;Password=123";await using var conn = new NpgsqlConnection(connString);await conn.OpenAsync();

// Creating a tablevar createCmd = new NpgsqlCommand(@"    CREATE TABLE IF NOT EXISTS Users (        Id SERIAL PRIMARY KEY,        Name VARCHAR(100),        Age INT    )", conn);await createCmd.ExecuteNonQueryAsync();

// Inserting datavar insertCmd = new NpgsqlCommand(    "INSERT INTO Users (Name, Age) VALUES (@n, @a)", conn);insertCmd.Parameters.AddWithValue("n", "Alexey");insertCmd.Parameters.AddWithValue("a", 30);await insertCmd.ExecuteNonQueryAsync();

// Reading datavar readCmd = new NpgsqlCommand("SELECT * FROM Users", conn);var reader = await readCmd.ExecuteReaderAsync();while (await reader.ReadAsync()){    Console.WriteLine($"{reader["Id"]}: {reader["Name"]}, {reader["Age"]} years old");}

1.3. Using Entity Framework Core

Install the package:

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

Define the model and context:

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

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

    protected override void OnConfiguring(DbContextOptionsBuilder options)        => options.UseNpgsql("Host=localhost;Database=testdb;Username=postgres;Password=123");}

CRUD via EF Core:

using var db = new AppDbContext();

// Addingdb.Users.Add(new User { Name = "Maria", Age = 25 });await db.SaveChangesAsync();

// Readingvar users = await db.Users.ToListAsync();foreach (var u in users)    Console.WriteLine($"{u.Name} - {u.Age}");

// Updatingvar user = await db.Users.FirstAsync(u => u.Name == "Maria");user.Age = 26;await db.SaveChangesAsync();

// Deletingdb.Users.Remove(user);await db.SaveChangesAsync();

2. Working with MongoDB in C#

MongoDB stores data in BSON (binary JSON) format. The MongoDB.Driver driver is used for C#.

2.1. Installation and Connection

Install the NuGet package:

dotnet add package MongoDB.Driver

Connecting to a local server:

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

2.2. CRUD Operations

Working with documents via BsonDocument:

// Creating a documentvar doc = new BsonDocument{    { "name", "Ivan" },    { "age", 28 },    { "skills", new BsonArray { "C#", "MongoDB", "Docker" } }};await collection.Insert