Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using LISTEN/NOTIFY in C#.NET

Using LISTEN/NOTIFY in C#.NET

From
Josephine de Castro
Date:
Hi guys!
 
Just the other day, I asked about the the equivalent of extended stored procedure in PostgreSQL.  A lot of people has been very helpful and i really appreciate all the suggestions and help that I got from this mailing list. :)
 
Anyway, i've done some serious google-ing and reading and i came across the listen/notify commands in Postgresql. 
 
Actually, what i want to do is to 'somehow' connect my C# application with the database such that i will be able to capture insertion on a particular table.  Since I didnt know about the listen/notify commands, i considered creating a file from the stored procedure such that my 'FileSystemWatcher' object in C# can monitor if a table has been inserted by checking if a file with a particular name has been created.. this will then signal the C# application to connect to the database and process the newly inserted data. 
 
But then, I came across listen/notify and decided to give this a try since it theoretically covered what i really want to do..and that is to notify my C# program of any changes in my tableX.
 
So,  I was able to set-up the basic stuff..created stored procedure, created trigger on tableX, created listener procedure that will be run in my C# application and so on...
 
To make the long story short...my question is this..is it possible to run the listener command in C# or will it only work in psql? my program didnt generate any errors but it also didnt do anything..well..i think it did because my program hanged on me and no amount of 'notify' commands (manually issued in psql) can wake it up from its hanging state...so i realized that maybe the program is really in 'listening' state..but it seems like its listening for something else! argh! by the way, the rest of the C# code completed successfully.. its just that the form didnt fundtion normally..
 
oh well.. google seems to be lacking this time..i cannot find anything in there..i really feel like a dork posting here again but i really need some 'expert' opinion on this one..
 
if my 'story' is not enough, ill also post some snippets of my codes here...
 

CREATE OR REPLACE FUNCTION event_check() RETURNS trigger AS '

  BEGIN

        IF NEW.event_id = 2 THEN   -- issue notification to client

           NOTIFY event;

        END IF;

       

        RETURN NEW;

  END;

' LANGUAGE plpgsql;

 

CREATE TRIGGER tr_event AFTER INSERT

ON EVENT FOR EACH ROW

EXECUTE PROCEDURE EVENT_CHECK();

 

CREATE OR REPLACE FUNCTION event_listener() RETURNS varchar AS '

  BEGIN

        LISTEN event;

        RETURN null;

  END;

' LANGUAGE plpgsql;

 

 

This is my C# code...

 

string cmdstr = "SELECT event_listener();";

OdbcCommand cmd = new OdbcCommand(cmdstr,oODBCConnection);

cmd.CommandType = CommandType.StoredProcedure;

 

try

{

oODBCConnection.Open();

string listening_result;

listening_result = cmd.ExecuteScalar().ToString();

rtbox_show.AppendText(listening_result.ToString() + "\r\n");

 

-- truncated chunks of codes --

 

i know that this is such a long read.. im sorry if i was such a bore but im just a student and im asking for everyone's patience..

 

Thanks!

 

      

Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

Re: Using LISTEN/NOTIFY in C#.NET

From
Douglas McNaught
Date:
Josephine de Castro <jedecastro23@yahoo.com> writes:

> To make the long story short...my question is this..is it possible to run
> the listener command in C# or will it only work in psql?

There's no theoretical reason why not.  You probably need to read the
docs on whatever C# Postgres driver you're using (ADO, ODBC etc) and
find out how it handles LISTEN/NOTIFY, as that's not (AFAIK) a
standard feature.

-Doug

Re: Using LISTEN/NOTIFY in C#.NET

From
"Magnus Hagander"
Date:
> > To make the long story short...my question is this..is it
> possible to
> > run the listener command in C# or will it only work in psql?
>
> There's no theoretical reason why not.  You probably need to
> read the docs on whatever C# Postgres driver you're using
> (ADO, ODBC etc) and find out how it handles LISTEN/NOTIFY, as
> that's not (AFAIK) a standard feature.

IIRC, you need to use the NPgsql native driver to do it. It doesn't work
through ODBC or such - at least not the way you want it.


//Magnus