sqldependency on change not always firing

Question

Here is my code:

 Public Sub RegisterNotification()
    Dim conStr As String = ConfigurationManager.ConnectionStrings("sqlConString").ConnectionString
    Dim sqlCommand As String = "SELECT [ID],[Description], [CreateDate], [NoteTypeID] from [note].[Notes]"

    Using con As New SqlConnection(conStr)
        Dim cmd As New SqlCommand(sqlCommand, con)
        If con.State <> System.Data.ConnectionState.Open Then
            con.Open()
        End If
        cmd.Notification = Nothing
        Dim sqlDep As New SqlDependency(cmd)
        AddHandler sqlDep.OnChange, AddressOf sqlDep_OnChange

        Using reader As SqlDataReader = cmd.ExecuteReader()
        End Using
    End Using
End Sub

Private Sub sqlDep_OnChange(sender As Object, e As SqlNotificationEventArgs)
    If e.Type = SqlNotificationType.Change Then
        Dim sqlDep As SqlDependency = TryCast(sender, SqlDependency)
        'Dim id
        RemoveHandler sqlDep.OnChange, AddressOf sqlDep_OnChange

        If e.Info = 1 Then


            Dim notificationHub = GlobalHost.ConnectionManager.GetHubContext(Of NotificationHub)()
            notificationHub.Clients.All.notify("added")
            RegisterNotification()
            'GetNotificationList(0, 1)
        End If


    End If
End Sub

Global.asax code:

 Protected Sub Application_Start()
    Try
        Dim _obj As New DAL.sap.Register
        _obj.Register()
        AreaRegistration.RegisterAllAreas()
        FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters)
        RouteConfig.RegisterRoutes(RouteTable.Routes)
        BundleConfig.RegisterBundles(BundleTable.Bundles)
        WebSecurity.InitializeDatabaseConnection("DefaultConnection", "webpages_UserProfile", "UserId", "UserName", True)


        SqlDependency.Start(con)
    Catch ex As Exception

    End Try



End Sub

Protected Sub Session_Start(sender As Object, e As EventArgs)
    Try
        Dim NC As New NotificationComponent()            
        NC.RegisterNotification()
    Catch ex As Exception

    End Try

End Sub
Protected Sub Application_End()
    Try
        SqlDependency.Stop(con)
    Catch ex As Exception

    End Try

End Sub

Startup code:

<Assembly: OwinStartupAttribute(GetType(www.Startup))>

Namespace www Partial Public Class Startup Public Sub Configuration(app As IAppBuilder) app.MapSignalR() End Sub End Class End Namespace

My problem is sqldep_onchange not always firing. Sometimes it works perfectly but sometimes nothing happen. is it a bug or something like that? what i'm doing wrong?

Kelso Sharp Kelso Sharp · Accepted Answer · 2016-09-08T13:10:13

It looks like everything is correct, although my VB is extremely rusty, I would try removing the if on the SqlNotificationType and see if that helps, it might be too restricting, I would also make sure that its actually the dependency that is not firing rather an something else swallowing it later on down the chain. Also what happens if your TryCast fails? Since its not in a try it will not throw and your notifications will not fire and your new handler will not be added.

Private Sub sqlDep_OnChange(sender As Object, e As SqlNotificationEventArgs)
If e.Type = SqlNotificationType.Change Then
    Dim sqlDep As SqlDependency = TryCast(sender, SqlDependency)
    'Dim id

   Delete --> ***RemoveHandler sqlDep.OnChange, AddressOf sqlDep_OnChange*** <--Delete

    If e.Info = 1 Then


        Dim notificationHub = GlobalHost.ConnectionManager.GetHubContext(Of NotificationHub)()
        notificationHub.Clients.All.notify("added")
        RegisterNotification()
        'GetNotificationList(0, 1)
    End If


End If

End Sub

sqldependency on change not always firing

1 Answers