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
