Update GridView dynamically when record is updated using SqlCacheDependency in ASP.Net

Using SQL Cache Notifications

SQL Cache dependency helps to cache tables in ASP.NET application in memory. So rather than making SQL server trips we can fetch the data from the cached object from ASP.NET.

Step- 1

ALTER DATABASE MyDatabase SET ENABLE_BROKER

Step- 2  Html Page

<asp:Repeater ID="RepDetails" runat="server">
    <ItemTemplate>
        <asp:Label ID="lblid" runat="server" Text='<%#Eval("Cat_Id") %>' Font-Bold="true"
            Visible="false" />
        <asp:LinkButton ID="LinkButton1" runat="server" Text='<%#Eval("Cat_Name") %>' ForeColor="Green"></asp:LinkButton>
    </ItemTemplate>
</asp:Repeater>

Step- 3 Namespace

using System.Configuration;
using System.Web;
using System.Web.Caching;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Services;
using NUnit.Framework;

Step-4 C# code

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        DataSet myCustomers;
        myCustomers = (DataSet)Cache["firmCustomers"];

        if (myCustomers == null)
        {
            string CS = ConfigurationManager.ConnectionStrings["ErpConnection"].ConnectionString;
            SqlConnection conn = new SqlConnection(CS);
            SqlDataAdapter da = new SqlDataAdapter("Select top(30) * from tbl_Category", conn);
            System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(CS);
            System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(CS, "tbl_Category");
            myCustomers = new DataSet();
            da.Fill(myCustomers);

            SqlCacheDependency myDependency = new SqlCacheDependency("Databasename", "tbl_Category");
            Cache.Insert("firmCustomers", myCustomers, myDependency);

            Label1.Text = "Produced from database.";
        }
        else
        {
            Label1.Text = "Produced from Cache object.";
        }

        RepDetails.DataSource = myCustomers;
        RepDetails.DataBind();
        {
            // BindGrid(null);
        }
    }
}

private static DataTable GetData(string query)
{
    string strConnString = ConfigurationManager.ConnectionStrings["ErpConnection"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = query;
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    return dt;
                }
            }
        }
    }
}

Step -5  some change in web.config

<connectionStrings>
  <add name="ErpConnection" connectionString="Data Source=MYPC\SQLEXPRESS;Initial Catalog=Databasename;integrated security=true" providerName="System.Data.SqlClient"/>
</connectionStrings>

add this code within <system.web>

<system.web>
  <caching>
    <sqlCacheDependency enabled="true"  pollTime = "9000000">
      <databases>
        <add name="Databasename" connectionStringName="ErpConnection" pollTime="600000"/>
      </databases>
    </sqlCacheDependency>
  </caching>
</system.web>