I am using signalR for notification i.e. when record is inserted in table, I have to inform user in my app as new notification. My code goes like this:
I global.asax file in Application_start():
SqlDependency.Start(@"data source=DESKTOP-591MN5Q\SQLEXPRESS01;initial catalog=Test_test;integrated security=True;");
My notification service class::
public static class Notification
{
static readonly string connString = @"data source=DESKTOP-591MN5Q\SQLEXPRESS01;initial catalog=Test_test;integrated security=True;";
internal static SqlCommand command = null;
internal static SqlDependency dependency = null;
public static string GetNotification()
{
try
{
var messages = new List<tblNotification>();
using (var connection = new SqlConnection(connString))
{
connection.Open();
using (command = new SqlCommand(@"SELECT [NotificationId],[UserId],[IsSeen],[Message],[CreatedDate],[ActionID] FROM [dbo].[tblNotification]", connection))
{
command.Notification = null;
if (dependency == null)
{
dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
}
if (connection.State == ConnectionState.Closed)
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
messages.Add(item: new tblNotification
{
NotificationId = (int)reader["NotificationId"],
UserId = (int)reader["UserId"],
IsSeen = (bool)reader["IsSeen"],
Message = reader["Message"] != DBNull.Value ? (string)reader["Message"] : "",
CreatedDate = (DateTime)reader["CreatedDate"],
ActionID = (int)reader["ActionID"]
});
}
}
}
var jsonSerialiser = new JavaScriptSerializer();
var json = jsonSerialiser.Serialize(messages);
return json;
}
catch (Exception ex)
{
return null;
}
}
private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
if (dependency != null)
{
dependency.OnChange -= dependency_OnChange;
dependency = null;
}
if (e.Type == SqlNotificationType.Change)
{
MyHub.Send();
}
}
}
What I understood is on page load, GetNotification() method will be called that will register dependency_OnChange() method such that if any changes is done in tblNotification, dependency_OnChange() will get triggered.
I have executed below script to enable change tracking:
ALTER DATABASE [Test_test]
SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
But still my code is not able to trigger dependency_OnChange() method when record is inserted in table.
What I have tried:
In executing GetNotification() method for first time, command.Notification = null. dependency will be initialized. After executing line var reader = command.ExecuteReader();, command.Notification will get value .