Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Caching in - Part 119

Suggested Videos
Part 116 - Adding custom events to 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
Waitfor Delay '00:00:05'

Select Id, Name, Description
from tblProducts

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 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();
GridView1.DataSource = DS;

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 tutorial. Click here to access 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.


  1. Hi Venkat,

    Thanks for your videos, are really fantastic.

    I have one request in a .NET application.

    I am trying to open an excel file which is Password protected, After I open programatically, the Password Dialog box is popped. Now I would like to unprotect the excel, I am using workbook.unprotect("abc") method to unprotect.

    My query is how can I handle the Dialogbox to suppress automatically or send keys using SendKeys Method and pass {ENTER}, I have tried using SendKeys however this is not working, can you let me know what is the bestway to handle this and automate even for the first time I do not want my program to be in waiting mode for the user to Click Enter or Click OK in dialog box.


  2. Hi venkat,
    Thanks a lot for u bcz u are explanation is very good...U r tutorials helped me a lot..

  3. Hello Sir,

    I was trying to try caching in the way u explained in Part 119, but for some reason it is not working.. do you think any changes have to made in the internet explorer's setting

  4. Venkat,

    All of your YouTube videos that I have watched to date have been excellent!

    I have been watching the C#, ASP.NET and ADO.NET videos and they are all fantastic.

    Just wanted to say thanks.



If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.