codingZombies

How to listen to PostgreSQL events using .NET?

In a recent project I needed to monitor PostgreSQL database. Needless to say I never worked with this database and I had a hard time finding anything interesting on the net. Finally after asking a question on StackOverflow I was directed to SQL notify mechanism. I’ve spend some time going through the documentation and I want to share what I’ve found out.

Before you begin you will need .NET data provider for PostgreSQL which you can download here.

Some theory

Many databases support something that is called triggers – it’s like .NET event, you tell it when it should execute a function. To add a trigger you need to execute some SQL code which you can throw at you database from you favourite database management tool or by executing NpgsqlCommand.ExecuteNonQuery() from you .NET application.

The code

Before you create your trigger you need to define a function that you want execute based on the event.

CREATE OR REPLACE FUNCTION notify_demo() RETURNS TRIGGER AS '
BEGIN
	NOTIFY demoApp;
	RETURN NULL;
END;
' LANGUAGE plpgsql;

The only thing to remember is that function cannot take any arguments and that it needs to return TRIGGER. In the function itself we use notify and set the name of the notification (later we will listen for a notification with the same name).
At this point we are ready to create the trigger.

CREATE TRIGGER demo AFTER UPDATE
   ON users FOR EACH ROW
   EXECUTE PROCEDURE notify_demo();

It easy to see that we create a trigger named demo which will execute function notify_demo() after update on table users. We can set the trigger to execute before or after any of the fallowing events: insert, update, delete (all of which work on rows) and truncate (which works on statements).

In my project function and the trigger are installed by the setup process by executing NpgsqlCommand.ExecuteNonQuery().

When we have our trigger in place it’s time to write some .NET code

using System;
using Npgsql;

namespace TriggerDemo
{
    class Program
    {
        static void Main(string[] args)
        {

            string connString = "Server=127.0.0.1;Port=5432;User Id=postgres;Password=pass;SyncNotification=true";
            NpgsqlConnection conn = new NpgsqlConnection(connString);

            try
            {
                conn.Open();
                NpgsqlCommand cmd = new NpgsqlCommand("listen demoApp;", conn);
                cmd.ExecuteNonQuery();
                conn.Notification += new NotificationEventHandler(conn_Notification);
                Console.ReadLine();
                conn.Close();
            }
            catch (NpgsqlException ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadLine();
            }
        }

        static void conn_Notification(object sender, NpgsqlNotificationEventArgs e)
        {
            Console.WriteLine("Row was updated");
        }
    }
}

In the code you can see standard usage of ADO.NET (if you don’t know what it is ask uncle Google – there are some great tutorials out there). The things you should notice are the fallowing:

• SyncNotification=true – what it does is opens another thread and using TCP based connection will regularly ask the database if anything interesting happen. This means that you can easily put your database monitoring application on a different machine until the network connection is present and both machines can see each other. One thing you need to remember is that when connection has SyncNotification set to true you cannot execute other queries inside of the notification handler function or it will hang npgsql – you need to create a new connection.
• listen demoApp - this command listens to particular named trigger (in our case “demoApp”)
• conn.Notification += new NotificationEventHandler(conn_Notification); – well till now you should know this drill we connect a delegate to an event.

Congratulations – your program is now monitoring changes in PostgreSQL database.

Posted in: coding

Tags: .NET, c#, coding, databases, npgsql, postgresql

This entry was posted on Monday, February 15th, 2010 at 10:46 pm and is filed under coding. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.