The Lowly SqlNotificationRequest
SqlDependency is a high level implementation of the SqlNotificationRequest class. With SqlNotificationRequest, you are required to create your own Service Broker Services and Queues in SQL Server as well as your own listener to grab the notification. You may choose to use this lower level class for more granular control over the notification architecture. Another benefit is that you can create your own messages that are returned along with the notification, similar to the SqlNotificationInfo values.

Here are the key parts you need for doing notification with the SqlNotificationRequest class.

Create a queue and a then a service that hooks back to the queue in SQL Server using T-SQL. You can do this directly in SQL Server or just execute this code from your .NET application. The latter method gives you even more control and you can additionally remove them if you like. I have named mine myNotifQueue and myNotifService.

   CREATE QUEUE myNotifQueue
   CREATE SERVICE myNotifService ON QUEUE 
      myNotifQueue ([http://schemas.microsoft.com/
      SQL/Notifications/PostQueryNotification])

Note that the schema is case sensitive. You won't get an error message if you mis-case it, but you won't get your notification either.

Starting with what you now know about using SqlDependency, make a few changes to that process.

1. Create a SqlNotificationRequest object rather than a SqlDependency object, but without the SqlCommand as a parameter. Attach it with the SqlCommand.Notification property.
2. Define key properties of the SqlNotificationRequest: give UserID a GUID, point Options to the service you created previously, and apply a Timeout.
3. Attach the request to the command through the SqlCommand.
4. Most importantly, you must create a listener to retrieve notifications. This happens on a separate thread.

SqlNotificationRequest sets up its own SqlConnection for the notification to come back through. Therefore, this process does not require that you use the Start method used for SqlDependency and SqlCacheDependency.

The next example takes the RobustDependency class and replaces the GetData method with one that uses a SqlNotificationRequest instead. Additionally, because you won't be using the SqlDependency OnChange event, you write your own method inside the TableProxy class to call when the notification is retrieved.

Here is the modified GetData method. If nothing happens within the timeout period, a notification is returned with an EventArg indicating that a timeout occurred.

   Private Sub GetData(ByVal dtlocal As DataTable)
      If dtlocal.Rows.Count = 0 Then
         cmd.Notification = Nothing
         Dim snr As New SqlNotificationRequest()
         snr.UserData = New Guid().ToString()
         snr.Options = _
            "service=ContactChangeNotifications;"_
             & "local database=pubs"
         snr.Timeout = 600
         cmd.Notification = snr
         Using adapter As New SqlDataAdapter(cmd)
            adapter.Fill(dtlocal)
         End Using
         StartListener()
      End If
   End Sub

The StartListener method sets up a new thread and then fires up the Listen method on that thread.

Listen creates a new T-SQL WaitFor command to query the queue you created for this job. Note that you have set the timeout of the command to 15 seconds more than the timeout of the QueryNotificationRequest. Here's the code to set up the listener.

   Private Sub StartListener()
      Dim listener As New Thread(AddressOf Listen)
   
      listener.Name = "Notification Listener"
      listener.Start()
   End Sub
   
   Private Sub Listen()
      Using conn As SqlConnection = New _
         SqlConnection(myConnString)
         Using comm As New SqlCommand _
            ("WAITFOR ( RECEIVE * FROM " & _
            "ContactChangeMessages);", conn)
            conn.Open()
            comm.CommandTimeout = 615
            Dim obj As Object = comm.ExecuteScalar
            ' listener stops here until something is 
            ' returned
            TableProxy.DataChanged()
         End Using
      End Using
   End Sub

Listing 1 shows the rest of the class, including the new DataChanged method in the TableProxy class that replaces the event handler.