Programmer's Highway

VB.NET Tips and simple code

I'm very sorry for machine translation in English.

VB.NET SQL Server 2005 Query Notification using SqlDependency

Sample code for query notification using SqlDependency.

By registering the event handler and SqlCommand to SqlDependency, database changes are notified.

Imports System.Data.SqlClient
    Private sqlDependency As SqlDependency
    ''' <summary>
    ''' Query Notification
    ''' </summary>
    Private Sub button1_Click(sender As Object, e As EventArgs)
        ' Connection Strings
        Dim connectionString As String = "Data Source=;Initial Catalog=TestDatabase;Integrated Security=True;"
        ' Start SqlDependency.
        Using sqlConnection As New SqlConnection(connectionString)
            Using sqlCommand As SqlCommand = sqlConnection.CreateCommand()
                sqlCommand.CommandText = "SELECT Student.Name, Student.Sex, Student. FROM dbo.Student"
                ' Register the event handler.
                Me.sqlDependency = New SqlDependency(sqlCommand)
                AddHandler Me.sqlDependency.OnChange, AddressOf OnChange
                ' If you need a query, call sqlCommand.ExecuteReader()
            End Using
        End Using
    End Sub
    ''' <summary>
    ''' Query Notification Event
    ''' </summary>
    Private Sub OnChange(sender As Object, e As EventArgs)
        ' If you do not need any more delete event
        RemoveHandler Me.sqlDependency.OnChange, AddressOf OnChange
    End Sub


  1. Query notification Supported by only a specific command(Special Considerations Using Query Notifications
  2. Are also notified if other than the SELECT column has been changed. So, If Age exists in the table, such as Name, Sex, in addition to Grade, it is also subject to monitoring
  3. The table name is specified by the schema name. So, The default schema name dbo in the sample so dbo.Student

For more information, please check the page for MSDN Query Notifications in SQL Server

Visual Studio 2010, .NET Framework 4, Microsoft SQL Server 2008