How to read or connect table’s data from PostgreSQL in C# .Net Core Application

Here in this article, we’ll create a .NET core Windows Forms application with ADO.NET and the PostgreSQL data provider.

By Following the steps below you’ll be able to do this:

Step 1: Create a .NET Core application for any platform like Console, Windows Forms, or any other web platform like ASP.NET Core.

I have created one Windows Forms Application in .NET Core, here is a quick look at my solution explorer:

Step 2:  Create a new database or table in your PostgreSQL. If you already have a table in your PostgreSQL Database, you can use that too.

I have created a new database named “Master” with one table named “Employees”. You can use the following SQL script if you want to use the same schema for this demo application.

-- Database: Master
-- DROP DATABASE IF EXISTS "Master";

CREATE DATABASE "Master"
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'English_India.1252'
    LC_CTYPE = 'English_India.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

-- Table: public.employees

-- DROP TABLE IF EXISTS public.employees;

CREATE TABLE IF NOT EXISTS public.employees
(
    employeeid integer,
    fullname character varying(50) COLLATE pg_catalog."default",
    salary integer
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.employees
    OWNER to postgres;

Once you ran/execute this SQL script in your PostgreSQL server you can find the database and table like this:

Step 3: To Execute the SQL commands from a .NET Application we need to have a PostgreSQL provider.

Open CLI and paste the bellow command to add “Npgsql” Official NuGet package for PostgreSQL:

dotnet add package Npgsql --version 7.0.1

Or you can open NuGet Package manager to manage the NuGet package by right-clicking on the project name from the solution explorer:

Now type “NpgSQL” in the first tab of this window and install this.

Step 4: We are all set now, Let's write the cone in your C# Code file now

we just need to have 8 statements which I mentioned in my code snippet as comments:

// Statement 1
using Npgsql; 

//Statement 2
var cs = "Host=localhost;Username=postgres;Password=pgadmin;Database=Master";

//Statement 3
NpgsqlConnection con = new NpgsqlConnection(cs);
con.Open();

//Statement 4
var sql = "Select * from Employees";

//Statement 5
NpgsqlCommand cmd = new NpgsqlCommand(sql, con);

//Statement 6
var dataReader = cmd.ExecuteReader();

//Statement 7
DataTable dt = new DataTable();
dt.Load(dataReader);

//Statement 8
con.Close();

Statement 1: using the package reference

using Npgsql;

Statement 2: Database connection string with a valid Host, Username and password

var cs = "Host=localhost;Username=postgres;Password=pgadmin;Database=Master";

Statement 3: Create a new PostgreSQL connection object and open this connection

NpgsqlConnection con = new NpgsqlConnection(cs);

Statement 4: SQL Query/command which needs to execute from .NET application.

var sql = "Select * from Employees";

Statement 5: Create a new PostgreSQL commands object and pass the query and connection reference to it.

NpgsqlCommand cmd = new NpgsqlCommand(sql, con);

Statement 6: Execute the command using the respective/suitable functions for the command’s result.

var dataReader = cmd.ExecuteReader();

Statement 7: Create the Data table object and load the data returned by your command from ExecuteReader()

DataTable dt = new DataTable();

dt.Load(dataReader);

Statement 8: Close the database connection. This may be optional based on your connection type.

con.Close();

Step 5: Display the data table's data in Grid View.

To show this table I have used a Data Grid view on my form and I’ll assign this table to its object “dataGridView1” on the Form1_Load event. Must look like this:

Press “F5” to run this sample:

I hope you got the idea, now go headed and use this in your real projects. Take care until next time 😊