protected override void DependencyDispose() { if (this._sql7DatabaseState != null) { SqlCacheDependencyManager.Release(this._sql7DatabaseState); } }
public SqlCacheDependency(string databaseEntryName, string tableName) : base(0, null, new string[] { GetDependKey(databaseEntryName, tableName) }) { this._sql7DatabaseState = SqlCacheDependencyManager.AddRef(databaseEntryName); this._sql7DepInfo._database = databaseEntryName; this._sql7DepInfo._table = tableName; object obj2 = HttpRuntime.CacheInternal[GetDependKey(databaseEntryName, tableName)]; if (obj2 == null) { this._sql7ChangeId = -1; } else { this._sql7ChangeId = (int)obj2; } base.FinishInit(); this.InitUniqueID(); }
private static string GetDependKey(string database, string tableName) { CheckPermission(); if (database == null) { throw new ArgumentNullException("database"); } if (tableName == null) { throw new ArgumentNullException("tableName"); } if (tableName.Length == 0) { throw new ArgumentException(System.Web.SR.GetString("Cache_null_table")); } string moniterKey = SqlCacheDependencyManager.GetMoniterKey(database, tableName); SqlCacheDependencyManager.EnsureTableIsRegisteredAndPolled(database, tableName); return(moniterKey); }
internal static void SetupNotifications(int flags, string table, string connectionString) { SqlConnection connection = null; SqlCommand command = null; bool flag = (flags & 9) != 0; bool flag2 = (flags & 2) != 0; if (flag) { bool flag3 = (flags & 8) != 0; if (table == null) { if (flag3) { throw new ArgumentException(System.Web.SR.GetString("Cache_null_table_in_tables"), "tables"); } throw new ArgumentNullException("table"); } if (table.Length == 0) { if (flag3) { throw new ArgumentException(System.Web.SR.GetString("Cache_null_table_in_tables"), "tables"); } throw new ArgumentException(System.Web.SR.GetString("Cache_null_table"), "table"); } } try { connection = new SqlConnection(connectionString); connection.Open(); command = new SqlCommand(null, connection); if (flag) { command.CommandText = !flag2 ? "dbo.AspNet_SqlCacheRegisterTableStoredProcedure" : "dbo.AspNet_SqlCacheUnRegisterTableStoredProcedure"; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new SqlParameter("@tableName", SqlDbType.NVarChar, table.Length)); command.Parameters[0].Value = table; } else if (!flag2) { command.CommandText = string.Format(CultureInfo.InvariantCulture, "/* Create notification table */ \nIF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{0}' AND type = 'U') \n IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{0}' AND type = 'U') \n CREATE TABLE dbo.{0} (\n tableName NVARCHAR(450) NOT NULL PRIMARY KEY,\n notificationCreated DATETIME NOT NULL DEFAULT(GETDATE()),\n changeId INT NOT NULL DEFAULT(0)\n )\n\n/* Create polling SP */\nIF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{1}' AND type = 'P') \n IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{1}' AND type = 'P') \n EXEC('CREATE PROCEDURE dbo.{1} AS\n SELECT tableName, changeId FROM dbo.{0}\n RETURN 0')\n\n/* Create SP for registering a table. */ \nIF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{2}' AND type = 'P') \n IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{2}' AND type = 'P') \n EXEC('CREATE PROCEDURE dbo.{2} \n @tableName NVARCHAR(450) \n AS\n BEGIN\n\n DECLARE @triggerName AS NVARCHAR(3000) \n DECLARE @fullTriggerName AS NVARCHAR(3000)\n DECLARE @canonTableName NVARCHAR(3000) \n DECLARE @quotedTableName NVARCHAR(3000) \n\n /* Create the trigger name */ \n SET @triggerName = REPLACE(@tableName, ''['', ''__o__'') \n SET @triggerName = REPLACE(@triggerName, '']'', ''__c__'') \n SET @triggerName = @triggerName + ''{3}'' \n SET @fullTriggerName = ''dbo.['' + @triggerName + '']'' \n\n /* Create the cannonicalized table name for trigger creation */ \n /* Do not touch it if the name contains other delimiters */ \n IF (CHARINDEX(''.'', @tableName) <> 0 OR \n CHARINDEX(''['', @tableName) <> 0 OR \n CHARINDEX('']'', @tableName) <> 0) \n SET @canonTableName = @tableName \n ELSE \n SET @canonTableName = ''['' + @tableName + '']'' \n\n /* First make sure the table exists */ \n IF (SELECT OBJECT_ID(@tableName, ''U'')) IS NULL \n BEGIN \n RAISERROR (''00000001'', 16, 1) \n RETURN \n END \n\n BEGIN TRAN\n /* Insert the value into the notification table */ \n IF NOT EXISTS (SELECT tableName FROM dbo.{0} WITH (NOLOCK) WHERE tableName = @tableName) \n IF NOT EXISTS (SELECT tableName FROM dbo.{0} WITH (TABLOCKX) WHERE tableName = @tableName) \n INSERT dbo.{0} \n VALUES (@tableName, GETDATE(), 0)\n\n /* Create the trigger */ \n SET @quotedTableName = QUOTENAME(@tableName, '''''''') \n IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = ''TR'') \n IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = ''TR'') \n EXEC(''CREATE TRIGGER '' + @fullTriggerName + '' ON '' + @canonTableName +''\n FOR INSERT, UPDATE, DELETE AS BEGIN\n SET NOCOUNT ON\n EXEC dbo.{6} N'' + @quotedTableName + ''\n END\n '')\n COMMIT TRAN\n END\n ')\n\n/* Create SP for updating the change Id of a table. */ \nIF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{6}' AND type = 'P') \n IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{6}' AND type = 'P') \n EXEC('CREATE PROCEDURE dbo.{6} \n @tableName NVARCHAR(450) \n AS\n\n BEGIN \n UPDATE dbo.{0} WITH (ROWLOCK) SET changeId = changeId + 1 \n WHERE tableName = @tableName\n END\n ')\n\n/* Create SP for unregistering a table. */ \nIF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{4}' AND type = 'P') \n IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{4}' AND type = 'P') \n EXEC('CREATE PROCEDURE dbo.{4} \n @tableName NVARCHAR(450) \n AS\n BEGIN\n\n BEGIN TRAN\n DECLARE @triggerName AS NVARCHAR(3000) \n DECLARE @fullTriggerName AS NVARCHAR(3000)\n SET @triggerName = REPLACE(@tableName, ''['', ''__o__'') \n SET @triggerName = REPLACE(@triggerName, '']'', ''__c__'') \n SET @triggerName = @triggerName + ''{3}'' \n SET @fullTriggerName = ''dbo.['' + @triggerName + '']'' \n\n /* Remove the table-row from the notification table */ \n IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = ''{0}'' AND type = ''U'') \n IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = ''{0}'' AND type = ''U'') \n DELETE FROM dbo.{0} WHERE tableName = @tableName \n\n /* Remove the trigger */ \n IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = ''TR'') \n IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = ''TR'') \n EXEC(''DROP TRIGGER '' + @fullTriggerName) \n\n COMMIT TRAN\n END\n ')\n\n/* Create SP for querying all registered table */ \nIF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{5}' AND type = 'P') \n IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{5}' AND type = 'P') \n EXEC('CREATE PROCEDURE dbo.{5} \n AS\n SELECT tableName FROM dbo.{0} ')\n\n/* Create roles and grant them access to SP */ \nIF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole = 1 AND name = N'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess') \n EXEC sp_addrole N'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess' \n\nGRANT EXECUTE ON dbo.{1} to aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess\n\n", new object[] { "AspNet_SqlCacheTablesForChangeNotification", "AspNet_SqlCachePollingStoredProcedure", "AspNet_SqlCacheRegisterTableStoredProcedure", "_AspNet_SqlCacheNotification_Trigger", "AspNet_SqlCacheUnRegisterTableStoredProcedure", "AspNet_SqlCacheQueryRegisteredTablesStoredProcedure", "AspNet_SqlCacheUpdateChangeIdStoredProcedure" }); command.CommandType = CommandType.Text; } else { command.CommandText = string.Format(CultureInfo.InvariantCulture, "/* Remove notification table */ \nIF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{0}' AND type = 'U') \n IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{0}' AND type = 'U') \n BEGIN\n /* First, unregister all registered tables */ \n DECLARE tables_cursor CURSOR FOR \n SELECT tableName FROM dbo.{0} \n DECLARE @tableName AS NVARCHAR(450) \n\n OPEN tables_cursor \n\n /* Perform the first fetch. */ \n FETCH NEXT FROM tables_cursor INTO @tableName \n\n /* Check @@FETCH_STATUS to see if there are any more rows to fetch. */ \n WHILE @@FETCH_STATUS = 0 \n BEGIN \n EXEC {3} @tableName \n\n /* This is executed as long as the previous fetch succeeds. */ \n FETCH NEXT FROM tables_cursor INTO @tableName \n END \n CLOSE tables_cursor \n DEALLOCATE tables_cursor \n\n /* Drop the table */\n DROP TABLE dbo.{0} \n END\n\n/* Remove polling SP */ \nIF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{1}' AND type = 'P') \n IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{1}' AND type = 'P') \n DROP PROCEDURE dbo.{1} \n\n/* Remove SP that registers a table */ \nIF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{2}' AND type = 'P') \n IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{2}' AND type = 'P') \n DROP PROCEDURE dbo.{2} \n\n/* Remove SP that unregisters a table */ \nIF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{3}' AND type = 'P') \n IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{3}' AND type = 'P') \n DROP PROCEDURE dbo.{3} \n\n/* Remove SP that querys the registered table */ \nIF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{4}' AND type = 'P') \n IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{4}' AND type = 'P') \n DROP PROCEDURE dbo.{4} \n\n/* Remove SP that updates the change Id of a table. */ \nIF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{5}' AND type = 'P') \n IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{5}' AND type = 'P') \n DROP PROCEDURE dbo.{5} \n\n/* Drop roles */ \nIF EXISTS ( SELECT name FROM sysusers WHERE issqlrole = 1 AND name = 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess') BEGIN\nCREATE TABLE #aspnet_RoleMembers \n( \n Group_name sysname, \n Group_id smallint, \n Users_in_group sysname, \n User_id smallint \n) \nINSERT INTO #aspnet_RoleMembers \nEXEC sp_helpuser 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess' \n \nDECLARE @user_id smallint \nDECLARE @cmd nvarchar(500) \nDECLARE c1 CURSOR FORWARD_ONLY FOR \n SELECT User_id FROM #aspnet_RoleMembers \n \nOPEN c1 \n \nFETCH c1 INTO @user_id \nWHILE (@@fetch_status = 0) \nBEGIN \n SET @cmd = 'EXEC sp_droprolemember ''aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess'',''' + USER_NAME(@user_id) + '''' \n EXEC (@cmd) \n FETCH c1 INTO @user_id \nEND \n \nclose c1 \ndeallocate c1 \n EXEC sp_droprole 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess'\nEND\n", new object[] { "AspNet_SqlCacheTablesForChangeNotification", "AspNet_SqlCachePollingStoredProcedure", "AspNet_SqlCacheRegisterTableStoredProcedure", "AspNet_SqlCacheUnRegisterTableStoredProcedure", "AspNet_SqlCacheQueryRegisteredTablesStoredProcedure", "AspNet_SqlCacheUpdateChangeIdStoredProcedure" }); command.CommandType = CommandType.Text; } command.ExecuteNonQuery(); command.CommandText = string.Empty; if (HttpRuntime.IsAspNetAppDomain) { SqlCacheDependencyManager.UpdateAllDatabaseNotifState(); } } catch (Exception exception) { string str2; SqlException exception2 = exception as SqlException; bool flag4 = true; if (exception2 != null) { if (exception2.Number == 0xafc) { if (!flag2) { if (table != null) { throw new DatabaseNotEnabledForNotificationException(System.Web.SR.GetString("Database_not_enabled_for_notification", new object[] { connection.Database })); } throw; } if (table != null) { throw new DatabaseNotEnabledForNotificationException(System.Web.SR.GetString("Cant_disable_table_sql_cache_dep")); } flag4 = false; } else { if (((exception2.Number == 0xe5) || (exception2.Number == 0x106)) || ((exception2.Number == 0xac8) || (exception2.Number == 0x1205))) { string str; if (!flag2) { if (table != null) { str = "Permission_denied_table_enable_notification"; } else { str = "Permission_denied_database_enable_notification"; } } else if (table != null) { str = "Permission_denied_table_disable_notification"; } else { str = "Permission_denied_database_disable_notification"; } if (table != null) { throw new HttpException(System.Web.SR.GetString(str, new object[] { table })); } throw new HttpException(System.Web.SR.GetString(str)); } if ((exception2.Number == 0xc350) && (exception2.Message == "00000001")) { throw new HttpException(System.Web.SR.GetString("Cache_dep_table_not_found", new object[] { table })); } } } if ((command != null) && (command.CommandText.Length != 0)) { str2 = System.Web.SR.GetString("Cant_connect_sql_cache_dep_database_admin_cmdtxt", new object[] { command.CommandText }); } else { str2 = System.Web.SR.GetString("Cant_connect_sql_cache_dep_database_admin"); } if (flag4) { throw new HttpException(str2, exception); } } finally { if (connection != null) { connection.Close(); } } }