Category: sql server notification
tanmay.mishra on Wed, 29 Jan 2014 05:39:05
I am facing an issue while using SQL server Notifications. I am developing a web application in ASP.net where one of the page needs to be notified about new entries in one of the tables in SQL server database. I am using SQL Notification services along with Signal R to achieve this functionality.All seems to work fine with my web page getting updates about new data entries.
The problem arises when the page using notification is refreshed. I find the no of notification for single entry in database go up by the number of refreshes. So if I refresh the page thrice, I get 3 notifications for one entry. I am bit concerned if this would be a burden on server when the no of connected users increases. Also if there is an error while processing the request to update the page with new entry, the user gets multiple error messages with same text. I tried debugging my code and found out that the on change event of sqldependency object used is fired multiple time with different IDs every time. Below is brief overview of what my code is doing to use notifications -
1) I am using SQL Server 2012 and enable_broker is set for the database.
2) In global.asax, I am using application_start and application_stop events to start and stop SqlDependency.
3) In page code, I am setting a new sqldependency object on page load using a command object to monitor the exact data field of the table.
4) When onchange of sqldependency object fires, I am notifying the UI using Signal R hub class. Then I remove the onchange handler of sqldependency object, call for SqlDependency.Stop(connectionstring), set sqldependecny object to nothing, call for SqlDependency.Start(connectionstring) and finally set up the sqldependency object again using the command object for updated data. This whole set to nothing-stop-start-reset object is to continue monitoring the data for changes.
The above steps work fine but when I refresh the page, those are repeated for the number of refreshes. I tried a lot of things by changing code and debugging but nothing seems to resolve the issue. Now I am wondering if it is some setting somewhere that I missed.
Please help me resolve this issue. Also let me know if any other information such as environment, coding details etc are required.
Elvis Long on Fri, 31 Jan 2014 02:37:26
Thank you for your question. I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
Thank you for your understanding and support.
Puneeth - MSFT on Thu, 20 Feb 2014 16:04:41
We would require a sample repro to look at the issue. However, I'm writing down few steps which can be followed to make sure we have not missed any thing
1) Define a connection string in web.config
2) To have listener in Global.asax where we need to have SqlDependency.Start(.....) in Application_Start()
3) To stop the listener in in application_end() in global.asax by calling SqlDependency.Stop(...)
4) Create a class to have event on SQl Dependency change event by calling Hub method when we refresh the page.
5) Have hub class where we call the client method.
6) Have a view to display your data
Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
S.Lewandowski on Wed, 18 Jun 2014 07:39:49
Try to use
cachep123 on Wed, 25 Nov 2015 02:54:19
I'm sorry to lift your question up. I'm facing to the similar issue, I already gooled, binged but the answer still goes around. My solution is similar to github.com/venkatbaggu/signalrdatabasenotifications/
Have you resolved your issue? Please help me!