View Only

Connect .NET C# application to EDB PostgreSQL database on IBM Power

By Vikas Gupta posted Thu April 06, 2023 10:11 AM

Like

Updated: Dec 5, 2023

Over the past few months, we have been actively publishing technical blogs in the .NET on IBM Power resources domain. In response to numerous requests from our readers, we are excited to provide a comprehensive step-by-step guide for installing EDB PostgreSQL on Linux on Power. Additionally, we will demonstrate how to create a sample C# program that connects to the EDB PostgreSQL server and performs basic CRUD (Create, Read, Update, Delete) operations. So, let's dive in and get started with the installation process!

Prerequisites

Like we always love to say, first things first! Before you begin, check if you have the following infrastructure up and running:

A Power VM (virtual machine) with Red Hat Enterprise Linux (RHEL) 8.7. Refer to Accelerate your open source development with access to IBM Power resources for more information on access to Power hardware.
EDB PostgreSQL Advanced Server on Power VM. Refer to EDB installers on enterprisedb.com for PostgreSQL installer details.

For this blog, I installed the EDB PostgreSQL Advanced Server V14 on a Power VM with RHEL 8.7, however the steps here should also work without issues on RHEL 9. Perform the following steps on IBM Power VM to complete this activity.

Install the following packages.

$ yum install tar perl libxslt boost-date-time boost-filesystem boost-regex boost-system boost-thread boost-atomic boost-chrono lz4  tcl -y

Set up the EDB PostgreSQL database engine

Install EDB PostgreSQL on the Power VM by entering the following RPM commands into the terminal:

# Install EDBPostgreSQL packages
$ rpm -ivh edb-as12-server-libs-12.11.15-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-libs-14.4.0-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-client-14.4.0-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-contrib-14.4.0-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-libicu66-66.1-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-core-14.4.0-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-pgagent-4.2.2-2.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-parallel-clone-1.9-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-plperl-14.4.0-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-cloneschema-1.16-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-devel-14.4.0-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-docs-14.4.0-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-indexadvisor-14.4.0-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-pldebugger-1.1-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-plpython3-14.4.0-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-sqlprofiler-4.0-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-sqlprotect-14.4.0-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-sslutils-1.3-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-pltcl-14.4.0-1.rhel8.ppc64le.rpm
$ rpm -ivh edb-as14-server-14.4.0-1.rhel8.ppc64le.rpm

Start the EDB PostgreSQL server, using the following commands:

# Intialize and Start EDBPostgreSQL Server
$ PGSETUP_INITDB_OPTIONS="-E UTF-8" /usr/edb/as14/bin/edb-as-14-setup initdb
$ systemctl start edb-as-14

# Check the status of the "EDBPostgres Server" to verify it is running.
$ systemctl status edb-as-14

Configure the authentication method for the EDB PostgreSQL server, using these steps:
1. Open the ‘/var/lib/edb/as14/data/pg_hba.conf’ file in a vi editor.
2. Search for the following lines:
  
  host all all 127.0.0.1/32 md5
  
  host all all ::1/128 md5
3. Change the authentication method for both lines to md5.
4. Save the changes and close the editor.
Restart the EDB Postgres service to apply the recent changes, by running the following command:
```
$ systemctl reload edb-as-14
```

Modify the password of 'enterprisedb' user.

su - enterprisedb
psql -U enterprisedb -d postgres
postgres=# ALTER USER enterprisedb WITH PASSWORD '<some password>';
ALTER ROLE        # output of above ALTER USER command
# Exit from psql command prompt which will switch back to enterprisedb user
postgres=# quit
# Exit from enterprisedb user which will switch back to root user
exit

Create a database schema

To create a database schema, follow these steps:

Switch to the enterprisedb user, using the following command:
```
$ su – enterprisedb
```
Note that the default user type is enterprisedb.
Verify that you have successfully switched to the enterprisedb user by checking the output of previous command. It should display the current time stamp.
```
$ su - enterprisedb
Last login: Fri Feb 17 04:02:31 EST 2023 on pts/1
```

Create the ‘blogs.sql’ file at ‘/home’ location with the following contents. This will ensure that the ‘enterprisedb’ user has create/access permissions to the ‘/home’ directory.

CREATE DATABASE test_db;
\c test_db;

CREATE TABLE Blogs (
    BlogId INT GENERATED BY DEFAULT AS IDENTITY,
    Name VARCHAR (200) NULL,
    Url  VARCHAR (200) NULL,
    CONSTRAINT PK_Blogs PRIMARY KEY (BlogId)
);

CREATE TABLE Posts (
    PostId INT GENERATED BY DEFAULT AS IDENTITY,
    Title VARCHAR (200) NULL,
    Content TEXT NULL,
    BlogId INT NOT NULL,
    CONSTRAINT PK_Posts PRIMARY KEY (PostId),
    CONSTRAINT PK_Posts_Blogs_BlogId FOREIGN KEY (BlogId) REFERENCES Blogs (BlogId) ON DELETE CASCADE
);

Run the following commands to clear the ‘test_db’ if it already exists and then create the database schema.
```
psql -c "DROP DATABASE test_db" -U enterprisedb -d postgres
psql -f ./blogs.sql -d postgres
```
Note: ‘-d postgres’ is another database as we cannot perform a query on the same database.
Exit the ‘enterprisedb’ user by using the following command:
```
Exit
```
This will switch you back to the root user.

Install .NET

Install .NET by entering the following command in the terminal:
```
$ yum install dotnet-sdk-7.0
```

Verify the installation by using the dotnet –info command. Upon successful installation the following output displays:

$ dotnet --info
.NET SDK:
 Version:   7.0.102
 Commit:    4bbdd14480

