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
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();
Statement 8: Close the database connection. This may be optional based on your connection type.
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 😊