PostgreSQL CRUD operations with C# and Dapper

Read Time:5 Minute, 49 Second

In a previous article, we’ve seen how to perform simple CRUD operations on a Postgres database by using Npgsql, a library that allows you to write and perform queries to be executed 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 for .NET applications, and we will see how performing those operations becomes easier.

Introducing the project

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

This project performs CRUD (Create, Read, Update, Delete) operations on a Postgres database with a single table: Games. All those operations (plus a bunch of other additional ones) are executed by a class that implements this interface:

This allows me to define and use a new class without modifying too much the project: in fact, I simply have to replace the dependency in the Startup class to use the Dapper repository.

But first…

Dapper, a micro-ORM

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

ORM stands for Object-relational mapping and is a technique that allows you to map data from one format to another. This technique simplifies developers’ lives since they don’t have to manually map everything that comes from the database to an object – the ORM takes care of 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 the operations commonly executed by other ORMs; for example, Dapper allows you to map query results to objects, but it does not automatically generate the queries.

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

Or add the NuGet package via Visual Studio:

PostgreSQL CRUD operations with C# and Dapper

Dapper will take care of only a part of the operations; for instance, it cannot open a connection to your DB. That’s why you need to install Npgsql, just as we did in a previous article. We can say the whole Dapper library is a set of Extension Methods built on top of the native data access implementation – in the case of PostgreSQL, on to op Npgsql.

Now we have all the dependencies installed, so we can start writing our queries.

Open the connection

Once we have created the application, we must instantiate and open a connection against our database.

We will use the connection object later when we will perform the queries.

CRUD operations

We are working on a table, Games, whose name is stored in a constant:

The Games table consists of several fields:

Field name Field type
id INTEGER PK
Name VARCHAR NOT NULL
MinPlayers SMALLINT NOT NULL
MaxPlayers SMALLINT
AverageDuration SMALLINT

And it is mapped to the BoardGame class:

So, the main task of Dapper is to map the result of the queries performed on the Games table to one or more BoardGame objects.

Create

To create a new row on the Games table, we need to do something like this:

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

The query contains various parameters, marked with the @ symbol (@id, @name, @minPl, @maxPl, @avgDur). Those are placeholders, whose actual values are defined in the queryArguments anonymous object:

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

Comparison with Npgsql library

Using dapper simplifies our code. In fact, when using the native Npgsql library without Dapper, we have to declare every parameter explicitly.

As a comparison, have a look at how we implemented the same operation using Npgsql:

When using Dapper, we declare the parameter values in a single anonymous object, and we don’t create a NpgsqlCommand instance to define our query.

Read

As we’ve seen before, an ORM simplifies how you read data from a database by automatically mapping the query result to a list of objects.

When we want to get all the games stored on our table, we can do something like that:

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

In particular, connection.QueryAsync<BoardGame> fetches all the data from the query and converts it to a collection of BoardGame objects, performing the mapping for us.

Of course, you can also query for BoardGames with a specific Id:

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

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

Comparison with Npgsql

The power of Dapper is the ability to automatically map query results to C# object.

With the plain Npgsql library, we would have done:

to perform the query and open a reader on the result set. Then we would have defined a custom mapper to convert the Reader to a BoardGame object.

With Dapper, all of this is done in a single instruction:

Update and Delete

Update and Delete operations are quite similar: just a query, with a parameter, whose operation is executed in an asynchronous way.

I will add them here just for completeness:

and

Again: define the SQL operation, specify the placeholders, and execute the operation with ExecuteAsync.

Further readings

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

🔗 Dapper official documentation

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

🔗 PostgresCrudOperations repository | GitHub

Dapper adds a layer above the data access. If you want to go a level below, to have full control over what’s going on, you should use the native PostgreSQL library, Npgsql, as I explained in a previous article.

🔗CRUD operations on PostgreSQL using C# and Npgsql | Code4IT

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

🔗 How to run PostgreSQL locally with Docker | Code4IT

Wrapping up

In this article, we’ve seen how to use Dapper to simplify our data access. Dapper is useful for querying different types of RDBMS, not only PostgreSQL.

To try those examples out, download the code from GitHub, specify the connection string, and make sure that you are using the DapperBoardGameRepository class (this can be configured in the Startup class).

In a future article, we will use Entity Framework to – guess what? – perform CRUD operations on the Games table. In that way, you will have 3 different ways to access data stored on PostgreSQL by using .NET Core.

Happy coding!

🐧

Source: https://www.code4it.dev/blog/postgres-crud-dapper

WP Ad Inserter plugin for WordPress

Tag Cloud

Java Java Logical Programs OTP Generation in Java python Recursion youtube video ASCII Upper and Lower Case blockchain javascript graph learn to code software development Successful Software Engineers breadth first search Java Array Programs Java Programs Uncategorized android ios programming kotlin web-development django data sql cybersecurity database swiftui serverless aws swift rust react background-position gradients loader mask grid nth-child pseudo elements indieweb WordPress Print Array without brackets C++ factorial Java String Programs Final Keyword Static Variable Axie Infinity Cryptokitties NFT games tool inserting MISC Tips Codes python code python projects python3 system info python project Bigginers How to Do Integrations Payment Gateways PHP checkout page in php Implement stripe payment gateway in Step by step in PHP integrate stripe gatway in php mysql payment gateway integration in php step by step payment gateway integration in php step by step with source code payment gateway integration in website PHP Integrate Stripe Payment Gateway Tutorial PHP shopping cart checkout code shopping cart in php stripe php checkout PHP/MySQL/JSON best international payment gateway does google pay accept international payments how to accept international payments in india paytm payment gateway razorpay codeigniter github razorpay custom checkout github razorpay get payment details razorpay integration in codeigniter github razorpay international payments Razorpay payment gateway integration in CodeIgniter razorpay payment gateway integration in php code Razorpay payment gateway integration with PHP and CodeIgniter Razorpay payment gateway setup in CodeIgniter Library & Frameworks Tips & Tricks UI/UX & Front-end coding birds online html code for google sign in login with google account in PHP login with google account using javascript login with google account using javascript codeigniter login with google account using php login with google account using php source code
Web Component Pseudo-Classes and Pseudo-Elements are Easier Than You Think Previous post Web Component Pseudo-Classes and Pseudo-Elements are Easier Than You Think
IE Down, Edge Up… Global Browser Usage Stats Are for Cocktail Parties and Conference Slides Next post IE Down, Edge Up… Global Browser Usage Stats Are for Cocktail Parties and Conference Slides

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.