.Net, .NET Core, PostgreSQL, Web API

Listen PostgreSQL in ASP.Net Core

Recently I was working with real-time charting application to display data on data changes to a particular table using PostgreSQL. In this post i am going to share that experience about how we can listen notified data from PostgreSQL using ASP.Net Core WebAPI.

Let’s start with creating database in PostgreSQL. Download and install PostgreSQL if not installed by following : https://www.postgresql.org/download

After completing installation process let’s create a database by login PostgreSQL admin panel.

Database:

In PgAdmin Right click on “Databases” then click create > Database to create a new database. Name it as you need, in this example i just named it “applicationlog”.

Or create database in “psql” with below create database statement.

-- Database: applicationlog
-- DROP DATABASE applicationlog;
CREATE DATABASE applicationlog
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'English_United States.1252'
    LC_CTYPE = 'English_United States.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

Table:

Create the table that is going to watch in PgAdmin.

Or create table using “psql” with below table create statement.

-- Table: public.tbllog
-- DROP TABLE public.tbllog;
CREATE TABLE public.tbllog
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE  CACHE 1 ),
    logdetails text COLLATE pg_catalog."default",
    logdate date,
    CONSTRAINT tbllog_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE public.tbllog
    OWNER to postgres;

Next is the most interesting part, sending notifications. We need to create a function to notify client on any DML operation. Also need to create a trigger that will pass the changes to that function.

Function:

Open query editor in PgAdmin execute below function statement

-- FUNCTION: public."NotifyLogChange"()
-- DROP FUNCTION public."NotifyLogChange"();
CREATE OR REPLACE FUNCTION public."NotifyLogChange"()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$ 
DECLARE 
  data JSON; notification JSON;
BEGIN   

  IF (TG_OP = 'INSERT')     THEN
         data = row_to_json(NEW);
  ELSIF (TG_OP = 'UPDATE')  THEN
         data = row_to_json(NEW);
  ELSIF (TG_OP = 'DELETE')  THEN
         data = row_to_json(OLD);
  END IF;
  
  notification = json_build_object(
            'table',TG_TABLE_NAME,
            'action', TG_OP,
            'data', data);  
                        
   PERFORM pg_notify('logchange', notification::TEXT);
   
  RETURN NEW;
END
$BODY$;

ALTER FUNCTION public."NotifyLogChange"()
    OWNER TO postgres;

Next create the trigger on particular table that will pass the changes to above function on any Insert, Update or Delete operation.

Trigger:

CREATE TRIGGER "OnLogChange"
    AFTER INSERT OR DELETE OR UPDATE 
    ON public.tbllog
    FOR EACH ROW
    EXECUTE PROCEDURE public."NotifyLogChange"();

Open query editor in PgAdmin execute above trigger statement.

As we can see from above image, the database creation and configuration part is done so far, next let’s focus on another part which is listening the fired event.

ASP.Net Core WebAPI:

Let’s create ASP.Net Core WebAPI application using Visual Studio 2019. Get help from:

Npgsql – .NET Access to PostgreSQL

Install “Npgsql” package using NuGet package manager. Npgsql is an open source ADO.NET Data Provider for PostgreSQL. More details from : https://www.npgsql.org

Process Notifications:

After completing installation process let’s create listener class that is going to receive notified data.

Event Listener

public async Task BrokerConfig()
{
    await using var con = new NpgsqlConnection(Staticinfos.conString);
    await con.OpenAsync();
    con.Notification += LogNotificationHelper;
    await using (var cmd = new NpgsqlCommand())
    {
        cmd.CommandText = "LISTEN logchange;";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        cmd.ExecuteNonQuery();
    }

    while (true)
    {
        // Waiting for Event
        con.Wait();
    }
}

After receiving data from above code section, broadcast received data using SIgnalR Hub with “LogNotificationHelper” event handler.

Event Handler

private void LogNotificationHelper(object sender, NpgsqlNotificationEventArgs e)
{
    //Deserialize Payload Data 
    var dataPayload = JsonConvert.DeserializeObject(e.Payload);
    Console.WriteLine("{0}", dataPayload.table + " :: " + dataPayload.action + " :: " + dataPayload.data.logdetails);

    //Notify Client using SignalR
}

Next we need to register the listener service to the application middleware pipeline with IApplicationBuilder.

Middleware Extension Method:

static class PostgreSQLBrokerExtension
{
    public static async Task UsePostgreSQLBroker(this IApplicationBuilder builder)
    {
        var broker = new PostgreSQLBroker();
        broker.BrokerConfig();
    }
}

Next call the middleware from Startup.cs

Add Middleware:

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    app.UsePostgreSQLBroker();
}

So far we are done, let’s build and run the application.

To insert data i am using a console application that is calling WebAPI. Following is the insertion process:

logModel = Applog.getlogData();
if (logModel != null)
{
    using (var client = new HttpClient())
    {
        string contentType = "application/json";
        string PostUrl = baseUrl + "api/log/setLogData";
        var content = new StringContent(JsonConvert.SerializeObject(logModel), Encoding.UTF8, contentType);
        using (HttpResponseMessage response = client.PostAsync(PostUrl, content).Result)
        {
            if (response.IsSuccessStatusCode)
                Console.WriteLine("{0}", logModel.logdetails);
        }
    }
}

Finally:

As we can see from above image right side black section is inserting data to database and left side panel is receiving data through event listening.

Hope this will help creating real-time application using PostgreSQL & ASP.Net Core WebAPI.

Download/clone full source code from .

References:

Author: Shashangka Shekhar

Since March 2011, have 8+ years of professional experience on software development, currently working as Senior Software Engineer at s3 Innovate Pte Ltd.

Previous post Dynamic Highstock Chart
Next post RocksDB Administration and Data…

10 thoughts on “Listen PostgreSQL in ASP.Net Core”

Leave a Reply