Sql cache dependency, DOT NET Programming

Assignment Help:

How do we enable SQL Cache Dependency?

Below are some of the steps to enable the SQL Cache Dependency:-

  • Enable the notifications for database.
  • Enable the notifications for individual tables.
  • Enable the ASP.NET polling by using the "web.config" file
  • Finally use the Cache dependency object in your ASP.NET code

To enable notifications for the database.

Before you can use SQL Server cache invalidation, you need to enable notifications for the database. This task is done with the aspnet_regsql.exe command-line utility, which is located in the c:\[WinDir]\Microsoft.NET\Framework\[Version] directory.

aspnet_regsql -ed -E -d Northwind

-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

So now let's try to understand what happens in the database because of "aspnet_regsql.exe". After we execute the "aspnet_regsql -ed -E -d Northwind" command you will see one new table and four new stored procedures created.

1497_sql cache.png

Figure : - SQL Cache table created for notification

1413_sql cache1.png

Figure : - New stored procedures created

Just to make short run of what the stored procedures do.

"AspNet_SqlCacheRegisterTableStoredProcedure" :- This stored procedure fixed a table to support notifications. This method works by adding a notification trigger to the table, which will fire whenever any row is deleted,inserted , or updated,   .

"AspNet_SqlCacheUnRegisterTableStoredProcedure":- This stored procedure takes a registered table and discard the notification trigger so that notifications won't be created.

"AspNet_SqlCacheUpdateChangeIdStoredProcedure":- The notification trigger calls this stored procedure to update the AspNet_SqlCacheTablesForChangeNotification table, therefore  indicating that the table has modified.

AspNet_SqlCacheQueryRegisteredTablesStoredProcedure :- This extracts just the table names from the AspNet_SqlCacheTablesForChangeNotification table. It is used to get a quick view at all the registered tables.

AspNet_SqlCachePollingStoredProcedure :- This will get  the  complete list of changes from the AspNet_SqlCacheTablesForChangeNotification table. It is used to perform the polling.

Enabling notification for individual tables

When the necessary stored procedure and tables are generated then we have to notify by saying which table needs to be enabled for notifications.

There are 2 ways to achieve this:-

  • aspnet_regsql -et -E -d Northwind -t Products
  • Exec spNet_SqlCacheRegisterTableStoredProcedure 'TableName'

Registering the tables for the notification internally generate trigger for the tables. For instance for a "products" table the following trigger is generated  So any modifications  to the "Products" table will update the "AspNet_SqlCacheNotification' table.

CREATE TRIGGER

dbo.[Products_AspNet_SqlCacheNotification_Trigger] ON

[Products]

FOR INSERT, UPDATE, DELETE

AS BEGIN SET NOCOUNT ON EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure

N'Products'

END

The "AspNet_SqlCacheTablesForChangeNotification" contains a single record for each  table you're monitoring. When you make a change in the table (like  inserting, updating, or deleting a record), the change Id column is incremented by one .ASP.NET queries this table continuously keeps track of the most recent changed values for each  table. When this value changes in a subsequent read, ASP.NET knows that the table has changed.

357_sql cache2.png

Figure : - Entries in the Cache notification table


Related Discussions:- Sql cache dependency

I want licensing system, Project Description: I'm presently seeking at l...

Project Description: I'm presently seeking at least one more coder that will help me out manage all my concepts and in that way we will share each other's experience and make te

Describe the three levels of data abstraction, Describe the three levels of...

Describe the three levels of data abstraction? The are three levels of abstraction: Physical level: The lowest level of abstraction explains how data are stored.  Logical

Application for online game, Introduction This assignment aims to give yo...

Introduction This assignment aims to give you a "real world experience" that occurs far too often in the workplace. You have been hired to complete as much as possible a project

Explain compare validator and range validator, Explain Compare Validator an...

Explain Compare Validator and Range Validator Compare Validator uses Control To Validate, Control To Compare and operator properties to compare a control's value with another

Dataset object, What is Dataset object ? The DataSet gives the basis fo...

What is Dataset object ? The DataSet gives the basis for the disconnected storage and manipulation of the relational data. We fill it from the  data store, work with it while d

Difference between authentication and authorization, What is the difference...

What is the difference between Authentication and authorization? This is  a tricky question. These two concepts seem altogether same  but there is big  difference. The Authenti

What is lazy initialization, What is lazy initialization? Lazy initiali...

What is lazy initialization? Lazy initialization is a process by which an object is not initialized until it is first called in your code. The .NET 4.0 introduces a new wrapper

I need programmer to extracting data, I need Programmer to extracting data ...

I need Programmer to extracting data Project Description: Programmer role A skilled programmer is required to build a standalone module to extract, parse and store in data

Develop a windows desktop application, Develop a windows desktop applicatio...

Develop a windows desktop application Project Description: I am looking to get a desktop application developed. The desktop application will have the subsequent few features:

What are the criteria necessary for an effective network, What are the crit...

What are the criteria necessary for an effective and efficient network? a. Performance It can be measured in several ways, including transmit time and response time. b

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd