SQL Cache Dependency in ADO.NET

Question

What is SQL Cache Dependency and how it will be useful in ADO.NET

Muralidharan Deenathayalan · Answer

SqlCacheDependency class you can cache some data and make its expiration (invalidation) dependent on changes (updates deletes inserts) in a particular table. http://www.c-sharpcorner.com/UploadFile/vendettamit/681/ http://msdn.microsoft.com/en-us/library/system.web.caching.sqlcachedependency.aspx http://msdn.microsoft.com/en-us/library/ms178604.aspx Hope this helps you.

Hemant Kumar · Answer

aching is a very useful way for you to retain pages or data across HTTP requests so that you can reuse them without the recreating them. In ASP.net 2.0 it makes you easier to implement data caching. SQL cache dependencies are one of ASP.net 2.0 coolest features which allows you to create dependencies between cached items and database entities. This enables cached query results to be automatically evicted from the cache if the underlying data changed. This feature applies to SQL Server 7 2000 and 2005. SQL cache dependencies are more powerful when paired with SQL Server 2005 - you can set the cache dependencies are more granular - this is not rely on polling but on query notifications from SQL Server 2005 - this require no preparation of the database Here are the steps on how to enable the SQL cache dependency on a table. 1. Enable notifications using SQL registration tool To enable SQL cache dependency first you have to register the database tables by using the Sql registration tool (C:\Windows\Microsoft.NET\Framework\v2.0.50727) At the command prompt type aspnet_regsql -S [servername] -E -d [databasename] -ed -et -t [tablename] -S = Server -E = TrustedConnection -d = Database -ed = Enable caching at database -et = Enable caching at table -t = table other options -u = username -p = password After you run the statement you hit refresh at your database. You will notice there is an extra table created to store the cache data and a trigger created for the particular table. When there is changes on the table the trigger will be fired up and write the changes to the cache table. 2. Register the notification in the Web Configuration Enable the sql cache dependency at the web configuration file and set the poll time. system.web > caching > sqlCacheDependency enabled =  true  pollTime =  2000  > databases > add name =  conn  connectionStringName =  conn  /> databases > sqlCacheDependency > caching > system.web > The poll time is specifies how often the application checks to see whether the data has changed. 3. Define SQL Dependency There are two options to define the SQL dependency - You can specify on the OutputCache directive @ OutputCache Duration =10 VaryByParam =* SqlDependency =[sqldependencyname]:[tablename] %> - or specify directly on a datasource control asp : SqlDataSource EnableCaching =true CacheDuration =Infinite SqlCacheDependency =[sqldependencyname]:[tablename] ... /> After you have defined the SQL dependency any changes that made to the database will be reflected on your web pages immediately though you have set the cache duration on the page. This can make sure that you users always get the latest data from the server. 4. Notification-based cache invalidation If you are using SQL Server 2005 you can use the Notification-based cache invalidation. Unlike polling based validation no needs to be registered in your application's configuration. Furthermore no special configuration using the aspnet_regsql.exe tool is needed. A notification based dependency is configured on the OutputCache directive using the string CommandNotification . @ OutputCache Duration =10 VaryByParam =* SqlDependency = CommandNotification  %> This value indicates to ASP.NET that a notification based dependency should be created for the page or datasource control. System.Data.SqlClient.SqlDependency.Start() method must be called before the first SQL query is executed. This method could be placed in Application_Start() event in global.asax file. void Application_Start( object sender EventArgs e) { System.Data.SqlClient. SqlDependency .Start( ConfigurationManager .ConnectionStrings[ conn ].ConnectionString); } This option is much more efficient as there is no pollung going on but the ASP.net acts as client only if there is changes ASP.net will get notified. You may use SQL profiler to monitor the perfomance of your database to compare before and after you apply this SQL cache dependency.