Skip to content

SQLDependency – Query notification dependency between an application and an instance of SQL

January 12, 2012

Before you think about using SqlDependency review this link (http://msdn.microsoft.com/en-us/library/ms181122(SQL.90).aspx). It will save you alot of time.

     
USE YourDatabaseName;

GRANT CREATE PROCEDURE TO YourUsername ;
GRANT CREATE SERVICE TO YourUsername ;
GRANT CREATE QUEUE TO YourUsername ;
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO YourUsername ;
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO YourUsername ;
GRANT CONTROL ON SCHEMA::[dbo] TO YourUsername ;
GRANT IMPERSONATE ON USER::DBO TO YourUsername ;

--First try this
ALTER DATABASE YourDatabaseName SET ENABLE_BROKER 
--If the first one hangs run this
ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE
    
Private sqlConn As SqlConnection
ReadOnly tReconnect As New System.Timers.Timer(10000)

Public Sub Main()
    SqlDependency.Stop(yourConnectionString)
    SqlDependency.Start(yourConnectionString)
    sqlConn = New SqlConnection(yourConnectionString)
    AddHandler tReconnect.Elapsed, New ElapsedEventHandler(AddressOf tReconnect_Tick)
    GetData()
End Sub

Private Sub dep_OnChange(ByVal sender As Object, ByVal e As SqlNotificationEventArgs)
    If e.Source = SqlNotificationSource.Client And e.Type = SqlNotificationType.Change And e.Info = SqlNotificationInfo.Error Then
        If Not tReconnect.Enabled Then
            tReconnect.Enabled = True
        End If
        Exit Sub
    End If

    Dim dep As SqlDependency = CType(sender, SqlDependency)
    RemoveHandler dep.OnChange, AddressOf dep_OnChange

    GetData()
End Sub

Function GetData() As Boolean
    GetData = False
    Try
        Using cmd As New SqlCommand(yourQueryString, sqlConn) With {.Notification = Nothing}
            Dim dep As New SqlDependency(cmd)
            AddHandler dep.OnChange, AddressOf dep_OnChange

            cmd.Connection.Open()
            Dim c = cmd.ExecuteScalar()
            cmd.Connection.Close()

            GetData = True
        End Using
    Catch ex As Exception
        'Error Handling
    End Try
End Function

Private Sub tReconnect_Tick(ByVal sender As Object, ByVal e As EventArgs)
    tReconnect.Stop()
    Try
        SqlDependency.Stop(yourConnectionString)
        SqlDependency.Start(yourConnectionString)

        GetData()

        tReconnect.Enabled = False
    Catch
        tReconnect.Start()
    End Try
End Sub
Advertisements

From → .Net, CodeProject, SQL

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: