--启动Service Broker服务支持

ALTER DATABASE AdventureWorks SET ENABLE_BROKER
--
--【提示】我们无法直接在sp_dboption中（使用“EXEC sp_dboption AdventureWorks”语句）
--看出某个数据库是否启动了Service Broker服务
--需要观察sys.databases的is_broker_enabled字段才知道是否已经启动—使用如下语句：

SELECT * FROM sys.databases

--允许某个账号订阅查询
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [YourComputerName\UserName]

Public Class DeskNotification

Dim conn As New SqlConnection(ADONET20.My.Settings.AdventureWorksConnection)
    Delegate Sub PopulateList()
    Private Sub DeskNotification_Load(ByVal sender As System.Object， ByVal e As

System.EventArgs) Handles MyBase.Load
        SqlDependency.Start(ADONET20.My.Settings.AdventureWorksConnection)
        ‘取得初始数据
        ListProducts()
    End Sub

Private Sub productListBox_SelectedIndexChanged(ByVal sender As System.Object，

ByVal e As System.EventArgs) Handles productListBox.SelectedIndexChanged
        Dim strItem As String = productListBox.SelectedItem.ToString
        lblId.Text = strItem.Substring(0， strItem.IndexOf("-") - 1)
        txtPrice.Text = strItem.Substring(strItem.IndexOf(":") + 1)
    End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object，

ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim cnn As New SqlConnection(ADONET20.My.Settings.AdventureWorksConnection)
        If lblId.Text = "无" Then
            MessageBox.Show("请选择某一条记录")
            Exit Sub
        End If
        cnn.Open()
        Dim cmd As New SqlCommand( _
        "UPDATE Production.Product SET ListPrice=" & txtPrice.Text & " WHERE

ProductID=" & lblId.Text， _
        cnn)
        cmd.ExecuteNonQuery()
        cnn.Close()

End Sub
    Sub OnDependencyChanged(ByVal sender As Object， ByVal e As SqlNotificationEventArgs)
        'SqlDependency对象的OnChanged事件触发时
        '要执行的业务逻辑
        Dim dR As DialogResult
        dR = MessageBox.Show("数据已经完毕，要更新数据吗？"， e.Info.ToString， _
        MessageBoxButtons.YesNo， MessageBoxIcon.Question)
        If dR = Windows.Forms.DialogResult.Yes Then
            '由表单的主线程实现数据更新
            Try
                Me.Invoke(New PopulateList(AddressOf ListProducts))
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End If
    End Sub

Public Sub ListProducts()
        '重新装载数据
        ' SqlDependency设置后，仅会注册一次的事件通知
        Dim dep As New SqlDependency()
        '设置SqlDependency对象的OnChanged事件发生时要调用哪个事件处理器
        AddHandler dep.OnChange， AddressOf OnDependencyChanged
        '限制查询的范围，避免太大的范围的大量用户都影响到这个范围内的数据以致使SQL Server
        '频繁地触发通知
        Using cmd As New SqlCommand( _
        "SELECT ProductID， Name， ListPrice FROM Production.Product " & _
        "WHERE ProductID BETWEEN @Start AND @End"， conn)
            With cmd
                .Parameters.Add(New SqlParameter("@Start"， Data.SqlDbType.Int))
                .Parameters.Add(New SqlParameter("@End"， Data.SqlDbType.Int))
                .Parameters(0).Value = txtStart.Text
                .Parameters(1).Value = txtEnd.Text
            End With

'自动帮助我们设置SqlCommand的Notification属性所需的SqlNotificationRequest对象
            '可以通过Debug来观察SqlCommand对象执行前后的关系
            dep.AddCommandDependency(cmd)
            productListBox.Items.Clear()
            conn.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                productListBox.Items.Add(reader("ProductID") & " - " & _
                reader("Name").ToString & ": " & reader("ListPrice").ToString)
            End While
        End Using
        conn.Close()
    End Sub

Private Sub DeskNotification_FormClosing(ByVal sender As System.Object，

ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        SqlDependency.Stop(ADONET20.My.Settings.AdventureWorksConnection)

End Sub
End Class