SQL Server Cache Dependency - In easy words

Using the SQL Server Cache Dependency :

 

To utilize the new SQL Server Cache Dependency run setup of your SQL Server database using the aspnet_regsql.exe tool.

 

Found at C:\Windows\Microsoft.NET\Framework\v2.0xxxxx\. This tool makes the necessary

modifications to SQL Server so that you can start working with the new SQL cache invalidation features.

 

Follow these steps when using the new SQL Server Cache Dependency features:

 

1. Enable your database for SQL Cache Dependency support.

2. Enable a table or tables for SQL Cache Dependency support.

3. Include SQL connection string details in the ASP.NET application’s web.config.

4. Utilize the SQL Cache Dependency features in one of the following ways:

 

Ø  Programmatically create a SqlCacheDependency object in code.

Ø  Add a SqlDependency attribute to an OutputCache directive.

Ø  Add a SqlCacheDependency instance to the Response object via Response.AddCacheDependency.

 

This section explains all the steps required and the operations available to you.

 

See Below is the possible options.

 

Aspnet_regsql.exe -?

 

-d <Database Name>
-ed	Enable a database for SQL CacheDependency
-dd	Disable a database for SQL CacheDependency
-et	Enable a table for SQL CacheDependency
-dt	Disable a table for SQL CacheDependency
-t <Table Name>
-lt	List all the tables enabled for SQL CacheDependency

 

Step 1: Enabling Databases for SQL Server Cache Invalidation

 

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 > aspnet_regsql -C "Data Source=localhost;Integrated Security=True; Initial Catalog=Northwind" -ed

 

Step 2: Enabling Tables for SQL Server Cache Invalidation

 

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 > aspnet_regsql -C "Data Source=localhost;Integrated Security=True; Initial Catalog=Northwind"  -et -t Categories

 

Step 3: Include SQL connection string details in the ASP.NET application’s web.config

 

<configuration>

<connectionStrings>

<add name="Northwind" connectionString="data source=XIPL0060;initial catalog=Northwind; user id=sa;password=sa;persist security info=True;packet size=4096"/>

</connectionStrings>

<system.web>

    <caching>

      <sqlCacheDependency enabled="true" pollTime="5000">

        <databases>

          <add  name="Northwind"  connectionStringName="Northwind" />

        </databases>

      </sqlCacheDependency>

    </caching>

</system.web>

</configuration>

 

Name	Provides an identifier to the SQL Server DB
Connectionstringname	Connection String Name
pollTime	Not required for SQL Server 2005 Spedifies time intrerval to poll.

 

 

Before we start with 4th step, lets take a look at few other things.

 

Looking at SQL Server :

 

As the Northwind database and the Customers and Products tables have all been enabled

for SQL cache invalidation, look at what has happened in SQL Server. You’ll see a new table contained within the Northwind database— AspNet_SqlCacheTablesForChangeNotification

 

This is the table that ASP.NET uses to learn which tables are being monitored for change notification and also to make note of any changes to the tables being monitored. The table has three columns

 

Ø  tableName : Contains names of the tables enabled for SQL cache invalidation.

Ø  notificationCreated : shows the date and time when the table was enabled for SQL cache invalidation.

Ø  changeId : Used to communicate to ASP.NET any changes to the included tables. ASP.NET monitors this column for changes and, depending on the value, either uses what is stored in memory or makes a new database query.

 

ASP.NET makes a separate SQL Server request on a completely different thread to the AspNet_SqlCacheTablesForChangeNotification table to see if the changeId number has been incremented. If the number is changed, ASP.NET knows that an underlying change has been made to the SQL Server table and that a new result set should be retrieved. When it checks to see if it should make a SQL Server call, the request to the small AspNet_SqlCacheTablesForChangeNotification table has a single result. With SQL Server cache invalidation enabled, this is done so quickly that you

really notice the difference.

 

 

Looking at the Tables That Are Enabled :

 

To get a list of the tables that are enabled, use something similar to the following command:

aspnet_regsql.exe -S localhost -U sa -P password -d Northwind –lt

 

Disabling a Table for SQL Server Cache Invalidation:

aspnet_regsql.exe -S localhost -U sa -P password -d Northwind -t Products –dt

 

Disabling a Table for SQL Server Cache Invalidation:

