A Clockwork Engineer

PostgreSQL Advanced

Postgres Notes of a .NET Developer

This post is continuation of the series. If you do not have Postgres on your machine or sample data, you can read Installing PostgreSQL and Loading Sample Data post first.

There are functions like date_gt to compare date types.

select * from payment
where date_gt(payment_date::date, '2007-04-01'::date);

You can use date_part to get a part of datetime.

select amount, payment_date, date_part('quarter', payment_date) as quarter, 
date_part('year', payment_date) as year, concat('Q', date_part('quarter', payment_date), '-',
date_part('year', payment_date)) as display_quarter
from payment;

generate_series(start, stop, step) generates a series of values, from start to stop with a step size of step. We need a function like f(x) to get the value. If we use a negative number as step, it will be decreasing.

select x, md5(random()::text) from generate_series(100, 0, -5) as f(x);

select x from generate_series('2001-10-01'::TIMESTAMP, '2002-10-01'::TIMESTAMP, '10 days') as f(x);


Installing PostgreSQL and Loading Sample Data

Postgres Notes of a .NET Developer

As an experienced and certified .NET Developer, Postgres made my attention. I will tell about its reasons in another post.

I will write my notes about Postgres in this series. We will start with installing and loading sample data to our database in this post and we will have a result like below at the end.


First, download PostgreSQL database from PostgreSQL Downloads page to install. I use Windows so I chose Windows installer but you can use another operating system and also to develop because we can code ASP.NET Core Web Application in different operating systems.

Connect to Postgres with EF Core

Postgres Notes of a .NET Developer

We can use an ASP.NET Core Web Application template to create our Web API. We will use Npgsql, a PostgreSQL database provider for Entity Framework Core. Install its package from NuGet with the command below.

PM>  Install-Package Npgsql.EntityFrameworkCore.PostgreSQL

If you already have a Postgres database, add its connection string to appsettings.json as PostgreConnection or you can read Installing PostgreSQL and Loading Sample Data post in this series to install a database on your machine.


  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Warning"
  "Data": {
    "PostgreConnection": {
      "ConnectionString": "User ID=dvdclerk;Password=dvdsafe;Host=localhost;Port=5432;Database=dvdrental"

Write a model for film table of the sample data. Important part is Postgres is case sensitive here, so we need to define column and table names explicitly. Also EF wants to know the identity column of the table.

Deutschland 83 Encoded Floppy Disc

Deutschland 83 Encoded Floppy Disc

Deutschland 83 is a series about the political conflicts between West and East germany. In the third episode which is called “Atlantic Lion” of the first season, the underdeveloped technology of East Germany doesn’t let the officers to read the floppy disc of a NATO official.


They finally acquire an IBM Model 5155 computer from the West but they find the disc is encoded. When they insert the disc, it starts to print out number with 5 digits as 6 columns and unknown rows of matris. After that, they start to decode it.

On the next scene, we see a code block which generates random numbers on the screen with 3 for loops. As we can see from that, the number of rows is 64 and the matris is all random. Also there are some mistyped letters with Syntax error below.

C# 7.0 New Features in One File

C# 7.0 New Features in One File

C# now has some new features with the release of Visual Studio 2017. Those features focused on code simplification, performance and data consumption. I prepared a simple console application to show them in one file.

Out variables

out variables lets us to declare variables inline. I you don’t want to declare a variable for any of out variable you can use _ sign.

Patterns “is”

Constant, Type or Var patterns can be used in if statements and you can declare new inline variables as in out variables.

Switch statements

Switch has biggest improvements. You can switch, not just primitive types, any type you want. Patterns can be used in case clauses which can have additional conditions with when keyword.

New Octopus Deploy Step

My new pull request was recently merged to the Octopus Deploy Library.

Content Delivery Network (CDN) cache needs to be refreshed after deploying a Web UI project. I developed a custom step which runs a PowerShell script on Octopus, a well-known deployment tool in .NET eco-system, to trigger purge method of the CDN server which is Medianova CDN and added it to community step library.

Now, you can see the details of Medianova - Purge Step on Octopus Deploy Library and install it from the community step templates page of the Octopus dashboard.


PS: We should add the version parameter to the static files. This process will not renew the browser cache.

Run ASP.NET Core Websites with Command Line Arguments

The DLL file that contains ASP.NET Core application can run on KestrelHttpServer without the need to IIS. When you run it with dotnet cli, it works like a console application.

So we want to deploy it to Heroku cloud platform to see its cross-platform capabilites.

Heroku app engine runs the dotnet cli with the parameters below.

cd /app/heroku_output && dotnet ./Libton.dll --server.urls http://+:54372

The server.urls parameter sets the port variable which can be different everytime. The default value is 5000 in ASP.NET Core and our application doesn’t have the capability to read the command line arguments so it won’t set and listen the specified port.

In order to give this capability to our application, we could add the reference of Microsoft.Extensions.Configuration.CommandLine via NuGet Package Manager.

Now, we could convert the command line arguments to configuration settings and use them on the web host builder on Main method of the Program class.

Finally, the Program.cs file will look like this;

Attributes and Annotations of Testing Frameworks

Frequently used attribute and annotations in various testing frameworks

It is called attributes in the .NET environment and annotations in Java. We use them to declare information about methods, types, properties and so on.

We will discuss about frequently used ones to run test scenarious properly. You would find comparisons of NUnit, MSTest, xUnit.net and JUnit testing frameworks down below.

You could find very comprehensive tutorials on Guru99 website.

NUnit MSTest xUnit.net JUnit Description
[TestFixture] [TestClass] - - Indicates that the class has test methods.
[Test] [TestMethod] [Fact] @Test Marks a test case.
[OneTimeSetUp] [ClassInitialize] IClassFixture @BeforeClass The one time triggered method before test cases start.
[OneTimeTearDown] [ClassCleanup] IClassFixture @AfterClass The one time triggered method after test cases end.
[SetUp] [TestInitialize] Constructor @Before Triggered before every test case.
[TearDown] [TestCleanup] IDisposable.Dispose @After Triggered after every test case.
[Ignore] [Ignore] [Fact(Skip=”reason”)] @Ignore Ignores the test case.
[Category(“”)] [TestCategory(“”)] [Trait(“Category”, “”)] @Category(*.class) Categorizes the test cases or classes.