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 😊