Caching using SqlCacheDependency class with command notification

Caching is very simple when caching a table

SqlCacheDependency dep = new SqlCacheDependency("Connection String", "dbo.tablename")

but when caching a sql command I had to struggle and it just did not work until I read this article and changed my query to not have any of these mentioned in the below article. The reason to cache a sql command instead of table was that I had to join more than two tables.

The reasons why cache was not working for me was I was doing two things that I am not supposed to do for the notifications to work.

1. My table names did not have two-part names like in dbo.tablename
It also means that all tables referenced in the statement must be in the same database.

2. I was using 'DISTINCT'. One of the many keywords Query notification does not support.

For full list of requirements for the Query notification to work, read the following article.

http://msdn.microsoft.com/en-us/library/aewzkxxh%28VS.80%29.aspx

Same rules apply if you want to Cache data from a stored procedure. The reason I like to use SqlCacheDependency class is because it uses the notification mechanism instead of polling.

object o = HttpContext.Current.Cache["MITES"];
if (o == null)
{
using (SqlConnection conn = new SqlConnection(GetConnectionString.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand ("GetMites",conn))
{
cmd.CommandType = CommandType.StoredProcedure;
dtMites = new DataTable("dtMites");
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
SqlCacheDependency dep = new SqlCacheDependency(cmd);
da.Fill(dtMites);

// This is just test code to make sure cache is really working
/* DataRow row = dtMites.NewRow();
row[0] = "Retreived from database";
dtMites.Rows.Add(row);
*/
// The data returned has duplicates. dtMites.DefaultView.ToTable(true, "Table1")
// retrieves distinct rows from the datatable
HttpContext.Current.Cache.Add("MITES", dtMites.DefaultView.ToTable(true, "Table1"), dep,
Cache.NoAbsoluteExpiration, TimeSpan.FromHours(1),
CacheItemPriority.Default, null);
return (DataTable)HttpContext.Current.Cache["MITES"];
}
}
}
}
else
{
// This is just test code to make sure cache is really working
/* dtMites = (DataTable)o;
DataRow row = dtMites.NewRow();
row[0] = "Retreived from Cache";
dtMites.Rows.Add(row);
return dtMites;
*/
return (DataTable)o;
}

You need to add some tags to the web.config file for this to work

<system.web>
<caching>
<sqlcachedependency enabled = "true" pollTime = "60000" >
<databases>
<add name="Connection String"
connectionStringName="Connection String"
pollTime="9000000"
/>
</databases>
</sqlCacheDependency>
</caching>


You will also need to add this to Application_Start method in Global.asax.cs file

protected void Application_Start(object sender, EventArgs e)
{
System.Data.SqlClient.SqlDependency.Start(System.Configuration.ConfigurationManager.ConnectionStrings["Connection String"].ConnectionString);
// Code that runs on application startup
}

on the database server run this command
Aspnet_regsql -S ServerName -U username -P Password -d DatabaseName -ed

Using Sql management studio run this script on the database

Run the script

DECLARE @SQL nvarchar(max)
BEGIN TRY
SET @SQL = 'ALTER DATABASE' + DatBaseName + 'SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE '
PRINT @SQL
EXEC sp_executesql @SQL
END TRY
BEGIN CATCH
SET @SQL = 'ALTER DATABASE' + DatBaseName + 'SET NEW_BROKER WITH ROLLBACK IMMEDIATE '
PRINT @SQL
EXEC sp_executesql @SQL
END CATCH
GO


If the script is taking too long to run, restart the Database server instance by opening Control Panel > Administrative Tools > Services >