RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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

.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?"

When you recover from the small thrill of seeing the notification truly coming back from the database, take a look at the value of the variable e, which is a SqlNotificationEventArgs object. SqlDependency always returns this object with the OnChange event and it can be very useful. SqlNotificationInfo is an enum with 18 possible values. Some of these are good and some indicate problems. Among the enums are Update, Insert, and Delete, telling you what type of change took place in the data. There are others that will not be sent as a result of a change. For example, restarting the server fires all notifications. Drop or Truncate tells you that something was done to the dependent table.

SqlDependency is a higher-level implementation of SqlNotificationRequest and is most likely the one you will use when working with ADO.NET 2.0.
In addition, there are cases when the dependency cannot even be registered, for example Invalid returns if you attempt to set a dependency on an UPDATE query. Query indicates that the syntax of your query does not follow the strict rules for notification. These last two, as well as a few others related to not being able to register the query, are returned immediately upon executing the command.

You can get the full list of these enums by looking up the SqlNotificationInfo Enumeration document in the MSDN Library.

When I talk about Query Notification at conferences, I am always asked if notification tells you what changed. The answer is no. The event args give you the most detail you are going to see in a notification and can be really useful for troubleshooting.

Real-World Use of the Notification
Now that you have an understanding of how this works, let's look at a more robust scenario. What really happens when that notification is returned?

In this next sample, a DataTable is used to cache some frequently used data that does not change often. It lives on the server and can be used, for example, in a Web service accessed by a Smart Client or in remoting. You can use it in an ASP.NET application, but there you also have the option of using SqlCacheDependency.

The DataTable is exposed by a static (C#) or shared (Visual Basic) method in a class. Static methods are available without instantiating the class they are a member of. The beauty of this is that the DataTable object will always be around and retain its state.

Whenever the cached data is requested, a method is called to retrieve the table. If the table has data in it, it is simply returned. If the table is empty, it is first populated with a database query and then returned.

Query notification comes into this pattern as a mechanism for clearing the data from the table and thereby requiring a data refresh. When the query is executed, a SqlDependency object is sent up to the database. When the notification is retrieved, the class reacts by clearing the DataTable. Therefore, the next time someone needs that DataTable, the class is forced to go to the database for fresh data.

Because of the differences in static/shared functionality between Visual Basic and C#, the structure of this code is different in C# than what you see here. A C# version of this class is available in the download for this article.

Create the main class. Mine is called RobustDependency. This class has two nested classes. The first, TableProxy, is a wrapper for the cached DataTable. The second, Retriever, communicates with SQL Server to populate the table and retrieve notifications.

   Public Class RobustDependency
      Public Class TableProxy
      End Class
      Public Class Retreiver
      End Class
   End Class
Next, build the Retriever class, which uses the SqlDependency. The Retriever's GetData method looks much the same as the previous example, except for a few things. First, it takes the cached DataTable as a parameter. Next, it tests to see if there are rows in the table. If there are no rows, it fills the table using a command that has a SqlDependency attached, just as you did above. Notice the AddHandler code. It points to an OnDependencyChanged method that you will be creating in the other nested class, TableProxy. Remember that the TableProxy is a shared (static) class. Just as the table will always be around, so will this method because you make it shared, as well. You can see the results of these changes in the code below:

   Public Class Retreiver
      Dim dep As SqlDependency
      Public state As String
      Public Sub GetData(ByRef dtLocal As DataTable)
         If dtLocal.Rows.Count = 0 Then
            Dim conn As SqlConnection = New _
               SqlConnection(ConfigurationManager. _
            Dim cmd As New SqlCommand( _
               "SELECT au_id,au_lname,au_fname " _
               & "FROM dbo.authors", conn)
            dep = New SqlDependency(cmd)
            AddHandler dep.OnChange, AddressOf _
            Dim rdr As SqlDataReader
            rdr = cmd.ExecuteReader()
         End If
      End Sub
   End Class
Now let's build the TableProxy class. This is where the static DataTable object lives. In Visual Basic, static variables need to reside within shared methods (here, it is in the shared function called Table) in order to retain their state across instances of the class. TableProxy has two methods:

  • Table, which returns the cached table dataset dt.
  • OnDependencyChanged, which gets called when the notification is retrieved. This second method is where the cached table gets cleared of its data.
Here's the code for those methods:

   Public Class TableProxy
      Shared Function Table() As DataTable
         Static dt As New DataTable
         Return dt
      End Function
      Public Shared Sub OnDependencyChanged _
         (ByVal sender As Object, ByVal e As _
         Dim depdt As DataTable = Table()
      End Sub
   End Class
Now you can glue this all together with functionality in the parent class, RobustDependency. Add the GetCache method, which again, because of the way Visual Basic handles shared methods and variables, must be shared. Then create a DataTable object, which you set equal to the cached DataTable inside of the TableProxy class, and then pass that into the GetData method of the retriever, which either does nothing to it or populates it with new data as shown in the following code:

   Imports System
   Imports System.Data
   Imports System.Data.SqlClient
   Public Class RobustDependency
      Public Shared dt As DataTable
      Public Shared Function GetCache() As DataTable
         Dim dt As DataTable = TableProxy.Table
         Dim dtc As New Retriever
         Return dt
      End Function
      Public Class TableProxy
      End Class
      Public Class Retreiver
      End Class
   End Class

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