In this blog, we take a sample C# program running on an IBM Power partition with RHEL 8.7 installed and connect it to a PostgreSQL database on the same machine using the Npgsql.EntityFrameworkCore.PostgreSQL driver, which is published by Npgsql under PostgreSQL license - a relational database management system (RDBMS).
In this exercise you will learn how to write a console application that connects from a .NET C# program to a backend PostgreSQL database, to perform CRUD operations.
Note: Following steps are applicable for RHEL 8.7 or 9.1 on Power machine.
-
Set up the PostgreSQL database engine.
-
Install PostgreSQL packages.
# Install PostgreSQL packages
dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf update
dnf install postgresql-server postgresql postgresql-contrib
-
Start the PostgreSQL Server.
# Intialize and Start PostgreSQL Server
/usr/bin/postgresql-setup --initdb
systemctl start postgresql
systemctl enable postgresql
# Check the status of the SQLServer to verify it is running.
systemctl status postgresql
-
Modify password of the ‘postgres’ user.
passwd postgres
su - postgres
psql -c "ALTER USER postgres WITH PASSWORD '<some_password>';"
# Exit from postgres user which will switch back to root user
exit
-
Configure the authentication method
-
Open the ‘/var/lib/pgsql/data/pg_hba.conf’ file in a vi editor.
-
Look for the following lines and change the authentication method to md5.
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
-
Restart the Postgres service to apply the recent changes in the configuration.
systemctl reload postgresql
-
Install dotnet. See the Red Hat Product Documentation for more details about the installation.
yum install dotnet-sdk-7.0
Verify that dotnet is installed correctly.
dotnet --info
The output of the command is displayed below:
.NET SDK:
Version: 7.0.100
Commit: e12b7af219
Runtime Environment:
OS Name: rhel
OS Version: 8
OS Platform: Linux
RID: rhel.8-ppc64le
Base Path: /usr/lib64/dotnet/sdk/7.0.100/
Host:
Version: 7.0.0
Architecture: ppc64le
Commit: d099f075e4
.NET SDKs installed:
7.0.100 [/usr/lib64/dotnet/sdk]
.NET runtimes installed:
Microsoft.AspNetCore.App 7.0.0 [/usr/lib64/dotnet/shared/Microsoft.AspNetCore.App]
Microsoft.NETCore.App 7.0.0 [/usr/lib64/dotnet/shared/Microsoft.NETCore.App]
Other architectures found:
None
Environment variables:
DOTNET_ROOT [/usr/lib64/dotnet]
global.json file:
Not found
Learn more:
https://aka.ms/dotnet/info
Download .NET:
https://aka.ms/dotnet/download
-
Create a console program that connects to the PostgreSQL database and performs CRUD operations.
$ dotnet new console -o EFPostrgreSQL
$ cd EFPostrgreSQL
-
Create the Model.cs file with the following contents. This 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;database=test_db;user id=postgres;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; }
}
-
Edit the the Program.cs file with the following contents to connect to the PostgreSQL database and perform CRUD operations.
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 PostgreSQL database.
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL -v 7.0.0
After the above commands are run, the contents of the EFPostrgreSQL.csproj file should look as follows:
# cat EFPostrgreSQL.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>
-
Create the database schema using the following commands.
-
Switch to postgres user.
su - postgres
The output of the command is displayed below:
# su - postgres
Last login: Thu Dec 29 03:39:24 EST 2022 on pts/0
-
Create the blogs.sql file with the following contents at ‘/home’ location so that the ‘postgres’ user has create/access permissions to ‘/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 exists, and then to create the database schema.
psql -c "DROP DATABASE test_db"
psql -f /home/blogs.sql
# Exit from postgres user which will switch back to root user
exit
The output of the commands is displayed below:
$ psql -c "DROP DATABASE test_db"
psql: /lib64/libpq.so.5: no version information available (required by psql)
psql: /lib64/libpq.so.5: no version information available (required by psql)
DROP DATABASE
$ psql -f /home/blogs.sql
psql: /lib64/libpq.so.5: no version information available (required by psql)
psql: /lib64/libpq.so.5: no version information available (required by psql)
CREATE DATABASE
You are now connected to database "test_db" as user "postgres".
CREATE TABLE
CREATE TABLE
$ exit
logout
-
Build and run the program.
dotnet run
The output of the program after it runs successfully is displayed below:
# dotnet run
Inserting a new blog
Dumping database snapshot...
Number of records = 1
BlogId = 1, Url = http://blogs.msdn.com/adonet
Updating the blog and adding a post, blogid = 1
Dumping database snapshot...
Number of records = 1
BlogId = 1, Url = https://devblogs.microsoft.com/dotnet
----->PostId = 1, Title = Hello World, Content = I wrote an app using EF Core!
Delete the blog
Dumping database snapshot...
Number of records = 0
By implementing this functionality to connect from .NET applications on Linux on Power to a PostgreSQL database using EF Core, IBM Power customers will be able to more easily modernize their apps.