SQL Server 2005 Query Notifications Tell .NET 2.0 Apps When Critical Data Changes : Page 3

Your First Notification
Let's use SqlDependency to investigate how all of this works together.

Start by creating a separate class for your data access. I'll call mine NotificationTest. In this class, create a typical a function to query some data from the Authors table in the Pubs database returning a SqlDataReader.

   Imports System.Data.SqlClient
   Public Class NotificationTest
      Public Function DepTest() As SqlDataReader
         Dim conn As New SqlConnection(connstring)
         conn.Open()
         Dim cmd As New SqlCommand(
            "SELECT * FROM authors(", conn)")
         Dim rdr As SqlDataReader
         rdr = cmd.ExecuteReader()
         Return rdr
      End Function
   End Class

Now let's add in the dependency by modifying this code. First, declare a SqlDependency object named dep. Because you want this available to other functions in the class, make it a class variable.

Next, you need to change the query. Query Notification requires that you explicitly list the columns in your query as well as always use two-part table names. Note the new query text in the modified code sample.

Next, instantiate the new SqlDependency and attach it to the command.

That's it. When the command is executed, the dependency goes along with it up to the database. At the same time that it processes the query, SQL Server sees the dependency and sends it along to the Service Broker to get it registered.

   Imports System.Data.SqlClient
   Public Class NotificationTest
      Dim dep As SqlDependency
      Public Function DepTest() As SqlDataReader
         Dim conn As New SqlConnection(connstring)
         conn.Open()
         Dim cmd As New SqlCommand( _
            "SELECT au_id, au_lname,au_fname " & _
            "FROM dbo.authors", conn)
         dep = New SqlDependency(cmd)
         Dim rdr As SqlDataReader
         rdr = cmd.ExecuteReader()
         Return rdr
      End Function
   End Class

Now the dependency is registered but you have nothing to catch the notification when it comes back to the application. The SqlDependency class provides two ways to be aware of a notification. One is the OnChange event, which you can create a delegate to capture, and the other is the property HasChanges, which you can test for in your application logic. In the following code, I have added code into the OnDepChange event used for testing the notification at a later point.

   Imports System.Data.SqlClient
   Public Class NotificationTest
      Dim dep As SqlDependency
   
      Public Function DepTest() As SqlDataReader
   
         Dim conn As New SqlConnection(connstring)
         conn.Open()
         Dim cmd As New SqlCommand( _
            "SELECT au_id,au_lname,au_fname FROM " + _
            "dbo.authors", conn)
            dep = New SqlDependency(cmd)
         AddHandler dep.OnChange, AddressOf OnDepChange
   
         Dim rdr As SqlDataReader
         rdr = cmd.ExecuteReader()
         Return rdr
      End Function
   
      'Handler method
      Public Sub OnDepChange(ByVal sender As Object, _
         ByVal e As SqlNotificationEventArgs)
   
         Dim DepInfo As String = e.Info.ToString
         'do something in response to the notification
      End Sub
   
      Public ReadOnly Property HasChanges() As Boolean
         Get
            Return dep.HasChanges
         End Get
      End Property
   End Class

Now it is time to see it work. Place a breakpoint on the End Sub line of code in the OnDepChange event. Call the DepTest function from your favorite Web page, Windows form, or console application for testing purposes. After the SqlDataReader has been returned, open up the Authors table in Visual Studio 2005's Server Explorer or in SQL Server Management Studio and edit something in one of the fields. As soon as that change is locked in, for example, when you move your cursor to a new row in the table, the breakpoint should get hit.

Previous Page

Next Page