Supports database cache relevance in ASP. NET

Jeff prosise

DownloadCode: Wickedcode0304.exe (135kb)

Developers like ASP. NET ApplicationsProgramCache. One reason is that ASP. NET can create a correlation between items in the cache and files in the file system. If the file for which the relevance is modified, ASP. NET automatically deletes the related items from the cache. By combining with the cache deletion call back (notifying all followers when a cache item is deleted), the cache relevance provides developers with convenience, this allows them to maximize performance by minimizing time-consuming file access, as this allows them to safely allow file data caching without worrying about data becoming obsolete.

Although cache relevance is very practical, there is still a lack of a crucial feature in ASP. NET 1.0. This feature, once available, will prove the cache relevance as developers dream come true. This feature supports database entities. In reality, most web applications extract data from databases rather than files. Although ASP. NET can deliver a cache necklace to a file, it cannot connect it to a database entity. That is to say, you can read the file content into dataset, then cache dataset, and automatically delete the file where dataset is located during initialization when it is changed. However, dataset cannot be initialized using database queries, so dataset cannot be cached and the dataset is automatically discarded when the database is changed. This is indeed too bad, because it is precisely because of excessive database access (for example, excessive file I/O) that leads to a decline in application performance.

ASP. NET does not support database relevance, which does not mean that database relevance cannot be achieved. This section of wicked Code demonstrates the technology that extends the ASP. NET application cache to support database relevance. It includes database triggers and extended stored procedures. Although the implementation shown here only applies to Microsoft? SQL Server ?, However, the general technology is also applicable to any database that supports triggers for interaction with file systems and user-defined processes.

Activity database relevance

Let's take a look at a demonstration. Figure 1Source codeThe ASP. NET page displays quotations randomly selected from the SQL Server database named quotes. To create a database, run the installation script. Figure 2 shows a scaling-down installation script. The complete script is included in the downloadable ZIP file attached to this column. (You can execute this script in the SQL Server Query analyzer or use the osql command to execute this script from the command line .) Every time this page is extracted, page_load initializes a dataset using all records in the database's quotations table, then randomly selects a record from the dataset, and writes it to this page. Press F5 several times and you will see a variety of random quotations from some famous (and not so famous) people, as shown in 3.

Figure 3 random quotes

For some reason, this page is named dumbdbquotes. aspx. It is not wise to query the database every time you request this page. Accessing the database every time you access this page (especially when the database is hosted by a remote server) is a reliable way to generate a non-scalable database.

