ASP.NET Tutorial/Cache/SQL Cache Dependencies — различия между версиями

Текущая версия на 11:57, 26 мая 2010

Содержание

1 SqlDependency (C#)
2 SqlDependency="Northwind:Customers"
3 SqlDependency (VB)
4 Using Polling SQL Cache Dependencies with Data Caching
5 Using Polling SQL Cache Dependencies with DataSource Caching
6 Using Polling SQL Cache Dependencies with Page Output Caching
7 Using SQL Cache Dependencies
8 Using the Cache object with the SqlDependency object (C#)
9 Using the Cache object with the SqlDependency object (VB)

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>

ASP.NET Tutorial/Cache/SQL Cache Dependencies — различия между версиями

Текущая версия на 11:57, 26 мая 2010

Содержание

SqlDependency (C#)

SqlDependency="Northwind:Customers"

SqlDependency (VB)

Using Polling SQL Cache Dependencies with Data Caching

Using Polling SQL Cache Dependencies with DataSource Caching

Using Polling SQL Cache Dependencies with Page Output Caching

Using SQL Cache Dependencies

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

Using the Cache object with the SqlDependency object (VB)

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Инструменты

Навигация

Версия 15:30, 26 мая 2010 (просмотреть исходный код) (обсуждение) ← Предыдущая правка	Текущая версия на 11:57, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)
(нет различий)