The secret to getting the most out of caching is choosing the right time to use it. A good caching strategy identifies the most frequently used pieces of data that are the most time-consuming to create and stores them. If you store too much information, you risk filling up the cache with relatively unimportant data and forcing out the content you really want to keep.

Here are two caching guidelines to keep you on the right track:

If you keep these two rules in mind, you can get two benefits from caching at once—you can improve both performance and scalability.

Performance is a measure of how quickly a web page works for a single user. Caching improves performance, because it bypasses bottlenecks like the database. As a result, web pages are processed and sent back to the client more quickly.

Scalability measures how the performance of your web application degrades as more and more people use it at the same time. Caching improves scalability, because it allows you to reuse the same information for requests that happen in quick succession. With caching, more and more people can use your website, but the number of trips to the database won't change very much. Therefore, the overall burden on the system will stay relatively constant, as shown in Figure 23-1.

Figure 23.1. The effect of good caching

Many optimization techniques enhance scalability at the cost of performance, or vice versa. Caching is remarkable because it gives you the best of both worlds.

ASP.NET really has two types of caching. Your applications can and should use both types, because they complement each other:

Also, two specialized types of caching build on these models:

In this chapter, you'll learn about all these types of caching. You'll begin by learning the basics of output caching, fragment caching, and data caching. Next, you'll examine the caching in the data source controls. Finally, you'll explore one of ASP.NET's hottest features—linking cached items to tables in a database with SQL cache dependencies.

Another option is to cache the page exclusively on the client side. In this case, the browser stores a copy and will automatically use this page if the client browses back to the page or retypes the page's URL. However, if the user clicks the Refresh button, the cached copy will be abandoned, and the page will be rerequested from the server, which will run the appropriate page code once again. You can cache a page on the client side using the Location attribute in the OutputCache directive, which specifies a value from the System.Web.UI.OutputCacheLocation enumeration, as shown here:

<%@ OutputCache Duration="20" VaryByParam="None" Location="Client" %>

Client-side caching is less common than server-side caching. Because the page is still re-created for every separate user, it won't reduce code execution or database access nearly as dramatically as server-side caching (which shares a single cached copy among all users). However, client-side caching can be a useful technique if your cached page uses some sort of personalized data. For example, imagine a page that displays a user-specific greeting. In this situation, server-side caching isn't ideal. The problem is that the page will be created just once and reused for all clients, ensuring that most will receive the wrong greeting. In this situation, you can either use fragment caching to cache the generic portion of the page or use client-side caching to store a user-specific version on each client's computer.

One of the main considerations in caching is deciding when a page can be reused and when information must be accurate up to the latest second. Developers, with their love of instant gratification (and lack of patience), generally tend to overemphasize the importance of real-time information. You can usually use caching to efficiently reuse slightly stale data without a problem and with a considerable performance improvement.

Of course, sometimes information needs to be dynamic. One example is if the page uses information from the current user's session to tailor the user interface. In this case, full page caching just isn't appropriate, because the same page can't be reused for requests from different users (although fragment caching may help). Another example is if the page is receiving information from another page through the query string. In this case, the page is too dynamic to cache—or is it?

The current example sets the VaryByParam attribute on the OutputCache directive to None, which effectively tells ASP.NET that you need to store only one copy of the cached page, which is suitable for all scenarios. If the request for this page adds query string arguments to the URL, it makes no difference—ASP.NET will always reuse the same output until it expires. You can test this by adding a query string parameter manually in the browser window. For example, try tacking ?a=b on to the end of your URL. The cached output is still used.

Based on this experiment, you might assume that output caching isn't suitable for pages that use query string arguments. But ASP.NET actually provides another option. You can set the VaryByParam attribute to * to indicate that the page uses the query string and to instruct ASP.NET to cache separate copies of the page for different query string arguments:

<%@ OutputCache Duration="20" VaryByParam="*" %>

