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

  1. CREATE TABLE public.customer(  
  2. id serial NOT NULL,  
  3. firstname character varying(45) NOT NULL,  
  4. lastname character varying(45) NOT NULL,  
  5. email character varying(50) NOT NULL,  
  6. createtime timestamp without time zone NOT NULL,  
  7. PRIMARY KEY (id)  
  8. WITH (OIDS = FALSE) TABLESPACE pg_default;  
  9. ALTER TABLE  
  10. public.customer OWNER to dev;  

 

Step 2

Create a new Console Application (.NET Core).

Edit the project.json

  1. {  
  2.     "version""1.0.0-*",  
  3.     "buildOptions": {  
  4.         "emitEntryPoint"true  
  5.     },  
  6.     "dependencies": {  
  7.         "Microsoft.NETCore.App": {  
  8.             "type""platform",  
  9.             "version""1.0.1"  
  10.         },  
  11.         "Dapper""1.50.2",  
  12.         "Npgsql""3.1.9"  
  13.     },  
  14.     "frameworks": {  
  15.         "netcoreapp1.0": {  
  16.             "imports""dnxcore50"  
  17.         }  
  18.     }  
  19. }  

 

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.

  1. public class Customer {  
  2.     public long Id {  
  3.         get;  
  4.         set;  
  5.     }  
  6.     public string FirstName {  
  7.         get;  
  8.         set;  
  9.     }  
  10.     public string LastName {  
  11.         get;  
  12.         set;  
  13.     }  
  14.     public string Email {  
  15.         get;  
  16.         set;  
  17.     }  
  18.     public DateTime CreateTime {  
  19.         get;  
  20.         set;  
  21.     }  
  22. }  

 

Step 4 

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

Insert

 

  1. //1. Insert  
  2. using (var conn = OpenConnection(_connStr))  
  3. {  
  4.     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);  
  5.     var res = conn.Execute(insertSQL);  
  6.     Console.WriteLine(res > 0 ? "insert successfully!" : "insert failure");  
  7. PrintData();  
  8. }  

 

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.

 

  1. /// <summary>  
  2. /// get the db connection  
  3. /// </summary>  
  4. /// <param name="connStr"></param>  
  5. /// <returns></returns>  
  6. public static IDbConnection OpenConnection(string connStr)  
  7. {  
  8.     var conn = new NpgsqlConnection(connStr);  
  9.     conn.Open();  
  10. return conn;  
  11. }  
  12. /// <summary>  
  13. /// print the data  
  14. /// </summary>  
  15. public static void PrintData()  
  16. {  
  17.     IList<Customer> list;  
  18.     //2.query  
  19.     using (var conn = OpenConnection(_connStr))  
  20.     {  
  21.         var querySQL = @"SELECT id, firstname, lastname, email, createtime                      FROM public.customer;";  
  22.         list = conn.Query<Customer>(querySQL).ToList();  
  23.     }  
  24.     if (list.Count > 0)  
  25.     {  
  26.         foreach (var item in list)  
  27.         {//print  
  28.             Console.WriteLine($"{item.FirstName}'s email is {item.Email}");  
  29.         }  
  30.     }  
  31.     else  
  32.     {  
  33.         Console.WriteLine("the table is empty!");  
  34. }  
  35. }  

 

Update 

 

  1. //2.update  
  2. using (var conn = OpenConnection(_connStr))  
  3. {  
  4.     var updateSQL = string.Format(@"UPDATE public.customer                                 SET email='{0}'                                 WHERE id={1};""catcher_hwq@163.com", GetMaxId());  
  5.     var res = conn.Execute(updateSQL);  
  6.     Console.WriteLine(res > 0 ? "update successfully!" : "update failure");  
  7. PrintData();  
  8. }  

 

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

Delete 

 

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

 

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.