.NET 7.0 + Postgres - Connect to PostgreSQL Database with Dapper in C# and ASP.NET Core

Tutorial built with .NET 7.0, Dapper 2.0 and PostgreSQL

This is a quick post to show how to connect a .NET 7 API to a Postgres database with C# using Dapper and ADO.NET.

The example code is from of a .NET 7 CRUD API tutorial I posted recently, the full project and documentation is available at .NET 7.0 + Dapper + PostgreSQL - CRUD API Tutorial in ASP.NET Core.

Dapper ORM (Object Relational Mapper)

Dapper is a micro ORM that supports executing raw SQL queries and mapping results to C# objects and collections, it's implemented as a set of extension methods on top of the ADO.NET IDbConnection interface, so ADO.NET provides the connection to the Postgres database when using Dapper.

PostgreSQL Database

PostgreSQL (also known as Postgres) is a free and open-source full featured relational database, it's a good option for any size application in production or development. The ADO.NET provider for Postgres (Npgsql) is used to connect to the database.

Connect to Postgres in C# with Dapper and ADO.NET

Below is the data context class from the example .NET 7 CRUD API, the CreateConnection() method connects to the PostgreSQL database by creating a new instance of the ADO.NET NpgsqlConnection() class with the specified connection string.

The Init() method creates the Postgres database and tables if they don't exist, it is executed once on API startup. The Dapper extension method ExecuteAsync() is used to execute SQL queries.

namespace WebApi.Helpers;

using System.Data;
using Dapper;
using Microsoft.Extensions.Options;
using Npgsql;

public class DataContext
{
    private DbSettings _dbSettings;

    public DataContext(IOptions<DbSettings> dbSettings)
    {
        _dbSettings = dbSettings.Value;
    }

    public IDbConnection CreateConnection()
    {
        var connectionString = $"Host={_dbSettings.Server}; Database={_dbSettings.Database}; Username={_dbSettings.UserId}; Password={_dbSettings.Password};";
        return new NpgsqlConnection(connectionString);
    }

    public async Task Init()
    {
        await _initDatabase();
        await _initTables();
    }

    private async Task _initDatabase()
    {
        // create database if it doesn't exist
        var connectionString = $"Host={_dbSettings.Server}; Database=postgres; Username={_dbSettings.UserId}; Password={_dbSettings.Password};";
        using var connection = new NpgsqlConnection(connectionString);
        var sqlDbCount = $"SELECT COUNT(*) FROM pg_database WHERE datname = '{_dbSettings.Database}';";
        var dbCount = await connection.ExecuteScalarAsync<int>(sqlDbCount);
        if (dbCount == 0)
        {
            var sql = $"CREATE DATABASE \"{_dbSettings.Database}\"";
            await connection.ExecuteAsync(sql);
        }
    }

    private async Task _initTables()
    {
        // create tables if they don't exist
        using var connection = CreateConnection();
        await _initUsers();

        async Task _initUsers()
        {
            var sql = """
                CREATE TABLE IF NOT EXISTS Users (
                    Id SERIAL PRIMARY KEY,
                    Title VARCHAR,
                    FirstName VARCHAR,
                    LastName VARCHAR,
                    Email VARCHAR,
                    Role INTEGER,
                    PasswordHash VARCHAR
                );
            """;
            await connection.ExecuteAsync(sql);
        }
    }
}