CACHING IN ASP.NET 2.0+SQL SERVER

It’s so easy to implement sql caching in asp.net applications.
In first, you need to enable the cache notifications. To enable that you want to execute

aspnet_regsql -ed -E -d databasename (here you need to give your database name)

in command line. After executing this command in visual studio command prompt, a table SqlCacheTablesForChangeNotification in the database you given. The SqlCacheTablesForChangeNotification has the columns tableName, notificationCreated, and changeId to track the changes.

Also you need to enable notification for the individual table.

aspnet_regsql -et -E -d databasename -t tablename (here you need to give your table name)

This will generate a trigger for the table you have mentioned

CREATE TRIGGER dbo.[tblEmployee_AspNet_SqlCacheNotification_Trigger] ON [tblEmployee]
FOR INSERT, UPDATE, DELETE AS BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'tblEmployee'
END


[Sqlserver enterprise manager->select the a table in your database->all tasks->manage trigger->choose the trigger tblEmployee_AspNet_SqlCacheNotification_Trigger from the dropdown name]


Implementing sql caching in Asp.net 2.0+c#

Web.config
In we.config, you need to mention


















Namespace

using System.Web.Caching;


In the code behind


DataTable _dt = new DataTable ();
_dt = (DataTable) Cache ["cachename"]; //example Cache ["test"];
// you can provide any cache name, but remember to use the same in all the cases.

if (_dt == null)//_dt null only when first time and on a db change
{
_dt = DBAccess.GetData ();
// binding data you want from sql server db put your own code here

SqlCacheDependency _dep = new SqlCacheDependency("databasename", "tablename");
//here the databasename will be the name that you have given in the
// SqlCacheDependency in web.config and table name will be
//the table you have enabled the cache notification. sample given //below

Cache.Insert ("cachename", _dt, _dep);

}
dgData.DataSource = _dt; // dgdata is a sample grid view control
dgData.DataBind();

---

Hope this will help you