Cache application (ASP. NET 2.0 SQL data cache dependency [SqlCacheDependency])

Source: Internet
Author: User
Tags connectionstrings

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[id][int]identity (Notnull),
3[name][varchar] (50)
4)
5



2 Create a new ASP. NET project using VS2005.

Web. config is as follows

1<?xmlversion= "1.0"?>
2<configurationxmlns= "http://schemas.microsoft.com/.NetConfiguration/v2.0" >
3<appsettings/>
4<connectionstrings>
5<addname= "MySource" connectionstring= "datasource=.\sql2005;initialcatalog=test; Persistsecurityinfo=true; Userid=sa; Password=sasa "Providername=" System.Data.SqlClient "></add>
6</connectionstrings>
7<system.web>
8<compilationdebug= "true"/>
9<authenticationmode= "Windows"/>
10</system.web>
11</configuration>
12

3. Write the Global.asax file and start the Monitoring SQL2005 notification event.

<% @ApplicationLanguage = "C #"%>
<% @ImportNamespace = "System.Data.SqlClient"%>

<scriptrunat= "Server" >

Voidapplication_start (OBJECTSENDER,EVENTARGSE)
{
stringconnstr=configurationmanager.connectionstrings["MySource"]. ConnectionString;
Sqldependency.start (CONNSTR);
}

Voidapplication_end (OBJECTSENDER,EVENTARGSE)
{
stringconnstr=configurationmanager.connectionstrings["MySource"]. ConnectionString;
Sqldependency.stop (CONNSTR);
}
</script>

4. Write the data access code. Create a EmployeeData class with the following code

Usingsystem;
Usingsystem.data;
UsingSystem.Data.SqlClient;
Usingsystem.configuration;
UsingSystem.Data.Common;
Usingsystem.web;
usingSystem.Web.Caching;
usingSystem.Web.Security;
UsingSystem.Web.UI;
UsingSystem.Web.UI.WebControls;
UsingSystem.Web.UI.WebControls.WebParts;
UsingSystem.Web.UI.HtmlControls;

<summary>
Summary description of EmployeeData
</summary>
Publicclassemployeedata
{
Publicemployeedata ()
{
}

Privatehttpcontextcontext;

Publicdatasetgetcachedata ()
{
Context=httpcontext.current;
Datasetcache= (DataSet) context. cache["Employee"];
if (cache==null)
{
Returngetdata ();
}
Else
{
Returncache;
}
}


Publicdatasetgetdata ()
{
stringconnstr=configurationmanager.connectionstrings["MySource"]. ConnectionString;
Sqlconnectionconn=newsqlconnection (CONNSTR);
Sqldataadapteradp=newsqldataadapter ("Selectid,namefromdbo.employee", conn);
Sqlcachedependencydep=newsqlcachedependency (ADP. SelectCommand);
Datasetds=newdataset ();
Adp. Fill (DS);
Context. Cache.Add ("Employee", Ds,dep,cache.noabsoluteexpiration,cache.noslidingexpiration,cacheitempriority.default, Newcacheitemremovedcallback (this. Datadiff));
Returnds;
}

Publicvoiddatadiff (Stringkey,objectvalue,cacheitemremovedreasonreason)
{
Console.WriteLine ("Key:" +key);
GetData ();
}

}

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.

<% @PageLanguage = "C #" autoeventwireup= "true" codefile= "Default.aspx.cs" inherits= "_default"%>

<! Doctypehtmlpublic "-//w3c//dtdxhtml1.0transitional//en" "Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">

<title> Untitled Page </title>
<body>
<formid= "Form1" runat= "Server" >
<div>
<asp:gridviewid= "GridView1" runat= "Server" >
</asp:GridView>
</div>
</form>
</body>

6. Inserting background code

Usingsystem;
Usingsystem.data;
Usingsystem.configuration;
usingSystem.Web.Caching;
UsingSystem.Data.SqlClient;
Usingsystem.web;
usingSystem.Web.Security;
UsingSystem.Web.UI;
UsingSystem.Web.UI.WebControls;
UsingSystem.Web.UI.WebControls.WebParts;
UsingSystem.Web.UI.HtmlControls;

Publicpartialclass_Default:System.Web.UI.Page
{
Protectedvoidpage_load (OBJECTSENDER,EVENTARGSE)
{
Employeedataem=newemployeedata ();
Gridview1.datasource=em. Getcachedata ();
Gridview1.databind ();
}

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

SQL SERVER 7.0/2000

SQL SERVER 2005

Implementation mechanism

Polling

Notification delivery Service (service Broker)

Whether you need to configure enable

Need

Not required, built-in support

Data Change detection

Limited to table-level change monitoring

Table-level, row-level change monitoring

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.