http://nfex.ru/index.php?action=history&feed=atom&title=ASP.NET_Tutorial%2FCache%2FSQL_Cache_Dependencies
ASP.NET Tutorial/Cache/SQL Cache Dependencies - История изменений
2026-04-29T18:55:08Z
История изменений этой страницы в вики
MediaWiki 1.30.0
http://nfex.ru/index.php?title=ASP.NET_Tutorial/Cache/SQL_Cache_Dependencies&diff=2895&oldid=prev
в 15:30, 26 мая 2010
2010-05-26T15:30:57Z
<p></p>
<table class="diff diff-contentalign-left" data-mw="interface">
<tr style="vertical-align: top;" lang="ru">
<td colspan="1" style="background-color: white; color:black; text-align: center;">← Предыдущая</td>
<td colspan="1" style="background-color: white; color:black; text-align: center;">Версия 15:30, 26 мая 2010</td>
</tr><tr><td colspan="2" style="text-align: center;" lang="ru"><div class="mw-diff-empty">(нет различий)</div>
</td></tr></table>
http://nfex.ru/index.php?title=ASP.NET_Tutorial/Cache/SQL_Cache_Dependencies&diff=2896&oldid=prev
Admin: 1 версия
2010-05-26T11:57:24Z
<p>1 версия</p>
<p><b>Новая страница</b></p><div>== SqlDependency (C#)==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="csharp"><br />
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %><br />
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><br />
<html xmlns="http://www.w3.org/1999/xhtml" ><br />
<head runat="server"><br />
<title>Untitled Page</title><br />
</head><br />
<body><br />
<form id="form1" runat="server"><br />
<div><br />
<asp:GridView ID="GridView1" runat="server" Height="400px" Width="400px"><br />
</asp:GridView><br />
<br />
</div><br />
</form><br />
</body><br />
</html><br />
File: Default.aspx.cs<br />
using System;<br />
using System.Data;<br />
using System.Data.SqlClient;<br />
using System.Web.Caching;<br />
using System.Configuration;<br />
using System.Web;<br />
using System.Web.Security;<br />
using System.Web.UI;<br />
using System.Web.UI.WebControls;<br />
using System.Web.UI.WebControls.WebParts;<br />
using System.Web.UI.HtmlControls;<br />
public partial class _Default : System.Web.UI.Page <br />
{<br />
protected void Page_Load(object sender, EventArgs e)<br />
{<br />
Response.Write("Page created: " + DateTime.Now.ToLongTimeString());<br />
string connStr = ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString;<br />
SqlDependency.Start(connStr);<br />
SqlConnection connection = new SqlConnection(connStr);<br />
SqlCommand command = new SqlCommand("Select * FROM Customers", connection);<br />
SqlCacheDependency depends = new SqlCacheDependency(command);<br />
<br />
connection.Open();<br />
GridView1.DataSource = command.ExecuteReader();<br />
GridView1.DataBind();<br />
connection.Close();<br />
Response.AddCacheDependency(depends);<br />
}<br />
}<br />
<br />
File: Web.config<br />
<?xml version="1.0"?><br />
<configuration><br />
<appSettings/><br />
<connectionStrings><br />
<add name="AppConnectionString1" connectionString="Data Source=.\SQLEXPRESS;User ID=wrox;Password=wrox1*c;Database=Northwind;Persist Security Info=False" providerName="System.Data.SqlClient"/><br />
</connectionStrings><br />
<system.web><br />
<compilation debug="true" strict="false" explicit="true"/><br />
<pages><br />
<namespaces><br />
<clear/><br />
<add namespace="System"/><br />
<add namespace="System.Collections"/><br />
<add namespace="System.Collections.Specialized"/><br />
<add namespace="System.Configuration"/><br />
<add namespace="System.Text"/><br />
<add namespace="System.Text.RegularExpressions"/><br />
<add namespace="System.Web"/><br />
<add namespace="System.Web.Caching"/><br />
<add namespace="System.Web.SessionState"/><br />
<add namespace="System.Web.Security"/><br />
<add namespace="System.Web.Profile"/><br />
<add namespace="System.Web.UI"/><br />
<add namespace="System.Web.UI.WebControls"/><br />
<add namespace="System.Web.UI.WebControls.WebParts"/><br />
<add namespace="System.Web.UI.HtmlControls"/><br />
</namespaces><br />
</pages><br />
<authentication mode="Windows"/><br />
</system.web><br />
</configuration></source><br />
<br />
<br />
<br />
== SqlDependency="Northwind:Customers"==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="csharp"><br />
<%@ Page Language="C#" AutoEventWireup="true"%><br />
<%@ OutputCache Duration="30" VaryByParam="none" SqlDependency="Northwind:Customers"%><br />
<script runat="server"><br />
protected void Page_Load(object sender, System.EventArgs e) <br />
{<br />
Label1.Text = "Page created at " + DateTime.Now.ToShortTimeString();<br />
}<br />
</script><br />
<br />
<html xmlns="http://www.w3.org/1999/xhtml" ><br />
<head id="Head1" runat="server"><br />
<title>Sql Cache Invalidation</title><br />
</head><br />
<body><br />
<form id="form1" runat="server"><br />
<asp:Label ID="Label1" Runat="server"></asp:Label><br /><br />
<br /><br />
<asp:GridView ID="GridView1" Runat="server" DataSourceID="SqlDataSource1"><br />
</asp:GridView><br />
<asp:SqlDataSource ID="SqlDataSource1" Runat="server" <br />
SelectCommand="Select * From Customers"<br />
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>" <br />
ProviderName="<%$ ConnectionStrings:AppConnectionString1.providername %>"><br />
</asp:SqlDataSource><br />
</form><br />
</body><br />
</html><br />
File: Web.config<br />
<?xml version="1.0"?><br />
<configuration><br />
<appSettings/><br />
<connectionStrings><br />
<add name="AppConnectionString1" connectionString="Data Source=.\SQLEXPRESS;User ID=wrox;Password=wrox1*c;Database=Northwind;Persist Security Info=False" providerName="System.Data.SqlClient"/><br />
</connectionStrings><br />
<system.web><br />
<caching><br />
<sqlCacheDependency enabled="true"><br />
<databases><br />
<add name="Northwind" connectionStringName="AppConnectionString1" pollTime="500"/><br />
</databases><br />
</sqlCacheDependency><br />
</caching><br />
<compilation debug="true" strict="false" explicit="true"/><br />
<pages><br />
<namespaces><br />
<clear/><br />
<add namespace="System"/><br />
<add namespace="System.Collections"/><br />
<add namespace="System.Collections.Specialized"/><br />
<add namespace="System.Configuration"/><br />
<add namespace="System.Text"/><br />
<add namespace="System.Text.RegularExpressions"/><br />
<add namespace="System.Web"/><br />
<add namespace="System.Web.Caching"/><br />
<add namespace="System.Web.SessionState"/><br />
<add namespace="System.Web.Security"/><br />
<add namespace="System.Web.Profile"/><br />
<add namespace="System.Web.UI"/><br />
<add namespace="System.Web.UI.WebControls"/><br />
<add namespace="System.Web.UI.WebControls.WebParts"/><br />
<add namespace="System.Web.UI.HtmlControls"/><br />
</namespaces><br />
</pages><br />
<authentication mode="Windows"/><br />
</system.web><br />
</configuration></source><br />
<br />
<br />
<br />
== SqlDependency (VB)==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="csharp"><br />
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %><br />
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><br />
<html xmlns="http://www.w3.org/1999/xhtml" ><br />
<head runat="server"><br />
<title>Untitled Page</title><br />
</head><br />
<body><br />
<form id="form1" runat="server"><br />
<div><br />
<asp:GridView ID="GridView1" runat="server" Height="400px" Width="400px"><br />
</asp:GridView><br />
<br />
</div><br />
</form><br />
</body><br />
</html><br />
File: Default.aspx.vb<br />
Imports System.Data<br />
Imports System.Configuration<br />
Imports System.Data.SqlClient<br />
Partial Class _Default<br />
Inherits System.Web.UI.Page<br />
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load<br />
Response.Write("Page created: " + DateTime.Now.ToLongTimeString())<br />
Dim connStr As String = ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString<br />
SqlDependency.Start(connStr)<br />
Dim connection As New SqlConnection(connStr)<br />
Dim command As New SqlCommand("Select * FROM Customers", connection)<br />
Dim depends As New SqlCacheDependency(command)<br />
connection.Open()<br />
GridView1.DataSource = command.ExecuteReader()<br />
GridView1.DataBind()<br />
connection.Close()<br />
Response.AddCacheDependency(depends)<br />
End Sub<br />
End Class<br />
<br />
File: Web.config<br />
<?xml version="1.0"?><br />
<configuration><br />
<appSettings/><br />
<connectionStrings><br />
<add name="AppConnectionString1" connectionString="Data Source=.\SQLEXPRESS;User ID=wrox;Password=wrox1*c;Database=Northwind;Persist Security Info=False" providerName="System.Data.SqlClient"/><br />
</connectionStrings><br />
<system.web><br />
<caching><br />
<sqlCacheDependency enabled="true"><br />
<databases><br />
<add name="Northwind" connectionStringName="AppConnectionString1" pollTime="500"/><br />
</databases><br />
</sqlCacheDependency><br />
</caching><br />
<compilation debug="true" strict="false" explicit="true"/><br />
<pages><br />
<namespaces><br />
<clear/><br />
<add namespace="System"/><br />
<add namespace="System.Collections"/><br />
<add namespace="System.Collections.Specialized"/><br />
<add namespace="System.Configuration"/><br />
<add namespace="System.Text"/><br />
<add namespace="System.Text.RegularExpressions"/><br />
<add namespace="System.Web"/><br />
<add namespace="System.Web.Caching"/><br />
<add namespace="System.Web.SessionState"/><br />
<add namespace="System.Web.Security"/><br />
<add namespace="System.Web.Profile"/><br />
<add namespace="System.Web.UI"/><br />
<add namespace="System.Web.UI.WebControls"/><br />
<add namespace="System.Web.UI.WebControls.WebParts"/><br />
<add namespace="System.Web.UI.HtmlControls"/><br />
</namespaces><br />
</pages><br />
<authentication mode="Windows"/><br />
</system.web><br />
</configuration></source><br />
<br />
<br />
<br />
== Using Polling SQL Cache Dependencies with Data Caching==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="csharp"><br />
<%@ Page Language="C#" Trace="true" %><br />
<%@ Import Namespace="System.Data" %><br />
<%@ Import Namespace="System.Data.SqlClient" %><br />
<%@ Import Namespace="System.Web.Configuration" %><br />
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"<br />
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><br />
<script runat="server"><br />
void Page_Load()<br />
{<br />
DataTable products = (DataTable)Cache["Products"];<br />
if (products == null)<br />
{<br />
products = GetProductsFromDB();<br />
SqlCacheDependency sqlDepend = new SqlCacheDependency("MyDatabase", "Products");<br />
Cache.Insert("Products", products, sqlDepend);<br />
}<br />
grdProducts.DataSource = products;<br />
grdProducts.DataBind();<br />
}<br />
private DataTable GetProductsFromDB()<br />
{<br />
Trace.Warn("Retrieving data from database");<br />
string conString = WebConfigurationManager.ConnectionStrings ["Products"].ConnectionString;<br />
SqlDataAdapter dad = new SqlDataAdapter("SELECT Title,Director FROM Products", conString);<br />
DataTable products = new DataTable();<br />
dad.Fill(products);<br />
return products;<br />
}<br />
</script><br />
<html xmlns="http://www.w3.org/1999/xhtml" ><br />
<head id="Head1" runat="server"><br />
<title>Polling SQL Data Cache</title><br />
</head><br />
<body><br />
<form id="form1" runat="server"><br />
<div><br />
<asp:GridView<br />
id="grdProducts"<br />
Runat="server" /><br />
</div><br />
</form><br />
</body><br />
</html></source><br />
<br />
<br />
<br />
== Using Polling SQL Cache Dependencies with DataSource Caching==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="csharp"><br />
<%@ Page Language="C#" %><br />
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"<br />
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><br />
<script runat="server"><br />
protected void srcProducts_Selecting(object sender, SqlDataSourceSelectingEventArgs e)<br />
{<br />
lblMessage.Text = "Retrieving data from database";<br />
}<br />
</script><br />
<html xmlns="http://www.w3.org/1999/xhtml" ><br />
<head id="Head1" runat="server"><br />
<title>Polling SQL DataSource Cache</title><br />
</head><br />
<body><br />
<form id="form1" runat="server"><br />
<div><br />
<asp:Label<br />
id="lblMessage"<br />
EnableViewState="false"<br />
Runat="server" /><br />
<hr /><br />
<asp:GridView<br />
id="grdProducts"<br />
DataSourceID="srcProducts"<br />
Runat="server" /><br />
<asp:SqlDataSource<br />
id="srcProducts"<br />
ConnectionString="<%$ ConnectionStrings:Products %>"<br />
SelectCommand="SELECT Title, Director FROM Products"<br />
EnableCaching="true"<br />
SqlCacheDependency="MyDatabase:Products"<br />
OnSelecting="srcProducts_Selecting"<br />
Runat="server" /><br />
</div><br />
</form><br />
</body><br />
</html></source><br />
<br />
<br />
<br />
== Using Polling SQL Cache Dependencies with Page Output Caching==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="csharp"><br />
<%@ Page Language="C#" %><br />
<%@ OutputCache Duration="9999" VaryByParam="none"<br />
SqlDependency="MyDatabase:Products" %><br />
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"<br />
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><br />
<html xmlns="http://www.w3.org/1999/xhtml" ><br />
<head id="Head1" runat="server"><br />
<title>Polling SQL Output Cache</title><br />
</head><br />
<body><br />
<form id="form1" runat="server"><br />
<div><br />
<%= DateTime.Now.ToString("T") %><br />
<hr /><br />
<asp:GridView<br />
id="grdProducts"<br />
DataSourceID="srcProducts"<br />
Runat="server" /><br />
<asp:SqlDataSource<br />
id="srcProducts"<br />
ConnectionString="<%$ ConnectionStrings:Products %>"<br />
SelectCommand="SELECT Title, Director FROM Products"<br />
Runat="server" /><br />
</div><br />
</form><br />
</body><br />
</html></source><br />
<br />
<br />
<br />
== Using SQL Cache Dependencies==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="csharp"><br />
File: Web.Config<br />
<configuration><br />
<connectionStrings><br />
<add name="Products" connectionString="Data Source=.\SQLEXPRESS;<br />
AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;<br />
User Instance=True" /><br />
</connectionStrings><br />
<system.web><br />
<caching><br />
<sqlCacheDependency enabled="true" pollTime="5000"><br />
<databases><br />
<add<br />
name="MyDatabase"<br />
connectionStringName="Products" /><br />
</databases><br />
</sqlCacheDependency><br />
</caching><br />
</system.web><br />
</configuration></source><br />
<br />
<br />
<br />
== Using the Cache object with the SqlDependency object (C#)==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="csharp"><br />
<%@ Page Language="C#" %><br />
<%@ Import Namespace="System.Data" %><br />
<%@ Import Namespace="System.Data.SqlClient" %><br />
<script runat="server"><br />
protected void Page_Load(object sender, System.EventArgs e)<br />
{<br />
DataSet myCustomers;<br />
myCustomers = (DataSet)Cache["firmCustomers"];<br />
if (myCustomers == null)<br />
{<br />
SqlConnection conn = new SqlConnection( ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString);<br />
SqlDataAdapter da = new SqlDataAdapter("Select * from Customers", conn);<br />
<br />
myCustomers = new DataSet();<br />
da.Fill(myCustomers);<br />
SqlCacheDependency myDependency = new SqlCacheDependency("Northwind", "Customers");<br />
Cache.Insert("firmCustomers", myCustomers, myDependency);<br />
Label1.Text = "Produced from database."; <br />
}<br />
else<br />
{<br />
Label1.Text = "Produced from Cache object.";<br />
}<br />
GridView1.DataSource = myCustomers;<br />
GridView1.DataBind();<br />
}<br />
</script><br />
<html xmlns="http://www.w3.org/1999/xhtml" ><br />
<head id="Head1" runat="server"><br />
<title>Sql Cache Invalidation</title><br />
</head><br />
<body><br />
<form id="form1" runat="server"><br />
<asp:Label ID="Label1" Runat="server"></asp:Label><br /><br />
<br /><br />
<asp:GridView ID="GridView1" Runat="server"></asp:GridView><br />
</form><br />
</body><br />
</html><br />
File: Web.config<br />
<?xml version="1.0"?><br />
<configuration><br />
<appSettings/><br />
<connectionStrings><br />
<add name="AppConnectionString1" connectionString="Data Source=.\SQLEXPRESS;User ID=wrox;Password=wrox1*c;Database=Northwind;Persist Security Info=False" providerName="System.Data.SqlClient"/><br />
</connectionStrings><br />
<system.web><br />
<caching><br />
<sqlCacheDependency enabled="true"><br />
<databases><br />
<add name="Northwind" connectionStringName="AppConnectionString1" pollTime="500"/><br />
</databases><br />
</sqlCacheDependency><br />
</caching><br />
<compilation debug="true" strict="false" explicit="true"/><br />
<pages><br />
<namespaces><br />
<clear/><br />
<add namespace="System"/><br />
<add namespace="System.Collections"/><br />
<add namespace="System.Collections.Specialized"/><br />
<add namespace="System.Configuration"/><br />
<add namespace="System.Text"/><br />
<add namespace="System.Text.RegularExpressions"/><br />
<add namespace="System.Web"/><br />
<add namespace="System.Web.Caching"/><br />
<add namespace="System.Web.SessionState"/><br />
<add namespace="System.Web.Security"/><br />
<add namespace="System.Web.Profile"/><br />
<add namespace="System.Web.UI"/><br />
<add namespace="System.Web.UI.WebControls"/><br />
<add namespace="System.Web.UI.WebControls.WebParts"/><br />
<add namespace="System.Web.UI.HtmlControls"/><br />
</namespaces><br />
</pages><br />
<authentication mode="Windows"/><br />
</system.web><br />
</configuration></source><br />
<br />
<br />
<br />
== Using the Cache object with the SqlDependency object (VB)==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="csharp"><br />
<%@ Page Language="VB" %><br />
<%@ Import Namespace="System.Data"%><br />
<%@ Import Namespace="System.Data.SqlClient"%><br />
<script runat="server"><br />
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)<br />
Dim myCustomers As DataSet<br />
myCustomers = CType(Cache("firmCustomers"), DataSet)<br />
If myCustomers Is Nothing Then<br />
Dim conn As SqlConnection = New SqlConnection( ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString)<br />
Dim da As SqlDataAdapter = New SqlDataAdapter("Select * From Customers", conn)<br />
myCustomers = New DataSet<br />
da.Fill(myCustomers)<br />
Dim myDependency As SqlCacheDependency = New SqlCacheDependency("Northwind", "Customers")<br />
Cache.Insert("firmCustomers", myCustomers, myDependency)<br />
Label1.Text = "Produced from database."<br />
Else<br />
Label1.Text = "Produced from Cache object." <br />
End If<br />
GridView1.DataSource = myCustomers<br />
GridView1.DataBind()<br />
End Sub<br />
</script><br />
<html xmlns="http://www.w3.org/1999/xhtml" ><br />
<head id="Head1" runat="server"><br />
<title>Sql Cache Invalidation</title><br />
</head><br />
<body><br />
<form id="form1" runat="server"><br />
<asp:Label ID="Label1" Runat="server"></asp:Label><br /><br />
<br /><br />
<asp:GridView ID="GridView1" Runat="server"></asp:GridView><br />
</form><br />
</body><br />
</html><br />
File: Web.config<br />
<?xml version="1.0"?><br />
<configuration><br />
<appSettings/><br />
<connectionStrings><br />
<add name="AppConnectionString1" connectionString="Data Source=.\SQLEXPRESS;User ID=wrox;Password=wrox1*c;Database=Northwind;Persist Security Info=False" providerName="System.Data.SqlClient"/><br />
</connectionStrings><br />
<system.web><br />
<caching><br />
<sqlCacheDependency enabled="true"><br />
<databases><br />
<add name="Northwind" connectionStringName="AppConnectionString1" pollTime="500"/><br />
</databases><br />
</sqlCacheDependency><br />
</caching><br />
<compilation debug="true" strict="false" explicit="true"/><br />
<pages><br />
<namespaces><br />
<clear/><br />
<add namespace="System"/><br />
<add namespace="System.Collections"/><br />
<add namespace="System.Collections.Specialized"/><br />
<add namespace="System.Configuration"/><br />
<add namespace="System.Text"/><br />
<add namespace="System.Text.RegularExpressions"/><br />
<add namespace="System.Web"/><br />
<add namespace="System.Web.Caching"/><br />
<add namespace="System.Web.SessionState"/><br />
<add namespace="System.Web.Security"/><br />
<add namespace="System.Web.Profile"/><br />
<add namespace="System.Web.UI"/><br />
<add namespace="System.Web.UI.WebControls"/><br />
<add namespace="System.Web.UI.WebControls.WebParts"/><br />
<add namespace="System.Web.UI.HtmlControls"/><br />
</namespaces><br />
</pages><br />
<authentication mode="Windows"/><br />
</system.web><br />
</configuration></source></div>
Admin