ASP.NET Tutorial/Cache/SQL Cache Dependencies

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

SqlDependency (C#)

   <source lang="csharp">

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

       <asp:GridView ID="GridView1" runat="server" Height="400px" Width="400px">
       </asp:GridView>
   

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


SqlDependency="Northwind:Customers"

   <source lang="csharp">

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

       

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


SqlDependency (VB)

   <source lang="csharp">

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

       <asp:GridView ID="GridView1" runat="server" Height="400px" Width="400px">
       </asp:GridView>
   

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


Using Polling SQL Cache Dependencies with Data Caching

   <source lang="csharp">

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

   <asp:GridView
       id="grdProducts"
       Runat="server" />

   </form>

</body> </html></source>


Using Polling SQL Cache Dependencies with DataSource Caching

   <source lang="csharp">

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

   <asp:Label
       id="lblMessage"
       EnableViewState="false"
       Runat="server" />

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

   </form>

</body> </html></source>


Using Polling SQL Cache Dependencies with Page Output Caching

   <source lang="csharp">

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

   <%= DateTime.Now.ToString("T") %>

   <asp:GridView
       id="grdProducts"
       DataSourceID="srcProducts"
       Runat="server" />
   <asp:SqlDataSource
       id="srcProducts"
       ConnectionString="<%$ ConnectionStrings:Products %>"
       SelectCommand="SELECT Title, Director FROM Products"
       Runat="server" />

   </form>

</body> </html></source>


Using SQL Cache Dependencies

   <source lang="csharp">

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


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

   <source lang="csharp">

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

       

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


Using the Cache object with the SqlDependency object (VB)

   <source lang="csharp">

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

       

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

Источник — «http://nfex.ru/index.php?title=ASP.NET_Tutorial/Cache/SQL_Cache_Dependencies&oldid=2896»