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
