Rob C Rob C - 2 months ago 60
ASP.NET (C#) Question

SqlDependency with signalR not firing dependency_OnChange consistently

Setup

•Visual Studio 2010

•IIS 8.5

•.NET Framework 4.6

•Microsoft SQL Server 2014

•AppPool Account on IIS is domain\web

I have a web page that monitors changes in a database table. I am using dependency_OnChange to monitor the database and pass the data to the user via signalR. I set a breakpoint in the dependency_OnChange method and it is only getting hit a few times out of thousands of database updates.

In web.config... I am using Integrated Security=True.

My user is a sysadmin on the sql box. (This is just for proof of concept)

In Global.asax... specifying a queuename and stopping and starting sqldependency

void Application_Start(object sender, EventArgs e)
{
var queuename = "Q_Name";
var sConn = ConfigurationManager.ConnectionStrings["singalR_ConnString"].ConnectionString;
SqlDependency.Stop(sConn, queuename);
SqlDependency.Start(sConn, queuename);
}

void Application_End(object sender, EventArgs e)
{
var queuename = "Q_Name";
var sConn = ConfigurationManager.ConnectionStrings["singalR_ConnString"].ConnectionString;
SqlDependency.Stop(sConn, queuename);
}


In code behind...

public void SendNotifications()
{
//Identify Current User and Row No
string CurrentUser = GetNTName();


string message = string.Empty;
string conStr = ConfigurationManager.ConnectionStrings["singalR_ConnString"].ConnectionString;

using (SqlConnection connection = new SqlConnection(conStr))
{
string query = "SELECT [RowNo] FROM [dbo].[Test] WHERE [User] = @User";
string SERVICE_NAME = "Serv_Name";

using (SqlCommand command = new SqlCommand(query, connection))
{
// Add parameters and set values.
command.Parameters.Add("@User", SqlDbType.VarChar).Value = CurrentUser;
//Need to clear notification object
command.Notification = null;
//Create new instance of sql dependency eventlistener (re-register for change events)
SqlDependency dependency = new SqlDependency(command, "Service=" + SERVICE_NAME + ";", 0);
//SqlDependency dependency = new SqlDependency(command);
//Attach the change event handler which is responsible for calling the same SendNotifications() method once a change occurs.
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
connection.Open();
SqlDataReader reader = command.ExecuteReader();

if (reader.HasRows)
{
reader.Read();
message = reader[0].ToString();
}
}
}
//If query returns rows, read the first result and pass that to hub method - NotifyAllClients.
NotificationsHub nHub = new NotificationsHub();
nHub.NotifyAllClients(message);
}


private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
//Check type to make sure a data change is occurring
if (e.Type == SqlNotificationType.Change)
{
// Re-register for query notification SqlDependency Change events.
SendNotifications();
}
}

Answer

The first thing I would do here is refactor the Sql Dependency setup out to a stand alone method and call it from your send notification. (SoC and DRY) because if you are creating other SqlDependencies in other places they are going to trip each other up. Secondly your are creating a new NotificationsHub, You should be getting the currently active hub.

DefaultHubManager hubManager = new DefaultHubManager();
hub = hubManager.ResolveHub("NotificationsHub");
hub.NotifyAllClients(message);

I also have an example of a simpler version in this answer. Polling for database changes: SqlDependency, SignalR is Good

Let me know if you have any questions.