Postgres C# Trigger Function

Question

I am trying to call a function when new data is add to the database.

NpgsqlConnection connListenRun = new NpgsqlConnection("Server=main;Port=5432;Database=netdb;UserId=postgres;Password=password;");
        try
        {
            connListenRun.Open();
            NpgsqlCommand cmd = new NpgsqlCommand("listen RunLogClient;", connListenRun);
            cmd.ExecuteNonQuery();
            connListenRun.Notification += new NotificationEventHandler(RunFinishNotification);
        }
        catch (NpgsqlException ex)
        {
            MessageBox.Show(ex.ToString());
        }
        finally
        {
            //connListen.Close();
        }

private void RunFinishNotification(object sender, NpgsqlNotificationEventArgs e)
    {
        MessageBox.Show("Data!");
    }

However, my message box isn't showing when new data is added. On another program using the same trigger function has 'SyncNotification=true;' on the end of the conListenRun.

NpgsqlConnection connListenRun = new NpgsqlConnection("Server=main;Port=5432;Database=netdb;UserId=postgres;Password=password;SyncNotification=true;");

However, when i put 'SyncNotification=true;' on the statment i get this error:

: 'Keyword not supported: syncnotification Parameter name: keyword'

What am i doing wrong?

Thanks

Have a look here: npgsql.org/doc/wait.html ...Npgsql will only process it and emit an event to the user the next time a command is sent and processed. Can you check if its true for you? — Severius5
– Severius5, Commented Jun 30, 2018 at 19:41
Unrelated, but stick this in a using block, then you don't need finally — 404
– 404, Commented Jun 30, 2018 at 20:14

Adam · Accepted Answer · 2018-07-07 11:29:18Z

1

I was using the latest version of NPGSQL, 3.6.2 (I think) where as the other project was using version 2.11.96. Since using the older version the program works. I guess the newer NPGSQL uses a different way to do trigger functions.

answered Jul 7, 2018 at 11:29

Adam

6522 gold badges10 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

quicklikerabbit · Accepted Answer · 2019-05-13 19:46:51Z

Sample code with the npgsql.dll version
Know this works for 3.2.6.0 or form Postgres 10 onwards (which would likely mean ngpsql.dll v3.0 onwards).

public static void Main(string[] args)   
{
    bool flag = true;
    string server = "127.0.0.1";
    string port = "5432";
    string database = "postgres";
    string uid = "postgres";
    string password = "postgres";
    string connStr;

    // the connection string
    connStr = $"SERVER = {server}; Port = {port};  DATABASE = {database}; User Id = 
    {uid}; PASSWORD = {password};"; 
    NpgsqlConnection notificationConnection;
    NpgsqlConnectionStringBuilder csb = new NpgsqlConnectionStringBuilder(connStr);


    try
    {
        notificationConnection = new NpgsqlConnection(connStr);
        notificationConnection.Open();
        if (notificationConnection.State != System.Data.ConnectionState.Open)
        {
            WriteLine("Connection to database failed");
            return;
        }

        using (NpgsqlCommand cmd = new NpgsqlCommand($"LISTEN {notificationName}", 
               notificationConnection))
        {
            cmd.ExecuteNonQuery();
        }

        notificationConnection.Notification += PostgresNotification;
        notificationConnection.WaitAsync();
    }   
    catch(Exception ex)
   {
       WriteLine($"Exception thrown with message : {ex.Message}");
       return;
   }

   //  wait forever, press enter key to exit program  
   ReadLine();

   // stop the db notifcation
   notificationConnection.Notification -= PostgresNotification;
   using (var command = new NpgsqlCommand($"unlisten {notificationName}", 
      notificationConnection))
   {
       command.ExecuteNonQuery();
   }
       notificationConnection.Close();
}






//  the callback method that handles notification
static void PostgresNotification(object sender, NpgsqlNotificationEventArgs e)
{
     WriteLine("Notification Received");
}

Максим Попов · Accepted Answer · 2019-12-05 14:19:14Z

0

you are missing connconnListenRun.WaitAsync(); or connconnListenRun.Wait();

answered Dec 5, 2019 at 14:19

Максим Попов

11 bronze badge

Collectives™ on Stack Overflow

Postgres C# Trigger Function

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related