In this article we will discuss about caching application data. It is possible to store application data in the web server memory, using the CACHE object, so that the data can be retrieved faster. For example, let us say, we have a stored procedure that takes 5 seconds to execute and return data. We can cache the data returned by this stored procedure with in an asp.net web application using the CACHE object, so that, next time when we try to access the data, we can get it from the cache, rather than reprocessing the stored procedure again.
We will be using "tblProducts" table for this demo. If you need the script to create and populate this table, please refer to Part 122
The following stored procedure takes 5 seconds to execute and return data. We are using WAITFOR DELAY, to introduce artificial query processing time of 5 seconds.
CREATE Procedure spGetProducts
as
Begin
Waitfor Delay '00:00:05'
Select * from tblProducts
End
Create an asp.net web application, copy and paste the following HTML in WebForm1.aspx.
<div style="font-family:Arial">
<asp:Button ID="btnGetProducts" runat="server" Text="Get Products Data"
onclick="btnGetProducts_Click" />
<br /><br />
<asp:GridView ID="gvProducts" runat="server">
</asp:GridView>
<br />
<asp:Label ID="lblMessage" Font-Bold="true" runat="server"></asp:Label>
</div>
Copy and paste the following code in WebForm1.aspx.cs. The code is well documented and is self explanatory.
protected void btnGetProducts_Click(object sender, EventArgs e)
{
DateTime dtStartDateTime = DateTime.Now;
System.Text.StringBuilder sbMessage = new System.Text.StringBuilder();
// Check if the data is already cached
if (Cache["ProductsData"] != null)
{
// If data is cached, retrieve data from Cache using the key "ProductsData"
DataSet ds = (DataSet)Cache["ProductsData"];
// Set the dataset as the datasource
gvProducts.DataSource = ds;
gvProducts.DataBind();
// Retrieve the total rows count
sbMessage.Append(ds.Tables[0].Rows.Count.ToString() + " rows retrieved from cache.");
}
// If the data is not cached
else
{
// Get the data from the database
DataSet ds = GetProductsData();
// Cache the dataset using the key "ProductsData"
Cache["ProductsData"] = ds;
// Set the dataset as the datasource
gvProducts.DataSource = ds;
gvProducts.DataBind();
sbMessage.Append(ds.Tables[0].Rows.Count.ToString() + " rows retrieved from database.");
}
DateTime dtEndDateTime = DateTime.Now;
sbMessage.Append((dtEndDateTime - dtStartDateTime).Seconds.ToString() + " Seconds Load Time");
lblMessage.Text = sbMessage.ToString();
}
private DataSet GetProductsData()
{
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
SqlConnection con = new SqlConnection(CS);
SqlDataAdapter da = new SqlDataAdapter("spGetProducts", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet dsProducts = new DataSet();
da.Fill(dsProducts);
return dsProducts;
}
In this video, we discussed about storing application data in cache, using direct assignment. That is using a key and assiging value to it, as shown below.
Cache["ProductsData"] = ds
We will be using "tblProducts" table for this demo. If you need the script to create and populate this table, please refer to Part 122
The following stored procedure takes 5 seconds to execute and return data. We are using WAITFOR DELAY, to introduce artificial query processing time of 5 seconds.
CREATE Procedure spGetProducts
as
Begin
Waitfor Delay '00:00:05'
Select * from tblProducts
End
Create an asp.net web application, copy and paste the following HTML in WebForm1.aspx.
<div style="font-family:Arial">
<asp:Button ID="btnGetProducts" runat="server" Text="Get Products Data"
onclick="btnGetProducts_Click" />
<br /><br />
<asp:GridView ID="gvProducts" runat="server">
</asp:GridView>
<br />
<asp:Label ID="lblMessage" Font-Bold="true" runat="server"></asp:Label>
</div>
Copy and paste the following code in WebForm1.aspx.cs. The code is well documented and is self explanatory.
protected void btnGetProducts_Click(object sender, EventArgs e)
{
DateTime dtStartDateTime = DateTime.Now;
System.Text.StringBuilder sbMessage = new System.Text.StringBuilder();
// Check if the data is already cached
if (Cache["ProductsData"] != null)
{
// If data is cached, retrieve data from Cache using the key "ProductsData"
DataSet ds = (DataSet)Cache["ProductsData"];
// Set the dataset as the datasource
gvProducts.DataSource = ds;
gvProducts.DataBind();
// Retrieve the total rows count
sbMessage.Append(ds.Tables[0].Rows.Count.ToString() + " rows retrieved from cache.");
}
// If the data is not cached
else
{
// Get the data from the database
DataSet ds = GetProductsData();
// Cache the dataset using the key "ProductsData"
Cache["ProductsData"] = ds;
// Set the dataset as the datasource
gvProducts.DataSource = ds;
gvProducts.DataBind();
sbMessage.Append(ds.Tables[0].Rows.Count.ToString() + " rows retrieved from database.");
}
DateTime dtEndDateTime = DateTime.Now;
sbMessage.Append((dtEndDateTime - dtStartDateTime).Seconds.ToString() + " Seconds Load Time");
lblMessage.Text = sbMessage.ToString();
}
private DataSet GetProductsData()
{
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
SqlConnection con = new SqlConnection(CS);
SqlDataAdapter da = new SqlDataAdapter("spGetProducts", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet dsProducts = new DataSet();
da.Fill(dsProducts);
return dsProducts;
}
In this video, we discussed about storing application data in cache, using direct assignment. That is using a key and assiging value to it, as shown below.
Cache["ProductsData"] = ds