Mastering PostgreSQL Connection with Dapper in .NET Core: A Step-by-Step Guide

 

If you're working with .NET Core and PostgreSQL, you might have come across Dapper, a popular micro ORM (Object-Relational Mapper) that provides a fast, lightweight way to interact with databases. This blog will guide you through the process of connecting your .NET Core application to a PostgreSQL database using Dapper—an elegant combination for developers looking for high performance without the complexity of full-fledged ORMs like Entity Framework.

Whether you're new to Dapper or PostgreSQL or just want a quick refresher, this blog will take you through the setup step by step. Let's dive into it!

Why Dapper + PostgreSQL? 🤔

Before we dive into the code, let's briefly cover why this combo makes sense:

• Dapper is super fast because it doesn't abstract away SQL; you write your queries, giving you total control over performance.
• PostgreSQL is one of the most powerful, open-source relational databases, well known for its scalability, performance, and advanced features.

The combination of Dapper's simplicity and performance with PostgreSQL's reliability makes for a robust solution in any .NET Core application.

Step 1: Set Up Your PostgreSQL Database 🛠️

First, if you haven’t already, you’ll need to have PostgreSQL installed on your machine or cloud service (AWS RDS, Azure PostgreSQL, etc.). Then create a sample database and table that we will use to test our connection.

CREATE DATABASE MyItemDB;

CREATE TABLE Items (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(100),
    Price DECIMAL(10, 2)
);

In this example, we'll be working with an Items table, which stores information about items such as Id, Name, and Price.

Step 2: Install Necessary NuGet Packages 📦

To work with PostgreSQL and Dapper, you’ll need the following NuGet packages:

1. Npgsql - A PostgreSQL database provider for .NET.
2. Dapper - The micro ORM.
3. Microsoft.Extensions.Configuration - For managing your connection strings.

Install them via NuGet Package Manager or using the command line:

dotnet add package Npgsql
dotnet add package Dapper
dotnet add package Microsoft.Extensions.Configuration

Step 3: Configure PostgreSQL Connection String in appsettings.json 📝

In your appsettings.json file, add the PostgreSQL connection string. This is where you'll specify the credentials and database you're connecting to.

{
  "ConnectionStrings": {
    "PostgresConnection": "Host=localhost;Port=5432;Username=postgres;Password=mysecretpassword;Database=MyItemDB"
  }
}

Make sure to replace the values with your actual PostgreSQL credentials.

Step 4: Create a Model for Your Data 🏗️

Next, create a model that will represent the data from your Items table. This is the class that Dapper will map the data to.

{
  "ConnectionStrings": {
    "PostgresConnection": "Host=localhost;Port=5432;Username=postgres;Password=mysecretpassword;Database=MyItemDB"
  }
}

Step 5: Create the Repository Using Dapper and Npgsql 📄

Now, let's create the repository class that will handle database operations using Dapper.

using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using Dapper;
using Npgsql;
using Microsoft.Extensions.Configuration;

public class ItemRepository
{
    private readonly IConfiguration _configuration;
    private readonly string _connectionString;

    public ItemRepository(IConfiguration configuration)
    {
        _configuration = configuration;
        _connectionString = _configuration.GetConnectionString("PostgresConnection");
    }

    private IDbConnection Connection => new NpgsqlConnection(_connectionString);

    public async Task<IEnumerable<Item>> GetAllItemsAsync()
    {
        using (var connection = Connection)
        {
            string query = "SELECT * FROM Items";
            var items = await connection.QueryAsync<Item>(query);
            return items;
        }
    }

    public async Task<Item> GetItemByIdAsync(int id)
    {
        using (var connection = Connection)
        {
            string query = "SELECT * FROM Items WHERE Id = @Id";
            var item = await connection.QuerySingleOrDefaultAsync<Item>(query, new { Id = id });
            return item;
        }
    }

    public async Task<int> AddItemAsync(Item item)
    {
        using (var connection = Connection)
        {
            string query = "INSERT INTO Items (Name, Price) VALUES (@Name, @Price) RETURNING Id";
            var id = await connection.ExecuteScalarAsync<int>(query, item);
            return id;
        }
    }

    public async Task<int> UpdateItemAsync(Item item)
    {
        using (var connection = Connection)
        {
            string query = "UPDATE Items SET Name = @Name, Price = @Price WHERE Id = @Id";
            return await connection.ExecuteAsync(query, item);
        }
    }

    public async Task<int> DeleteItemAsync(int id)
    {
        using (var connection = Connection)
        {
            string query = "DELETE FROM Items WHERE Id = @Id";
            return await connection.ExecuteAsync(query, new { Id = id });
        }
    }
}

Breaking Down the Code:

• We use the NpgsqlConnection to establish a connection with the PostgreSQL database.
• Dapper's QueryAsync<T>() method is used for reading data, and ExecuteAsync() is used for insert/update/delete operations.
• The repository follows the basic CRUD (Create, Read, Update, Delete) operations for our Item entity.

Step 6: Register Repository in Startup.cs 🛠️

In the Startup.cs file, register your repository class in the dependency injection (DI) container.

public void ConfigureServices(IServiceCollection services)
{
    services.AddScoped<ItemRepository>();
}

This allows your repository to be injected wherever needed in the application.

Step 7: Create a Controller to Test Your API 🎯

Now, create a basic API controller that will use the ItemRepository to handle requests.

using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Threading.Tasks;

[ApiController]
[Route("api/[controller]")]
public class ItemsController : ControllerBase
{
    private readonly ItemRepository _itemRepository;

    public ItemsController(ItemRepository itemRepository)
    {
        _itemRepository = itemRepository;
    }

    [HttpGet]
    public async Task<IEnumerable<Item>> GetItems()
    {
        return await _itemRepository.GetAllItemsAsync();
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<Item>> GetItem(int id)
    {
        var item = await _itemRepository.GetItemByIdAsync(id);

        if (item == null)
        {
            return NotFound();
        }

        return item;
    }

    [HttpPost]
    public async Task<ActionResult<int>> AddItem(Item item)
    {
        var id = await _itemRepository.AddItemAsync(item);
        return CreatedAtAction(nameof(GetItem), new { id = id }, id);
    }

    [HttpPut("{id}")]
    public async Task<IActionResult> UpdateItem(int id, Item item)
    {
        if (id != item.Id)
        {
            return BadRequest();
        }

        await _itemRepository.UpdateItemAsync(item);

        return NoContent();
    }

    [HttpDelete("{id}")]
    public async Task<IActionResult> DeleteItem(int id)
    {
        await _itemRepository.DeleteItemAsync(id);
        return NoContent();
    }
}

Testing the API:

• GET /api/items: Retrieve all items.
• GET /api/items/{id}: Retrieve a specific item by ID.
• POST /api/items: Add a new item.
• PUT /api/items/{id}: Update an item.
• DELETE /api/items/{id}: Delete an item.

Conclusion: You're Ready to Rock Dapper with PostgreSQL! 🎉

By following this guide, you've successfully connected a .NET Core application to a PostgreSQL database using Dapper. This lightweight yet powerful combination gives you control over your SQL queries without the overhead of a full ORM, while PostgreSQL provides you with the scalability and reliability your application needs.

In the next steps, you can start optimizing your queries, adding transaction handling, and exploring more advanced features like stored procedures. Stay tuned for more tips and tricks on building high-performance apps with .NET Core and PostgreSQL!

Coming Up Next: In future posts, we’ll dive into advanced query optimizations using Dapper and how to handle large datasets efficiently. Keep an eye out for that!