Now when you request the page with additional query string information, ASP.NET will examine the query string. If the string matches a previous request and a cached copy of that page exists, it will be reused. Otherwise, a new copy of the page will be created and cached separately.

To get a better idea of how this process works, consider the following series of requests:

You can try this on your own, although you might want to lengthen the amount of time that the cached page is retained to make it easier to test.

Setting VaryByParam to the wildcard asterisk (*) is unnecessarily vague. It's usually better to specifically identify an important query string variable by name. Here's an example:

<%@ OutputCache Duration="20" VaryByParam="ProductID" %>

In this case, ASP.NET will examine the query string, looking for the ProductID parameter. Requests with different ProductID parameters will be cached separately, but all other parameters will be ignored. This is particularly useful if the page may be passed additional query string information that it doesn't use. ASP.NET has no way to distinguish the "important" query string parameters without your help.

You can specify several parameters as long as you separate them with semicolons:

<%@ OutputCache Duration="20" VaryByParam="ProductID;CurrencyType" %>

In this case, ASP.NET will cache separate versions, provided the query string differs by ProductID or CurrencyType.

The following example uses two web pages to demonstrate how multiple versions of a web page can be cached separately. The first page, QueryStringSender.aspx, isn't cached. It provides three buttons, as shown in Figure 23-3.

Figure 23.3. Three page options

A single event handler handles the Click event for all three buttons. The event handler navigates to the QueryStringRecipient.aspx page and adds a Version parameter to the query string to indicate which button was clicked—cmdNormal, cmdLarge, or cmdSmall.

Protected Sub cmdVersion_Click(ByVal sender As Object, _
  ByVal e As System.EventArgs) _
  Handles cmdNormal.Click, cmdLarge.Click, cmdSmall.Click

    Response.Redirect("QueryStringRecipient.aspx" & _
      "?Version=" & CType(sender, Control).ID)
End Sub

The QueryStringRecipient.aspx destination page displays the familiar date message. The page uses an OutputCache directive that looks for a single query string parameter (named Version):

<%@ OutputCache Duration="60" VaryByParam="Version" %>

In other words, this has three separately maintained HTML outputs: one where Version equals cmdSmall, one where Version equals cmdLarge, and one where Version equals cmdNormal.

Although it isn't necessary for this example, the Page.Load event handler in QueryRecipient.aspx tailors the page by changing the font size of the label accordingly. This makes it easy to distinguish the three versions of the page and verify that the caching is working as expected.

Protected Sub Page_Load(ByVal sender As Object, _
  ByVal e As System.EventArgs) Handles Me.Load

    lblDate.Text = "The time is now:<br />" & DateTime.Now.ToString()

    Select Case Request.QueryString("Version")
        Case "cmdLarge"
            lblDate.Font.Size = FontUnit.XLarge
        Case "cmdNormal"
            lblDate.Font.Size = FontUnit.Large
        Case "cmdSmall"
            lblDate.Font.Size = FontUnit.Small
    End Select
End Sub

Figure 23-4 shows one of the cached outputs for this page.

Figure 23.4. One page with three cached outputs

In some cases, you may find that you can't cache an entire page, but you would still like to cache a portion that is expensive to create and doesn't vary frequently (like a list of categories in a product catalog). One way to implement this sort of scenario is to use data caching to store just the underlying information used for the page. You'll examine this technique in the next section. Another option is to use fragment caching.

To implement fragment caching, you need to create a user control for the portion of the page you want to cache. You can then add the OutputCache directive to the user control. The result is that the page will not be cached, but the user control will.

Fragment caching is conceptually the same as page caching. It has only one catch—if your page retrieves a cached version of a user control, it cannot interact with it in code. For example, if your user control provides properties, your web page code cannot modify or access these properties. When the cached version of the user control is used, a block of HTML is simply inserted into the page. The corresponding user control object is not available. To avoid potential problems in your code, don't attempt to interact with it in your code or check that it's not a null reference (Nothing) before you do.

