Enabling SQL Cache Dependency in ASP.NET

1• Enable notifications for the database.
2• Enable notifications for individual tables.
3• Enable ASP.NET polling using “web.config” file
4• Finally use the Cache dependency object in your ASP.NET code

1. Enabling notifications for the database.

a. Enable notifications for the database, that can be done by using aspnet_regsql.exe, a command line utility.

CD c:\[WinDir]\Microsoft.NET\Framework\[Version] directory

b. execute the command at the same location as

c:>[WinDir]\Microsoft.NET\Framework\[Version] directory> aspnet_regsql -ed -E -d MyDBName

Switch	Description
-ed	command-line switch
-E	Use trusted connection
-S	Specify server name it other than the current computer you are working on
-d	Database Name

The above command will cause to create one new table and new stored procedures as specified below in the database.

Essentially, when a change takes place, a record is written in this table. The SQL Server
polling queries this table for changes.

Procedure Name	Description
AspNet_Sql CacheRegisterTable StoredProcedure	This stored procedure sets a table to support notifications. This process works by adding a notification trigger to the table, which will fire when any row is inserted, deleted, or updated.
AspNet_SqlCache UnRegisterTable StoredProcedure	This stored procedure takes a registered table and removes the notification trigger so that notifications won't be generated.
AspNet_Sql CacheUpdateChange IdStoredProcedure	The notification trigger calls this stored procedure to update the AspNet_SqlCacheTablesFor ChangeNotification table, thereby indicating that the table has changed.
AspNet_SqlCache QueryRegistered TablesStored Procedure	This extracts just the table names from the AspNet_SqlCacheTablesForChangeNotification table. It’s used to get a quick look at all the registered tables.
AspNet_SqlCache PollingStored Procedure	This will get the list of changes from the AspNet_SqlCacheTablesForChangeNotification table. It is used to perform polling.

Table Name	Description
AspNet_SqlCacheTablesForChangeNotification	Has three columns: tableName, notificationCreated, and changeId. This table is used to track changes. Essentially, when a change takes place, a record is written into this table. The SQL Server polling queries this table. Also a set of stored procedures is added to the database as well. See the following table.

2. Enabling notification for individual tables

After enabling notification for database, need to enable notification support for each individual table.

a. This also can be done manually using AspNet_SqlCacheRegisterTableStoredProcedure in query analyzer, for example, as given below:

exec AspNet_SqlCacheRegisterTableStoredProcedure 'TableName'

b. This also can be done with the use of "aspnet_regsql" with parameters as discussed

Parameter	Description
-et	to enable a able for sql cache dependency notifications
-t	to name the table

e.g.

aspnet_regsql -et -E -d Northwind -t 'TableName'

 Registering tables for notification internally creates trigger for the tables. For instance for
a "TableName" table the following trigger is created. So any modifications done to the
"TableName" table will update the "AspNet_SqlCacheNotification" table.

CREATE TRIGGER
dbo.[Products_AspNet_SqlCacheNotification_Trigger] ON
[TableName]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure
N'Products‘
END

When you make a change in the table (such as inserting, deleting or
updating a record), the change Id column is incremented by 1.ASP.NET queries this table
repeatedly keeps track of the most recent changed values for every table. When this
value changes in a subsequent read, ASP.NET knows that the table has changed.

Continue .......

What are the various ways to maintain state in web application?

√ session variables
√ Hidden fields
√ View state
√ Hidden frames
√ Cookies
√ Query strings

What is scavenging ?

The feature of caching is one of the useful technologies that exists in web application to improve the performance of the application in many scenarios but also have drawback that it uses numerous resources of the server that includes a great amount of memory which sometimes causes the complete memory gets exhausted.  

 So when server running your ASP.NET application runs low on memory resources, items are removed from cache depending on cache item priority. Cache item priority is set when you add item to cache. By setting the cache item priority controls the items scavenging are removed first

Call Back method creation for a cached item

Ensures cache to be updated based on changes after removal of corresponding cached item.
 
CacheItemRemovedCallback delegate: An instance of this delegate is passed to the insert method when we insert a cache item.


Signature of the delegate:

public delegate void CacheItemRemovedCallback( string key, 
    object value, CacheItemRemovedReason reason );

Parameter	Description
key	The index location for the item removed from the cache.
value	The object item removed from the cache.
reason	The reason the item was removed from the cache, as specified by the CacheItemRemovedReason enumeration.

