Auto Refresh GridView using SqlCacheDependency and Toolkit’s Timer.

SqlCacheDependency basically can send a notification to the web application, whenever any change occurs to the underlying data source.You may learn more about ‘SqlCacheDependency’ here — http://msdn.microsoft.com/en-us/library/system.web.caching.sqlcachedependency.aspx
In this example below, a GridView & Timer control is inside an UpdatePanel. The GridView displays certain records from the DB. When the page first loads , the DB is queried, the Cache and GridView is populated. The Timer actually polls the cache object at a certain predetermined interval. Now, whenever the query output changes, SqlCacheDependency invalidates the cache. When the cache is null,the query runs again and binds the grid. Please realize that, this Query notification using SQLCacheDependency does not refresh the web page. The aspx page still needs a postback to reflect the changes. That’s where the Toolkit’s timer helps. The timer polls at a certain interval & checks if the cache is empty or not. If the cache is empty , it runs the query again , grabs new data , binds the grid & updates the UpdatePanel.
So the polling thing is not totally eliminated. But still it’s better since the DB is not polled, instead the application’s cache object and the GridView databinding occurs only when data changes. Another thing to keep in mind, since it’s cache , the scope is application.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="UnderstandingSQLCacheDependency5.aspx.cs" Inherits="UnderstandingSQLCacheDependency5" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxToolkit" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<ajaxToolkit:ToolkitScriptManager ID="ScriptManager1" runat="server"></ajaxToolkit:ToolkitScriptManager>

<div>
<asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional"><ContentTemplate> 
<asp:GridView id="GridView1" runat="server" ></asp:GridView>
    <br />
<br />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<br />
<asp:Timer ID="timer1" runat="server" Interval="15000" OnTick="Timer1_Tick" />
</ContentTemplate>
</asp:UpdatePanel>
</div>
</form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Web.Caching;
using System.Configuration;

public partial class UnderstandingSQLCacheDependency5 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGrid(null);
    }
}

private DataTable LoadData()
{
    DataTable dtCustomers = new DataTable();
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString))
    {
        SqlCommand command = new SqlCommand(GetSQL(), connection);
        SqlCacheDependency dependency = new SqlCacheDependency(command);
        if (connection.State == ConnectionState.Closed)
            connection.Open();
        dtCustomers.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
        Cache.Insert("Customers", dtCustomers, dependency);
    }
    return dtCustomers;
}


private void BindGrid(DataTable dtCustomers)
{
    if (dtCustomers == null)
    {
        dtCustomers = this.LoadData();
        Label1.Text = string.Format("Grid refreshed -- DateTime : {0}", System.DateTime.Now);
    }
    else
    {
        //No need to bind the grid, as the underlying data did not change
        return;
    }
    GridView1.DataSource = dtCustomers;
    GridView1.DataBind();
    UpdatePanel1.Update();
}

private string GetSQL()
{
    return "SELECT CustomerKey, " +
    "FirstName, " +
    "MiddleName, " +
    "LastName " +
    "FROM [dbo].[DimCustomer] " +
    "WHERE FirstName like 'johnn%'";
}

protected void Timer1_Tick(object sender, EventArgs e)
{
    DataTable dtCustomers = (DataTable)Cache.Get("Customers");
    BindGrid(dtCustomers);
}

}

<%@ Application Language="C#" %>

<script runat="server">

    string connectionString =
        ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;

    void Application_Start(object sender, EventArgs e) 
    {
        // Code that runs on application startup
        System.Data.SqlClient.SqlDependency.Start(connectionString);
    }
    
    void Application_End(object sender, EventArgs e) 
    {
        //  Code that runs on application shutdown
        System.Data.SqlClient.SqlDependency.Stop(connectionString);
    }
        
    void Application_Error(object sender, EventArgs e) 
    { 
        // Code that runs when an unhandled error occurs
    }

    void Session_Start(object sender, EventArgs e) 
    {
        // Code that runs when a new session is started
    }

    void Session_End(object sender, EventArgs e) 
    {
        // Code that runs when a session ends. 
        // Note: The Session_End event is raised only when the sessionstate mode
        // is set to InProc in the Web.config file. If session mode is set to StateServer 
        // or SQLServer, the event is not raised.

    }
       
</script>

That’s it. Hope this helps.

Advertisements
This entry was posted in AJAXToolKit. Bookmark the permalink.

3 Responses to Auto Refresh GridView using SqlCacheDependency and Toolkit’s Timer.

  1. Adeel Ahmed says:

    Well! this is really great…….Clear and concise example

  2. Cenk says:

    Hi your code is great but I wonder if there is a way to avoid going to database when user refreshes the page? Because page is already refreshing itself for every minute or whatever.

  3. Tesfamariam says:

    It is wonderful example

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s