Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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

.NET 2.0 and SQL Server 2005 combine in Query Notifications to notify applications when critical data changes occur and eliminate the need to repeatedly ask the database, "Has it changed yet?"


advertisement
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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date