SQL Server Cache Dependency

 Optimization

Building website is not enough, as everything depends on its performance. If you have developed a good looking site with lots of data handling but your website is taking higher response time, then it’s all senseless having such website even if you are handling data smartly. Here comes the role of optimization process, it plays crucial role behind the success of your website. And this dependency becomes higher if you are not using prebuild CMS like WordPress or DotNetNuke. There are many ways to optimize your website contents like DataCaching, Network Caching, and SQL Server Caching etc. Today, we are going to explain SQL Server Cache using in ASP.NET Web Application with MS SQL Server 2005.

SQL Server Cache Dependency

It’s one of the most complex caching mechanisms available for .net applications, it provides functionality to invalidate data object whenever a change occurs within the related table of Database. For asp.net application we can consider it as the best possible mechanisms for caching data. In MS SQL Server 2005 and later versions this message system is built with Service Broker. The Service Broker manages queues, which are database objects that have the same standing as tables, stored procedure, or view.

To build Cache Dependency you need to enable Service Broker on each database where you want to use it. This service can be enabled easily with the help of Visual Studio Command Tool by executing the following command – 

SqlCmd –S .\SQLEXPRESS 

USE database_name

ALTER DATABASE database_name SET ENABLE_BROKER

Go 

Now you need to enable Subscribe Query Notification for this database to receive messages, type the following code to do so – 

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO database_name 

To check all the subscriptions those are currently stored into the database use this command – 

SELECT * FROM sys.dm_qn_subscriptions 

You can also kill a subscription using -

KILL QUERY NOTIFICATION SUBSCRIPTION id 

Where ‘id’ is the subscription id, you can kill all subscription using ‘All’ keyword in place of subscription id. Now we have to enable SQL Cache Dependency on your database with the following command – 

aspnet_regsql -d -E -d database_name 

Here –

1) –d: Specifies the name of the database to create or modify for use with application services. If the database is not specified, the default database name of “aspnetdb” is used.

2) –E: Authenticates using the Windows credentials of the currently logged-on user.

3) –ed: Enables a database for SQL cache dependency.

Now we have to specify the particular table name for enabling cache on that’s data by executing this command – 

aspnet_regsql -et -E -d database_name -t table_name 

Here –

1) – t: Specifies the table name to enable or disable for use with SQL cache dependency. This option must be used with the -et or -dt options.

2) – et: Enables a table for SQL cache dependency. The -t option must also be included in the parameter string.

Now move on to ‘web.config’ file of your application and add the following parameters to make SqlCacheDependency work – 

<connectionStrings>

<clear />

<add name=”LocalSqlServer”

connectionString=”data source=.\SQLEXPRESS;

Integrated Security=SSPI;

initial catalog=database_name” />

</connectionStrings> 

<system.web>

<caching>

<sqlCacheDependency pollTime=”1000″ enabled=”true” >

<databases>

<add connectionStringName=”LocalSqlServer” name=”database_name”/>

</databases>

</sqlCacheDependency>

</caching>

</system.web> 

In above code we set PollTime to 1000 milliseconds that means after every 10 seconds database will be checked for any changes in particular table. Now take a look how to use SqlCacheDependency in asp.net code – 

private void BindData()

{

// if null then fetch from the database 

if (Cache["any_name"] == null)

{

// Create the cache dependency

SqlCacheDependency dep = new SqlCacheDependency(“database_name”, “table_name”);

string connectionString = ConfigurationManager.ConnectionStrings[

"LocalSqlServer"].ConnectionString;

SqlConnection myConnection = new SqlConnection(connectionString);

SqlDataAdapter ad = new SqlDataAdapter(“Your Query”, myConnection);

DataSet ds = new DataSet();

ad.Fill(ds);

// put in the cache object

Cache.Insert(“any_name”, ds, dep);

}

// use any data control to bind with cached object

GridView1.DataSource = (DataSet) Cache["any_name"] ;

GridView1.DataBind();

} 

Here first we checked whether cache object is null or have any data, if it has data then we can bind our data control (in this example Grid View) with this cache item, else we will create a SqlCacheDependency object by passing parameters of database name and table name on which we enabled the SQL Cache Dependency. Then after took the connection string value set in the web.config file and executed our query and got the returned data into Data Set object. Then we insert this data set object into cache object with the parameters of its name, data set object and the SqlCacheDependency object we created earlier and bound the Grid View with this cache object.

Note – 

We cannot use Data Reader object to implement SqlCacheDependency in our asp.net application, because it uses an active SQL connection unlike Data Set which works with disconnected environment.