PostgreSQL CRUD operations with C# and Dapper

In a previous article we saw how to perform simple CRUD operations on a Postgres database using Npgsql, a directory that allows you to write and execute queries to perform specifically on a PostgreSQL database.

In this article, we will take a step further: we will perform the same operations using Dapper, one of the most popular ORM applications for NET, and we will see how these operations become easier.

Presentation of the project

For this article, I will reuse the project I used for the previous article.

This project performs CRUD operations (Create, Read, Update, Delete) in a Postgres database with a single table: Games. All of these actions (plus a bunch of other additional actions) are performed by a department that implements this interface:

public interface IBoardGameRepository
{
    Task<IEnumerable<BoardGame>> GetAll();

    Task<BoardGame> Get(int id);

    Task Add(BoardGame game);

    Task Update(int id, BoardGame game);

    Task Delete(int id);

    Task<string> GetVersion();

    Task CreateTableIfNotExists();
}

This allows me to set up and use a new class without changing the project too much: in fact, I just need to replace the dependency in the Startup class to use the Dapper repository.

But first …

Dapper, micro- ORM

In the introduction, I said we would use Dapper, a popular ORM. Let me explain.

ORM Abbreviations for Object Relationship Mapping And is a technique that allows you to map data from one format to another. This technique simplifies the lives of developers because they do not have to manually map everything that comes from the database to the object – the ORM handles this task.

Dapper is one of the most popular ORMs, created by the Stack Overflow team. Well actually Dapper is a micro-ORM: It performs only a subset of operations typically performed by other ORMs; For example, Dapper allows you to map query results to objects, but it does not generate the queries automatically.

To add Dapper to your .NET project, simply run this command:

dotnet add package Dapper

Or add the NuGet package through Visual Studio:

Dapper will only take care of some of the operations; For example, it could not open a connection to your DB. This is why you need to install Npgsql, Just as we did in a previous article. We can say that the entire Dapper library is a set of extension methods built on top of the original data access application – in the case of PostgreSQL, on to op Npgsql.

We have now installed all the dependencies so we can start writing our queries.

Open the connection

Once we have created the app, we need to create an instance and open a connection in front of our database.

private NpgsqlConnection connection;

public DapperBoardGameRepository()
{
    connection = new NpgsqlConnection(CONNECTION_STRING);
    connection.Open();
}

We will use connection Resist later when we execute the queries.

CRUD operations

We work on a table, games, whose name is stored regularly:

private const string TABLE_NAME = "Games";

God Games The table consists of several fields:

And is mapped to BoardGame status:

public class BoardGame
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int MinPlayers { get; set; }
    public int MaxPlayers { get; set; }
    public int AverageDuration { get; set; }
}

Therefore, Dapper’s main task is to map the result of the queries performed in the game table to one or more BoardGame objects.

to create

To create a new row in the game table, we must do the following:

public async Task Add(BoardGame game)
{
    string commandText = $"INSERT INTO {TABLE_NAME} (id, Name, MinPlayers, MaxPlayers, AverageDuration) VALUES (@id, @name, @minPl, @maxPl, @avgDur)";

    var queryArguments = new
    {
        id = game.Id,
        name = game.Name,
        minPl = game.MinPlayers,
        maxPl = game.MaxPlayers,
        avgDur = game.AverageDuration
    };

    await connection.ExecuteAsync(commandText, queryArguments);
}

Because Dapper does not create queries for us, we still need to define them explicitly.

The query contains various parameters, marked with @ Icon (@id, @name, @minPl, @maxPl, @avgDur). These are placeholders, whose true values ​​are defined in queryArguments Anonymous object:

var queryArguments = new
{
    id = game.Id,
    name = game.Name,
    minPl = game.MinPlayers,
    maxPl = game.MaxPlayers,
    avgDur = game.AverageDuration
};

Finally, we can perform our query on the connection we opened in the constructor:

await connection.ExecuteAsync(commandText, queryArguments);

Comparison with Npgsql library

Using dapper simplifies our code. In fact, when using the original Npgsql library without Dapper, we need to explicitly state each parameter.

For comparison, see How we implemented the same operation using Npgsql:

public async Task Add(BoardGame game)
{
    string commandText = $"INSERT INTO {TABLE_NAME} (id, Name, MinPlayers, MaxPlayers, AverageDuration) VALUES (@id, @name, @minPl, @maxPl, @avgDur)";
    await using (var cmd = new NpgsqlCommand(commandText, connection))
    {
        cmd.Parameters.AddWithValue("id", game.Id);
        cmd.Parameters.AddWithValue("name", game.Name);
        cmd.Parameters.AddWithValue("minPl", game.MinPlayers);
        cmd.Parameters.AddWithValue("maxPl", game.MaxPlayers);
        cmd.Parameters.AddWithValue("avgDur", game.AverageDuration);

        await cmd.ExecuteNonQueryAsync();
    }
}

