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", "[e-mail]", 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};", "[e-mail]", 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.