Advertisements
Advertisements
In this article, we are going to learn how to implement SQL Caching in ASP.NET using Poll based SQL Cache dependency. The push based SQL Cache dependency shall be covered in other article. In poll based, SQL Cache dependency checks for the updated data after every specified duration.
With the help of Caching, we can retain pages or data across HTTP requests, so that we can reuse them without recreating them.
Introduction
ASP.NET allows us to use the SqlCacheDependency class to create a cache item dependency on a table in a database. It has the ability to automatically invalidate a cached data object when the related data is modified in the database. This feature is supported in from SQL Server 2005+ versions.
To implement SQL Cache where data is cached in the SQL Server instead of server memory, we can follow this approach.
Recently, I have written a .NET How to ebook with source code and video tutorials on frequently faced .NET problems, click here to get this.
Below is my database (DemoDatabase) structure before enabling SQL Cache Dependency.
DATABASE STRUCTURE BEFORE ENABLING SQL CACHE DEPENDENCY
ENABLIING SQL CACHE DEPENDENCY FOR THE DEMODATABASE
Run the aspnet_regsql.exe command from the Visual Studio command prompt, you should open the Visual Studio Command prompt with Administrative privilege.
(Command name and options are case sensitive; you need to only change the databases servername, userid, and password and database name to enable SQL Cache dependency in your database).
Here
i. –S = name of the server (as sunita-pc\sqlexpress in the above screen shot)
ii. –U = database username (as demo in the above screen shot)
iii. –P = database password (as demo in the above screen shot)
iv. –d = database name (as DemoDatabase in the above screen shot)
v. –ed = enable the database for SQL Cache dependency
DATABASE STRUCTURE AFTER ENABLING SQL CACHE DEPENDENCY
Notice the Stored Procedures folder. You will find many stored procedure added starting with “AspNet_SqlCache…”.
Now, we need to enable the database table for which we want to cache the data, so for that again we need to run the aspnet_regsql.exe command.
ENABLING DATABASE TABLE FOR SQL CACHE DEPENDENCY
This command is almost similar as the previous command of enabling database for SQL Cache dependency; however we need to add –t (table name to enable cached dependency on) and then –et (enable table) at the last.
Notice that a new table “AspNet_SqlCacheTablesForChangeNotification” has been added.
LISTING DATABASE TABLES THAT HAS SQL CACHE DEPDENCY ENABLED
Above command is also same as earlie, we have kept –lt (list tables) at the end after the database name. Enabling SQL Cache dependency on the table adds one record into the “AspNet_SqlCacheTablesForChangeNotification” table.
DISABLING SQL CACHE DEPENDENCY ON DATABASE TABLE
In case we want to disable SQL Cache for a particular table, we can run use below command. In this case we are disabling SQL Cache for PersonalDetail table.
Just replace –et to –dt at the last of the command.
WEB.CONFIG
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="ConnStr" connectionString="Data Source=SUNITAPC\
SQLEXPRESS;Initial Catalog=DemoDatabase;Persist Security Info=True;User
ID=demo;Password=demo;"/>
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.0"/>
<caching>
<sqlCacheDependency enabled="true">
<databases>
<add name="DemoDatabase" connectionStringName="ConnStr" pollTime="500"/>
</databases>
</sqlCacheDependency>
</caching>
</system.web>
</configuration>Once our database and table are configured for SQL Cache dependency, we need to specify Caching tag (with enabled=true) in web.config file and then add the databases on which caching needs to be enabled. Ensure that the database name attribute value (name=”DemoDatabase”) is similar to the name of the database specified into the connectionString (ConnStr) and then specify the pollTime (in millisecond). Polltime ensures that SQL Cache dependency checks for the updated data after every specified duration (in this case .5 seconds).
Let’s create a sample page to demonstrate SQL Cache dependency.
ASPX PAGE
<form id="form1" runat="server">
<div>
<p>
<asp:Label ID="lblMessage" runat="server" EnableViewState="false" /></p>
<asp:GridView ID="GridView1" runat="server" EnableViewState="false" />
</div>
</form>CODE BEHIND
protected void Page_Load(object sender, EventArgs e)
{
lblMessage.Text = DateTime.Now.ToString();
if (!IsPostBack)
{
GetData();
}
}
private void GetData()
{
DataTable table = new DataTable();
// Check for cache if it is then get it from cache
if (Cache["PersonalDetailData"] != null)
{
table = (DataTable)Cache["PersonalDetailData"];
lblMessage.Text += "<br />Being loaded from the Cache";
}
else
{
// get the connection
using (SqlConnection conn = new SqlConnection(_connStr))
{
// write the sql statement to execute
string sql = "SELECT AutoId, FirstName, LastName, Age, Active FROM PersonalDetail ORDER By AutoId";
// instantiate the command object to fire
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
// get the adapter object and attach the command object to it
using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
{
// fire Fill method to fetch the data and fill into DataTable
ad.Fill(table);
}
}
SqlCacheDependency dependency = new SqlCacheDependency("DemoDatabase",
"PersonalDetail");
// store data into Cache
Cache.Insert("PersonalDetailData", table, dependency);
lblMessage.Text += "<br />Being loaded from the database";
}
}
// specify the data source for the GridView
GridView1.DataSource = table;
// bind the data now
GridView1.DataBind();
}In the code behind, to insert the data into Cache, first instantiate the SqlCacheDependency object by passing the database entry name (this name is name of the database defined in the databases element of SqlCacheDependency in the web.config file) and table name (PersonalDetail), then add it as dependency into the Cache using Cache.Insert method.
OUTPUT
When we run the page, the first request to this page looks like this where the GridView data is coming from the database.
The second request loads the data from the cache and all remaining request to this page loads the GridView data from the Cache unless the “PersonalDetail” table data has been modified.
Request after modifying the last record’s FirstName
CONCLUSION:
When we cache data from database, the data remains in the cache until it’s source is modified in the database. With the help of this we can improve the performance of ASP.NET applications to the large extent and can also solve the problem of ASP.NET distributed cache scenarios.
If you like this article, subscribe to our 
RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.