When using Dapper, we declare the parameter values ​​in a single anonymous object, and do not create a NpgsqlCommand Show to define our query.

to read

As we have seen before, ORM simplifies the way you read data from a database by automatically mapping the query result to an object list.

When we want to store all the games on our table, we can do something like this:

public async Task<IEnumerable<BoardGame>> GetAll()
{
    string commandText = $"SELECT * FROM {TABLE_NAME}";
    var games = await connection.QueryAsync<BoardGame>(commandText);

    return games;
}

Again, we define our query and allow Dapper to do the rest for us.

In a special way, connection.QueryAsync<BoardGame> Extracts all the data from the query and converts it to a collection of BoardGame objects, performs the mapping for us.

Of course, you can also ask for board games with a specific ID:

public async Task<BoardGame> Get(int id)
{
    string commandText = $"SELECT * FROM {TABLE_NAME} WHERE ID = @id";

    var queryArgs = new { Id = id };
    var game = await connection.QueryFirstAsync<BoardGame>(commandText, queryArgs);
    return game;
}

As we did before, you define the query with a placeholder @idWhich will have the value defined in queryArgs Anonymous object.

To store the result in the C # object, we map only the first object returned by the query, by using QueryFirstAsync instead of QueryAsync.

Comparison with Npgsql

Dapper’s power is the ability to automatically map query results to a C # object.

With the simple Npgsql directory, we would do:

await using (NpgsqlDataReader reader = await cmd.ExecuteReaderAsync())
    while (await reader.ReadAsync())
    {
        BoardGame game = ReadBoardGame(reader);
        games.Add(game);
    }

To execute the query and open a reader on the result set. Then we would set up a custom map to convert the Reader to a BoardGame object.

 private static BoardGame ReadBoardGame(NpgsqlDataReader reader)
{
    int? id = reader["id"] as int?;
    string name = reader["name"] as string;
    short? minPlayers = reader["minplayers"] as Int16?;
    short? maxPlayers = reader["maxplayers"] as Int16?;
    short? averageDuration = reader["averageduration"] as Int16?;

    BoardGame game = new BoardGame
    {
        Id = id.Value,
        Name = name,
        MinPlayers = minPlayers.Value,
        MaxPlayers = maxPlayers.Value,
        AverageDuration = averageDuration.Value
    };
    return game;
}

With Dapper, all this is done in one instruction:

var games = await connection.QueryAsync<BoardGame>(commandText);

Update and delete

Update and delete operations are quite similar: only a query, with a parameter, whose operation is performed asynchronously.

I will add them here just for the sake of completeness:

public async Task Update(int id, BoardGame game)
{
    var commandText = $@"UPDATE {TABLE_NAME}
                SET Name = @name, MinPlayers = @minPl, MaxPlayers = @maxPl, AverageDuration = @avgDur
                WHERE id = @id";

    var queryArgs = new
    {
        id = game.Id,
        name = game.Name,
        minPl = game.MinPlayers,
        maxPl = game.MaxPlayers,
        avgDur = game.AverageDuration
    };

    await connection.ExecuteAsync(commandText, queryArgs);
}

and

public async Task Delete(int id)
{
string commandText = $"DELETE FROM {TABLE_NAME} WHERE ID=(@p)";

var queryArguments = new {  p = id  };

await connection.ExecuteAsync(commandText, queryArguments);
}

Again: Set the SQL operation, specify the placeholders, and perform the operation with them ExecuteAsync.

Additional readings

As always, the best way to get started with a new library is by reading the official documentation:

🔗 Crazy official documentation

To see the full code for these examples, you can look at the related GitHub repository.

🔗 PostgresCrudOperations Database | GitHub

Dapper adds a layer above the data access. If you want to go down to the level, to have full control over what happens, you must use the original PostgreSQL directory, Npgsql, as I explained in a previous article.

CRUD operations in PostgreSQL using C # and Npgsql | Code4IT

How to run a Postgres show? You can use any cloud implementation, or you can download and run a PostgreSQL instance on your local computer using Docker as I explained in this guide:

How to Run PostgreSQL Locally with Docker | Code4IT

Finishing

In this article, we have seen how to use Dapper to simplify access to our data. Dapper is useful for queries of different types of RDBMS, not just PostgreSQL.

To try these examples, download the code From GitHubEnter the connection string and make sure you are using DapperBoardGameRepository class (this can be set in the Startup class).

In a future article, we will use the Entity Framework to – guess what? – Perform CRUD actions on the game table. This way, you will have 3 different ways to access data stored in PostgreSQL using .NET Core.

Happy coding!

🐧