Runtime Environment:
 OS Name:     rhel
 OS Version:  8
 OS Platform: Linux
 RID:         rhel.8-ppc64le
 Base Path:   /usr/lib64/dotnet/sdk/7.0.102/

Host:
  Version:      7.0.2
  Architecture: ppc64le
  Commit:       d037e070eb

.NET SDKs installed:
  7.0.102 [/usr/lib64/dotnet/sdk]

.NET runtimes installed:
  Microsoft.AspNetCore.App 7.0.2 [/usr/lib64/dotnet/shared/Microsoft.AspNetCore.App]
  Microsoft.NETCore.App 7.0.2 [/usr/lib64/dotnet/shared/Microsoft.NETCore.App]

Other architectures found:
  None

Environment variables:
  DOTNET_ROOT       [/usr/lib64/dotnet]

global.json file:
  Not found

For more information, refer to the following:

Dotnet command for additional information on .NET.
Download .NET for .NET download options.
Red Hat Product Documentation for more information on installation of .NET.

Create a C# application to access EDB PostgreSQL

Create a sample .NET console application using the following command:
```
$ dotnet new console -o EDBPostgreSQL
```
Note: I created the sample application with name “EDBPostgreSQL”.
Change the directory to ‘EDBPostgreSQL’ using the following command:
```
$ cd EDBPostgreSQL
```

Create the ‘Model.cs’ file using the following contents. It includes all the models that are required in the program.

using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

public class BloggingContext : DbContext
{
    public DbSet<Blog> blogs { get; set; }
    public DbSet<Post> posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseNpgsql("host=localhost:5444;database=test_db;username=enterprisedb;password=Dotnet123;");
    //protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    //    => optionsBuilder.UseNpgsql("host=localhost;database=test_db;user id=enterprisedb;password=Dotnet@70;");
}

public class Blog
{
    public int blogid { get; set; }
    public string url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int postid { get; set; }
    public string title { get; set; }
    public string content { get; set; }

    public int blogid { get; set; }
    public Blog Blog { get; set; }
}

Your project directory should contain a ‘Program.cs’ file by default. Open the file and paste the following code into it, then save and close the file. This code connects to the EDB PostgreSQL Server and performs the following actions: Creates a table, inserts/updates/deletes data, and reads the data.

using System;
using System.Linq;

public class Program
{
        public static void DumpDatabaseSnapshot(BloggingContext db)
        {
                // Read
                Console.WriteLine("\nDumping database snapshot...");
                var blogs = db.blogs
                        .OrderBy(b => b.blogid)
                        .ToList();

                Console.WriteLine("Number of records = {0}", blogs.Count);

                foreach(Blog b in blogs)
                {
                        Console.WriteLine("BlogId = {0}, Url = {1}", b.blogid, b.url);
                        if (b.Posts != null)
                        {
                                foreach(Post p in b.Posts)
                                {
                                        Console.WriteLine("----->PostId = {0}, Title = {1}, Content = {2}", p.postid, p.title, p.content);
                                }
                        }
                }

                Console.WriteLine("\n");
        }

        public static void Main(String[] args)
        {
                using var db = new BloggingContext();

                // Create
                Console.WriteLine("Inserting a new blog");
                db.Add(new Blog { url = "http://blogs.msdn.com/adonet" });
                db.SaveChanges();

                DumpDatabaseSnapshot(db);

                var blog = db.blogs
                        .OrderBy(b => b.blogid)
                        .First();

                // Update
                Console.WriteLine("Updating the blog and adding a post, blogid = {0}", blog.blogid);
                blog.url = "https://devblogs.microsoft.com/dotnet";
                blog.Posts = new List<Post>();
                blog.Posts.Add(
                new Post { title = "Hello World", content = "I wrote an app using EF Core!" });
                db.blogs.Update(blog);
                db.SaveChanges();

                DumpDatabaseSnapshot(db);

                // Delete
                Console.WriteLine("Delete the blog");
                db.Remove(blog);
                db.SaveChanges();

                DumpDatabaseSnapshot(db);
        }
}

Add the Entity Framework (EF) Core Npgsql package to the C# project file. This database provider allows EF Core to be used with EDB PostgreSQL database.

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL -v 7.0.0

After the above commands are run, the contents of the EDBPostgreSQL.csproj file should look as follows:

# cat EDBPostgreSQL.csproj
<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net7.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="7.0.0" />
  </ItemGroup>

</Project>

Run the dotnet build command to build your sample application. You should see the output displayed as follows.

$ dotnet build
MSBuild version 17.4.1+9a89d02ff for .NET
  Determining projects to restore...
  All projects are up-to-date for restore.
  EDBPostgreSQL -> /root/vikas/EDBPostgreSQL/bin/Debug/net7.0/EDBPostgreSQL.dll

Build succeeded.
    0 Warning(s)
0	Error(s)

Run the dotnet run command to verify your sample application. You should see the output displayed as follows.

$ dotnet run
Inserting a new blog

Dumping database snapshot...
Number of records = 1
BlogId = 4, Url = http://blogs.msdn.com/adonet


Updating the blog and adding a post, blogid = 4

Dumping database snapshot...
Number of records = 1
BlogId = 4, Url = https://devblogs.microsoft.com/dotnet
----->PostId = 4, Title = Hello World, Content = I wrote an app using EF Core!


Delete the blog

Dumping database snapshot...
Number of records = 0

Summary

To summarize, this blog has covered the installation of EDB PostgreSQL on a Power VM running RHEL. Additionally, we have shown you how to create a C# application that connects to the EDB PostgreSQL server and performs CRUD operations. We encourage readers to experiment with the platform's various capabilities and use cases to improve their development skills. If you have any questions or feedback, please don't hesitate to leave a comment below. We hope this guide has been helpful in your journey towards working with EDB PostgreSQL and C#.

Programming Languages on Power