SQL cache dependency

Source: Internet
Author: User
Tags connectionstrings
SQL Cache dependency

Function:

UseSQLCache dependency Technology,Can be implemented when the applicationProgramWhen the data in the corresponding database in

To clear the corresponding cache in the application.,Maximize program performance.

Implementation Method:

 

I,Create a database

A)The database cannot be dynamically attached.

B)Enable message transmission in the database

Alter database cachedependencytest set enable_broker

"Cachedependencytest" is the database created for you.

II,Used in applicationsSQLCache dependency Technology

A)CreateGlobal. asaxFile, add the following in the "application_start" methodCode:

// Start the SQL cache dependency listener when the program is running

String connstring = configurationmanager. connectionstrings ["cachedependencytestconnectionstring"]. connectionstring;

Sqldependency. Start (connstring );

 

 

B)Use cache dependency on the page to use the cache

 

Protected void page_load (Object sender, eventargs E)

{

// The SELECT statement must contain the exact column name, and the table name must also contain the user name,

// For example, DBO in the following DBO. cachetable table cannot be omitted

String SQL = "select username from DBO. cachetable ";

 

String connstring = configurationmanager

. Connectionstrings ["cachedependencytestconnectionstring"]

. Connectionstring;

Sqlconnection conn = new sqlconnection (connstring );

Sqldataadapter da = new sqldataadapter (SQL, Conn );

Datatable dt = new datatable ();

// Create an SQL cache dependency. Use a selectcommand as the parameter.

System. Web. caching. sqlcachedependency denpendency

= New system. Web. caching. sqlcachedependency (DA. selectcommand );

// Fill in the data in the database to DT. You can fill in the data only after creating the cache dependency.

Da. Fill (DT );

 

// If the table dt is not in the cache, add the table DT to the cache.

If (Cache ["cachetable"] = NULL ){

Cache. insert (

"Cachetable" // cache name

, DT // the object to be cached. Here is a datatable

, Denpendency // SQL cache dependent object

// Cache expiration time, which is set to never expire.

// The cache will not be cleared until the table data in the database is changed

, System. Web. caching. cache. noabsoluteexpiration

// Set the sliding time to 0;

// If it is set to 5, it indicates that if you do not send a request within 5 seconds, it will be cleared from the cache

, Timespan. Zero

);

// Used for testing, so that you can know whether the data is read from the cache on the interface.

This. lblflag. Text = "out of cache" + datetime. Now. tostring ();

}

Else

{

This. lblflag. Text = "use cache" + datetime. Now. tostring ();

}

// Read data from the cache

This. gridview1.datasource = cache ["cachetable"] As datatable;

This. gridview1.databind ();

}

 

 

 

C)Page code:

<% @ Page Language = "C #" autoeventwireup = "true" codebehind = "appcache. aspx. cs" inherits = "cachetest. appcache" %>

 

<! Doctype HTML public "-// W3C // dtd xhtml 1.0 transitional // en" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<HTML xmlns = "http://www.w3.org/1999/xhtml">

<Head runat = "server">

<Title> </title>

</Head>

<Body>

<Form ID = "form1" runat = "server">

<Div>

<Asp: Label id = "lblflag" runat = "server"> </ASP: Label>

<Asp: gridview id = "gridview1" runat = "server">

</ASP: gridview>

</Div>

</Form>

</Body>

</Html>

Address: http://www.cnblogs.com/seerlin/archive/2008/12/19/1358128.html

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.