CacheItemRemovedReason enumeration: a very useful enumeration used for getting reason of expiration.

Parameter	Description
DependencyChanged	The item is removed from the cache because a file or key dependency changed.
Expired	The item is removed from the cache because it expired.
Removed	The item is removed from the cache by a Remove method call, or by an Insert method call that specified the same key.
Underused	The item is removed from the cache because the system removed it to free memory.

e.g.

public void RemovedCallback(String k,
Object v,CacheItemRemovedReason r) {
// Code to check the reason of expiration.

// Logic to repopulate cache item using new values

}

What is a dependency?

Any change in the resources which our cache item depends on[*], will make our cache item expire.

 [*] Expiration policy of the item based on other resources such as physical file(s) in the server, another cache item(s), or time that can be set to be dependency for item to be cached.

There are three types of dependencies:

• File based dependency
• Key based dependency
• Time based dependency

File based dependency

We can set the validity of the cache item based on an external file or files. When this resource changes, the cached object becomes obsolete and is removed from the cache.

e.g.

CacheDependency dependency = new CacheDependency(Server.MapPath("isbn.xml"));

For multiple file dependencies, we can pass a string array containing the filename to the constructor.

Key based dependency

Key based dependency enables you to associate one cached item with another item in the cache and invalidates the first item when the second item changes. The following code fragment demonstrates how to insert an item into your application's Cache with a dependency on a key to another item placed in the cache. Since this method uses array syntax, you must define the number of keys the item you are adding to the Cache is dependent on.

e.g.

String[] dependencyKey = new String[1];
dependencyKey[0] = "key1"; 
CacheDependency dependency =  

new CacheDependency(null, dependencyKey);

You can use multiple key dependencies by increasing the array size and adding the keys to the array.

Time based dependency.

A time based dependency expires the item at a predefined time. There are two types of time-based dependency: Absolute and Sliding explained in under ASP.NET Cache.

Cache Insert() Method

Insert() ---> it overwrites the item if same key already exists in the cache.

public void Insert(string key, object value,CacheDependency dependencies,
DateTime absoluteExpiration,
TimeSpan slidingExpiration,
CacheItemPriority priority,
CacheItemRemovedCallback onRemoveCallback );

Parameter	Description
key	The cache key used to reference the object.
value	The object to be inserted in the cache.
dependencies	The file or cache key dependencies for the item. When any dependency changes, the object becomes invalid and is removed from the cache. If there are no dependencies, this parameter contains a null reference (Nothing in Visual Basic).
absoluteExpiration	The time at which the inserted object expires and is removed from the cache.
slidingExpiration	The interval between the time the inserted object was last accessed and when that object expires. If this value is the equivalent of 20 minutes, the object will expire and be removed from the cache 20 minutes after it was last accessed.
priority	The cost of the object relative to other items stored in the cache, as expressed by the CacheItemPriority enumeration. This value is used by the cache when it evicts objects; objects with a lower cost are removed from the cache before objects with a higher cost.
onRemoveCallback	A delegate that, if provided, will be called when an object is removed from the cache. You can use this to notify applications when their objects are deleted from the cache.

e.g.

Cache.Insert ("DSN", connectionString,new CacheDependency("filepath") 
  DateTime.Now.AddMinutes(2), 
  TimeSpan.Zero, 
  CacheItemPriority.High,
  new CacheItemRemovedCallback(this.RemovedCallback)
);

dependency and a call back function executed when the cache expires.

CacheItemRemovedCallback is a delegate, which defines a callback method for notifying applications when a cached item is removed from the Cache.

e.g. 1. Insert xml data from a file into the cache, eliminating

the need to read from the file on subsequent requests.

Cache.Insert("key", myXMLFileData, new

System.Web.Caching.CacheDependency(Server.MapPath("users.xml")));

CacheDependency ----> ensures that cache immediately expires while files changes and updated with changes. Similary can happen with array of filenames, if cached data needs to depend on several files.

e.g. 2. Inserting second piece of data that depends on the existing first piece of data ["key" (say)].

Cache.Insert("dependentkey", myDependentData, new

System.Web.Caching.CacheDependency(new string[] {}, new string[]

{"key"}));

  If no key existed in the cache called "key", or if the item associated with that key expires or is updated, then the cache entry for "dependentkey" would expire.

e.g. 3. Absolute Expiration:

