CODEWITHSUNDEEP
c#.netsql-serversignalrsqldependency
Mukesh Kumar
Mukesh Kumar

Reputation: 696

Why SqlDependency.OnChange is not being fired?

After struggling with this issue from past 3 day, finally I'm putting my problem here.

I'm trying to build a real time application using SignalR and SqlDependency. Apparently, SQLDependency is not working. However, my SignalR is working fine as I've tried many functions that do not require Database interactions.

Below is my code. Here is I'm taking reference from.

Global.asax.cs

public class MvcApplication : System.Web.HttpApplication
{
    NotificationHub objNotificationHub;

    protected void Application_Start()
    {
        string connectionString = WebConfigurationManager.AppSettings["SQL"];
        // SQL Command Text
        string commandText = "SELECT status From tableTask";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(commandText, connection);
            SqlDependency dependency = new SqlDependency(command);
            dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);    
            SqlDependency.Start(connectionString);
            command.ExecuteReader().Dispose();
            objNotificationHub = new NotificationHub();
        }
    }

    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
        {
            objNotificationHub.SendNotifications();
        }
    }
}

NotificationHub.cs (SignalR Hub Class)

[HubMethodName("sendNotifications")]
public void SendNotifications()
{
    IHubContext context = GlobalHost.ConnectionManager.GetHubContext<NotificationHub>();
    context.Clients.All.recieveNotification("asbc");
}

My SqlDependency.OnChange event i.e. dependency_OnChange is not firing on database update.

I've tried all the methods like granting permissions

ALTER DATABASE MyDB SET ENABLE_BROKER

and many others like this. But no success.

Could there be anything that I'm missing. Also, is there any way to check if my code is communicating with SQL Server?

TIA

Upvotes: 3

Views: 3011

Answers (1)

Evk
Evk

Reputation: 101483

You are not executing your command, and that is needed to get notification:

SqlDependency.Start(connectionString);
string commandText = "SELECT status From dbo.tableTask"; // don't forget schema here
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(commandText, connection);
    SqlDependency dependency = new SqlDependency(command);
    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);    
    command.ExecuteReader().Dispose();
    objNotificationHub = new NotificationHub();
}

Ensure that you understand how those dependencies work (for example - after you receive one notification - you need to re-register to get subsequent). Or better, use some wrapper library, like this one.

You can test it with this simple example:

static void Main(string[] args) {
    var cs = "connection string";        
    using (SqlConnection connection = new SqlConnection(cs))
    {
        connection.Open();
        SqlCommand command = new SqlCommand("select ErrorCode from dbo.Error", connection);
        SqlDependency dependency = new SqlDependency(command);
        dependency.OnChange += OnChange;
        SqlDependency.Start(cs);
        command.ExecuteReader().Dispose();                
    }
    Console.ReadKey();
}

private static void OnChange(object sender, SqlNotificationEventArgs e) {
    Console.WriteLine(e.Info);
}

Upvotes: 5

Related Questions