SQL Cache Dependency with SqlDataSource

To enable a cache dependency on a particular database, run this command in from a command-line prompt:

aspnet_regsql.exe -S server -U user -P password -d database -ed

This creates a new table, AspNet_SqlCacheTablesForChangeNotification, in the designated database. Next, several AspNet_SqlCacheXxxx stored procs are created in the same database.

To enable a table for SQL cache dependency use, you’ll need to first run the aspnet_regsql.exe tool from a command-line prompt, with these options:

aspnet_regsql -S servername -U login -P password -ed -d databasename -et -t tablename

After these are done, database side is over. You need to add this into your web.config file inside of system.web blocks:

<caching>
  <sqlCacheDependency enabled="true" pollTime="30000">
    <databases>
      <add name="DatabaseName" pollTime="30000" connectionStringName="ConnectionString" />
    </databases>
  </sqlCacheDependency>
</caching>

* You can change pollTime with miliseconds whatever you want.

After this, you can use it with your SqlDataSource like this:

<asp:SqlDataSource ID="dataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    SqlCacheDependency="DatabaseName:TableName; DatabaseName:TableName2"
    SelectCommand="SELECT * FROM TableName">
</asp:SqlDataSource>