Getting Started With PostgreSQL Using Dapper In .NET Core

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;

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"  
            }  
        }  
    }

{

"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;  
        }  
    }

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", "catcher_hwq@outlook.com", DateTime.Now);  
        var res = conn.Execute(insertSQL);  
        Console.WriteLine(res > 0 ? "insert successfully!" : "insert failure");  
    PrintData();  
    }

//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!");  
    }  
    }

/// <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};", "catcher_hwq@163.com", GetMaxId());  
    var res = conn.Execute(updateSQL);  
    Console.WriteLine(res > 0 ? "update successfully!" : "update failure");  
PrintData();  
}

//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();  
    }

//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.