ASP.NET Tutorial/Cache/SQL Cache Dependencies

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

SqlDependency (C#)

<%@ 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 runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" Height="400px" Width="400px">
        </asp:GridView>
    
    </div>
    </form>
</body>
</html>
File: Default.aspx.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Caching;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Response.Write("Page created: " + DateTime.Now.ToLongTimeString());
        string connStr = ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString;
        SqlDependency.Start(connStr);
        SqlConnection connection = new SqlConnection(connStr);
        SqlCommand command = new SqlCommand("Select * FROM Customers", connection);
        SqlCacheDependency depends = new SqlCacheDependency(command);
        
        connection.Open();
        GridView1.DataSource = command.ExecuteReader();
        GridView1.DataBind();
        connection.Close();
        Response.AddCacheDependency(depends);
    }
}

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>
    <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>


SqlDependency="Northwind:Customers"

<%@ Page Language="C#" AutoEventWireup="true"%>
<%@ OutputCache Duration="30" VaryByParam="none" SqlDependency="Northwind:Customers"%>
<script runat="server">
    protected void Page_Load(object sender, System.EventArgs e) 
    {
        Label1.Text = "Page created at " + DateTime.Now.ToShortTimeString();
    }
</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" DataSourceID="SqlDataSource1">
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" Runat="server" 
         SelectCommand="Select * From Customers"
         ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>" 
         ProviderName="<%$ ConnectionStrings:AppConnectionString1.providername %>">
        </asp:SqlDataSource>
    </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>


SqlDependency (VB)

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" 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 runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" Height="400px" Width="400px">
        </asp:GridView>
    
    </div>
    </form>
</body>
</html>
File: Default.aspx.vb
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Response.Write("Page created: " + DateTime.Now.ToLongTimeString())
        Dim connStr As String = ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString
        SqlDependency.Start(connStr)
        Dim connection As New SqlConnection(connStr)
        Dim command As New SqlCommand("Select * FROM Customers", connection)
        Dim depends As New SqlCacheDependency(command)
        connection.Open()
        GridView1.DataSource = command.ExecuteReader()
        GridView1.DataBind()
        connection.Close()
        Response.AddCacheDependency(depends)
    End Sub
End Class

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>


Using Polling SQL Cache Dependencies with Data Caching

<%@ Page Language="C#" Trace="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
    void Page_Load()
    {
        DataTable products = (DataTable)Cache["Products"];
        if (products == null)
        {
            products = GetProductsFromDB();
            SqlCacheDependency sqlDepend = new SqlCacheDependency("MyDatabase", "Products");
            Cache.Insert("Products", products, sqlDepend);
        }
        grdProducts.DataSource = products;
        grdProducts.DataBind();
    }
    private DataTable GetProductsFromDB()
    {
        Trace.Warn("Retrieving data from database");
        string conString = WebConfigurationManager.ConnectionStrings ["Products"].ConnectionString;
        SqlDataAdapter dad = new SqlDataAdapter("SELECT Title,Director FROM Products", conString);
        DataTable products = new DataTable();
        dad.Fill(products);
        return products;
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Polling SQL Data Cache</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:GridView
        id="grdProducts"
        Runat="server" />
    </div>
    </form>
</body>
</html>


Using Polling SQL Cache Dependencies with DataSource Caching

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
    protected void srcProducts_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        lblMessage.Text = "Retrieving data from database";
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Polling SQL DataSource Cache</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:Label
        id="lblMessage"
        EnableViewState="false"
        Runat="server" />
    <hr />
    <asp:GridView
        id="grdProducts"
        DataSourceID="srcProducts"
        Runat="server" />
    <asp:SqlDataSource
        id="srcProducts"
        ConnectionString="<%$ ConnectionStrings:Products %>"
        SelectCommand="SELECT Title, Director FROM Products"
        EnableCaching="true"
        SqlCacheDependency="MyDatabase:Products"
        OnSelecting="srcProducts_Selecting"
        Runat="server" />
    </div>
    </form>
</body>
</html>


Using Polling SQL Cache Dependencies with Page Output Caching

<%@ Page Language="C#" %>
<%@ OutputCache Duration="9999" VaryByParam="none"
  SqlDependency="MyDatabase:Products" %>
<!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>Polling SQL Output Cache</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <%= DateTime.Now.ToString("T") %>
    <hr />
    <asp:GridView
        id="grdProducts"
        DataSourceID="srcProducts"
        Runat="server" />
    <asp:SqlDataSource
        id="srcProducts"
        ConnectionString="<%$ ConnectionStrings:Products %>"
        SelectCommand="SELECT Title, Director FROM Products"
        Runat="server" />
    </div>
    </form>
</body>
</html>


Using SQL Cache Dependencies

File: Web.Config
<configuration>
  <connectionStrings>
    <add name="Products" connectionString="Data Source=.\SQLEXPRESS;
      AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;
User Instance=True" />
  </connectionStrings>
  <system.web>
    <caching>
      <sqlCacheDependency enabled="true" pollTime="5000">
        <databases>
          <add
            name="MyDatabase"
            connectionStringName="Products" />
        </databases>
      </sqlCacheDependency>
    </caching>
  </system.web>
</configuration>


Using the Cache object with the SqlDependency object (C#)

<%@ 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>


Using the Cache object with the SqlDependency object (VB)

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<script runat="server">
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim myCustomers As DataSet
        myCustomers = CType(Cache("firmCustomers"), DataSet)
        If myCustomers Is Nothing Then
           Dim conn As SqlConnection = New SqlConnection( ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString)
           Dim da As SqlDataAdapter = New SqlDataAdapter("Select * From Customers", conn)
           myCustomers = New DataSet
           da.Fill(myCustomers)
           Dim myDependency As SqlCacheDependency = New SqlCacheDependency("Northwind", "Customers")
           Cache.Insert("firmCustomers", myCustomers, myDependency)
           Label1.Text = "Produced from database."
        Else
           Label1.Text = "Produced from Cache object."      
        End If
        GridView1.DataSource = myCustomers
        GridView1.DataBind()
    End Sub
</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>
Источник — «http://nfex.ru/index.php?title=ASP.NET_Tutorial/Cache/SQL_Cache_Dependencies&oldid=2896»