Getting Started With PostgreSQL Using Dapper In .NET Core

Introduction

PostgreSQL is a powerful, open-source object-relational database system that is very easy to use. Dapper is a high-performance micro-ORM, which is my favorite.

Now, I will show you how to use this combination in .NET Core.

Requirement

Npgsql 3.1.9
Dapper 1.50.2

My Environment

Visual Studio 2015 Community with update 3
PostgreSQL 9.6
pgAdmin 4

Demonstration

Step 1

Create a new table in pgAdmin

CREATE TABLE public.customer(
id serial NOT NULL,
firstname character varying(45) NOT NULL,
lastname character varying(45) NOT NULL,
email character varying(50) NOT NULL,
createtime timestamp without time zone NOT NULL,
PRIMARY KEY (id)
) WITH (OIDS = FALSE) TABLESPACE pg_default;
ALTER TABLE
public.customer OWNER to dev;

Step 2
Create a new Console Application (.NET Core).

Edit the project.json

{
"version": "1.0.0-*",
"buildOptions": {
"emitEntryPoint": true
},
"dependencies": {
"Microsoft.NETCore.App": {
"type": "platform",
"version": "1.0.1"
},
"Dapper": "1.50.2",
"Npgsql": "3.1.9"
},
"frameworks": {
"netcoreapp1.0": {
"imports": "dnxcore50"
}
}
}

After saving the file, it will restore automatically and you can see the references as follow:

Step 3

Create a new class, Customer.cs, mapping to the table customer.

public class Customer {
public long Id {
get;
set;
}
public string FirstName {
get;
set;
}
public string LastName {
get;
set;
}
public string Email {
get;
set;
}
public DateTime CreateTime {
get;
set;
}
}

Step 4

Now, we will use Dapper to operate the PG database.

Insert

//1. Insert
using (var conn = OpenConnection(_connStr))
{
var insertSQL = string.Format(@"INSERT INTO public.customer(firstname, lastname, email,createtime) VALUES('{0}', '{1}', '{2}','{3}');", "Catcher", "Wong", "[email protected]", DateTime.Now);
var res = conn.Execute(insertSQL);
Console.WriteLine(res > 0 ? "insert successfully!" : "insert failure");
PrintData();
}

After running the above code, we will get the message "inserted successfully!" and get the customer's first name and his email.

Methods OpenConnection and PrintData are as follow.

/// <summary>
/// get the db connection
/// </summary>
/// <param name="connStr"></param>
/// <returns></returns>
public static IDbConnection OpenConnection(string connStr)
{
var conn = new NpgsqlConnection(connStr);
conn.Open();
return conn;
}
/// <summary>
/// print the data
/// </summary>
public static void PrintData()
{
IList<Customer> list;
//2.query
using (var conn = OpenConnection(_connStr))
{
var querySQL = @"SELECT id, firstname, lastname, email, createtime FROM public.customer;";
list = conn.Query<Customer>(querySQL).ToList();
}
if (list.Count > 0)
{
foreach (var item in list)
{//print
Console.WriteLine($"{item.FirstName}'s email is {item.Email}");
}
}
else
{
Console.WriteLine("the table is empty!");
}
}

Update

//2.update
using (var conn = OpenConnection(_connStr))
{
var updateSQL = string.Format(@"UPDATE public.customer SET email='{0}' WHERE id={1};", "[email protected]", GetMaxId());
var res = conn.Execute(updateSQL);
Console.WriteLine(res > 0 ? "update successfully!" : "update failure");
PrintData();
}

After running the above code, we will get the message "updated successfully!" and get the customer's first name and his new email.

Delete

//3.delete
using (var conn = OpenConnection(_connStr))
{
var deleteSQL = string.Format(@"DELETE FROM public.customer WHERE id={0};", GetMaxId());
var res = conn.Execute(deleteSQL);
Console.WriteLine(res > 0 ? "delete successfully!" : "delete failure");
PrintData();
}

After running the above code, we will get the message "deleted successfully!" and "the table is empty!"

Here is the result!

Summary

There are many combinations we can use during our dev times in .NET Core, such as EF Core + MS SQLServer, EF Core + SQLite, Dapper + MS SQL Server, etc.

4 Comments

Join the conversation! Your thoughts help the community grow.

Bohdan Stupak6y ago
It was quite helpful. Although it would be nice to have an example of setup for asp.net core.
Amol Vasaikar7y ago
Can you please using store procedure
Arjun Chandran7y ago
Hi , Can u tell me what does your connection string look like for your postgres db ?
- Catcher Wong7y ago
  You can following this page http://www.npgsql.org/doc/index.html