dotConnect for PostgreSQL Documentation
Devart.Data.PostgreSql Namespace / PgSqlAlerter Class
Members Example

In This Topic
    PgSqlAlerter Class
    In This Topic
    Manages PostgreSQL notifications with LISTEN and NOTIFY commands.
    Syntax
    Remarks

    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.

    Example

    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
    Inheritance Hierarchy

    System.Object
       System.MarshalByRefObject
          System.ComponentModel.Component
             Devart.Data.PostgreSql.PgSqlAlerter

    Requirements

    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

    See Also