Working with Databases in C#: PostgreSQL and MongoDB in Practice

Working with Databases in C#: PostgreSQL and MongoDB in Practice

Working with databases is one of the key tasks for any backend developer. In the C# (.NET) ecosystem, hundreds of drivers and ORMs are available to developers, but the most in-demand remain relational DBMS (PostgreSQL) and document-oriented NoSQL solutions (MongoDB). In this article, we will use practical examples to cover how to connect to both databases, perform CRUD operations, and choose the right tool for a specific task.

1. PostgreSQL in C#: Connection and CRUD via Npgsql

To work with PostgreSQL in .NET, the Npgsql library is used — a high-performance ADO.NET provider. Install it via NuGet:

dotnet add package Npgsql

1.1. Connecting to the Database

The connection string for a local PostgreSQL server looks like this:

string connectionString = "Host=localhost;Port=5432;Database=testdb;Username=postgres;Password=your_password";

Let's create a helper class for working with the database:

using Npgsql;

public class PostgresService{    private readonly string _connectionString;

    public PostgresService(string connectionString)    {        _connectionString = connectionString;    }

    public async Task InsertUserAsync(string name, int age)    {        await using var conn = new NpgsqlConnection(_connectionString);        await conn.OpenAsync();

        var cmd = new NpgsqlCommand(            "INSERT INTO users (name, age) VALUES (@name, @age)", conn);        cmd.Parameters.AddWithValue("name", name);        cmd.Parameters.AddWithValue("age", age);

        await cmd.ExecuteNonQueryAsync();    }

    public async Task<List<User>> GetAllUsersAsync()    {        var users = new List<User>();

        await using var conn = new NpgsqlConnection(_connectionString);        await conn.OpenAsync();

        var cmd = new NpgsqlCommand("SELECT id, name, age FROM users", conn);        await using var reader = await cmd.ExecuteReaderAsync();

        while (await reader.ReadAsync())        {            users.Add(new User            {                Id = reader.GetInt32(0),                Name = reader.GetString(1),                Age = reader.GetInt32(2)            });        }

        return users;    }}

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

1.2. Using Entity Framework Core with PostgreSQL

For more convenient work with relational data, use the ORM Entity Framework Core with the Npgsql.EntityFrameworkCore.PostgreSQL provider:

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

Example of a 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 optionsBuilder)    {        optionsBuilder.UseNpgsql("Host=localhost;Database=testdb;Username=postgres;Password=your_password");    }}

Now CRUD operations become trivial:

using (var db = new AppDbContext()){    // Create    db.Users.Add(new User { Name = "Alice", Age = 30 });    db.SaveChanges();

    // Read    var users = db.Users.ToList();

    // Update    var user = db.Users.First();    user.Age = 31;    db.SaveChanges();

    // Delete    db.Users.Remove(user);    db.SaveChanges();}

2. MongoDB in C#: Working with Documents via the Official Driver

MongoDB is a document NoSQL database,