SqlDependency OnChange does not fire when database changed

68 Views Asked by At

I use SignalR to update the interface whenever there is a change in the SQL database. I'm sure I have enabled the Service Broker. The SqlDependency OnChange function still doesn't seem to respond when there is a change in the database. I've been searching the internet all day, but still haven't found a solution.

Here my code - Startup file

using Microsoft.Owin;
using Owin;
using System;
using System.Threading.Tasks;

[assembly: OwinStartup(typeof(JPGame.Startup))]

namespace JPGame
{
    public class Startup
    {
        public void Configuration(IAppBuilder app)
        {
            app.MapSignalR();
        }
    }
}

Register file

using JPGame.Hubs;
using Microsoft.AspNet.SignalR;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace JPGame.Component
{
    public class MemberCardComponent
    {
        public void RegisterMemberCard()
        {
            //string readerID = "ef7f36c1fc1d31c7";
            string conStr = ConfigurationManager.ConnectionStrings["sqlConString"].ConnectionString;
            string sqlCommand = @"SELECT [ID], [CardID], [ReaderID], [ScanAt] FROM [LiveCards]";
            //you can notice here I have added table name like this [dbo].[Contacts] with [dbo], its mendatory when you use Sql Dependency
            using (SqlConnection con = new SqlConnection(conStr))
            {
                SqlCommand cmd = new SqlCommand(sqlCommand, con);
                //cmd.Parameters.AddWithValue("@readerID", readerID);
                if (con.State != System.Data.ConnectionState.Open)
                {
                    con.Open();
                }

                //cmd.Notification = null;
                SqlDependency sqlDep = new SqlDependency(cmd);
                sqlDep.OnChange += sqlDep_OnChange;

                // we must have to execute the command here
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    // nothing need to add here now
                }
            }
        }

        private void sqlDep_OnChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type == SqlNotificationType.Change)
            {
                //SqlDependency sqlDep = sender as SqlDependency;
                //sqlDep.OnChange -= sqlDep_OnChange;

                //from here we will send notification message to client
                var memberCardHub = GlobalHost.ConnectionManager.GetHubContext<MemberCardHub>();
                memberCardHub.Clients.All.notify("cardscanned");
                //re-register notification
                RegisterMemberCard();
            }
        }
    }
}

Global

using JPGame.Component;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;

namespace JPGame
{
    public class MvcApplication : System.Web.HttpApplication
    {
        // Cấu hình chuỗi kết nối cơ sở dữ liệu từ web.config
        string con = ConfigurationManager.ConnectionStrings["sqlConString"].ConnectionString;
        protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);
            // Khởi động SqlDependency
            SqlDependency.Start(con);
        }

        protected void Session_Start(object sender, EventArgs e)
        {
            //Khởi tạo component
            MemberCardComponent MCC = new MemberCardComponent();
            MCC.RegisterMemberCard();
        }

        protected void Application_End()
        {
            SqlDependency.Stop(con);
        }
    }
}

I have tried changing the database, removing the SignalR NuGet package, but still nothing happens.

0

There are 0 best solutions below