Sql Cache dependency in Asp.Net

Question

I m loading table data into cache .And binding it to dropdown .But if there any new value added in datatable then that cache should invalid and new cache should get create.I am using Asp.Net 3.5 ,linq to SQL

Folloing is my .aspx code

public List<Employee> GetEmployeeData()
    {
        //get from cache
        object obj = HttpRuntime.Cache.Get(Constants.Constants.cacheEmployee);
        if (obj != null)
        {
            return obj as List<Employee>;
        }
        else // get from database
        {

                using (TestdbEntities entities = new TestdbEntities(connectionString))
                {
                    try
                    {

                        IEnumerable<Employee> employee= entities.Employees.OrderBy(l => l.EmployeeName);

                        System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(connectionString);

                        if (!System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connectionString).Contains             ("master.Employee"))

                            System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, "master.Employee");

                        System.Web.Caching.SqlCacheDependency sqldep = new System.Web.Caching.SqlCacheDependency(connectionString, "master.Employee"); ;

                        HttpRuntime.Cache.Insert(Constants.Constants.cacheEmployee, employee.ToList(), sqldep);

                        return employee.ToList();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }

But i am getting following error

Please make sure the database name and the table name are valid. Table names must conform to the format of regular identifiers in SQL.

Please suggest

Do you have a table called master.Employee in your database, the error implies you do not. — Coding Flow
– Coding Flow, Commented Nov 10, 2014 at 14:24
Haven't tried it but I would hazard a guess that SQL is parsing the master.Employee as referring to a table called Employee in the master system database. — PhilPursglove
– PhilPursglove, Commented Nov 10, 2014 at 15:50

jhoelz · Accepted Answer · 2015-09-22 14:15:07Z

The other answer is correct that the "." in the table name is the issue. For some reason the AspNet_SqlCacheRegisterTableStoredProcedure wasn't written to handle different schemas.

I must credit this link with cluing me into the issue: http://forums.asp.net/t/1249281.aspx?SQL+Cache+Dependency+with+custom+schema

However, I came up with my own re-write of the stored proc below that is a little better :) It's a little cleaner, handles the situation even if [ are included in the name, and also keeps the schema name in the AspNet_SqlCacheTablesForChangeNotification table in case you have the same table name in two different schemas.

DECLARE @triggerName AS NVARCHAR(3000) 
DECLARE @fullTriggerName AS NVARCHAR(3000)
DECLARE @canonTableName NVARCHAR(3000) 
DECLARE @quotedTableName NVARCHAR(3000) 
DECLARE @schemaName NVARCHAR(3000)
DECLARE @schemaAndTableName NVARCHAR(3000)

--Replace these if they are in the param, we'll add them back later for the @canonTableName only
SET @tableName = REPLACE(REPLACE(@tableName, '[', ''), ']', '')

IF(CHARINDEX('.',@tableName) <> 0)
    BEGIN
        SET @schemaName = SUBSTRING(@tableName,0,CHARINDEX('.',@tableName))
        SET @tableName =  SUBSTRING(@tableName,CHARINDEX('.',@tableName) + 1,LEN(@tableName) - CHARINDEX('.',@tableName))
        SET @schemaAndTableName = @schemaName + '.' + @tableName
    END
ELSE
    BEGIN
        SET @schemaName = 'dbo' --If the param doesn't have a schema, use the default
        SET @schemaAndTableName = @tableName --If they didn't pass in the schema, we don't want it in the AspNet_SqlCacheTablesForChangeNotification table
    END

SET @triggerName = @tableName + '_AspNet_SqlCacheNotification_Trigger'
SET @fullTriggerName = '[' + @schemaName + '].[' + @triggerName + ']'
SET @canonTableName = '[' + @schemaName + '].[' + @tableName + ']'                  

/* First make sure the table exists */ 
IF (SELECT OBJECT_ID(@schemaAndTableName, 'U')) IS NULL
BEGIN
   RAISERROR ('00000001', 16, 1)
   RETURN
END

BEGIN TRAN
    /* Insert the value into the notification table */ 
    IF NOT EXISTS (SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH (NOLOCK) WHERE tableName = @schemaAndTableName) 
     IF NOT EXISTS (SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH (TABLOCKX) WHERE tableName = @schemaAndTableName) 
         INSERT  dbo.AspNet_SqlCacheTablesForChangeNotification 
         VALUES (@schemaAndTableName, GETDATE(), 0)

    /* Create the trigger */ 
    SET @quotedTableName = QUOTENAME(@schemaAndTableName, '''') 
    IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = 'TR') 
     IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = 'TR') 
         EXEC('CREATE TRIGGER ' + @fullTriggerName + ' ON ' + @canonTableName +'
               FOR INSERT, UPDATE, DELETE AS BEGIN
               SET NOCOUNT ON
               EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N' + @quotedTableName + '
               END
               ')
COMMIT TRAN

PhilPursglove · Accepted Answer · 2014-11-10 23:11:46Z

The problem is the . in the table name master.Employee. The EnableTableForNotifications method calls the stored procedure AspNet_SqlCacheRegisterTableStoredProcedure in the database. Inside that is a call to the OBJECT_ID function, and I think that is parsing the table name as being a table called Employee in the master database. (When I first looked at this question, I was thinking the same way).

If you were to replace the . with an underscore _ in the table name, then your code will run correctly.

(You might also want to review your code in the case where the data isn't in the cache; data can disappear from the cache for any number of reasons but currently your code enables the database up for notifications every time, regardless of whether it's already enabled. The Single Responsibility Principle applies here to the GetEmployeeData method; it should only be concerned with getting data from the cache or database and not with setting up caching and notifications, that code should be broken out into a separate method.)

Collectives™ on Stack Overflow

Sql Cache dependency in Asp.Net

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related