PostgreSQL DB and Code-First .NET Core console application

In previous posts I showed how to build console .NET Core applications that use the EF Code First model to connect with SQL Server (http://blog.medhat.ca/2016/09/codefirst-with-net-core-command-line.html) and SQLite (http://blog.medhat.ca/2016/10/codefirst-with-net-core-console.html). In this post we will look at building a simple console .NET Core application that works with database server PostgreSQL. The database is created using the Code First development paradigm.
This is taken from https://www.postgresql.org/about/:

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP.

You can download PostgreSQL from https://www.postgresql.org/download/. Make sure you remember the password you entered because it belongs to the default super user named “postgres”.
After you download and install the software you can run the PostgreSQL management console by finding and launching “pgAdmin 4”.



When you double click on “PostgreSQL 9.6”, you will be prompted for the “postgres” password which you entered during installation. You can save the password if you enable the “Save Password” checkbox.



In the real world, you would not save the password in a production environment. I am saving the password for convenience as I am, essentially, in a test environment.

You can create a database named “test” and experiment with these SQL queries:

CREATE  TABLE employee (id SERIAL PRIMARY KEY, name VARCHAR(30) );
INSERT INTO employee (name) VALUES ('SAM');
SELECT * FROM employee;

This would create an employee table in the test database:



The command-line utility of PostgreSQL is psql. Find and launch SQL Shell (psql). Choose default values for Server [localhost], Database [postgres], Port [5432] and Username [postgres]. However, when prompted for “Password for user postgres:”, put the password you entered during installation.
To use the test database, enter “\c test;”. Remember to terminate all psql commands with the semicolon (;).

Next, let us view the contents of our employee table. Enter the following in the psql shell:

SELECT * FROM employee;

You should see the following:

id | name
----+------
  1 | SAM
(1 row)

To exit psql type “\q”.

Let is start building a simple console .NET Core app that uses the following Student entity:


1) Create a working directory named InstitutePostgreSQL.

2) From within a command prompt in that folder, type: dotnet new. This creates a Hello-World .NET Core console application.

3) Using Visual Studio Code, navigate to the InstitutePostgreSQL folder to open the project.

4) Open Program.cs and change the namespace to InstitutePostgreSQL then save the file.

5) To build the “Hello World” console app and run it, execute the following from the command prompt:

dotnet restore
dotnet build        (optional)
dotnet run

6) Since we will be accessing PostgreSQL using Entity Framework, we will need to add the following to the dependencies block of the project.json file:

"Microsoft.EntityFrameworkCore": "1.0.1",
"Npgsql.EntityFrameworkCore.PostgreSQL": "1.0.2",
"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final",
"Microsoft.Extensions.Configuration": "1.0.0",
"Microsoft.Extensions.Configuration.Json": "1.0.0"

7) Add another tools section to the project.json file as follows:

"tools": {
    "Microsoft.EntityFrameworkCore.Tools": {
      "version": "1.0.0-preview2-final",
      "imports": [
        "portable-net45+win8+dnxcore50",
        "portable-net45+win8"
      ]
    }
  }

8) At this stage it is appropriate to restore all the new additional dependencies that we will be using. Therefore, execute the following at the command prompt:

dotnet restore

9) Add a folder named “Models” and add to it a C# class file named Student.cs. Add the following code to Student.cs:

using System;
using System.Collections.Generic;
namespace InstitutePostgreSQL.Models {
  public class Student {
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Major { get; set; }
    public DateTime DateOfBirth { get; set; }
    public static List<Student> GetSampleStudents()   {
      List<Student> students = new List<Student>() {
        new Student {
          FirstName = "Ann",
          LastName = "Lee",
          Major = "Medicine",
          DateOfBirth = Convert.ToDateTime("2004/09/09")
        },
        new Student
        {
          FirstName = "Bob",
          LastName = "Doe",
          Major = "Engineering",
          DateOfBirth = Convert.ToDateTime("2005/09/09")
        },
        new Student {
          FirstName = "Sue",
          LastName = "Douglas",
          Major = "Pharmacy",
          DateOfBirth = Convert.ToDateTime("2006/01/01")
        },
        new Student {
          FirstName = "Tom",
          LastName = "Brown",
          Major = "Business",
          DateOfBirth = Convert.ToDateTime("2000/09/09")
        },
        new Student {
          FirstName = "Joe",
          LastName = "Mason",
          Major = "Health",
          DateOfBirth = Convert.ToDateTime("2001/01/01")
        }
      };
      return students;
    }
  }
}

