2

I have a table in MSSQL database, and I have an ASPX page, I need to push all new rows to the page in a descending order. I found this awesome tutorial which is using SignalR and SqlDependency and it shows only the last row descarding the previous rows which have been added when I'm online, it does that because it has a span element to show data and every time it overwrites this span, so I modified the JavaScript code to append the new data and it works fine.

The problem now is when I refreshed the page for the first time, I'll get the new rows twice, and if I refreshed the page again I'll get the new rows triple .. and so on.

The only solution is to close the application and reopen it again, it looks like reset the IIS.

So, what can I do to avoid duplicating data in the online show?

Wasp
  • 3,395
  • 19
  • 37
Ahmed Negm
  • 865
  • 1
  • 11
  • 30

2 Answers2

0

It is not a SignalR issue, that happens because the mentioned tutorial has a series of mistakes, the most evident being the fact that it continuously creates SqlDependency instances but then it trashes them without never unsubscribing from the OnChange event. You should start by adding something like this:

SqlDependency dependency = sender as SqlDependency;
dependency.OnChange -= dependency_OnChange;

before calling SendNotifications inside your event handler. Check this for some inspiration.

UPDATE (previous answer not fully accurate but kept in place for context)

The main problem here is that this technique creates a sort of auto-regenerating infinite sequence of SqlDependencies from inside instances of Web Forms pages, which makes them unreachable as soon as you page has finished rendering. This means, once your page lifecycle is complete and the page is rendered, the chain of dependencies stays alive and keeps on working even if the page instance which created has finished its cycle. The event handler also keeps the page instance alive even if unreachable, causing a memory leak.

The only way you can control this is actually to generate these chains somewhere else, for example within a static type you can call passing some unique identifier (maybe a combination of page name and username? that depends on your logic). At the first call it will do what currently happens in your current code, but as soon as you do another call with the same parameters it will do nothing, hence the previously created chain will go on being the only one notifying, with no duplicate calls.

It's just a suggestion, there would be many possible solutions, but you need to understand the original problem and the fact that it is practically impossible to remove those chains of auto-regenerating dependencies if you don't find a way to keep track of them and create them only when necessary. I hope that part is clear.

PS: this behavior is very similar to what you get sometimes with event handlers getting leaked and keeping alive objects which should be killed, this is what fooled me with the previous answer. It is in a way a similar problem (leaked objects), but with a totally different cause. The tutorial you follow does not clarify that and brings you to this situation where phantom code keeps on executing and memory is lost.

Community
  • 1
  • 1
Wasp
  • 3,395
  • 19
  • 37
  • 1
    SqlDependency doesn't fire event twice. To get the events flow continuously you should create SqlDependency again and again. – dyatchenko May 21 '15 at 11:14
  • @dyatchenko , what do you think? – Ahmed Negm May 21 '15 at 11:26
  • @AhmedNegm did you also try stopping the old dependency? – Wasp May 21 '15 at 11:30
  • @Wasp, yup, I stopped it in page load and reopen it again, and also I stopped it in Application_End event – Ahmed Negm May 21 '15 at 11:32
  • @dyatchenko you are right, I was having mixed memories, long time without using them + a lot of docs, even [official ones](https://msdn.microsoft.com/en-us/library/a52dhwx7(v=vs.80).aspx), recommending removing the handlers. – Wasp May 21 '15 at 12:34
0

I got it, although I don't like this way absolutely, I have declared a static member in the Global.asax file and in the Page_Load event I checked its value, if it was true don't run a new instance of SqlDependency, otherwise run it.

if (!Global.PageIsFired)
            {
                Global.PageIsFired = true;
                SqlDependency.Stop(ConfigurationManager.ConnectionStrings["db"].ConnectionString);
                SqlDependency.Start(ConfigurationManager.ConnectionStrings["db"].ConnectionString);
                SendNotifications();
            }

Dear @Wasp, Your last update helped me a lot to understand the problem, so thank you so much for your time and prompt support.

Dear @dyatchenko, Thanks a lot for your comments, it was very useful too.

Ahmed Negm
  • 865
  • 1
  • 11
  • 30