ASP. NET 2.0 provides a new data caching feature that uses the asynchronous notification feature of SQL server2005 to implement caching
1. First create a database of test in sqlserver2005.
Executing on SQL Server 2005
ALTER database <DatabaseName> SET enable_broker; The statement enables the Listener service for the corresponding database to support the SqlDependency feature.
Add a database table for employee.
1CREATETABLE[DBO]. [Employee] (
2 Create a new ASP. NET project using VS2005.
Web. config is as follows
3. Write the Global.asax file and start the Monitoring SQL2005 notification event.
<% @ApplicationLanguage = "C #"%>4. Write the data access code. Create a EmployeeData class with the following code
Usingsystem;It is important to note that the SELECT statement is written, you cannot use SELECT *, and you must add the schema name in front of the table name as we dbo.employee here.
5. Write the test page code.
6. Inserting background code
Usingsystem;Transferred from: http://www.cnblogs.com/wzyexf/archive/2008/12/26/1363195.html
SQL data cache dependency [SQL Server | Cache | SqlCacheDependency]Objective
This article is mainly for the "ASP 2.0 Development Guide"--< data cache > Chapter content extraction and slightly supplemented.
Resources
1. The ASP. NET 2.0 Development Guide
2..NET 2.0 SqlDependency Quick Start Guide
Support Database
SQL SERVER 7.0/2000/2005 version
Body
I. Introduction and comparison of SQL Server 7.0/2000 and SQL Server 2005
1.1SQL SERVER 7.0/2000
SQL SERVER 7.0/2000 does not provide built-in support for data cache dependencies, so you can check for data changes by continually polling the database through the background by adding specific database tables, triggers, and so on. When you perform an INSERT, UPDATE, delete operation on a datasheet, the change notification is automatically issued, so you can only monitor the table level, which line is not traceable.
To use the method steps:
1.1.1 Use the aspnet_regsql command line or sqlcachedependencyadmin to configure the connection database.
1.1.1.1ALTER DATABASE <DatabaseName> SET enable_broker;
Aspnet_regsql-s <server>-usa-p sa-d The data cache dependency feature of the <database>-ed startup database
Aspnet_regsql-s <server>-usa-p sa-d <database>-T <table>-et data cache dependencies for start data tables
1.1.1.2
SqlCacheDependencyAdmin. enablenotifications (connectionString);//Data cache dependency function for startup database
SqlCacheDependencyAdmin. enabletablefornotifications (connectionString, table);//Enable data table caching
It is recommended that this code be written in the Global.asax Application_Start method so that the database and data table cache dependencies are enabled when the application starts.
1.1.2 Configuring Web. config
<sqlcachedependency enabled= "true" Polltime= "10000" > Configured under <sytem.web> <caching> node, There's only one database. Do not specify Next level <database> node
1.1.3 in the Application data cache (can also be used when the data source control, output caches the entire page, not described here, the same below)
SqlCacheDependency SCD = new SqlCacheDependency ("Database name", "Table name");
Cache.Insert (..., ScD,...);
1.2SQL SERVER 2005
Built-in support for SQL data cache dependencies, built-in notification delivery service, provides smaller granularity of data change monitoring, ease of use and configuration.
To use the method steps:
1.2.1 Detect if service Broker is enabled
Select databasepropertyex (' Database name ', ' isbrokerenabled ')--1 indicates that 0 is not enabled
This place I see some friends translated into "whether can be enabled", this is not right, here I put out the original English: "This can is checked by calling" Select Databasepropertyex (' db Name ', ' Isbrokerenabled ') ". A ' 1 ' means that's the broker is enabled. A ' 0 ' means that the broker was not enabled. ".
According to my experience, if you create a new database directly on the current SqlServer2005, the default is open, if it is from another local database, the import is closed by default. (There may be inaccuracies, you can test it yourself). If already turned on can be directly adjusted to 1.2.2.
1.2.1.1 Enabling Service Broker
ALTER database name SET enable_broker;
1.2.2 in implementing a service-based SQL data cache dependency process, you need to explicitly call Sqldependency.start to start a listener that accepts dependency change notifications.
Sqldependency.start (connectionString);//recommend adding this code to the Global.asax Application_Start method,
Sqldependency.stop (connectionString);//For closing, can be added to the Application_End method of Global.asax.
1.2.3 using the Application data cache
SqlCommand cmd = new SqlCommand (sql,conn);
SqlCacheDependency SCD = new SqlCacheDependency (cmd);
Cache.Insert (..., ScD,...);
Attention:
a). The fully qualified name of the data table must be set. That is, the table name needs to be preceded by an owner, such as Dbo.test.
b). You must explicitly set the name of the database column you are accessing, and you cannot use "*".
c). You must ensure that it is not an aggregation function. such as Count, Max, and so on.
1.3 Comparison, Difference
|
And it is clear that the caching mechanism for SQL SERVER 2005 is more efficient. In addition, the SqlCacheDependency class is optimized specifically in conjunction with SQL SERVER 2005:
a). When using SQL SERVER 2005, the SqlCacheDependency class supports integration with the System.Data.SqlClient.SqlDependency class. An application can create a SqlDependency object and register it by accepting notifications through the OnChanged event handler. In this way, the application can use SQL Server 2005 's query notification mechanism to monitor data changes that are not valid using SQL query results, remove cached objects from the cache, and easily get data change notifications to flush the cache. (as can be seen here, when the onRemoveCallback delegate is triggered, the data has been removed from the cache, so that you can manually add the cache in the delegate, or simply set to NULL, so that he next call to cache. )
b). Not only do you add cache dependencies to your application, but you can also use it with the @outputcache directive to produce a page or user control that relies on the output cache of SQL Server database tables. For user controls, @OutputCache directives do not support query notifications using SQL SERVER 2005 (that is, onremovecallback delegates).
Second, System.Web.Caching.Cache insert and add difference
2.1Add method
Object Add (String key, object value, CacheDependency dependencies, DateTime absoluteexpiration, TimeSpan SlidingExpiration, CacheItemPriority priority, CacheItemRemovedCallback onRemoveCallback);
2.2Insert method
void Insert (String key, object value);
void Insert (String key, object value, CacheDependency dependencies);
void Insert (String key, object value, CacheDependency dependencies, DateTime absoluteexpiration, TimeSpan slidingexpiration);
void Insert (String key, object value, CacheDependency dependencies, DateTime absoluteexpiration, TimeSpan slidingexpiration, Cacheitemupdatecallback onupdatecallback);
void Insert (String key, object value, CacheDependency dependencies, DateTime absoluteexpiration, TimeSpan SlidingExpiration, CacheItemPriority priority, CacheItemRemovedCallback onRemoveCallback);
2.3 Comparison, Difference
a). The Insert method supports 5 overloads, is flexible to use, and the Add method must provide 7 parameters;
b). The Add method can return a cached item's data object, and Insert returns void;
c). When you add a duplicate cache, insert replaces the item, and the Add method causes an error.
Three, CacheDependency, AggregateCacheDependency, SqlCacheDependency
3.1CacheDependency is the parent class of AggregateCacheDependency and SqlCacheDependency. Primarily used to establish a dependency between an application data cache object and an array of files, cache keys, files or cache keys, or another CacheDependency object. CacheDependency monitors dependency ratios to automatically remove cached objects when any object changes. CacheDependency can monitor the change of a set of file paths (to files or directories).
3.2AggregateCacheDependency is primarily used to implement aggregate cache dependencies. As the pen data is cached on two tables, once any of the table data changes the cache is invalidated.
3.3SqlCacheDependency establishes a cache dependency between the application data cache object, the page output cache, the data source control, and so on with the specified SQL Server database table or SQL Server 2005 query results when the table changes (SQL Server 2005 row-level changes), the cached object associated with the table is automatically deleted and re-added from the cache. Generally speaking:
SqlCacheDependency (SqlCommand) for SQL SERVER 2005
SqlCacheDependency (database name, table name) for SQL SERVER 7.0/2000
End
Annoyed in search to search for so few articles, are reproduced, urgent need to deeply understand the knowledge of the cache, think of their own spending 10 pieces of genuine <<asp.net 2.0 Development Guide >> has not been carefully read, sure enough is a good book! Written in extreme detail, this place is sorted out and then affixed to prevent the 818 pages of the book is often carried around the run. Please correct me:)
Transferred from: http://www.cnblogs.com/over140/archive/2009/01/15/1376318.html
Error: Unable to enable Service Broker in database
Solution:
The Service Broker identifier should be unique across all instances on the same network. Otherwise, the message may be misrepresented.
So how can you get the database to use Service Broker?
ALTER DATABASE tpriusermanage SET new_broker
ALTER DATABASE tpriusermanage SET enable_broker
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
