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.