Using the Cache object with the SqlDependency object (C#) : SQL Cache Dependencies « Cache « ASP.NET Tutorial






<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">
    protected void Page_Load(object sender, System.EventArgs e)
    {
       DataSet myCustomers;
       myCustomers = (DataSet)Cache["firmCustomers"];

       if (myCustomers == null)
       {
          SqlConnection conn = new SqlConnection( ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString);
          SqlDataAdapter da = new SqlDataAdapter("Select * from Customers", conn);
          
          myCustomers = new DataSet();
          da.Fill(myCustomers);

          SqlCacheDependency myDependency = new SqlCacheDependency("Northwind", "Customers");
          Cache.Insert("firmCustomers", myCustomers, myDependency);

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

        GridView1.DataSource = myCustomers;
        GridView1.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Sql Cache Invalidation</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:Label ID="Label1" Runat="server"></asp:Label><br />
        <br />
        <asp:GridView ID="GridView1" Runat="server"></asp:GridView>
    </form>
</body>
</html>

File: Web.config

<?xml version="1.0"?>

<configuration>
  <appSettings/>
  <connectionStrings>
    <add name="AppConnectionString1" connectionString="Data Source=.\SQLEXPRESS;User ID=wrox;Password=wrox1*c;Database=Northwind;Persist Security Info=False" providerName="System.Data.SqlClient"/>
  </connectionStrings>
  <system.web>
    <caching>
      <sqlCacheDependency enabled="true">
        <databases>
          <add name="Northwind" connectionStringName="AppConnectionString1" pollTime="500"/>
        </databases>
      </sqlCacheDependency>
    </caching>

    <compilation debug="true" strict="false" explicit="true"/>
    <pages>
      <namespaces>
        <clear/>
        <add namespace="System"/>
        <add namespace="System.Collections"/>
        <add namespace="System.Collections.Specialized"/>
        <add namespace="System.Configuration"/>
        <add namespace="System.Text"/>
        <add namespace="System.Text.RegularExpressions"/>
        <add namespace="System.Web"/>
        <add namespace="System.Web.Caching"/>
        <add namespace="System.Web.SessionState"/>
        <add namespace="System.Web.Security"/>
        <add namespace="System.Web.Profile"/>
        <add namespace="System.Web.UI"/>
        <add namespace="System.Web.UI.WebControls"/>
        <add namespace="System.Web.UI.WebControls.WebParts"/>
        <add namespace="System.Web.UI.HtmlControls"/>
      </namespaces>
    </pages>

    <authentication mode="Windows"/>

  </system.web>
</configuration>








13.12.SQL Cache Dependencies
13.12.1.SqlDependency (C#)
13.12.2.SqlDependency (VB)
13.12.3.Using SQL Cache Dependencies
13.12.4.Using Polling SQL Cache Dependencies with Page Output Caching
13.12.5.Using Polling SQL Cache Dependencies with DataSource Caching
13.12.6.Using Polling SQL Cache Dependencies with Data Caching
13.12.7.SqlDependency="Northwind:Customers"
13.12.8.Using the Cache object with the SqlDependency object (C#)
13.12.9.Using the Cache object with the SqlDependency object (VB)