Cache.Insert("key", myTimeSensitiveData, null,

DateTime.Now.AddMinutes(1), TimeSpan.Zero);

Caches time sensitive data for one minute, at which point the cache will expire.

Note: absolute expiration and sliding expiration (below) cannot be used together

e.g. 4.  Sliding Expiration:

Cache.Insert("key", myFrequentlyAccessedData, null,

System.Web.Caching.Cache.NoAbsoluteExpiration,

TimeSpan.FromMinutes(1));

Caches some frequently used data and remain in cache until one minute passes without anything referencing it.

If anyone references even at 59th second then again time of expiration become one minute instead of one second that was expected to expire the data in case it might not referenced during the current minute.

ASP.NET Cache

Used while sharing data across users based on dependencies and expiration policies.
Cache values can be accessed using
Name space     -System.Web
Class object of - HttpContext or Page
Property           - Cache

It has the greatest potential impact on an application's performance

Tip:
Cache Early; Cache Often

Implement caching at every layer of your application. Add caching support to the data layer, the business logic layer, and the UI or output layer. Memory is cheap—by implementing caching in an intelligent fashion throughout the application, you can achieve great performance gains.

Types of dependencies
Type	Brief Description
File dependency	Allows you to invalidate a specific cache item when a disk based file or files change.
Time-based expiration	Allows you to invalidate a specific cache item depending on predefined time.
Key dependency	Allows you to invalidate a specific cache item depending when another cached item changes.

Table 1.

 

Cache Callback

As cache object is created according to some dependency (ref table 1) and expires or removed while there is change in corresponding dependency and ASP.NET execute/capable to fire a cache callback method corresponding to this removal of object to add the items back in cache.

ASP.NET has a full-featured cache engine that can be used by applications to store arbitrary objects in memory. This mechanism is implemented via the Cache class, an instance of which exists for each application configured on a web server. The application cache provides a simple dictionary interface using key/value pairs that enables you to store and retrieve objects from the cache.

Forms of caching
Type	Advantages
page level output caching,	provides additional flexibility and can be used to take advantage of caching throughout every layer of an application.
user control level output caching (or fragment caching)	advantage of being incredibly simple to implement
Cache API

Page Level Output Caching

e.g.

<%@OutputCache Duration="60" VaryByParam="*"%>

The various types of parameters that can be used in this form of caching is discussed as below:

Param name	Description
Duration	Required. Time, in seconds, the page should be cached. Must be a positive integer.
Location	Specifies where the output should be cached. If specified, must be one of: Any, Client, Downstream, None, Server or ServerAndClient.
VaryByParam	Required. The names of the variables in the Request, which should result in, separate cache entries. "none" can be used to specify no variation. "*" can be used to create new cache entries for every different set of variables. Separate variables with ";".
VaryByHeader	Varies cache entries based on variations in a specified header.
VaryByCustom	Allows custom variations to be specified in the global.asax (for example, "Browser").

Most situations can be handled with a combination of the required Duration and VaryByParam options. VaryByHeader and VaryByCustom are primarily used to allow customization of the page's look or content based on the client that is accessing them.

In order to enable separate cache entries for each browser, VaryByCustom can be set to a value of "browser". This functionality is built into the caching module, and will insert separate cached versions of the page for each browser name and major version.

<%@ OutputCache Duration="60" VaryByParam="None" VaryByCustom="browser"  %>

Fragment Caching, User Control Output Caching

Fragment caching uses the same syntax as page level output caching, but applied to a user control (.ascx file) instead of to a web form (.aspx file).

In addition to parameters used for page level output caching an additional an OutputCache attribute named "VaryByControl" is included in fragment caching, which will vary the caching of the user control depending on the value of a member of that control(typically a control on the page, such as a DropDownList).

If VaryByControl is specified, VaryByParam may be omitted.

By default each user control on each page is cached separately. However, if a user control does not vary between pages in an application and is named the same across all such pages, the Shared="true" parameter can be applied to it, which will cause the cached version(s) of the user control to be used by all pages referencing that control.

e.g. 1. Separate cache for variation of query string and page containing control.

<%@ OutputCache Duration="60" VaryByParam="*" %>

Creates a separate cache for control for 60 sec and created

for every variation of query string and page containing control.

e.g. 2. Separate cache creation with variation of selected value in

CountryDropDownList control.