ASP. NET application cache can solve the problem of excessive database access. If the dataset has been cached, it can be extracted directly from the memory (that is, the cache) to avoid unnecessary database access. Dataset cache is very easy. The application cache accepts any type of instances derived from system. object. In Microsoft. NET Framework, this means any managed instance (including dataset. The problem is that if the underlying database changes after the dataset cache, you will provide users with outdated data. You can implement a solution to regularly requery the database, but the ideal solution should meet the following conditions: No polling is required, and the updated data in the data source will be delivered immediately once it becomes available.

See figures 4 and 5, which contain the source code of the smarter quote application. Smartdbquotes. aspx retrieves quotations from the database rather than from the application cache. Global. asax fills in the cache and refreshes the cache when the database changes. The following is a trial description of the two source codes:

•	Create a subdirectory named aspnetsql in the root directory of the C: drive on the Web server. In aspnetsql, create a zero-byte file named quotes. quotations. Ensure that everyone, or at least system and ASPnet (special account created when ASP. NET is installed) have access to quotes. quotations.
•	Xsp. DLL is copied to the binn directory of SQL Server (for example, c: \ Program Files \ Microsoft SQL Server \ MSSQL \ binn, or copy to the Web server, Windows will automatically search for any DLL location (for example, c: \ windows \ system32 ).
•	Use the modified script in Figure 6 to regenerate the database.
•	Deploy global. asax and smartdbquotes. aspx to a virtual directory (for example, wwwroot) on the web server.
•	Request smartdbquotes. aspx in the browser. Refresh the page several times until the quotation "the use of COBOL cripples the mind; its teaching shoshould therefore be regarded as a criminal offense" appears.
•	Modify this quote in the quotations table of the quotes database using the SQL Server Enterprise Manager or the tool you selected. Change it to "the use of visual basic? Enriches the mind; its teaching shoshould therefore not be regarded as a criminal offense ". Refresh the page until the modified quotations appear. Note that new quotations are not old ones, even though the query results are stored in the application cache.

I just showed that ASP. NET application cache can now be combined with database relevance to generate an efficient data-driven application. The question is how can we combine the two? How does one form a link between the cached dataset and the database, and how is the solution scalable?

How database relevance works

Externally, dumbdbquotes. aspx is similar to smartdbquotes. aspx, and the output is identical. Internally, the two are completely different. The former performs database access each time it is requested, and the latter extracts data from the application cache. In addition, smartdbquotes. aspx uses database relevance to ensure that the cached data changes as the database changes. If the database is not changed, the database will be queried only once during the application lifecycle. If the database changes, the database will be queried again to update the cache.

Figure 7 database relevance

Figure 7 illustrates how database relevance works. When global. asax puts dataset into the cache, create a file system correlation between the dataset and files named quotes. Quotations in the C: \ aspnetsql directory. Quotes. Quotations is a zero-byte signal file. Its only function is to trigger the cache deletion logic of ASP. NET applications. The following is a statement in global. asax, which creates a cachedependency Object Linking dataset to quotes. Quotations:

 
New cachedependency ("C: \ aspnetsql \ quotes. Quotations ")
 

Global. asax also registers its own refreshcache method. This method is called when dataset is deleted from the cache (that is, when the signal file is changed. The code for this method is as follows:

 
New cacheitemremovedcallback (refreshcache)
 

The refreshcache task is to query the database and put the generated dataset into the application cache. It is called once when the application is started and deleted from the cache by dataset.

This is only half of the entire system. The other half involves databases. Figure 6 shows the modified database installation script, which adds an insert/update/delete trigger to the database's quotations table.

Create trigger datachanged on quotationsfor insert, update, deleteas exec master .. xsp_updatesignalfile 'quotes. Quotations 'Go
 

This trigger is triggered when a record is added or deleted in the table and when the record is changed. How does a trigger work? It calls an extended stored procedure named xsp_updatesignalfile (the extended stored procedure is used in SQL Server to refer to Win32? DLL ). Then, the extended stored procedure uses the Win32 createfile function to update the timestamp of quotes. quotations.

The lifetime of the cached dataset is determined by the cache relevance and quotes of the common file system. quotations Association; updating the quotations table triggers database triggers, and the trigger calls "Update" quotes. the extended stored procedure of quotations promotes ASP.. Net deletes the dataset from the application cache and calls global. asax's refreshcache method, which then executes a brand new database query and restarts the entire process.

The last key to this challenge is the extended storage. It is stored in the previously installed xsp. dll. This xsp. dll uses Visual C ++? 6.0 written in an unmanaged C ++. Its source code is shown in figure 8. The path c: \ aspnetsql of the signal file is hardcoded to this DLL, but you can change it as needed and make it an input parameter like a file name.

You must install the extended stored procedure before using it. The following statements in the executed SQL installation script install xsp_updatesignalfile into the master database and grant execution permissions to all visitors:

Use masterexec sp_addextendedproc 'xsp _ updatesignalfile ', 'xsp. dll' grant execute on xsp_updatesignalfile to publicgo
 

Since a built-in extended stored procedure such as xp_mongoshell can be used, why write a custom extended stored procedure to update the file timestamp? This is for security considerations: xp_mongoshell can be used for all forms of malicious attempts, while xsp_updatesignalfile is not. Because xsp_updatesignalfile is almost just calling the Windows createfile function, it is more efficient than xp_mongoshell.

Server farm

When the Web server and database server are installed on the same computer, smartdbquotes. aspx and related components can work well, but what if the database is installed on another computer? What will happen in the Web field? Is the change notification mechanism based on database triggers, extended stored procedures, and file system relevance compatible with multi-server installation?

Of course. In a group environment, the file system-based ASP. NET cache relevance depends on Win32 file change notifications. The Win32 file change notification also supports general naming conventions (UNC) for path names. To use the database cache relevance in the Web field, the signal file should reside on the database server, as shown in figure 9. Then, the UNC path name of the network address of the specified signal file is passed to the constructor of cachedependency:

 
New cachedependency (@ "\ servername \ aspnetsql \ quotes. Quotations "),
 

Figure 9 signal files on the database server

The biggest obstacle to creating relevance for remote files is security. By default, when ASP. the net auxiliary process works with Microsoft Internet Information Services (IIS) 5.0 and runs as ASPnet when it is configured to run in compatible mode under IIS 6.0. ASPnet is a local account that cannot be authenticated on a remote computer. Without changing the configuration, attempting to use a UNC path name to create a cache relevance will result in a denial of access error, even if you grant the everyone permission for Remote sharing.

There are several corresponding solutions. One of them is to configure ASP. NET to use a domain account that can perform authentication on the database server. This change is easy to implement: you only need to specify the user name and password for this account in the <processmodel> section of machine. config of each web server. However, many companies' security policies may prohibit passwords from being stored in plain text configuration files. If this is the case for your company, but you still want to use a domain account to run ASP. net, you can upgrade.. NET Framework Version 1.1 (allows encryption of auxiliary process creden。 so that they can be securely stored in the registry), or download patches of version 1.0 (for the same purpose ). You can find information about this patch in stronger credentials for processmodel, identity, and sessionstate.

One form of change in this technology is: set identical local accounts (using the same user name and password) on two computers, and set ASP. net is configured to run on the Web server with identical local accounts.

To solve the authentication problem on the backend database server that contains the signal file, another solution is to upgrade to Windows Server 2003. This is the latest member of the Windows Server family. It comes with IIS 6.0 and allows ASP. NET auxiliary processes to run as network services. Unlike ASPnet, network service can perform identity authentication on a remote computer. Alternatively, you can access the database by using the COM + component running on the Web server, and configure the component to run as a principal with network credencip.

No matter which method you choose to enable ASP. net Applications can access remote signal files. The key point is to combine the database cache relevance with the UNC path name to implement a scalable solution, that is, the solution can work well in the Web field or in the case of Single Server installation. This is good news for developers who use ASP. NET to build efficient data-driven applications, and for users.

Please send questions and comments to Jeff via wicked@microsoft.com

Jeff prosise is the author of several books, including Microsoft press's programming Microsoft. NET and programming windows with MFC. He is also one of the founders of wintellect. Wintellect is a consulting and training company specializing in Microsoft. NET Framework.

Go to the original English page