One problem with output caching is that you need to embed the instruction into the page—either in the .aspx markup portion or in the code of the class. Although the first option (using the OutputCache directive) is relatively clean, it still produces management problems if you create dozens of cached pages. If you want to change the caching for all these pages (for example, moving the caching duration from 30 to 60 seconds), you need to modify every page. ASP.NET also needs to recompile these pages.

ASP.NET includes a feature called cache profiles that makes it easy to apply the same caching settings to a group of pages. With cache profiles, you define a group of caching settings in the web.config file, associate a name with these settings, and then apply these settings to multiple pages using the name. That way, you have the freedom to modify all the linked pages at once simply by changing the caching profile in the web.config file.

To define a cache profile, you use the <add> tag in the <outputCacheProfiles> section, as follows. You assign a name and a duration.

<configuration>
  <system.web>
    <caching>
      <outputCacheSettings>
        <outputCacheProfiles>
          <add name="ProductItemCacheProfile" duration="60" />
        </outputCacheProfiles>
      </outputCacheSettings>
    </caching>
  ...
  </system.web>
</configuration>

You can now use this profile in a page through the CacheProfile attribute:

<%@ OutputCache CacheProfile="ProductItemCacheProfile" VaryByParam="None" %>

Interestingly, if you want to apply other caching details, such as the VaryByParam behavior, you can set it either as an attribute in the OutputCache directive or as an attribute of the <add> tag for the profile. Just make sure you start with a lowercase letter if you use the <add> tag, because the property names are camel case, as are all configuration settings, and case is important in XML.

You can insert an object into the cache in several ways. You can simply assign it to a new key name (as you would with the Session or Application collection):

Cache("KeyName") = objectToCache

However, this approach is generally discouraged because it doesn't give you any control over the amount of time the object will be retained in the cache. A better approach is to use the Insert() method.

The Insert() method has four overloaded versions. The most useful one requires five parameters:

Cache.Insert(key, item, dependencies, absoluteExpiration, slidingExpiration)

Table 23-1 describes these parameters.

Typically, you won't use all of these parameters at once. Cache dependencies, for example, are a special tool you'll consider a little later in the "Caching with Dependencies" section. Also, you cannot set both a sliding expiration and an absolute expiration policy at the same time. If you want to use an absolute expiration, set the slidingExpiration parameter to TimeSpan.Zero:

Cache.Insert("MyItem", obj, Nothing, _
  DateTime.Now.AddMinutes(60), TimeSpan.Zero)

Absolute expirations are best when you know the information in a given item can be considered valid only for a specific amount of time (such as a stock chart or a weather report). Sliding expiration, on the other hand, is more useful when you know that a cached item will always remain valid (such as with historical data or a product catalog) but should still be allowed to expire if it isn't being used. To set a sliding expiration policy, set the absoluteExpiration parameter to DateTime.MaxValue, as shown here:

Cache.Insert("MyItem", obj, Nothing, _
  DateTime.MaxValue, TimeSpan.FromMinutes(10))

