How can use sqlCacheDependency for fetching data from data base without page refreshing.

Database name : TQueryNotification

Table name : EMP_Detail , cpyjob.

  

select * from cpyjob

no	job
1	SW
2	TT
3	bank

select * from cpy.EMP_Detail

sno	name	address	email	phone	pin
1	chandra	delhi	cp@gmail.com	3455	2344
2	ravi	lucknow	rp@gmail.com	45667	2345
3	rohit	delhi	r@gmail.com	3455	344
4	roli	delhi	roli@gmailo.com	35335	353
5	raj1	kanpur	raJ@gmail.com	5556756	44545

Before uesing sqlCacheDependency to use them we first need to set up our database by executing the following scripts:

CREATE ROLE [cpy] AUTHORIZATION [dbo]

GO

 CREATE SCHEMA [cpy] AUTHORIZATION [cpy]

GO

GRANT CREATE PROCEDURE to [cpy];

GRANT CREATE QUEUE to [cpy];

GRANT CREATE SERVICE to [cpy];

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [cpy];

GRANT VIEW DEFINITION TO [cpy];

GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [cpy];

GRANT RECEIVE ON QueryNotificationErrorsQueue TO [cpy];

GO

SELECT  [no],[job]  FROM [cpy].[cpyjob]   no result

SELECT  [Sno],[Name]  FROM [cpy].[EMP_Detail]  result

SELECT  [no],[job]  FROM [dbo].[cpyjob]    result

Have you ever thought of a scenario in an ASP.NET website that if something is inserted, updated or deleted from the database the ASPX page should auto-reflect it without explicitly refreshing the page? You can using an ajax timer and database polling can easily accomplish the mission, right?

Give it a second thought; is it really a good practice to poll the database periodically? No, its not, because every database call will increase the load on the website. What if we put the data into a cache and poll it periodically for a change in data . Sql server provide we have option QueryNotification and SQLCacheDependency for above task.

In the getdata() function I am executing a select command and populating the data table with the results and assigning it to a cache. If you will see I have created an object of sqlCacheDependency and passed a command object into its constructor. Later this dependency object has been passed to the Cache.Insert method. A timer is periodically checking whether the cache is empty or not. If the cache is empty it will be populated back from the database.Query Notifier that we enabled will notify whether the data has changed or not as soon as the data is changed our the cache will be Null and the freshly changed data will be shown in the grid.

There are the following limitations of SQL Cache Dependency:

Works on plain query not SP

No support of * in the query

Query should not contain UNION, INTERSECT, EXCEPT operators

Query should not contain Distinct Keyword

Limited use of AGGREGATE functions

C# code :

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using System.Web.Caching; 

public partial class _Default : System.Web.UI.Page

{

    string con = "Data Source=HP11-PC\\SQLEXPRESS;Initial Catalog=TQueryNotification;integrated security=true";

  protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            getdata();

        }

    }

    public void getdata()

    {

        if (HttpRuntime.Cache["Hello1"] == null)

        {

            SqlConnection c = new SqlConnection(con);

            SqlCommand cmd = new SqlCommand("SELECT  [Sno],[Name]  FROM [cpy].[EMP_Detail]", c);

            cmd.Notification = null;

            cmd.NotificationAutoEnlist = true;

            SqlCacheDependency cac1 = new SqlCacheDependency(cmd);

            SqlDependency.Start(con);

            c.Open();

            DataTable dt = new DataTable();

            SqlDataAdapter sda = new SqlDataAdapter(cmd);

            sda.Fill(dt);

            c.Close();

            HttpRuntime.Cache.Insert("Hello1", dt, cac1);

        }

        Load1();

    }

    public void Load1()

    {

        DataTable dt = (DataTable)HttpRuntime.Cache.Get("Hello1");

        GridView1.DataSource = dt;

        GridView1.DataBind();

    }

    protected void Timer1_Tick(object sender, EventArgs e)

    {

        getdata();

    }

}

aspx page code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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 id="Head1" runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <asp:ScriptManager ID="ScriptManager1" runat="server">

    </asp:ScriptManager>

    <asp:UpdatePanel runat="server" ID="upd" UpdateMode="Conditional">

        <ContentTemplate>

            <asp:Timer ID="Timer1" runat="server" Interval="600" OnTick="Timer1_Tick">

            </asp:Timer>

            <div>

                <asp:GridView ID="GridView1" runat="server">

                </asp:GridView>

            </div>

        </ContentTemplate>

    </asp:UpdatePanel>

    </form>

</body>

</html>