剑锋博客

网站访问量大，数据量大而变更不是那么频繁时，使用缓存是提高网站页面执行速度的有效手段。为了清晰理解，掌握sql缓存的使用，我制作了一个demo。

在制作这个demo的时候，碰到了一些问题。

1，原来sql server 2005 developer edition 并不支持sql 缓存。只有企业版才支持。所以要用aspnet_regsql.exe 工具开启缓存机制，或者使用程序来开启。

2，使用aspnet_regsql.exe开启缓存机制的方法：

C:\Windows\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql.exe -S localhost -U
sa -P sa -d Northwind -ed

C:\Windows\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql.exe -S localhost -U
sa -P sa -d Northwind -t employee -et

3，使用SqlCacheDependencyAdmin.EnableNotifications方法开启缓存机制。

try {
                // Instantiate SqlDep using the SqlCacheDependency constructor.
                SqlDep = new SqlCacheDependency("Northwind", "Categories");
            }

            // Handle the DatabaseNotEnabledForNotificationException with
            // a call to the SqlCacheDependencyAdmin.EnableNotifications method.
            catch (DatabaseNotEnabledForNotificationException exDBDis) {
                try {
                    SqlCacheDependencyAdmin.EnableNotifications("Northwind");
                }

                // If the database does not have permissions set for creating tables,
                // the UnauthorizedAccessException is thrown. Handle it by redirecting
                // to an error page.
                catch (UnauthorizedAccessException exPerm) {
                    Response.Redirect(".\\ErrorPage.htm");
                }
            }

            // Handle the TableNotEnabledForNotificationException with
            // a call to the SqlCacheDependencyAdmin.EnableTableForNotifications method.
            catch (TableNotEnabledForNotificationException exTabDis) {
                try {
                    SqlCacheDependencyAdmin.EnableTableForNotifications("Northwind", "Categories");
                }

                // If a SqlException is thrown, redirect to an error page.
                catch (SqlException exc) {
                    Response.Redirect(".\\ErrorPage.htm");
                }
            }
完整的程序请参考微软的实例：http://msdn.microsoft.com/en-us/library/system.web.caching.sqlcachedependency.aspx。

如果不支持缓存，会收到如下异常信息：

The database 'dbemployee' is not enabled for SQL cache notification.

To enable a database for SQL cache notification, please use the System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications method, or the command line tool aspnet_regsql. To use the tool, please run 'aspnet_regsql.exe -?' for more information.

3，需要在web.config配置数据库连接和缓存依赖数据库条目。

详细配置请参考源码。