ImageGear .NET - Updated
Setting Up SQL Server 2005 and SQL Server 2008 Cache Dependencies
User Guide > How to Work with... > Common Operations > Viewing > Viewing Using ASP.NET > ASP.NET Web.Config Editor Application > Use the Web Configuration Categories Screen > Configuring & Working with Data Providers > Image Data Providers > SQL Image Data Providers > Setting Up SQL Server 2005 and SQL Server 2008 Cache Dependencies

SQL Server 2005 and SQL Server 2008 have their notification infrastructure built into the database with a messaging system called the "Service Broker". The Service Broker manages queues. These queues are database objects at the same level as the stored procedures, tables and views.

The steps provided in this section for Visual Studio 2010 are also applicable for Visual Studio 2012.

Enabling the Service Broker in SQL Server Express

SQL Server 2005 and SQL Server 2008 are usually locked down for security reasons. The SQL Server broker must be enabled for each specific database that you want to use for cache dependencies. In order to enable the service broker, you must first launch the Visual Studio 2010 command prompt window.

The following are the steps to do that: (The example uses SQL Express:2005 and the default database table "ImageStorage").

  1. Close all instances of Visual Studio 2010.
  2. Click Start Button and choose All Programs.
  3. Select Visual Studio Tools.
  4. Select Visual Studio 2010 Command Prompt.
  5. Run the SqlCmd.exe command line utility specifying the command line parameter -S in the command window:

    SQL
    Copy Code
    SqlCmd -S  .\SQLEXPRESS
  6. The above connects SqlExpress on the local machine.
  7. The SqlCmd provides a command prompt. Enter the following SQL statements:

    SQL
    Copy Code
    USE ImageStorage
    ALTER DATABASE ImageStorage SET ENABLE_BROKER;
    GO
  8. QUIT or EXIT.
If you have any Visual Studio instance with Database View window open then the command Set Enable_Broker will appear not to execute. It will appear to be hung/frozen. 

SqlDependency Rules

There are a number of rules that must be followed for SqlDependency to work correctly. These rules are documented in Microsoft msdn library article titled "Using SqlDependency in an ASP.NET Application" (http://msdn.microsoft.com/en-us/library/9dz445ks(VS.80).aspx). Here is a list of some to bear in mind:

The procedure that sends notifications is a .NET procedure. Make sure your Sql server has CLR support enabled.

Here is how it can be done in SQLEXPRESS:

  1. Run the SqlCmd.exe command line utility.
    SQL
    Copy Code
    SqlCmd -S .\SQLEXPRESS
  2. Enter the following SQL statements:
    SQL
    Copy Code
    EXEC sp_configure 'show advanced options', '1'
    GO
    RECONFIGURE
    GO
    EXEC sp_configure 'clr enabled', 1
    GO
    RECONFIGURE
    GO
  3. Enter QUIT or EXIT.

For more detailed explanation and usage of the SQL cache dependency refer to the article on the ASP.NET website: http://www.asp.net/learn/data-access/tutorial-61-vb.aspx