The above code defines the properties of a Student class and adds a static method GetSampleStudents() that retrieves some sample data.

10) We will save the database connection string in a configuration JSON file. Create a file named appsettings.json in the root of your project with the following content:

{
  "ConnectionStrings": {
    "DefaultConnection": "User ID=postgres;Password=password;Host=localhost;Port=5432;Database=InstituteDB;Pooling=true;"
  }
}

Ensure that you enter the correct password for username postgres.

11) It is necessary to have a helper method that reads name/value pairs from the appsettings.json configuration file. To this end, we will create a class file named Utility.cs in the Models folder that fulfills this task. This file contains the following code:

using Microsoft.Extensions.Configuration;
namespace InstitutePostgreSQL.Models {
    public class Utility {
        public static string GetConnectionString(string key) {
            // Defines the sources of configuration information for the
            // application.
            var builder = new ConfigurationBuilder()
                .AddJsonFile("appsettings.json");
            // Create the configuration object that the application will
            // use to retrieve configuration information.
            var configuration = builder.Build();
            // Retrieve the configuration information.
            var configValue = configuration[key];
            return configValue;
        }
    }
}

12) Add another C# class file to the Models folder named InstituteContext.cs with the following code:

using Microsoft.EntityFrameworkCore;
namespace InstitutePostgreSQL.Models {
    public class InstituteContext : DbContext {
        public DbSet<Student> Students { get; set; }
        protected override void OnModelCreating(ModelBuilder builder) {
            builder.Entity<Student>().HasKey(m => m.Id);
            base.OnModelCreating(builder);
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  {
            string constr = Utility.GetConnectionString("ConnectionStrings:DefaultConnection");
            optionsBuilder.UseNpgsql(constr);
        }
    }
}

13) When the application is built, the .dll file is created in a directory somewhere under the bin folder. We need to make sure that appsettings.json is also copied to the same directory as the dll file so that it can be read. This is accomplished by adding the following to the “buildOptions” section of the project.json file:

"copyToOutput": {
  "include": [ "appsettings.json" ]
}

14) It is time to build our application. At a command-prompt in the project folder, type the following:

dotnet build

15) We can now create the database using Entity Framework’s code-first paradigm. While in the command prompt, execute the following two EF commands:

dotnet ef migrations add FirstMigration
dotnet ef database update

Upon completion of the above EF commands, the database will have been created.

16) We are now in a position to add and retrieve data to and from the database. Add the following methods to your Program.cs file:

private static void addStudents() {
    using (var db = new InstituteContext()) {
        db.Students.AddRange(Student.GetSampleStudents());
        var count = db.SaveChanges();
        Console.WriteLine("{0} records saved to database", count);
    }
}
private static void displayStudents() {
    using (var db = new InstituteContext()) {
        Console.WriteLine();
        Console.WriteLine("All students in database:");
        foreach (var s in db.Students) {
            Console.WriteLine("{0}\t{1}\t{2}", s.Id, s.FirstName, s.LastName);
        }
    }
}

17) Resolve the namespace for the Student and InstituteContext classes.

18) Replace the Main() method in Program.cs with the following:

public static void Main(string[] args) {
    addStudents();
    displayStudents();
}

19) This final step is the most revealing. If you run the application, it should add data to the Students table in the database and retrieve its contents. Excited … lets do it. Execute the following from the command prompt:

dotnet build
dotnet run

20) If all goes well, you see the following results:

All students in database:
1       Ann     Lee
2       Bob     Doe
3       Sue     Douglas
4       Tom     Brown
5       Joe     Mason

Since we are using PostgreSQL with .NET Core, this application can run equally well on Windows, Linux and the Mac. This is also true of SQLite discussed in a previous post.

Related Posts:

SQLite DB and Code-First .NET Core console application
PostgreSQL DB and Code-First .NET Core console application