I'm trying to make use of SqlDependancy in a SignalR project, but I can't seem to get the OnChanged event to fire more than once. It fires initially on the subscribe event, but it never fires again after making changes to the underlying database. I've omitted my SignalR and controller code because the problem seems to lie in the repository class. SqlDependancy.Start() is declared in my Global.asax class.
Watching from the SQL server, I can see a notification queue is created when my application starts, and is terminated when I close as well.
public IEnumerable<Visitor> NotifyAllClients()
{
List<Visitor> visitors = new List<Visitor>();
using (var connection = new SqlConnection(new VisitorLogEntities().Database.Connection.ConnectionString))
{
using (var command = new SqlCommand(@"SELECT * FROM dbo.Visitors", connection))
//using (var command = new SqlCommand(@"SELECT [Id],[AgreeToTerms],[Base64Image],[CheckInDate],[CheckOutTime],[Company],[CountryOfOrigin],[email],[FirstName],[LastName],[IsInBuilding],[MeetingSubject],[MeetingTime],[PatriotHost],[phone],[title] FROM dbo.Visitors", connection))
{
var dependency = new SqlDependency(command);
dependency.OnChange += Database_OnChange;
if (connection.State == System.Data.ConnectionState.Closed)
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
////compile visitor objects
////visitors.add(new Visitor());
}
}
return visitors.OrderByDescending(x => x.CheckInDate);
}
}
private void Database_OnChange(object sender, SqlNotificationEventArgs e)
{
//var dependency = (SqlDependency)sender;
//dependency.OnChange -= Database_OnChange;
////this fires once, with the Type of 'Subscribe', but then never fires on CRUD changes
if (e.Type == SqlNotificationType.Change)
{
VisitorHub.SendVisitors();
}
//NotifyAllClients();
}
edit: lines of code commented out above indicate the changes needed to get this working correctly.
