Caching in asp.net - Part 119

Suggested Videos
Part 116 - Adding custom events to asp.net composite custom control
Part 117 - Assigning an image to the composite custom control in visual studio tool box
Part 118 - Difference between user controls and custom controls

Caching improves the performance and scalability of an application. Caching is the technique of storing frequently used data/pages in memory. Let us practically understand caching, with an example.



Create tblproducts table in sql server
Create Table tblProducts
(
[Id] int identity primary key,
[Name] nvarchar(50),
[Description] nvarchar(250)
)

Populate tblProducts with sample data
Insert into tblProducts values ('Laptops', 'Dell Laptops')
Insert into tblProducts values ('iPhone', 'iPhone 4S')
Insert into tblProducts values ('LCD TV', 'Samsung LCD TV')
Insert into tblProducts values ('Desktop', 'HP Desktop Computer')



Create "spGetProducts" stored procedure. In this procedure we are using WAITFOR DELAY, to block the execution of the stored procedure for 5 seconds. In real time, we may have large tables, where the query processing can take some time before the data is returned. Table "tblProducts" is a very small table, with only 4 rows. So the stored procedure "spGetProducts" would execute in a fraction of second. Just to simulate artifical query processing time of 5 seconds, we are using WAITFOR DELAY.
Create Procedure spGetProducts
As
Begin
Waitfor Delay '00:00:05'

Select Id, Name, Description
from tblProducts
End

Click here for SQL Server video tutorial that can help you, if you are new to sql server and need help creating tables, stored procedures and understanding sql server concepts.

Now, let us invoke the stored procedure in an asp.net web application, and display the "Products" data in a gridview control. Drag and drop a "gridview" control onto the web form. Copy and paste the following code in the code-behind file Page_Load() event.
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
SqlConnection con = new SqlConnection(CS);
SqlDataAdapter da = new SqlDataAdapter("spGetProducts", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet DS = new DataSet();
da.Fill(DS);
GridView1.DataSource = DS;
GridView1.DataBind();

Also make sure you have the following "using" declarations
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

We discussed about retrieving  data from database in ado.net tutorial. Click here to access ado.net tutorial.

At this point, if you run the application, the page takes about 5 seconds to load. This is because, when you request the webform, the web server has to process the web form events, execute the stored procedure, create objects, generate HTML and send that HTML to the client broswer. 

Now let us cache the webform. To cache a webform, use the @OutputCache page directive. The @OutputCache directive has 2 mandatory attributes
Duration - Specifies the time in seconds for which the webform should be cached
VaryByParam - Cache multiple responses of a single webform. For now set the value to "None". We will discuss about "VaryByParam" in a later video.

Webform with the following "OutputCache" directive is cached for 30 seconds.
<%@ OutputCache Duration="30" VaryByParam="None" %>

When any user requests this Web form for the first time, the web server will process the web form events, execute the stored procedure, create objects, generate HTML and send that HTML to the client browser, and retains a copy of the response in memory for the next 30 seconds. Any subsequent requests during that time receive the cached response. 

After the cache duration has expired, the next request for the Web form, has to process the web form events, execute the stored procedure, create objects, generate HTML, which is then cached for another 30 seconds. So this web form is processed by the server, once every 30 second, at the most.

Next Video: We will discuss about caching multiple responses for a single webform.