'Declaration Public Class PgSqlAlerter Inherits System.ComponentModel.Component Implements System.ComponentModel.IComponent, System.ComponentModel.ISupportInitialize, System.IDisposable
public class PgSqlAlerter : System.ComponentModel.Component, System.ComponentModel.IComponent, System.ComponentModel.ISupportInitialize, System.IDisposable
PgSqlAlerter allows you to register new notifications or listen to existing notifications on PostgreSQL server.
Notifications are posted to all clients when a client executes the NOTIFY command. Use PgSqlAlerter to handle events for responding to actions and database changes made by other applications.
To get events, application must register its intereset in the required events. To do it, enumerate names of the notifications in the AlertNames property and call the Start method. PgSqlAlerter issues the LISTEN command to listen to the specified events. When one of the registered events occurs, the Alert handler is called.
Notifications are transaction-based. This means that the waiting connection does not get an event until the transaction posting the event commits.
For detailed information on the mechanism please refer to PostgreSQL documentation, NOTIFY SQL command: http://www.postgresql.org/docs/8.3/interactive/sql-notify.html.
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
System.Object
System.MarshalByRefObject
System.ComponentModel.Component
Devart.Data.PostgreSql.PgSqlAlerter
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