Notifier for System Tray - Fire when new record gets created in SQL Server

Question

Work on a support helpdesk. New tickets come in and records are created in a SQL Table and they are dumped to a specific category where any analyst can pick them up.
Front end does not notify analysts when there are new tickets, so have to contonously refresh the front-end (browser) to see if new tickets are coming into the 'To Be Assigned' folder.

Id like to create a notifier (similar to something like GMail Notifier) that sits in the system tray.

Anyone got some good starting points, or possible snippets that would give me some enthusiasm to actually get onto creating some sort of notifier?

Thanks

Answer 1

A systray notifier is basically going to do the same thing as a browser refresh, ping the server for new records. You'd probably want it to "ping" your server every 60 seconds, or faster or slower depending on how quick you want to be notified.

Making one in C# would be nearly trivial. But you didn't say which language you'd be using. I'm sure a Java guy would come in here and say its trivial in Java. A python guy would then come in and tell us both he can do it even easier.

Answer 2

If you are using .NET, you can use a Timer object and set the "tick" interval to 60 seconds. On every Tick event you would poll the SQL database table and store the date/time of the last ticket record creation in a variable. Then just check to see if there were any ticket records inserted with a creation date/time greater than the last creation date/time. You could even add another Timer component to "count down" to the next database poll. If you really wanted to get slick you put the database polling code in a BackGroundWorker thread so your GUI never blanks out while you wait for a SQL Server connection.

Answer 3

Polling is one way to do this, but if you are using SQL Server 2005 or later, a more elegant solution is to use SQL Server Query Notifications. This allows you to specify a query (for example "SELECT ID FROM Tickets") and be notified when the results change. There is a great tutorial on how to do this on CodeProject:

http://www.codeproject.com/KB/database/QueryNotifications.aspx