The following page presents a simple caching test. An item is cached for 30 seconds and reused for requests in that time. The page code always runs (because the page itself isn't cached), checks the cache, and retrieves or constructs the item as needed. It also reports whether the item was found in the cache.

Public Partial Class SimpleDataCache
    Inherits System.Web.UI.Page

     Protected Sub Page_Load(ByVal sender As Object, _
       ByVal e As System.EventArgs) Handles Me.Load

        If Me.IsPostBack Then
            lblInfo.Text &= "Page posted back.<br />"
        Else
            lblInfo.Text &= "Page created.<br />"
        End If

        If Cache("TestItem") Is Nothing Then
            lblInfo.Text &= "Creating TestItem...<br />"
            Dim testItem As DateTime = DateTime.Now

            lblInfo.Text &= "Storing TestItem in cache "
            lblInfo.Text &= "for 30 seconds.<br />"
            Cache.Insert("TestItem", testItem, Nothing, _
              DateTime.Now.AddSeconds(30), TimeSpan.Zero)
        Else
            lblInfo.Text &= "Retrieving TestItem...<br />"
            Dim testItem As DateTime = CType(Cache("TestItem"), DateTime)
            lblInfo.Text &= "TestItem is '" & testItem.ToString()
            lblInfo.Text &= "'<br />"
        End If

        lblInfo.Text &= "<br />"
    End Sub

End Class

Figure 23-5 shows the result after the page has been loaded and posted back several times in the 30-second period.

Figure 23.5. A simple cache test

The next example shows a more interesting demonstration of caching, which includes retrieving information from a database and storing it in a DataSet. This information is then displayed in a GridView. However, the output for the web page can't be efficiently cached because the user is given the chance to customize the display by hiding any combination of columns. Note that even with just ten columns, you can construct more than a thousand different possible views by hiding and showing various columns. These are far too many columns for successful output caching!

Figure 23-6 shows the page. To hide a column, you simply click the corresponding check box.

Figure 23.6. Filtering information from a cached DataSet

Instead of attempting to use output caching, this page caches the DataSet object that holds the full information. This DataSet is constructed in the dedicated RetrieveData() function shown here. (In order to use this code as written, you must import the System.Data, System.Data.SqlClient, and System.Web.Configuration namespaces in the web page.)

Private Function RetrieveData() As DataSet
    Dim connectionString As String = _
      WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString
    Dim SQLSelect As String = "SELECT * FROM Customers"
    Dim con As New SqlConnection(connectionString)
    Dim cmd As New SqlCommand(SQLSelect, con)
    Dim adapter As New SqlDataAdapter(cmd)
    Dim ds As New DataSet()

    Try
        con.Open()
        adapter.Fill(ds, "Customers")
    Finally
        con.Close()

End Try

    Return ds
End Function

The RetrieveData() method handles the work of contacting the database and creating the DataSet. You need another level of code that checks to see whether the DataSet is in the cache and adds it when needed. The best way to write this code is to add another method. This method is called GetDataSet().

The GetDataSet() method attempts to retrieve the DataSet from the cache. If it cannot retrieve the DataSet, it calls the RetrieveData() method and then adds the DataSet to the cache. It also reports on the page whether the DataSet was retrieved from the cache or generated manually.

Private Function GetDataSet() As DataSet
    Dim ds As DataSet = CType(Cache("Customers"), DataSet)

    ' Contact the database if necessary.
    If ds Is Nothing Then
        ds = RetrieveData()
        Cache.Insert("Customers", ds, Nothing, _
          DateTime.MaxValue, TimeSpan.FromMinutes(2))
        lblCacheStatus.Text = "Created and added to cache."
    Else
        lblCacheStatus.Text = "Retrieved from cache."
    End If

    Return ds
End Function

The advantage of this approach is that you can call GetDataSet() in any event handler in your web page code to get the DataSet when you need it. You don't need to worry about checking the cache first and calling RetrieveDataSet() when needed—instead, GetDataSet() handles the whole process transparently.

When the page is first loaded, it calls GetDataSet() to retrieve the DataSet. It then gets the DataTable with the customer records and binds the DataTable.Columns collection to a CheckBoxList control named chkColumns:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) _
  Handles Me.Load
    If Not Me.IsPostBack Then
        Dim ds As DataSet = GetDataSet()
        chkColumns.DataSource = ds.Tables("Customers").Columns
        chkColumns.DataTextField = "ColumnName"
        chkColumns.DataBind()
    End If
End Sub

As you learned in Chapter 14, the DataTable.Columns collection holds one DataColumn object for each column in the DataTable. Each DataColumn specifies details such as data type and column name. In this example, the DataColumn.ColumnName property is used to display the name of each column (as configured by the DataTextField property of the CheckBoxList control).

