SQL Cache Dependency and its working

we will discuss about sql cache dependency which helps us to update the cached data automatically whenever their is change in the related database. We will also see this with the help of an example.

With the help of sql cache dependency we can find out whenever change in the database occurs and then accordingly we can change the data cached. 

There is 2 types of sql cache dependency :

1. Polling sql cache dependency
2. Push sql cache dependency 

We will discuss here polling cache dependency.

Polling uses trigger for determining the changes occurred.

Steps:

1. Enable the database and the tables in sql on which we have to apply sql cache dependency.
For this we will open command prompt of Microsoft framework tool and type:

aspnet_regsql -C "Data Source=dell;Integrated Security=True;Initial Catalog=EmployeeDB" -ed

and then we will enable tables for it:

aspnet_regsql -C "Data Source=dell;Integrated Security=True;Initial Catalog=EmployeeDB" -et -t Designation

2. We will configure the application for sql cache dependency.This will be done in web.config file:

<connectionStrings>
		<add name="EmployeeDBConnectionString" connectionString="Data Source=dell;Initial Catalog=EmployeeDB" providerName="System.Data.SqlClient"/>
	</connectionStrings>
	<system.web>
 <caching>
      <sqlCacheDependency enabled="true" pollTime="500">
        <databases>
          <add name="EmployeeDB" connectionStringName="EmployeeDBConnectionString"/>
        </databases>
      </sqlCacheDependency>
                          
    </caching>
</system.web>

3. Now we will add the directive:

<%@ OutputCache Duration="300" VaryByParam="None" SqlDependency="EmployeeDB:Designation" %>

then we will add a gridview and sqldatasource control like:

 <div>
    
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:EmployeeDBConnectionString %>" 
            SelectCommand="SELECT [empid], [empname], [empdesignation] FROM [Designation]">
        </asp:SqlDataSource>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            BorderWidth="1px" CellPadding="2" DataKeyNames="empid" DataSourceID="SqlDataSource1" 
            ForeColor="Black" GridLines="None" Height="191px" Width="375px">
            <Columns>
                <asp:BoundField DataField="empid" HeaderText="empid" ReadOnly="True" 
                    SortExpression="empid" />
                <asp:BoundField DataField="empname" HeaderText="empname" SortExpression="empname" />
                <asp:BoundField DataField="empdesignation" HeaderText="empdesignation" 
                    SortExpression="empdesignation" />
            </Columns>
                        
        </asp:GridView>
        <br />
        <asp:Label ID="Label1" runat="server"></asp:Label>
        <br />
        <br />
    
    </div>

and in codebehind u write:

Label1.Text = DateTime.Now.ToString();

and now when u refresh the page within 30 seconds same time will be displayed all the time. Now if you change the the database table designation and then again refresh the page same time will be displayed but in GridView updated record will be displayed. 

Note: All these actions should be performed within 30 seconds i.e. time specified in the "Duration" inside the directive.