검색결과 리스트
MSDN에 해당되는 글 3건
- 2007.04.30 [Study] MSDN - Improved Caching in ASP.NET 2.0
- 2007.04.25 [Study] Wicked Code : Supporting Database Cache Dependencies in ASP.NET
- 2007.04.02 [MSDN] Generic Sample
글
STUDY/SQL Cache Dependency 2007. 4. 30. 11:42[Study] MSDN - Improved Caching in ASP.NET 2.0
Stephen Walther
Microsoft Corporation
June 2004
Applies to:
Microsoft ASP.NET 2.0
Microsoft ASP.NET framework
Microsoft SQL Server
Microsoft Visual Studio .NET
Summary: Stephen Walther looks at the new caching features included in ASP.NET 2.0, and how you can use them to improve the performance and scalability of your ASP.NET applications. (20 printed pages)
Contents
Data Caching Made Easy
Using SQL Cache Invalidation
Using Post-Cache Substitution
Conclusion
The most dramatic way to improve the performance of a database driven Web application is through caching. Retrieving data from a database is one of the slowest operations that you can perform in a Web site. If, however, you can cache the database data in memory, then you can avoid accessing the database with every page request, and dramatically increase the performance of your application.
The one and only drawback to caching is the problem of stale data. If you cache the contents of a database table in memory, and the records in the underlying database table change, then your Web application will display old, inaccurate data. For certain types of data you might not care if the data being displayed is slightly out of date, but for other types of data—such as stock prices and auction bids—displaying data that is even slightly stale is unacceptable.
The initial release of the Microsoft ASP.NET framework did not provide a good solution to this problem. When using the ASP.NET 1.0 framework, you just had to live with this tradeoff between performance and stale data. Fortunately, the Microsoft ASP.NET 2.0 framework includes a new feature called SQL Cache Invalidation that solves this very problem.
In this article, you'll learn about many of the new caching enhancements in the ASP.NET 2.0 framework. First, you'll learn how caching support has been integrated into the new DataSource controls. Next, you'll learn how to configure and take advantage of SQL Cache Invalidation. Finally, we'll take a look at a new control introduced with the ASP.NET 2.0 framework, which enables you to inject dynamic content into a cached page: the Substitution control.
Data Caching Made Easy
One of the biggest changes in the ASP.NET 2.0 framework concerns how you access database data in an ASP.NET page. The ASP.NET 2.0 framework includes a new set of controls, known collectively as the DataSource controls, which enable you to represent a data source such as a database or an XML file.
In the ASP.NET 1.0 framework, you displayed database data with a control by binding the control to either a DataSet or a DataReader. In the ASP.NET 2.0 framework, you'll typically bind a control to a DataSource control instead. By taking advantage of the DataSource controls, you can build ASP.NET pages that display database data without writing any code for accessing the database.
When working with database data, you'll typically use one of the following three DataSource controls:
- SqlDataSource—Represents any SQL data source, such as a Microsoft SQL Server or an Oracle database.
- AccessDataSource—A specialized SqlDataSource control designed for working with a Microsoft Access database.
- ObjectDataSource—Represents a custom business object that acts as a data source.
For example, imagine that you need to display a list of book titles retrieved from a database in a DropDownList control (see Figure 1). The page in Listing 1 illustrates how you can bind a DropDownList control to a SqlDataSource control.
.gif)
Figure 1. Using the SqlDataSource control to retrieve data
Listing 1. DisplayTitles.aspx
Copy Code<html>
<head runat="server">
<title>Display Titles</title>
</head>
<body>
<form id="form1" runat="server">
<asp:DropDownList
ID="DropDownList1"
DataSourceId="SqlDataSource1"
DataTextField="Title"
Runat="server" />
<asp:SqlDataSource
ID="SqlDataSource1"
ConnectionString="Server=localhost;database=Pubs"
SelectCommand="SELECT Title FROM Titles"
Runat="server" />
</form>
</body>
</html>
Notice that the SqlDataSource control in Listing 1 is used to provide the connection string, and the SQL SELECT command used for retrieving the records from the database. The DropDownList control is bound to the SqlDataSource control through its DataSourceID property.
Caching with the DataSource Controls
The DataSource controls not only enable you to connect more easily to a database, they also make it easier for you to cache database data. Simply by setting a couple of properties on the SqlDataSource control, you can automatically cache the data represented by a DataSource control in memory.
For example, if you want to cache the Titles database table in memory for at least 10 minutes, you can declare a SqlDataSource control like this.
<asp:SqlDataSource ID="SqlDataSource1" EnableCaching="true" CacheDuration="600" ConnectionString="Server=localhost;database=Pubs" SelectCommand="SELECT Title FROM Titles" Runat="server" />
When the EnableCaching property has the value true, the SqlDataSource will automatically cache the data retrieved by the SelectCommand. The CacheDuration property enables you to specify, in seconds, how long the data should be cached before it is refreshed from the database.
By default, the SqlDataSource will cache data using an absolute expiration policy—every so many seconds, the data is refreshed from the database. You also have the option of enabling a sliding expiration policy. When the SqlDataSource is configured to use a sliding expiration policy, the data will not be dropped as long as it continues to be accessed. Employing a sliding expiration policy is useful whenever you have a large number of items that need to be cached, since this expiration policy enables you to keep only the most frequently accessed items in memory.
For example, the following SqlDataSourceControl is configured to use a sliding expiration policy of 10 minutes.
<asp:SqlDataSource
ID="SqlDataSource1"
EnableCaching="true"
CacheExpirationPolicy="Sliding"
CacheDuration="600"
ConnectionString="Server=localhost;database=Pubs"
SelectCommand="SELECT Title FROM Titles"
Runat="server" />
Since the CacheExpirationPolicy property is set to the value Sliding and the CacheDuration property is set to the value 600, the data represented by this SqlDataSource will remain in memory just as long as it continues to be accessed within a 10 minute window.
Using SQL Cache Invalidation
SQL Cache Invalidation is one of the most anticipated new features of the ASP.NET 2.0 framework. By taking advantage of SQL Cache Invalidation, you get all of the performance benefits of caching without the problem of stale data. SQL Cache Invalidation enables you to automatically update data in the cache whenever the data changes in the underlying database.
Behind the scenes, SQL Cache Invalidation works by constantly polling the database to check for changes. Every so many milliseconds, the ASP.NET framework checks whether or not there have been any updates to the database. If the ASP.NET framework detects any changes, then any items added to the cache that depend on the database are removed from the cache (they are invalidated).
Note Microsoft SQL Server 2005 supports a completely different method of SQL Cache Invalidation. You can configure SQL Server 2005 to notify your ASP.NET application whenever changes have been made to a database, a database table, or a database row. This means that the ASP.NET Framework does not need to constantly poll a SQL Server 2005 database for changes.
It is important to understand that SQL Cache Invalidation only works with Microsoft SQL Server version 7 and higher. You cannot use this feature with other databases such as Microsoft Access or Oracle.
You can use SQL Cache Invalidation when caching the output of an entire page, when working with the DataSource controls, or when working directly with the Cache object. We'll examine all three scenarios.
Configuring SQL Cache Invalidation
Before you can take advantage of SQL Cache Invalidation in your Web application, you must first perform some configuration steps. You must configure Microsoft SQL Server to support SQL Cache Invalidation and you must add the necessary configuration information to your application's Web configuration file.
There are two ways that you can configure SQL Server. You can either use the aspnet_regsql command line tool, or you can take advantage of the SqlCacheDependencyAdmin class.
Enabling SQL Cache Invalidation with ASPNET_REQSQL
The aspnet_regsql tool enables you to configure SQL Cache Invalidation from the command line. The aspnet_regsql tool is located in your Windows\Microsoft.NET\Framework\[version] folder. You must use this tool by opening a command prompt and navigating to this folder.
In order to support SQL Cache Invalidation when using the Pubs database, you need to execute the following command.
aspnet_regsql -E -d Pubs -ed
The -E option causes the aspnet_regsql tool to use integrated security when connecting to your database server. The -d option selects the Pubs database. Finally, the -ed option enables the database for SQL Cache Invalidation.
When you execute this command, a new database table named AspNet_SqlCacheTablesForChangeNotification is added to the database. This table contains a list of all of the database tables that are enabled for SQL Cache Invalidation. The command also adds a set of stored procedures to the database.
After you enable a database for SQL Cache Invalidation, you must select the particular tables in the database that you will enable for SQL Cache Invalidation. The following command enables the Titles database table.
aspnet_regsql -E -d Pubs -t Titles -et
The -t option selects a database table. The -et option enables a database table for SQL Cache Invalidation. You can, of course, enable multiple tables by re-executing this command for each database table.
When you execute this command, a trigger is added to the database table. The trigger fires whenever you make a modification to the table and it updates the AspNet_SqlCacheTablesForChangeNotification table.
Finally, if you want to get the list of tables that are currently enabled for SQL Cache Invalidation in a particular database, you can use the following command.
aspnet_regsql -E -d Pubs -lt
This method selects the list of tables from the AspNet_SqlCacheTablesForChangeNotification. Alternatively, you could retrieve this information by performing a query directly against this database table.
Using the SqlCacheDependencyAdmin Class
Behind the scenes, the aspnet_regsql tool uses the methods of the SqlCacheDependencyAdmin class to configure Microsoft SQL Server. If you prefer, you can use the methods of this class directly from within an ASP.NET page.
The SqlCacheDependencyAdmin class has five important methods:
- DisableNotifications—Disables SQL Cache Invalidation for a particular database.
- DisableTableForNotifications—Disables SQL Cache Invalidation for a particular table in a database.
- EnableNotifications—Enables SQL Cache Invalidation for a particular database.
- EnableTableForNotifications—Enables SQL Cache Invalidation for a particular table in a database.
- GetTablesEnabledForNotifications—Returns a list of all tables enabled for SQL Cache Invalidation.
For example, the ASP.NET page in Listing 2 enables you to configure SQL Cache Invalidation for any table in the Pubs database (see Figure 2).
.gif)
Figure 2. Enabling SQL Cache Invalidation from an ASP.NET page
Listing 2. EnableSCI.aspx (C#)
<%@ Page Language="c#" %>
<%@ Import Namespace="System.Web.Caching" %>
<script runat="server">
const string connectionString = "Server=localhost;Database=Pubs";
void Page_Load()
{
if (!IsPostBack)
{
SqlCacheDependencyAdmin.EnableNotifications(
connectionString);
SqlDataSource1.SelectParameters.Add("connectionString",
connectionString);
}
}
void EnableTable(Object s, EventArgs e)
{
try
{
SqlCacheDependencyAdmin.EnableTableForNotifications(
connectionString, txtTableName.Text);
}
catch (Exception ex)
{
lblErrorMessage.Text = ex.Message;
}
txtTableName.Text = "";
}
</script>
<html>
<head runat="server">
<title>Enable SQL Cache Invalidation</title>
</head>
<body>
<form id="form1" runat="server">
<h1>SQL Cache Invalidation</h1>
The following tables are enabled for SQL Cache Invalidation:
<p>
<asp:GridView id="grdTables"
DataSourceID="SqlDataSource1" CellPadding="10"
ShowHeader="false" Runat="Server" />
</p>
<asp:ObjectDataSource
ID="SqlDataSource1"
TypeName="System.Web.Caching.SqlCacheDependencyAdmin"
SelectMethod="GetTablesEnabledForNotifications"
Runat="Server" />
<p>
<asp:Label ID="lblErrorMessage" EnableViewState="false"
ForeColor="red" Runat="Server" />
</p>
<asp:TextBox ID="txtTableName" Runat="Server" />
<asp:Button Text="Enable Table" OnClick="EnableTable"
Runat="Server" />
</form>
</body>
</html>
Listing 2. EnableSCI.aspx (Visual Basic .NET)
<%@ Page Language="vb" %>
<%@ Import Namespace="System.Web.Caching" %>
<script runat="server">
Const connectionString As String = "Server=localhost;Database=Pubs"
Sub Page_Load()
If Not IsPostBack Then
SqlCacheDependencyAdmin.EnableNotifications( _
connectionString)
SqlDataSource1.SelectParameters.Add("connectionString", _
connectionString)
End If
End Sub
Sub EnableTable(ByVal s As Object, ByVal e As EventArgs)
Try
SqlCacheDependencyAdmin.EnableTableForNotifications( _
connectionString, txtTableName.Text)
Catch ex As Exception
lblErrorMessage.Text = ex.Message
End Try
txtTableName.Text = ""
End Sub
</script>
<html>
<head id="Head1" runat="server">
<title>ConfigureSCI</title>
</head>
<body>
<form id="form1" runat="server">
<h1>SQL Cache Invalidation</h1>
The following tables are enabled for SQL Cache Invalidation:
<p>
<asp:GridView id="grdTables" DataSourceID="SqlDataSource1"
CellPadding="10" ShowHeader="false" Runat="Server" />
</p>
<asp:ObjectDataSource
ID="SqlDataSource1"
TypeName="System.Web.Caching.SqlCacheDependencyAdmin"
SelectMethod="GetTablesEnabledForNotifications"
Runat="Server" />
<p>
<asp:Label ID="lblErrorMessage" EnableViewState="false"
ForeColor="red" Runat="Server" />
</p>
<asp:TextBox ID="txtTableName" Runat="Server" />
<asp:Button ID="Button1" Text="Enable Table"
OnClick="EnableTable" Runat="Server" />
</form>
</body>
</html>
In Listing 2, the connectionString constant is used to select the database for which SQL Cache Invalidation is enabled (You can change the value of this constant when you want to enable SQL Cache Invalidation for a database other than the Pubs database). Within the Page_Load method, the EnableNotifications method on the SqlCacheDependencyAdmin class is called to enable SQL Cache Invalidation for the database specified by the connectionString constant.
The GridView in Listing 2 displays all of the database tables that are currently enabled for SQL Cache Invalidation. The GridView is bound to an ObjectDataSource control that calls the GetTablesneabledForNotifications method for its SelectMethod.
Finally, you can use the page in Listing 2 to enable additional tables for SQL Cache Invalidation. When you enter the name of a table in the textbox and click the Enable Table button, the EnableTableForNotifications method is called.
Web Configuration Settings for SQL Cache Invalidation
The next step, before you can use SQL Cache Invalidation in your ASP.NET application, is to update your Web configuration file. You need to configure the ASP.NET framework to poll the databases that you have enabled for SQL Cache Invalidation.
The Web configuration file in Listing 3 contains the necessary configuration information to poll the Pubs database.
Listing 3. Web.Config
<configuration>
<connectionStrings>
<add name="mySqlServer"
connectionString="Server=localhost;Database=Pubs" />
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency enabled="true">
<databases>
<add
name="Pubs"
connectionStringName="mySqlServer"
pollTime="60000" />
</databases>
</sqlCacheDependency>
</caching>
</system.web>
</configuration>
The Web configuration file in Listing 3 contains two sections. The <connectionStrings> section is used to create a database connection string to the Pubs database named mySqlServer.
The caching section is used to configure the SQL Cache Invalidation polling. Within the <databases> subsection, you can list one or more databases that you want to poll for changes. In Listing 3, the database represented by the mySqlServer is polled once a minute (every 60000 milliseconds).
You can specify different polling intervals for different databases. The server must do a little bit of work every time the database is polled for changes. If you don't expect the data in the database to change very often, then you can increase the polling interval.
Using SQL Cache Invalidation with Page Output Caching
Now that we've gotten all of the configuration steps for SQL Cache Invalidation out of the way, we can start taking advantage of it in our ASP.NET pages. One way that you can use SQL Cache Invalidation is with page output caching. Page output caching enables you to cache the entire rendered contents of a page in memory. By taking advantage of SQL Cache Invalidation, you can automatically update the cached page when, and only when, a change is made to a database table.
For example, the page in Listing 4 displays the contents of the Titles database table in a GridView control. At the top of the page, the OutputCache directive is used to cache the contents of the page in memory. The SqlDependency attribute causes the page to be updated whenever the Titles database table changes.
Listing 4. OutputCacheTitles.aspx
<%@ OutputCache SqlDependency="Pubs:Titles"
Duration="6000" VaryByParam="none" %>
<html>
<head runat="server">
<title>Output Cache Titles</title>
</head>
<body>
<form id="form1" runat="server">
<%= DateTime.Now %>
<asp:GridView
ID="grdTitles"
DataSourceID="SqlDataSource1"
Runat="Server" />
<asp:SqlDataSource
ID="SqlDataSource1"
SelectCommand="Select * FROM Titles"
ConnectionString="<%$ ConnectionStrings:mySqlServer %>"
Runat="Server" />
</form>
</body>
</html>
Notice that the SqlDependency attribute references the name of the database defined within the Web configuration file. Since we specified that the Pubs database should be polled once every minute for changes, if a change is made to the database the page in Listing 4 will be updated within a minute.
You can list more than one database and/or more than one database table for the value of the SqlDependency attribute. To create more than one dependency, simply separate each dependency with a semicolon.
Using SQL Cache Invalidation with the DataSource Control
As an alternative to using SQL Cache Invalidation with page output caching, you can use SQL Cache Invalidation directly with the DataSource controls. You should consider using SQL Cache Invalidation with the DataSource controls when you need to work with the same database data in multiple pages. The SqlDataSource, AccessDataSource, and ObjectDataSource controls all support a SqlCacheDependency property.
For example, the page in Listing 5 uses the SQL Cache Invalidation with the SqlDataSource control.
Listing 5. SqlDataSourceCaching.aspx
<html>
<head id="Head1" runat="server">
<title>SqlDataSource Caching</title>
</head>
<body>
<form id="form1" runat="server">
<%= DateTime.Now %>
<asp:GridView
ID="grdTitles"
DataSourceId="SqlDataSource1"
Runat="server" />
<asp:SqlDataSource
ID="SqlDataSource1"
EnableCaching="true"
SqlCacheDependency="Pubs:Titles"
SelectCommand="select * from titles"
ConnectionString="<%$ ConnectionStrings:mySqlServer %>"
Runat="server" />
</form>
</body>
</html>
In Listing 5, the SqlDataSource control is declared with both an EnableCaching attribute and a SqlCacheDependency attribute. The SqlCacheDependency property uses the same syntax as the OutputCache directive's SqlDependency attribute. You list the name of the database, followed by the name of the database table.
Using SQL Cache Invalidation with the Cache Object
A final option is to use SQL Cache Invalidation with the Cache object. This option provides you with the greatest degree of programmatic control over SQL Cache Invalidation.
To use SQL Cache Invalidation with the Cache object, you need to create an instance of the SqlCacheDependency object. You can use the SqlCacheDependency object when inserting a new object into the Cache with the Insert method.
For example, the page in Listing 6 displays the number of records in the Titles database table. The count is cached with a dependency on the underlying database table.
Listing 6. DisplayTitleCount.aspx (C#)
<%@ Page Language="c#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
void Page_Load()
{
int count = 0;
if (Cache["TitleCount"] != null)
{
count = (int)Cache["TitleCount"];
}
else
{
string connectionString =
ConfigurationSettings.ConnectionStrings[
"mySqlServer"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new
SqlCommand("SELECT Count(*) FROM Titles", con);
con.Open();
count = (int)cmd.ExecuteScalar();
con.Close();
Cache.Insert("TitleCount", count,
new SqlCacheDependency("Pubs", "Titles"));
}
lblTitleCount.Text = count.ToString();
}
</script>
<html>
<head runat="server">
<title>Display Title Count</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label ID="lblTitleCount" Runat="Server" />
</form>
</body>
</html>
Listing 6. DisplayTitleCount.aspx (Visual Basic .NET)
<%@ Page Language="vb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
Sub Page_Load()
Dim count As Integer = 0
If Not Cache("TitleCount") Is Nothing Then
count = Convert.ToInt32(Cache("TitleCount"))
Else
Dim connectionString As String = _
ConfigurationSettings.ConnectionStrings( _
"mySqlServer").ConnectionString
Dim con As New SqlConnection(connectionString)
Dim cmd As New _
SqlCommand("SELECT Count(*) FROM Titles", con)
con.Open()
count = Convert.ToInt32(cmd.ExecuteScalar())
con.Close()
Cache.Insert("TitleCount", count, _
new SqlCacheDependency("Pubs", "Titles"))
End If
lblTitleCount.Text = count.ToString()
End Sub
</script>
<html>
<head id="Head1" runat="server">
<title>Display Titles Count</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label ID="lblTitleCount" Runat="Server" />
</form>
</body>
</html>
Using Post-Cache Substitution
There are many situations in which you need to cache part of a page, but not the entire page. For example, on the home page of your Web site, you might want to display both a random banner advertisement and the records from a database table. If you cache the entire page, then every user will see the same banner advertisement on each and every page request.
One way to handle this problem of mixing dynamic and cached content is to use Web User Controls. Since you can add an OutputCache directive to a Web User Control, you can cache the contents of a Web User Control even when you do not cache the contents of the containing page.
Sometimes, however, this gets things backwards. You can use Web User Controls to add cached content to a dynamic page. In many situations, however, what you really want to do is add dynamic content to a cached page. For example, imagine that you want to cache the entire contents of a page, except for one little area where you want to display the current user's username. This is a perfect situation in which you'll want to take advantage of post-cache substitution.
The ASP.NET 2.0 framework introduces a new control called the Substitution control. You can use the Substitution control to inject dynamic content into a cached page. The page in Listing 7 uses the Substitution control to inject a username into cached content (see Figure 3).
.gif)
Figure 3. Displaying a username with the Substitution control
Listing 7. PostCacheSubstitution.aspx (C#)
<%@ Page Language="C#" %>
<%@ OutputCache Duration="6000" VaryByParam="none" %>
<script runat="server">
static string DisplayUsername(HttpContext context)
{
if (!context.Request.IsAuthenticated)
return "Anonymous";
else
return context.User.Identity.Name;
}
</script>
<html>
<head runat="server">
<title>Post Cache Substitution</title>
</head>
<body>
<form id="form1" runat="server">
Welcome <asp:Substitution
MethodName="DisplayUsername" Runat="Server" />!
<p>
This page is cached as you'll notice from the fact that the time
<%= DateTime.Now.ToString("t") %> never changes.
</p>
</form>
</body>
</html>
Listing 7. PostCacheSubstitution.aspx (Visual Basic .NET)
<%@ Page Language="vb" %>
<%@ OutputCache Duration="6000" VaryByParam="none" %>
<script runat="server">
Shared Function DisplayUsername(ByVal context As HttpContext) _
As String
If Not context.Request.IsAuthenticated Then
Return "Anonymous"
Else
Return context.User.Identity.Name
End If
End Function
</script>
<html>
<head id="Head1" runat="server">
<title>Post Cache Substitution</title>
</head>
<body>
<form id="form1" runat="server">
Welcome <asp:Substitution
ID="Substitution1"
MethodName="DisplayUsername"
Runat="Server" />!
<p>
This page is cached as you'll notice from the fact that the time
<%= DateTime.Now.ToString("t") %> never changes.
</p>
</form>
</body>
</html>
The Substitution control has one important property: the MethodName property. The MethodName property is used to represent the method that is called to return the dynamic content. The method called by the Substitution control must be a static method (shared in the Visual Basic .NET world). Furthermore, the method must have one parameter that represents the current HttpContext.
In the ASP.NET 2.0 framework, the AdRotator control has been modified to support post-cache substitution. If you add the AdRotator control to a page that uses the OutputCache directive, the AdRotator control is automatically exempt from its containing page's caching policy.
Conclusion
Caching has a dramatic impact on the performance of database-driven Web applications. Fortunately, the ASP.NET 2.0 framework includes a number of significant new enhancements that make it easier to take advantage of caching in your applications.
The new DataSource controls include properties that make it easy to cache database data in memory. By taking advantage of the DataSource controls, you can retrieve database data and cache the data without writing a single line of code.
The new support for SQL Cache Invalidation enables you to automatically reload database data in the cache whenever the data is modified in the underlying database. This feature provides you with all the performance benefits of caching, without the worries of stale data.
Finally, the new Substitution control enables you to more easily mix dynamic content in a cached page. The Substitution control gives you an island of dynamic content in an otherwise cached page.
Related Books
About the author
Stephen Walther wrote the best-selling book on ASP.NET, ASP.NET Unleashed. He was also the architect and lead developer of the ASP.NET Community Starter Kit, a sample ASP.NET application produced by Microsoft. He has provided ASP.NET training to companies across the United States, including NASA and Microsoft, through his company Superexpert (http://www.superexpert.com).
트랙백
댓글
글
STUDY/SQL Cache Dependency 2007. 4. 25. 16:44[Study] Wicked Code : Supporting Database Cache Dependencies in ASP.NET
[출처 : http://msdn.microsoft.com/msdnmag/issues/03/04/wickedcode/]
Developers love the ASP.NET application cache. One reason they love it is that ASP.NET lets them create dependencies between items placed in the cache and files in the file system. If a file targeted by a dependency changes, ASP.NET automatically removes dependent items from the cache. Combined with cache removal callbacks—notifications broadcast to interested parties when cached items are removed—cache dependencies are a boon to developers seeking to maximize performance by minimizing time-consuming file accesses because they permit file data to be cached without fear of it becoming stale.
As awesome as cache dependencies are, in ASP.NET version 1.0 they lack one critical feature that, if present, would qualify them as a developer's dream come true: support for database entities. In real life, most Web apps fetch data from databases, not files. But while ASP.NET is perfectly willing to link cached items to files, it is incapable of linking cached items to database entities. In other words, you can read the contents of a file into a DataSet, cache the DataSet, and have the DataSet automatically removed from the cache if the file it was initialized from changes. But you can't initialize a DataSet with a database query, cache the DataSet, and have the DataSet automatically discarded if the database changes. That's too bad because too many database accesses, like too much file I/O, is a performance killer.
The fact that ASP.NET doesn't support database dependencies doesn't mean database dependencies are impossible to achieve. This installment of Wicked Code presents a technique for extending the ASP.NET application cache to support database dependencies. It involves database triggers and extended stored procedures. Though the implementation presented here works exclusively with Microsoft® SQL Server™, the general technique is applicable to any database that supports triggers and user-defined procedures that interact with the file system.
Database Dependencies in Action
Let's begin with a demonstration. Figure 1 contains the source code for an ASP.NET page that displays randomly selected quotations from a SQL Server database named Quotes. To create the database, run the installation script shown in abbreviated form in Figure 2. The complete script is included in the downloadable zip file that accompanies this column. You can execute the script inside the SQL Server Query Analyzer or from the command line with an OSQL command.) Each time the page is fetched, Page_Load initializes a DataSet with all the records in the database's Quotations table, randomly selects a record from DataSet, and writes it to the page. Press F5 a few times and you'll see a random variety of quotations from some famous (and not-so-famous) people, as shown in Figure 3.
Figure 3 Random Quote
The page is called DumbDBQuotes.aspx for a reason. It's not very smart considering it queries the database each time it's requested. Accessing a database on every page access—especially a database hosted on a remote server—is a guaranteed way to build an application that won't scale.
The ASP.NET application cache is the solution to the problem of too many database accesses. If the DataSet were cached, it could be fetched directly from memory—that is, from the cache—thereby eliminating the redundant database accesses. It's easy enough to cache a DataSet; the application cache accepts instances of any type that derives from System.Object. In the Microsoft .NET Framework, that means instances of any managed type, including DataSet. The problem is that if you cache a DataSet and the database changes underneath it, you serve stale data to your users. You could implement a solution that requeries the database periodically, but the ideal solution is the one that requires no polling and that delivers fresh data from the data source the moment that data becomes available.
Take a look at Figure 4 and Figure 5, which contain the source code for a smarter quotes application. SmartDBQuotes.aspx doesn't retrieve quotations from the database; it gets them from the application cache. Global.asax primes the cache and refreshes it if the database changes. Here are directions for taking them for a test drive:
- Create a subdirectory named AspNetSql in the root directory of your Web server's C: drive. Inside AspNetSql, create a zero-byte file named Quotes.Quotations. Make sure Everyone, or at least SYSTEM and ASPNET (a special account created when ASP.NET is installed), has access to Quotes.Quotations.
- Copy XSP.dll, which is included in this column's downloadable code sample, into the SQL Server binn directory (for example, C:\Program Files\Microsoft SQL Server\MSSQL\Binn) or to any location on your Web server that Windows will automatically search for DLLs (for example, C:\Windows\System32).
- Rebuild the database using the modified script in Figure 6.
- Deploy Global.asax and SmartDBQuotes.aspx to a virtual directory on your Web server (for example, wwwroot).
- Request SmartDBQuotes.aspx in your browser. Refresh the page a few times until the quote "The use of COBOL cripples the mind; its teaching should therefore be regarded as a criminal offense" appears.
- Use SQL Server Enterprise Manager or the tool of your choice to modify the quotation in the Quotes database's Quotations table. Change it to read "The use of Visual Basic® enriches the mind; its teaching should therefore not be regarded as a criminal offense." Then refresh the page until the modified quotation appears. Observe that the new quotation appears, not the old one, even though the query results are now being stored in the application cache.
You just demonstrated that the ASP.NET application cache can be combined with database dependencies to produce high-volume, data-driven applications. The question is how? How was a link formed between the cached DataSet and the database, and how scalable is the solution?
How Database Dependencies Work
On the outside, both DumbDBQuotes.aspx and SmartDBQuotes.aspx look the same, producing identical output. On the inside, they could hardly be more different. The former performs a database access every time it's requested; the latter fetches data from the application cache. Moreover, SmartDBQuotes.aspx uses a database dependency to ensure that if the database changes, the cached data changes, too. If the database doesn't change, the database is queried just once during the lifetime of the application. If the database changes, one more query updates the cache.
Figure 7 Database Dependencies
Figure 7 illustrates how the database dependency works. When it places the DataSet in the cache, Global.asax creates a file-system dependency between the DataSet and a file named Quotes.Quotations in the C:\AspNetSql directory. Quotes.Quotations is a zero-byte signal file—a file whose only purpose is to trigger the ASP.NET application cache-removal logic. Here's the statement in Global.asax that creates the CacheDependency object linking the DataSet to Quotes.Quotations:
new CacheDependency ("C:\\AspNetSql\\Quotes.Quotations")
Global.asax also registers its own RefreshCache method to be called when the DataSet is removed from the cache—that is, when the signal file changes: new CacheItemRemovedCallback (RefreshCache)
RefreshCache's job is to query the database and place the resulting DataSet in the application cache. It's called once when the application starts up and again when—or if—the DataSet is removed from the cache.
That's half of the equation. The other half involves the database. The revised database installation script in Figure 6 adds an insert/update/delete trigger to the database's Quotations table:
CREATE TRIGGER DataChanged ON Quotations FOR INSERT, UPDATE, DELETE AS EXEC master..xsp_UpdateSignalFile 'Quotes.Quotations' GOThe trigger fires when records are added to or deleted from the table and when records change. What does the trigger do? It calls an extended stored procedure—the SQL Server euphemism for code in a Win32® DLL—named xsp_UpdateSignalFile. The extended stored procedure, in turn, uses the Win32 CreateFile function to update Quotes.Quotations' time stamp.
The cached DataSet's lifetime is tied to Quotes.Quotations using an ordinary file-system cache dependency; updating the Quotations table causes a database trigger to fire; and the trigger calls an extended stored procedure that "updates" Quotes.Quotations, prompting ASP.NET to remove the DataSet from the application cache and call Global.asax's RefreshCache method, which then performs a brand new database query and starts the whole process all over again.
The final piece of the puzzle is the extended stored procedure. It's housed in XSP.dll, the DLL that you installed earlier. I wrote XSP.dll in unmanaged C++ using Visual C++® 6.0. Its source code appears in Figure 8. The path to the signal file—C:\AspNetSql—is hardcoded into the DLL, but you can change that if you'd like and make it an input parameter just like the file name.
Extended stored procedures must be installed before they're used. The following statements in the SQL installation script that you executed install xsp_UpdateSignalFile in the master database and grant execute permission to all comers:
USE master EXEC sp_addextendedproc 'xsp_UpdateSignalFile', 'XSP.dll' GRANT EXECUTE ON xsp_UpdateSignalFile TO PUBLIC GOWhy write a custom extended stored procedure to update a file's time stamp when a built-in extended stored procedure such as xp_cmdshell could be used instead? The reason is security—xp_cmdshell can be used for all sorts of malicious purposes, while xsp_UpdateSignalFile cannot. Because xsp_UpdateSignalFile does little more than call the Windows CreateFile function, it is also more efficient than xp_cmdshell.
Server Farms
SmartDBQuotes.aspx and friends work great if the Web server and the database server live on the same machine, but what if the database is installed on a different machine? And what about Web farms? Would a change notification mechanism based on database triggers, extended stored procedures, and file-system dependencies be compatible with multiserver installations?
You bet. Under the hood, ASP.NET cache dependencies that are based on the file system rely on Win32 file change notifications. And Win32 file change notifications support Universal Naming Convention (UNC) path names. To take advantage of database cache dependencies on Web farms, let the signal file reside on the database server, as shown in Figure 9. Then pass CacheDependency's constructor a UNC path name specifying the signal file's network address:
new CacheDependency (@"\\ServerName\AspNetSql\Quotes.Quotations"),
Figure 9 Signal File on Database Server
The greatest obstacle to creating dependencies that target remote files is security. By default, the ASP.NET worker process runs as ASPNET when paired with Microsoft Internet Information Services (IIS) 5.0 and when configured to run in compatibility mode under IIS 6.0. ASPNET is a local account that can't authenticate on remote machines. Without configuration changes, attempting to create a cache dependency with a UNC path name produces an access denied error—even if you give Everyone access to the remote share.
Several solutions exist. One is to configure ASP.NET to use a domain account that can be authenticated on the database server. That change is easy enough to accomplish: you simply specify the account's user name and password in the <processModel> section of each Web server's Machine.config. Many companies, however, have security policies that prevent passwords from being stored in plaintext configuration files. If that's true of your company, but you'd still like to run ASP.NET using a domain account, you can either upgrade to version 1.1 of the .NET Framework (which allows worker process credentials to be encrypted and stored securely in the registry) or download a hotfix for version 1.0 that does the same. You'll find information about the hotfix at Stronger Credentials for processModel, identity, and sessionState.
A variation on this technique involves setting up identical local accounts (using the same user name and password) on both machines and configuring ASP.NET to run as that identical local account on the Web server.
Another solution to the problem of authenticating on a back-end database server containing a signal file is to upgrade to Windows Server 2003. The latest addition to the Windows Server family comes with IIS 6.0, which allows the ASP.NET worker process to run using the identity of Network Service. Unlike ASPNET, Network Service can perform authentication on remote machines. Or you could pull an old trick out of the ASP playbook and access the database through a COM+ component running on the Web server and configure the component to run as a principal that has network credentials.
However you choose to make the remote signal file accessible to your ASP.NET app, the bottom line is that combining database cache dependencies with UNC path names delivers a scalable solution that works equally well for Web farms and single-server installations. That's good news for developers who are using ASP.NET to build high-volume, data-driven applications—and good news for users as well.
트랙백
댓글
글
WEB/ASP.NET With C# 2007. 4. 2. 14:40[MSDN] Generic Sample
http://download.microsoft.com/download/9/A/6/9A639BEE-F7B0-423D-BFFD-4356299205D8/Generics.zip.exe
// Copyright (C) Microsoft Corporation. All rights reserved.
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
namespace Generics_CSharp
{
//Type parameter T in angle brackets.
public class MyList<T> : IEnumerable<T>
{
protected Node head;
protected Node current = null;
// Nested type is also generic on T
protected class Node
{
public Node next;
//T as private member datatype.
private T data;
//T used in non-generic constructor.
public Node(T t)
{
next = null;
data = t;
}
public Node Next
{
get { return next; }
set { next = value; }
}
//T as return type of property.
public T Data
{
get { return data; }
set { data = value; }
}
}
public MyList()
{
head = null;
}
//T as method parameter type.
public void AddHead(T t)
{
Node n = new Node(t);
n.Next = head;
head = n;
}
// Implement GetEnumerator to return IEnumerator<T> to enable
// foreach iteration of our list. Note that in C# 2.0
// you are not required to implement Current and MoveNext.
// The compiler will create a class that implements IEnumerator<T>.
public IEnumerator<T> GetEnumerator()
{
Node current = head;
while (current != null)
{
yield return current.Data;
current = current.Next;
}
}
// We must implement this method because
// IEnumerable<T> inherits IEnumerable
IEnumerator IEnumerable.GetEnumerator()
{
return GetEnumerator();
}
}
public class SortedList<T> : MyList<T> where T : IComparable<T>
{
// A simple, unoptimized sort algorithm that
// orders list elements from lowest to highest:
public void BubbleSort()
{
if (null == head || null == head.Next)
return;
bool swapped;
do
{
Node previous = null;
Node current = head;
swapped = false;
while (current.next != null)
{
// Because we need to call this method, the SortedList
// class is constrained on IEnumerable<T>
if (current.Data.CompareTo(current.next.Data) > 0)
{
Node tmp = current.next;
current.next = current.next.next;
tmp.next = current;
if (previous == null)
{
head = tmp;
}
else
{
previous.next = tmp;
}
previous = tmp;
swapped = true;
}
else
{
previous = current;
current = current.next;
}
}// end while
} while (swapped);
}
}
// A simple class that implements IComparable<T>
// using itself as the type argument. This is a
// common design pattern in objects that are
// stored in generic lists.
public class Person : IComparable<Person>
{
string name;
int age;
public Person(string s, int i)
{
name = s;
age = i;
}
// This will cause list elements
// to be sorted on age values.
public int CompareTo(Person p)
{
return age - p.age;
}
public override string ToString()
{
return name + ":" + age;
}
// Must implement Equals.
public bool Equals(Person p)
{
return (this.age == p.age);
}
}
class Generics
{
static void Main(string[] args)
{
//Declare and instantiate a new generic SortedList class.
//Person is the type argument.
SortedList<Person> list = new SortedList<Person>();
//Create name and age values to initialize Person objects.
string[] names = new string[] { "Franscoise", "Bill", "Li", "Sandra", "Gunnar", "Alok", "Hiroyuki", "Maria", "Alessandro", "Raul" };
int[] ages = new int[] { 45, 19, 28, 23, 18, 9, 108, 72, 30, 35 };
//Populate the list.
for (int x = 0; x < names.Length; x++)
{
list.AddHead(new Person(names[x], ages[x]));
}
Console.WriteLine("Unsorted List:");
//Print out unsorted list.
foreach (Person p in list)
{
Console.WriteLine(p.ToString());
}
//Sort the list.
list.BubbleSort();
Console.WriteLine(String.Format("{0}Sorted List:", Environment.NewLine));
//Print out sorted list.
foreach (Person p in list)
{
Console.WriteLine(p.ToString());
}
Console.WriteLine("Done");
}
}
}
RECENT COMMENT