SqlDependancy is firing more times

Question

Hi i am trying to implement sqldependancy with signalr for new database changes. but the problem is if i edit only one row then also sqldependancy is firing more time. and some time it is not firing. I have web application without MVC. bellow is my code

 public void page_load(object sender, EventArgs e)
        {
            //notification.GetAllUnreadSalesNotifications();
            NewScrapNotifications();

        }



        public void NewScrapNotifications()
        {


            string message = string.Empty;

            using (SqlConnection connection = new SqlConnection(IFTDAL.IFTCommon.DSN))
            {
                string query = "SELECT [Message] FROM [dbo].[DummyData]";

                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Notification = null;
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        reader.Read();
                        message = reader[0].ToString();
                    }
                }
            }
        }



        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {

            if (e.Type == SqlNotificationType.Change)
            {
                IFTHub nHub = new IFTHub();
                nHub.NotfiyAllClients();
               // NewScrapNotifications();
                //LoadDashboardByDashboardViewID(1);
            }
        }

and I also have bellow code in Globa.asax

 void Application_Start(object sender, EventArgs e)
            {
                // Code that runs on application startup
                RouteConfig.RegisterRoutes(RouteTable.Routes);
                BundleConfig.RegisterBundles(BundleTable.Bundles);
                SqlDependency.Start(DAL.DSN);

            }
            protected void Application_End(object sender, EventArgs e)
            {
                SqlDependency.Stop(DAL.DSN);
            }

Answer 1

You are creating new SQLDependency on each page load (refresh). I bet there is correlation between number of page reloads and number of notifications page is receiving via SignalR.

Register your notification only once per app lifetime...