1

I am developing a windows application using C# . I want to show a balloon notification when a new row is inserted into a specific table. The application will be installed on many computers and will share a common database through LAN. Currently I am achieving this using polling. That is the application will query the database table each second to check if a new row is inserted.Please let me know whether this is the correct method or is there a better method for this? I know that using trigger we can execute queries in SQL server for table inserts, but is it possible to send a message back to the particular application from SQL server that has inserted the new row into the table? Please help

Akhilesh
  • 1,243
  • 4
  • 16
  • 49
  • Very open question... There are lots of approaches! Do you have a central service or do all the clients hit the database on each request? – Belogix Nov 04 '13 at 15:00
  • 1
    polling a database every second is likely bad for performance. Personally I would look into having a windows/web service that handles notifications and relays them out to all connected application. So when one instance of your app inserts a row, it notifies the service and then the service send out a notification to all the other connected (listening) applications. Good luck! – musefan Nov 04 '13 at 15:00
  • @Belogix Thanks. I don't have a central service. All the clients will poll the database. Please let me know the best approach. How can I create a central service? – Akhilesh Nov 04 '13 at 16:01
  • @musefan Thanks. Please let me know how can I create a windows service that handles notifications. – Akhilesh Nov 04 '13 at 16:04
  • @user1690835: Sorry but it is too much for me to be able to guide you through. You need to go a research the concept and see what you can implement on your own. Perhaps look into WCF, [here is a good start](http://idunno.org/archive/2008/05/29/wcf-callbacks-a-beginners-guide.aspx).. like I said, good luck, it's not easy! – musefan Nov 04 '13 at 16:24

1 Answers1

2

I think you can accomplish this with a combination of a SQL CLR Trigger and a Win32 API Function. Using the CLR Trigger will help avoid polling.

Here's an MSDN article on writing the CLR Trigger.

The sample code shown is logging to some audit table. Swap that part to make a call to the Shell_NotifyIcon Win32 API function. Here's a sample from MSDN on using the Shell_NotifyIcon to display the balloon notification.

The actual sample code is on this page (search for notificationicon.zip) and written in C++, but you should be able to modify it for C# once you do the DLL Import and use PInvoke. See this SO thread for more on that.

Community
  • 1
  • 1
Shiva
  • 20,575
  • 14
  • 82
  • 112
  • Do I understand this correctly: this CLR trigger would notify **any** instance of the application of an insert? Or would it notify only the instance that initiated the insert? – Andriy M Nov 05 '13 at 16:33
  • I haven't tested it, but I think it will notify only the instance that initiated the insert. To broadcast it, I wonder if you can raise an event in the CLR function that clients can subscribe to and in that event, do the Balloon notification. – Shiva Nov 05 '13 at 16:38
  • Then perhaps you should expand your answer. The OP is using polling, which most probably means they want to detect *any* new inserts to the table. – Andriy M Nov 05 '13 at 17:18
  • We are talking 3 different needs here. 1 is to detect new INSERTS. 2 Broadcast those inserts to ALL Clients. 3 Use Taskbar Ballon notification to broadcast individual messages. OP is using Polling to detect INSERTS, not to broadcast the messages. The CLR Trigger will solve #1, and the Win32 API code will solve #3. I'm leaving it up to OP to implement #2. – Shiva Nov 05 '13 at 18:56
  • They are polling the table to detect an insert. They use that method so that *any* instance of the application can see *any* insert. And they are looking for an alternative to the polling method. If you meant to answer the question, I would expect your suggestion to be that alternative, but you yourself said to the effect that it might not be so. That's why I am suggesting that you expand your *answer* with whatever additional information necessary to make your approach an actual replacement for the OP's implementation. (I realise, though, that it already may be a good starting point as it is) – Andriy M Nov 05 '13 at 20:18