SQL Server Cache Dependency - Polling Approach

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 are 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

1.	Enabling Databases for SQL Server Cache Invalidation	aspnet_regsql -C "Data Source=localhost;Integrated Security=True; Initial Catalog=Northwind" -ed
2.	Enabling Tables for SQL Server Cache Invalidation	aspnet_regsql -C "Data Source=localhost;Integrated Security=True; Initial Catalog=Northwind"  -et -t Categories
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>

Here:

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.

Few more things to know (these are not part of any steps):

1.	Looking at the Tables That Are Enabled	aspnet_regsql.exe -S localhost -U sa -P password -d Northwind –lt
2.	Disabling a Table for SQL Server Cache Invalidation	aspnet_regsql.exe -S localhost -U sa -P password -d Northwind -t Products –dt
3.	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.

Approach 1 - Using Programmatically :

private void BindGrid() {      GridView1.DataSource = GetDataTable();    GridView1.DataBind(); }
public DataTable GetDataTable () {     DataTable dt = null;     SqlConnection cnn = null;     SqlCommand cmd = null;     DataSet ds = null;     SqlDataAdapter da = null;     string cn = string.Empty;     dt = HttpContext.Current.Cache["HashData"] as DataTable;     if (dt == null)     {             cn = ConfigurationManager.ConnectionStrings["Pubs"].ConnectionString;             cnn = new SqlConnection(cn);             cnn.Open();             cmd = new SqlCommand("SELECT * FROM EMPLOYEE where pub_id < 800", cnn);             ds = new DataSet();             da = new SqlDataAdapter(cmd);             da.Fill(ds);             dt = ds.Tables[0];             SqlCacheDependency myDependency = new SqlCacheDependency("Pubs", "Employee");             //SqlCacheDependency myDependency = new SqlCacheDependency(cmd);  // Both will do             HttpContext.Current.Cache.Insert("HashKey", dt, myDependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration);     }     return dt; }

Approach 2 - Using OutputCache :

This is used when SQLDataSourceControls is used for GridView sourcing.

<%@ 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”

Approach 3 - Using Request Object :

Please take help of internet.

Hope this helps.

Regards,

Arun

---

Disclaimer: The information contained in this message may be privileged, confidential, and protected from disclosure. If you are not the intended recipient, or an employee, or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer.