cyphersec A blog about Web Application Security and .NET development best practices

Cache Dependecies with ASP.NET 3.5 and SQL Server 2008

SQL Server 2008 as SQL Server 2005 get closest to the ideal notification solution we were looking for. For all of that say thanks to the notification infrastructure which is built into the database with a massaging system, called the Service Broker

The Service Broker manages queues, which are database objects that have the same standing as tables, stored procedures and views.

Using Service Broker, you can receive notifications for specific databas events. The most direct approach is to use the CREATE EVENT NOTIFICATION command to indicate the event you want to monitor. However, .NET offers a higher-level model that's integrated with ADO.NET. Using this model, you simply register a query command, and .NET automatically instructs SQL Server to send notifications for any operations that would affect the results of that query. ASP.NET offers an even higher-level that builds on this infrastructure, and allows to invalidate cached items automatically when a query is invalidated.

The SQL Server notification mechanism works in a similar way to indexed views. Everytime you perfom an operation, SQL Server determines whether your operation affects a registered command. If it does, SQL Server sends a notification message and stops the notification process.

When using notifications in SQL Server 2008 we get the following benefits:

• Finer-grained notification Instead of invalidating your cached obhect when the table changes, SQL Server invaldidates your object only when a row that affects your query is inserted / updated or deleted.
• More intelligent notification A notification message is sent the first time the data is changed, but not if the data is changed again.
• Fewer configuration steps Forget about aspnet_regsql or caching

The Only configuration step you need to perform is to make sure our database has the ENABLE_BROKER flag set.

   1: Use MyDatabase

   2: ALTER DATABASE MyDatabase SET ENABLE_BROKER

Notifications work with SELECT queries and stored procedures. However, some restrictions exist for the SELECT syntax you can use. To properly support notifications, your command must adhere to the following rules:

• You must fully qualify table names in the form as dbo.Employees (not just Employees)
• Your query cannot use an aggrate function such: COUNT(),MIN(),MAX()
• You cannot selecte all columns with the wildcard (star). Instead, you must specifically name each column so that SQL Server can proplery track changes that do and do not affect the results of your query.

Creating and getting the ASP.NET Cache Dependecy is now a joke.
Here is the code.

   1: string connectionString = WebConfigurationManager.ConnectionStrings["databaseName"].ConnectionString;

   2: SqlConnection connection = new SqlConnection(connectionString);

   3: string tQuery = "SELECT EmployeeID, FirstName, LastName, City FROM dbo.Employees";

   4: SqlCommand command = new SqlCommand(tQuery,connection);

   5: SqlDataApdater adapter = new SqlDataAdapter(command);

   6:  

   7: DataSet employeesDataset = new DataSet();

   8: adapter.Fill(ds, "Employees");

   9:  

  10: // Cache Dependecy

  11: SqlCacheDependency empDependency = new SqlCacheDependency(command);

  12:  

  13: // Add a cahce item that will be invalidated if one of its records changes

  14: Cache.Insert("Employees", employeesDataSet, empDependency);

  15:  

  16: // Dont Forget to add 

  17: // SqlDependency.Start(connectionString); (Global.asax.cs Application_Start)

  18: // SqlDependency.Stop(connetionString); (Global.asax.cs Application_Ends)

Event Raised: The event rised for any new incomig notification is SqlDependency.OnChange event

About Alessio Marziali

Alessio Marziali (MCTS) is a Security Consultant with 10 years of experience developing secure applications with Microsoft .NET in a variety of sectors in UK and Italy. Published technical author with two ASP.NET books currently available for purchase and OWASP Code Crawler Project Leader.