Skip to main content

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!

Labels:

Comments

Post a Comment

Popular posts from this blog

C# : How can we access private method outside class

Introduction In object-oriented programming, encapsulation is a fundamental principle that restricts direct access to the internal implementation details of a class. Private methods, being part of this internal implementation, are designed to be accessible only within the confines of the class they belong to. However, there might be scenarios where you need to access a private method from outside the class. In this blog post, we'll explore several techniques to achieve this in C#. 1. Reflection: A Powerful Yet Delicate Approach Reflection is a mechanism in C# that allows inspecting and interacting with metadata about types, fields, properties, and methods. While it provides a way to access private methods, it should be used cautiously due to its potential impact on maintainability and performance. using System ; using System . Reflection ; public class MyClass { private void PrivateMethod ( ) { Console . WriteLine ( "This is a private method."
Post a Comment
Read more

C# : Understanding Types of Classes

In C#, classes serve as the building blocks of object-oriented programming, providing a blueprint for creating objects. Understanding the types of classes and their applications is crucial for designing robust and maintainable software. In this blog, we’ll delve into various types of classes in C#, accompanied by real-world scenarios and code snippets for a practical understanding. 1. Regular (Instance) Classes Definition: Regular classes are the most common type and are used to create instances or objects. They can contain fields, properties, methods, and other members. Example Scenario: A Person class representing individual persons with properties like Name and Age. public class Person { public string Name { get ; set ; } public int Age { get ; set ; } } 2. Static Classes Definition: A static class cannot be instantiated and can only contain static members (methods, properties, fields). It’s often used for utility functions. Example Scenario: A MathUtility cla
Post a Comment
Read more

C# : 12.0 : Primary constructor

Introduction In C# 12.0, the introduction of the "Primary Constructor" simplifies the constructor declaration process. Before delving into this concept, let's revisit constructors. A constructor is a special method in a class with the same name as the class itself. It's possible to have multiple constructors through a technique called constructor overloading.  By default, if no constructors are explicitly defined, the C# compiler generates a default constructor for each class. Now, in C# 12.0, the term "Primary Constructor" refers to a more streamlined way of declaring constructors. This feature enhances the clarity and conciseness of constructor declarations in C# code. Lets see an simple example code, which will be known to everyone. public class Version { private int _value ; private string _name ; public Version ( int value , string name ) { _name = name ; _value = value ; } public string Ve
Post a Comment
Read more