Every time the Filter button is clicked, the page calls GetDataSet() to retrieve the DataSet. To provide the configurable grid, the code loops through the DataTable, removing all the columns that the user has selected to hide. The code then binds the data by calling GridView.DataBind().

The full code for the Filter button is as follows:

Protected Sub cmdFilter_Click(ByVal sender As Object, _
  ByVal e As System.EventArgs) Handles cmdFilter.Click

    Dim ds As DataSet = GetDataSet()

    ' Copy the DataSet so you can remove columns without
    ' changing the cached item.
    ds = ds.Copy()

    For Each item As ListItem in chkColumns.Items
        If item.Selected Then
            ds.Tables(0).Columns.Remove(item.Text)
        End If
    Next

    gridCustomers.DataSource = ds.Tables(0)
    gridCustomers.DataBind()

End Sub

This example demonstrates an important fact about the cache. When you retrieve an item, you actually retrieve a reference to the cached object. If you modify that object, you're actually modifying the cached item as well. For the page to be able to delete columns without affecting the cached copy of the DataSet, the code needs to create a duplicate copy before performing the operations using the DataSet.Copy() method.

The SqlDataSource (Chapter 15) and ObjectDataSource (Chapter 22) support built-in data caching. Using caching with these controls is highly recommended, because they can be more inefficient than handwritten data access code. For example, they query the data source once for every bound control, so if you have three controls bound to the same data source, three separate queries are executed against the database just before the page is rendered. Even a little caching can reduce this overhead dramatically.

To support caching, the SqlDataSource and ObjectDataSource controls use the same properties, which are listed in Table 23-2.

Caching with SqlDataSource

When you enable caching for the SqlDataSource control, you cache the results of the SelectCommand. However, if you create a select query that takes parameters, the SqlDataSource will cache a separate result for every set of parameter values.

For example, imagine you create a page that allows you to view employees by city. The user selects the desired city from a list box, and you use a SqlDataSource control to fill in the matching employee records in a grid (see Figure 23-7).

Figure 23.7. Retrieving data from the cache

There are two SqlDataSource controls at work in this example. The first SqlDataSource gets the list of cities for the drop-down list. These results don't change often, and so they are cached for one hour (3600 seconds):

<asp:SqlDataSource ID="sourceEmployeeCities" runat="server"
 ProviderName="System.Data.SqlClient"
 EnableCaching="True" CacheDuration="3600"
 ConnectionString="<%$ ConnectionStrings:Northwind %>"
 SelectCommand="SELECT DISTINCT City FROM Employees">
</asp:SqlDataSource>

<asp:DropDownList ID="lstCities" runat="server"
 DataSourceID="sourceEmployeeCities"
 DataTextField="City" AutoPostBack="True">
</asp:DropDownList>

The second SqlDataSource gets the employees in the currently selected city. These results are cached for 600 seconds and bound to a GridView:

<asp:SqlDataSource ID="sourceEmployees" runat="server"
 ProviderName="System.Data.SqlClient"
 EnableCaching="True" CacheDuration="600"
 ConnectionString="<%$ ConnectionStrings:Northwind %>"
 SelectCommand="SELECT EmployeeID, FirstName, LastName, Title, City
FROM Employees WHERE City=@City">
  <SelectParameters>
    <asp:ControlParameter ControlID="lstCities" Name="City"
     PropertyName="SelectedValue" />
  </SelectParameters>

</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server"
 DataSourceID="sourceEmployees" ... >
  ...
</asp:GridView>

