Nine ASP.NET Site Navigation Problem Solutions: Part 2 : Page 4

Detecting Changed Data
Wouldn't it be great if the database could notify the site map provider any time its data changed without polling the server or using triggers? In fact, this is exactly what SQL Server 2005 can do with the new SqlCacheDependency class introduced in ASP.NET 2.0. To implement this new technology you must perform four steps:

1. Configure the environment
2. Initialize the database connection
3. Implement the SqlCacheDependency
4. Implement the call-back logic

Configuring the environment involves setting up the database and modifying Web.config. The documentation makes it sound like setting up the database is as easy as issuing the following statement, which turns on the service broker that handles callbacks:

   ALTER DATABASE Northwind SET ENABLE_BROKER

Unfortunately there are a number of other subtle requirements. The owner property of your SQL Server database must contain a value. You can find the property by right clicking on the database, selecting Properties, and then selecting Files from the Properties dialog. You will most likely want to set the property to sa. Your database must also have a Compatibility Level of 90. You can set this property under the Options category, also in the database properties dialog. Furthermore the account your application runs under must be granted a plethora of permissions if it's not an administrator account (one should hope not!). Here's a SQL script that grants the required permissions.

   GRANT CREATE PROCEDURE to [ASPNET];
   GRANT CREATE QUEUE to [ASPNET];
   GRANT CREATE SERVICE to [ASPNET];
   GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/
      PostQueryNotification] to [ASPNET];
   GRANT VIEW DEFINITION to [ASPNET];
   EXEC sp_addrole 'sql_dependency_subscriber'
   GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [ASPNET]
   GRANT RECEIVE ON QueryNotificationErrorsQueue TO [ASPNET]
   GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/
      PostQueryNotification] to [ASPNET]
   EXEC sp_addrolemember 'sql_dependency_subscriber', '[ASPNET]'
   GRANT SELECT TO [ASPNET]

You must also modify the Web.config file to notify ASP.NET to use the SqlCacheDependency. To do that, simply add the following within the <system.Web> element:

     <caching>
       <sqlCacheDependency enabled="true" />
     </caching>
   </system.Web>

After the configuration is complete the custom site map provider must initialize the database connection with a call to SqlDependency.Start(). This initialization must occur once during the lifetime of the application, so clearly the Initialize() method is the place to do it, as shown below:

   public override void Initialize(string name, 
      NameValueCollection attributes) {
   
      base.Initialize(name, attributes);
      string strConnectionName = attributes["connectionStringName"];
      if (String.IsNullOrEmpty("connectionStringName"))
         throw new ProviderException();
      mstrConnectionString =   
         WebConfigurationManager.ConnectionStrings[
         strConnectionName].ConnectionString;
      SqlDependency.Start(mstrConnectionString);
   }

The preceding Initialize() method retrieves the connectionStringName attribute from Web.config and uses that to retrieve the actual connection string from the connectionStrings section. The call to SqlCacheDependency.Start prepares the database to accept dependency information.

The third step, implementing SqlCacheDependency, involves instantiating the class and passing it a SqlCommand. This must occur prior to opening the connection and using the command, because ASP.NET 2.0 sends the dependency information along with the command's SQL or stored procedure (SQL Server 2005 can monitor either SQL statements or stored procedures).

   SqlConnection cn = new SqlConnection(mstrConnectionString);
   SqlCommand cm = new SqlCommand(
      "SELECT CategoryId, CategoryName FROM dbo.Categories," cn);
   SqlCacheDependency dependency = new SqlCacheDependency(cm);
   cn.Open();
   SqlDataReader sdr = cm.ExecuteReader();

Any SQL used by the SqlCommand must adhere to certain restrictions. First, it must specify each column, so SELECT * is not an option. Second, table names must use two-part names. For example, if the table belongs to the dbo schema (as in the preceding example), then the SQL must specify table names as the two-part dbo.<tablename>.

To complete implementation of the SqlCacheDependency, you need to insert the SqlCacheDependency into the HttpRuntime.Cache:

   HttpRuntime.Cache.Insert(
     "CategoriesDependency,"
     new object(),
     dependency,
     Cache.NoAbsoluteExpiration,
     Cache.NoSlidingExpiration,
     CacheItemPriority.NotRemovable,
     new CacheItemRemovedCallback(OnSiteMapChanged)
   );

The preceding code inserts a simple object named CategoriesDependency into the Cache that will expire only when the information in the database changes. When that happens, SQL Server 2005 notifies ASP.NET 2.0, which then removes the object and calls the callback function OnSiteMapChanged().

The last step is to implement the callback logic. If BuildSiteMap rebuilds when mnodeRoot is null, then the implementation of OnSiteMapChanged() is simple:

   public void OnSiteMapChanged(string strKey, 
      object item, CacheItemRemovedReason reason) {
   
      lock (mobjLock) {
         if (strKey.Equals("CategoriesDependency") && reason.Equals(
            CacheItemRemovedReason.DependencyChanged)) {
               Clear();
               mnodeRoot = null;
         }
      }
   }

Author's Note: Don't forget to lock the code because the function updates the state.

That completes the process. Remember to insert the date into your nodes to confirm that everything worked before testing. You can see the entire class in Listing 2.

A Little Debug Help
There are quite a few moving parts, and since everything occurs asynchronously troubleshooting the process can be difficult. If you're having difficulties, the first thing to check is whether the notification was successfully set in SQL Server, which you can check with a call to:

   select * from sys.dm_qn_subscriptions

If that command fails to return anything, check the SQL Server logs, because they're likely to describe the reason for failure. SQL Server Books Online contains a section about Troubleshooting Query Notifications.

Prior to ASP.NET 2.0, site navigation was either time consuming to develop, or—more likely—involved numerous maintenance hours when the site changed. But thanks to the new features in ASP.NET 2.0, everything from the initial setup through advanced techniques such as security is remarkably easy to implement and maintain. After reviewing the nine solutions presented in this two-part article, you should be well on your way to greatly simplifying the life of your Web site administrator—or perhaps your own.

Previous Page