Manages PostgreSQL notifications with LISTEN and NOTIFY commands.
In the following sample, an instance of PgSqlAlerter, 'recipient', is subscribed for several alerts. Another instance, 'sender', notifies server that these alerts should be sent. One more alert is sent by a trigger that fires when a new row is inserted into the Dept table.
The definitions of the trigger and the corresponding trigger function are:
CREATE OR REPLACE FUNCTION dept_insert_notice()
RETURNS trigger AS
$BODY$
BEGIN
NOTIFY "trigger event";
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER dept_insert_trigger
BEFORE INSERT
ON dept
FOR EACH ROW
EXECUTE PROCEDURE dept_insert_notice();
static public void PgSqlAlerter_Test(PgSqlConnection connection) {
string alerts = "event1,event2,trigger event";
// Create an alerter subscribed to the alerts specified above.
PgSqlAlerter recipient = new PgSqlAlerter(connection, alerts);
// Add event handlers for the events when an alert is incoming and when the alerter stops to listen.
recipient.Alert += new PgSqlAlerterAlertEventHandler(recipient_Alert);
recipient.Stopped += new PgSqlAlerterStoppedEventHandler(recipient_Stopped);
// Register alerts and start the waiting process
recipient.Start();
Console.WriteLine("The waiting process has been started");
// Signal all registered alerts
Notify(connection);
// Wait for the notifications from server
Thread.Sleep(1000);
recipient.Stop();
}
static public void Notify(PgSqlConnection connection) {
// Create another instance of PgSqlAlerter that will send notifications to the server.
PgSqlAlerter sender = new PgSqlAlerter();
sender.Connection = connection;
// Signal to the server that an alert should be sent.
sender.Signal("event1");
// Send several alerts at once:
sender.Signal("event2,event1");
//The following command inserts a row into the Dept table, firing the 'dept_insert_trigger' trigger:
PgSqlCommand cmd = new PgSqlCommand("insert into dept values (50, 'IT', 'San Diego')", connection);
cmd.ExecuteNonQuery();
//The following notification name was not registered, so the client will not receive this signal
sender.Signal("unknown event");
}
// A simple event handler that receives the incoming alert and prints its content.
static public void recipient_Alert(object sender, PgSqlAlerterAlertEventArgs e) {
Console.WriteLine("Received alert - {0}", e.AlertName);
}
// A simple event handler processing the Stopped event of the alerter.
static public void recipient_Stopped(object sender, PgSqlAlerterStoppedEventArgs e) {
Console.WriteLine("The waiting process has been stopped");
}
Sub PgSqlAlerter_Test(ByVal connection As PgSqlConnection)
Dim alerts As String = "event1,event2,trigger event"
' Create an alerter subscribed to the alerts specified above.
Dim recipient As PgSqlAlerter = New PgSqlAlerter(connection, alerts)
' Add event handlers for the events when an alert is incoming and when the alerter stops to listen.
AddHandler recipient.Alert, New PgSqlAlerterAlertEventHandler(AddressOf recipient_Alert)
AddHandler recipient.Stopped, New PgSqlAlerterStoppedEventHandler(AddressOf recipient_Stopped)
' Register alerts and start the waiting process
recipient.Start()
Console.WriteLine("The waiting process has been started")
' Signal all registered alerts
Notify(connection)
' Wait for the notifications from server
Thread.Sleep(100)
recipient.Stop()
End Sub
Sub Notify(ByVal connection As PgSqlConnection)
' Create another instance of PgSqlAlerter that will send notifications to the server.
Dim sender As New PgSqlAlerter
sender.Connection = connection
' Signal to the server that an alert should be sent.
sender.Signal("event1")
' Send several alerts at once:
sender.Signal("event2,event1")
'The following command inserts a row into the Dept table, firing the 'dept_insert_trigger' trigger:
Dim cmd As PgSqlCommand = New PgSqlCommand("insert into dept values (50, 'IT', 'San Diego')", connection)
cmd.ExecuteNonQuery()
' The following notification name was not registered, so the client will not receive this signal
sender.Signal("unknown event")
End Sub
' A simple event handler that receives the incoming alert and prints its content.
Sub recipient_Alert(ByVal sender As Object, ByVal e As PgSqlAlerterAlertEventArgs)
Console.WriteLine("Received alert - {0}", e.AlertName)
End Sub
' A simple event handler processing the Stopped event of the alerter.
Sub recipient_Stopped(ByVal sender As Object, ByVal e As PgSqlAlerterStoppedEventArgs)
Console.WriteLine("The waiting process has been stopped")
End Sub
Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2