<%@ OutputCache Duration="60" VaryByParam="none" 
VaryByControl="CountryDropDownList" %>

User control cached for 60 sec and cached for each changed
value of control "CountryDropDownList".

e.g. 3.  Separate cache creation with each browser name and
variation of major version

<%@ OutputCache Duration="60" VaryByParam="none" 

VaryByCustom="browser" Shared="true %>

Created cache expires after 60 sec which gets created

for every change in browser name and major version.

The cache entries for each browser would then be 

shared by all pages referencing this user control (as long

as all pages refer to the control with the same ID).

 

Caching API, Using the Cache Object

Using the Cache object, you can store any serializeable data object, and control how that cache entry expires based on a combination of one or more dependencies.

Storing Data in the Cache

1. The simplest way to store data in the Cache is simply to assign it, using a key, just like a HashTable or Dictionary object:

Cache["key"] = "value";

This will store the item in the cache without any dependencies,
so it will not expire unless the cache engine removes it in order to
make room for additional cached data.

2. To include specific cache dependencies, the Add() or Insert()
method is used. Each of these has several overloads.

Add() --> Returns a reference to the cached object and do nothing if
item with same key already exists
Insert() --> Has no return value and overwrite the item if item with
same key name already exists.


3. Other Options:

a. Specifying the priority of the item (from low to high to NotRemovable):  defined in the System.Web.Caching.CacheItemPriority enumeration

b. CacheItemRemovedCallback option allows some interesting possibilities.

Ajax types most know list

AJAX Library Name	Brief Description
ASP.NET AJAX Extensions ("Atlas")	AJAX functionality for ASP.Net via server components, with auto generated JavaScript and tightly related to server controls.
AJAXPro	Ajax for ASP.NET development to create a fully client-side based and require a few server-side API calls to get/set data
Direct Web Remoting (DWR)	AJAX solution for Java based web solutions.
Dojo	Provides a strong abstraction between them and the browser's DOM Provides the most impressive end effects from the least code
Google Web Toolkit (GWT)	For Java based solutions that require to invest more time to get a more robust, easier-to-debug solution
Prototype	Client-side development that require minimal abstraction between them and the DOM Client-side development that need a library to speed up common DOM manipulation tasks
xAJAX	For PHP development of all stripes

Note: There are the strengths and weaknesses of popular pure JavaScript libraries and a few options offered by the server-based AJAX libraries. AJAX Library Type Pros Cons Server Based Have ability to map native server objects such as database results to JavaScript equivalents Reduce efforts required to code JavaScript (depending on the library) This kind of AJAX library JS code ties tightly to the server language or controls. Pure JavaScript Have ability to handle multiple server languages  Have a clear line of sepration between client and server code In this kind of library developer has to manage the logic for to manage how to translate server-side return values to XML or JSON.

A Simple trigger example

The below is simplest trigger that execute while any record is inserted in the table tbl_registrations and while insert operation is complete it displays all the values inserted in the table


Create trigger tr_MyTrigger1
ON tbl_registrations
FOR INSERT
AS
Begin
        SELECT * FROM Inserted
End

List of procedures as comma seprated string

Following is the code that can be used to get  list of all procedures having name started with string 'Proc' and this returns a single string containing all procedures as comma separated names.

i.e. if there are procedures proc_1 ,proc_2,proc_3 and proc_4 exists in sql server then the returned result will look like

proc_1 ,proc_2,proc_3 , proc_4

Also  @listStr variable contains the result that can be used to process this list further according to the requirements.

DECLARE @listStr VARCHAR(MAX)
select @listStr = COALESCE(@listStr+',' , '') + [name]
FROM sysobjects WHERE type ='P' AND name LIKE 'Proc%' AND [name]  IS NOT NULL
SELECT @listStr

Getting List of added/deleted records in trigger

To list of all records that has been added in a table for which trigger executed
records

SELECT * FROM Inserted

To list of all records that has been deleted in a table for which trigger executed
records

SELECT * FROM Deleted


To list of all records that has been modified but not delete modified in a table for which trigger executed
records


SELECT * FROM Inserted
EXCEPT
SELECT * FROM Deleted

Get List of procedure in SQL Server

Select so.name FROM syscolumns sc,master..systypes st,sysobjects so
WHERE sc.id in (select id from sysobjects where type ='P')
 AND so.type ='P'
 AND sc.id = so.id
 AND sc.type = st.type
GROUP BY so.name