aspnet_regsql -S localhost -U sa -P wrox -d Northwind –dd

 

 

Note: That disabling an entire database for SQL Server cache invalidation also means that every single

table contained within this database is also disabled.

 

If you now open the Northwind database in the SQL Server Enterprise Manager, you can see that the

AspNet_SqlCacheTablesForChangeNotification table has been removed for the database.

 

Step 4: Utilizing the SQL Cache Dependency features in one of the following ways

 

Now that the web.config file is set up and ready to go, the next step is to actually apply these new

capabilities to a page.

 

Ø  Programmatically create a SqlCacheDependency object in code.

Ø  Add a SqlDependency attribute to an OutputCache directive.

Ø  Add a SqlCacheDependency instance to the Response object via Response.AddCacheDependency.

 

Using Programmatically :

 

<%@ Page Language=”C#” %>

<%@ Import Namespace=”System.Data” %>

<%@ Import Namespace=”System.Data.SqlClient” %>

 

<script runat=”server”>

protected void Page_Load(object sender, System.EventArgs e)

{

DataSet myCustomers;

myCustomers = (DataSet)Cache[“firmCustomers”];

if (myCustomers == null)

{

SqlConnection conn = new SqlConnection(

ConfigurationManager.ConnectionStrings[“AppConnectionString1”].ConnectionString);

SqlDataAdapter da = new SqlDataAdapter(“Select * from Customers”, conn);

myCustomers = new DataSet();

da.Fill(myCustomers);

 

if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString).Contains("CUSTOMERS "))

                                     SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString, " CUSTOMERS ");

SqlCacheDependency myDependency = new SqlCacheDependency(“Northwind”, “Customers”);

Cache.Insert(“firmCustomers”, myCustomers, myDependency);

 

Label1.Text = “Produced from database.”;

}

else

{

Label1.Text = “Produced from Cache object.”;

}

 

GridView1.DataSource = myCustomers;

GridView1.DataBind();

}

</script>

 

The Complete Syntax is as below:

 

Cache.Insert(key As String, value As Object,

dependencies As System.Web.Caching.CacheDependency

absoluteExpiration As Date, slidingExpiration As System.TimeSpan)

priority As System.Web.Caching.CacheItemPriority,

onRemoveCallback As System.Web.Caching.CacheItemRemovedCallback)

 

Using OutputCache :

 

<%@ Page Language=”VB” %>

<%@ OutputCache Duration=”3600” VaryByParam=”none” SqlDependency=”Northwind:Customers”%>

 

<script runat=”server”>

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

Label1.Text = “Page created at “ & DateTime.Now.ToShortTimeString ()

End Sub

</script>

 

<html xmlns=”http://www.w3.org/1999/xhtml” >

<head runat=”server”>

<title>Sql Cache Invalidation</title>

</head>

<body>

 

<form id=”form1” runat=”server”>

<asp:Label ID=”Label1” Runat=”server”></asp:Label><br />

<br />

<asp:GridView ID=”GridView1” Runat=”server” DataSourceID=”SqlDataSource1”></asp:GridView>

 

<asp:SqlDataSource ID=”SqlDataSource1” Runat=”server”

SelectCommand=”Select * From Customers”

ConnectionString=”<%$ ConnectionStrings:AppConnectionString1 %>”

ProviderName=”<%$ ConnectionStrings:AppConnectionString1.providername %>”>

</asp:SqlDataSource>

</form>

</body>

</html>

 

 

To add more than one table, you use the OutputCache directive shown here:

SqlDependency=”database:table;database:table”

 

 

Using Request Object :

 

SqlCacheDependency myDependency = new SqlCacheDependency(“Northwind”, “Customers”);

 

Response.AddCacheDependency(myDependency);

Response.Cache.SetValidUntilExpires(true);

Response.Cache.SetExpires(DateTime.Now.AddMinutes(60));

Response.Cache.SetCacheability(HttpCacheability.Public);

 

 

The SqlCacheDependency class takes the following parameters:

SqlCacheDependency(databaseEntryName As String, tablename As String)

 

You use this parameter construction if you are working with SQL Server 7.0 or with SQL Server 2000. If

you are working with SQL Server 2005 you use the following construction:

SqlCacheDependency(sqlCmd As System.Data.SqlClient.SqlCommand)

 

Hope this helps.

 

Regards,

Arun