This SqlDataSource is a bit more sophisticated because it uses a parameter. Each time you select a city, a separate query is performed to get just the matching employees in that city. The query is used to fill a DataSet, which is then cached for up to ten minutes (600 seconds). If you select a different city, the process repeats, and the new DataSet is cached separately. However, if you pick a city that you or another user has already requested, the appropriate DataSet is fetched from the cache (provided it hasn't yet expired).

Thus, this single SqlDataSource can result in a surprisingly large number of cache entries. If there are 20 different cities in your list (and therefore 20 different possible parameter values), you can end up with as many as 20 different DataSet objects in the cache at once.

Note

SqlDataSource caching works only when the DataSourceMode property is set to DataSet (the default). It doesn't work when the mode is set to DataReader, because the DataReader object maintains a live connection to the database and can't be efficiently cached. If you try to use caching with the DataReader mode, you'll receive a NotSupportedException when you bind the grid.

On the other hand, if the parameter values are all used with similar frequency, this approach isn't as suitable. One of the problems it imposes is that when the items in the cache expire, you'll need multiple database queries to repopulate the cache (one for each combination of parameter values), which isn't as efficient as getting the combined results with a single query.

If you fall into the second situation, you can change the SqlDataSource so it retrieves a DataSet with all the employee records and caches that. The SqlDataSource can then extract just the records it needs to satisfy each request from the DataSet. This way, a single DataSet with all the records is cached, which can satisfy any parameter value.

To use this technique, you need to rewrite your SqlDataSource to use filtering. First, the select query should return all the rows and not use any SelectParameters:

<asp:SqlDataSource ID="sourceEmployees" runat="server"
 SelectCommand=
"SELECT EmployeeID, FirstName, LastName, Title, City FROM Employees"
 ...>
</asp:SqlDataSource>

Second, you need to define the filter expression. This is the portion that goes in the WHERE clause of a typical SQL query. However, this has a catch—if you're supplying the filter value from another source (such as a control), you need to define one or more placeholders, using the syntax {0} for the first placeholder, {1} for the second, and so on. You then supply the filter values using the <FilterParameters> section, in much the same way you supplied the select parameters in the first version.

Here's the completed SqlDataSource tag:

<asp:SqlDataSource ID="sourceEmployees" runat="server"
 ProviderName="System.Data.SqlClient"
 ConnectionString="<%$ ConnectionStrings:Northwind %>"
 SelectCommand=
"SELECT EmployeeID, FirstName, LastName, Title, City FROM Employees"
 FilterExpression="City='{0}'" EnableCaching="True">
  <FilterParameters>
    <asp:ControlParameter ControlID="lstCities" Name="City"
     PropertyName="SelectedValue" />
  </FilterParameters>
</asp:SqlDataSource>

Tip

Don't use filtering unless you are using caching. If you use filtering without caching, you are essentially retrieving the full result set each time and then extracting a portion of its records. This combines the worst of both worlds—you have to repeat the query with each postback, and you fetch far more data than you need each time.

To use a cache dependency, you need to create a CacheDependency object. You then need to supply the CacheDependency object when you add the dependent cached item.

For example, the following code creates a CacheDependency that depends on an XML file named ProductList.xml. When the XML file is changed, the CacheDependency will be invalidated, and the dependent cached item will be evicted from the cache immediately.

' Create a dependency for the ProductList.xml file.
Dim prodDependency As New CacheDependency( _
  Server.MapPath("ProductList.xml"))

' Add a cache item that will be dependent on this file.
Cache.Insert("ProductInfo", prodInfo, prodDependency)

Monitoring begins as soon as the CacheDependency object is created. If the XML file changes before you have added the dependent item to the cache, the item will expire immediately as soon as it's added.

Figure 23-8 shows a simple test page that is included with the samples for this chapter. It sets up a dependency, modifies the file, and allows you to verify that the cached item has been dropped from the cache.

Figure 23.8. Testing cache dependencies

The CacheDependency object provides several constructors. You've already seen how it can make a dependency based on a file by using the file name constructor. You can also specify a directory that needs to be monitored for changes, or you can use a constructor that accepts an array of strings that represent multiple files and directories.

The CacheDependency class provides another constructor that accepts an array of file names and an array of cache keys. Using the array of cache keys, you can create a cached item that's dependent on another item in the cache. (If you don't want to use file dependencies at all, you simply supply a null reference (Nothing) for the first parameter.)

