Working With SQL Dependency Using Caching

Note: I assume that reader of this blog know about caching in ASP.NET and they have knowledge to use SQL Server.

 

Let's start to work with SQL dependency in caching, using ASP.NET.

Step 1: Enable caching in database and table

 

Enable dependencies on the database table.

aspnet_regsql -S <server_name> -U <user_name> -P <password> -d <database_name> -t <table_name> -et

Step 2: Enable Service broker in SQL Server.

 ALTER DATABASE <database_name>SET ENABLE_BROKER

EXEC sp_configure 'show advanced options', '1'

RECONFIGURE

EXEC sp_configure 'clr enabled', 1

 RECONFIGURE

Step 3: Modify your web.config to enable the caching.

<configuration>
    <connectionStrings>
        <add name="ConnStr" connectionString="Data Source=<server_name>;Initial Catalog=<database_name>;Persist Security Info=True;User ID=<user_name>;Password=<password>" />
    </connectionStrings>
    <system.web>
        <compilation debug="true" targetFramework="4.0" />

        <caching>
            <sqlCacheDependency enabled="true">
                <databases>
                    <add name="EMS" connectionStringName="ConnStr" pollTime="500" />
                </databases>
            </sqlCacheDependency>
        </caching>
    </system.web>
</configuration>

Step 4: Get the data from the database to test SQL dependency. 

using System.Data.SqlClient;
using System.Data;

Note: These two namespace are required to use ADO.NET to get the data from the database, using System.Web.Caching.

Note: This namespace is used to get cache object in ASP.NET, using System.Configuration.

Note: This namespace used to get the connection string information from web.config file.

public partial class Default3 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Cache["emp"] == null)
        {
            string str = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            SqlConnection con = new SqlConnection(str);

            SqlDependency.Start(str);
            SqlCommand com = new SqlCommand("select * from Employee", con);
            SqlCacheDependency dep = new SqlCacheDependency("EMS","Employee");
            SqlDataAdapter a = new SqlDataAdapter(com);
            DataSet ds = new DataSet();
            a.Fill(ds, "Employee");
            Cache.Insert("emp", ds, dep, DateTime.Now.AddMinutes(1), TimeSpan.Zero);
            GridView1.DataSource = ds.Tables[0];
            GridView1.DataBind();
            Response.Write("<script>alert('Data comming from Database')</script>");
        }
        else
        {
            DataSet ds = (DataSet)Cache["emp"];
            GridView1.DataSource = ds.Tables[0];
            GridView1.DataBind();
            Response.Write("<script>alert('Data comming from Cache Memory')</script>");
        }
    }
}

Note:

First time, when the page is going to load, the value of Cache["emp"] is null. Cache object used to store the data. Second time, when page is going to load, the value of Cache["emp"] contains the data (table information). Thus, the data will load from cache object into control. If any changes will happen into the table, which is already enabled with cache dependency and cache object will process a round trip to the database and re-load cache object with the modified data.