Here's an example that makes one item dependent on another cached item, without using file dependencies:

Cache("Key1") = "Cache Item 1"

' Make Cache("Key2") dependent on Cache("Key1").
Dim dependencyKey(0) As String
dependencyKey(0) = "Key1"
Dim dependency As New CacheDependency(Nothing, dependencyKey)

Cache.Insert("Key2", "Cache Item 2", dependency)

Now, when the first cached item changes or is removed from the cache, the second cached item will automatically be dropped from the cache as well.

A more complex kind of cache dependency is the SQL Server cache dependency. In a nutshell, SQL cache dependencies provide the ability to automatically invalidate a cached data object (such as a DataSet) when the related data is modified in the database.

Although this feature is technically supported in SQL Server 2000, it's a better idea to use SQL Server 2005 or later. That's because these versions of SQL Server have a built-in notification system, which makes tasks like these much more efficient.

To understand how database dependencies work, you first need to know a bit about SQL Server's built-in messaging system, which is called the Service Broker. The Service Broker manages queues, which are database objects that have the same standing as tables, stored procedures, or views.

Thanks to queues, you can instruct SQL Server to send notifications for specific events using the CREATE EVENT NOTIFICATION command. But ASP.NET offers a more convenient, higher-level model—you register a query, and ASP.NET automatically instructs SQL Server to send notifications for any operations that would affect the results of that query. Every time you perform 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. Figure 23-9 shows an overview of how this cache invalidation system works.

Figure 23.9. Monitoring a database for changes in SQL Server

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:

Here's an acceptable command:

SELECT EmployeeID, FirstName, LastName, City FROM dbo.Employees

These are the most important rules, but the SQL Server Books Online has a lengthy list of caveats and exceptions. If you break one of these rules, you won't receive an error. However, the notification message will be sent as soon as you register the command, and the cached item will be invalidated immediately.

Enabling the Service Broker

SQL Server is often installed with carefully locked-down settings for optimum security. To use SQL Server notifications, you may have to enable features that are currently switched off.

First, you need to enable the Service Broker, which watches for changes in the database and delivers the notifications to the appropriate queue. The Service Broker must be specifically enabled for each database that you want to use with cache dependencies.

If the Service Broker isn't currently enabled for your database (or if you're just not sure), there's an easy solution. First, launch the Visual Studio 2010 Command Prompt window (click the Start button and choose All Programs

SqlCmd -S localhostSQLEXPRESS

This connects to SQL Server Express on the current computer. If you're using the full version of SQL Server, you won't need to supply the instance name (you can use just localhost instead of localhostSQLEXPRESS). If your database is installed on another server, use its computer name instead of localhost.

The SqlCmd.exe utility provides a command prompt where you can enter SQL commands. Use it to enter the following SQL statements:

USE Northwind
ALTER DATABASE Northwind SET ENABLE_BROKER
GO

Of course, if you want to enable the Service Broker for a different database (other than Northwind), you can modify this SQL accordingly. You can enable the Service Broker for as many databases as you'd like.

Once you're finished, type quit to exit the SqlCmd tool.

Dim connectionString As String = _
  WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString
SqlDependency.Start(connectionString)

' Create the ADO.NET objects.
Dim con As New SqlConnection(connectionString)
Dim query As String = _
 "SELECT EmployeeID, FirstName, LastName, City FROM dbo.Employees"
Dim cmd As New SqlCommand(query, con)
Dim adapter As New SqlDataAdapter(cmd)

' Fill the DataSet.
Dim ds As New DataSet()
adapter.Fill(ds, "Employees")

' Create the dependency.
Dim empDependency As New SqlCacheDependency(cmd)

' Add a cache item that will be invalidated if one of its records changes
' (or a new record is added in the same range).
Cache.Insert("Employees", ds, empDependency)

SqlCmd -S localhostSQLEXPRESS

EXEC sp_configure 'show advanced options', '1'
GO
RECONFIGURE
GO
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO