SqlCacheDependency.cs source code in C# .NET

Source code for the .NET framework in C#

                        

Code:

/ 4.0 / 4.0 / untmp / DEVDIV_TFS / Dev10 / Releases / RTMRel / ndp / fx / src / xsp / System / Web / Cache / SqlCacheDependency.cs / 1305376 / SqlCacheDependency.cs

                            //------------------------------------------------------------------------------ 
// 
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// 
//----------------------------------------------------------------------------- 

/* 
 * SqlCacheDepModule 
 *
 * Copyright (c) 1998-1999, Microsoft Corporation 
 *
 */

namespace System.Web.Caching { 

    using System; 
    using System.Threading; 
    using System.Collections;
    using System.Configuration; 
    using System.IO;
    using System.Web.Caching;
    using System.Web.Util;
    using System.Web.Configuration; 
    using System.Xml;
    using System.Data; 
    using System.Data.SqlClient; 
    using System.Globalization;
    using System.Security.Permissions; 
    using System.Text;
    using System.Runtime.InteropServices;
    using System.EnterpriseServices;
    using System.Web.UI; 
    using System.Web.DataAccess;
    using System.Security.Principal; 
    using System.Web.Hosting; 
    using System.Runtime.Serialization;
    using System.Web.Management; 
    using System.Security;

    public sealed class SqlCacheDependency : CacheDependency {
 
        internal static bool        s_hasSqlClientPermission;
        internal static bool        s_hasSqlClientPermissionInited; 
 
        const string SQL9_CACHE_DEPENDENCY_DIRECTIVE = "CommandNotification";
        internal const string SQL9_OUTPUT_CACHE_DEPENDENCY_COOKIE = "MS.SqlDependencyCookie"; 

        SqlDependency       _sqlYukonDep;       // SqlDependency for Yukon
        DatabaseNotifState  _sql7DatabaseState;     // Database state for SQL7/2000
        string              _uniqueID;              // used by HttpCachePolicy for the ETag 
#if DBG
        bool                _isUniqueIDInitialized; 
#endif 

        struct Sql7DependencyInfo { 
            internal string  _database;
            internal string  _table;
        }
 
        // For generating Unique Id
        Sql7DependencyInfo  _sql7DepInfo; 
        int                 _sql7ChangeId; 

 
        // For Non-SQL9 SQL servers, we create a dependency based on an internal cached item.

        public SqlCacheDependency(string databaseEntryName, string tableName)
        :base(0, null, new string[1] {GetDependKey(databaseEntryName, tableName)}) 
        {
            Debug.Trace("SqlCacheDependency", 
                            "Depend on key=" + GetDependKey(databaseEntryName, tableName) + "; value=" + 
                            HttpRuntime.CacheInternal[GetDependKey(databaseEntryName, tableName)]);
 
            // Permission checking is done in GetDependKey()

            _sql7DatabaseState = SqlCacheDependencyManager.AddRef(databaseEntryName);
            _sql7DepInfo._database = databaseEntryName; 
            _sql7DepInfo._table = tableName;
 
            object o = HttpRuntime.CacheInternal[GetDependKey(databaseEntryName, tableName)]; 
            if (o == null) {
                // If the cache entry can't be found, this cache dependency will be set to CHANGED already. 
                _sql7ChangeId = -1;
            }
            else {
                // Note that if the value in the cache changed between the base ctor and here, even though 
                // we get a wrong unqiue Id, but it's okay because that change will cause the CacheDependency's
                // state to become CHANGED and any cache operation using this CacheDependency will fail anyway. 
                _sql7ChangeId = (int)o; 
            }
 
            // The ctor of every class derived from CacheDependency must call this.
            FinishInit();

            InitUniqueID(); 
        }
 
        protected override void DependencyDispose() { 
            if (_sql7DatabaseState != null) {
                SqlCacheDependencyManager.Release(_sql7DatabaseState); 
            }
        }

        // For SQL9, we use SqlDependency 
        public SqlCacheDependency(SqlCommand sqlCmd) {
            HttpContext context = HttpContext.Current; 
 
            if (sqlCmd == null) {
                throw new ArgumentNullException("sqlCmd"); 
            }

            // Prevent a conflict between using SQL9 outputcache and an explicit
            // SQL9 SqlCacheDependency at the same time.  See VSWhidey 396429 and 
            // the attached email in the bug.
            if (context != null && context.SqlDependencyCookie != null &&  // That means We have already setup SQL9 dependency for output cache 
                sqlCmd.NotificationAutoEnlist) {    // This command will auto-enlist in that output cache dependency 
                throw new HttpException(SR.GetString(SR.SqlCacheDependency_OutputCache_Conflict));
            } 

            CreateSqlDep(sqlCmd);

            InitUniqueID(); 
        }
 
        void InitUniqueID() { 
            if (_sqlYukonDep != null) {
                // Yukon does not provide us with an ID, so we'll use a Guid. 
                _uniqueID = Guid.NewGuid().ToString("N", CultureInfo.InvariantCulture);
            }
            else if (_sql7ChangeId == -1) {
                // The database/tablen entry can't be found in the cache.  That means SQL doesn't have 
                // this database/table registered for sql cache dependency.  In this case, we can't
                // generate a unique id. 
                _uniqueID = null; 
            }
            else { 
                _uniqueID = _sql7DepInfo._database + ":" + _sql7DepInfo._table + ":" + _sql7ChangeId.ToString(CultureInfo.InvariantCulture);
            }
#if DBG
            _isUniqueIDInitialized = true; 
#endif
        } 
 
        public override string GetUniqueID() {
#if DBG 
            Debug.Assert(_isUniqueIDInitialized == true, "_isUniqueIDInitialized == true");
#endif
            return _uniqueID;
        } 

        private static void CheckPermission() { 
            if (!s_hasSqlClientPermissionInited) { 
                if (!System.Web.Hosting.HostingEnvironment.IsHosted) {
                    try { 
                        new SqlClientPermission(PermissionState.Unrestricted).Demand();
                        s_hasSqlClientPermission = true;
                    }
                    catch (SecurityException) {} 
                }
                else { 
                    s_hasSqlClientPermission = Permission.HasSqlClientPermission(); 
                }
 
                s_hasSqlClientPermissionInited = true;
            }

            if (!s_hasSqlClientPermission) { 
                throw new HttpException(SR.GetString(SR.SqlCacheDependency_permission_denied));
            } 
        } 

        void OnSQL9SqlDependencyChanged(Object sender, SqlNotificationEventArgs e) { 
            Debug.Trace("SqlCacheDependency", "SQL9 dependency changed: depId=" + _sqlYukonDep.Id);
            NotifyDependencyChanged(sender, e);
        }
 
        private SqlCacheDependency() {
            CreateSqlDep(null); 
            InitUniqueID(); 
        }
 
        void CreateSqlDep(SqlCommand sqlCmd) {
            _sqlYukonDep = new SqlDependency();

            // Note: sqlCmd is null in output cache case. 

            if (sqlCmd != null) { 
                Debug.Trace("SqlCacheDependency", "SqlCmd added to SqlDependency object"); 
                _sqlYukonDep.AddCommandDependency(sqlCmd);
            } 

            _sqlYukonDep.OnChange += new OnChangeEventHandler(OnSQL9SqlDependencyChanged);

            Debug.Trace("SqlCacheDependency", "SQL9 dependency created: depId=" + _sqlYukonDep.Id); 
        }
 
        internal static void ValidateOutputCacheDependencyString(string depString, bool page) { 
            if (depString == null) {
                throw new HttpException(SR.GetString(SR.Invalid_sqlDependency_argument, depString)); 
            }

            if (StringUtil.EqualsIgnoreCase(depString, SQL9_CACHE_DEPENDENCY_DIRECTIVE)) {
                if (!page) { 
                    // It's impossible for only a page, but not its controls, to use Yukon Cache Dependency; neither
                    // can the opposite scenario possible.  It's because once we create a SqlDependency and 
                    // stick it to the context, it's complicated (but not impossible) to clear it when rendering 
                    // the parts (either a page or a control) that doesn't depend on Yukon.
                    // To keep things simple, we restrict Yukon Cache Dependency only to page. 
                    throw new HttpException(
                        SR.GetString(SR.Attrib_Sql9_not_allowed));
                }
            } 
            else {
                // It's for non-SQL 9 scenario. 
                ParseSql7OutputCacheDependency(depString); 
            }
        } 

        public static CacheDependency CreateOutputCacheDependency(string dependency) {
            if (dependency == null) {
                throw new HttpException(SR.GetString(SR.Invalid_sqlDependency_argument, dependency)); 
            }
 
            if (StringUtil.EqualsIgnoreCase(dependency, SQL9_CACHE_DEPENDENCY_DIRECTIVE)) { 
                HttpContext context = HttpContext.Current;
                Debug.Assert(context != null); 

                SqlCacheDependency  dep = new SqlCacheDependency();

                Debug.Trace("SqlCacheDependency", "Setting depId=" + dep._sqlYukonDep.Id); 
                context.SqlDependencyCookie = dep._sqlYukonDep.Id;
 
                return dep; 
            }
            else { 
                ArrayList                   sqlDependencies;
                AggregateCacheDependency    aggr = null;
                Sql7DependencyInfo           info;
 
                sqlDependencies = ParseSql7OutputCacheDependency(dependency);
 
                // ParseSql7OutputCacheDependency will throw if we cannot find a single entry 
                Debug.Assert(sqlDependencies.Count > 0, "sqlDependencies.Count > 0");
 
                Debug.Trace("SqlCacheDependency", "Creating SqlCacheDependency for SQL8 output cache");

                if (sqlDependencies.Count == 1) {
                    info = (Sql7DependencyInfo)sqlDependencies[0]; 
                    return CreateSql7SqlCacheDependencyForOutputCache(info._database, info._table, dependency);
                } 
 
                aggr = new AggregateCacheDependency();
 
                for(int i=0; i < sqlDependencies.Count; i++) {
                    info = (Sql7DependencyInfo)sqlDependencies[i];
                    aggr.Add(CreateSql7SqlCacheDependencyForOutputCache(info._database, info._table, dependency));
                } 

                return aggr; 
            } 
        }
 
        static SqlCacheDependency CreateSql7SqlCacheDependencyForOutputCache(string database, string table, string depString) {
            try {
                return new SqlCacheDependency(database, table);
            } 
            catch (HttpException e) {
                HttpException outerException = new HttpException( 
                       SR.GetString(SR.Invalid_sqlDependency_argument2, depString, e.Message), e); 

                outerException.SetFormatter(new UseLastUnhandledErrorFormatter(outerException)); 

                throw outerException;
            }
        } 

        static string GetDependKey(string database, string tableName) { 
 
            // This is called by ctor SqlCacheDependency(string databaseEntryName, string tableName)
            // before the body of that ctor is executed.  So we have to make sure the app has 
            // the right permission here.
            CheckPermission();

            // First is to check whether Sql cache polling is enabled in config or not. 
            if (database == null) {
                throw new ArgumentNullException("database"); 
            } 

            if (tableName == null) { 
                throw new ArgumentNullException("tableName");
            }

            if (tableName.Length == 0) { 
                throw new ArgumentException(SR.GetString(SR.Cache_null_table));
            } 
 
            string  monitorKey = SqlCacheDependencyManager.GetMoniterKey(database, tableName);
 
            // Make sure the table is already registered with the database and
            // we've polled the database at least once, so that there is an
            // entry in the cache.
            SqlCacheDependencyManager.EnsureTableIsRegisteredAndPolled(database, tableName); 
            return monitorKey;
        } 
 
        static string VerifyAndRemoveEscapeCharacters(string s) {
            int     i; 
            bool    escape = false;

            for (i=0; i < s.Length; i++) {
                if (escape) { 
                    if (s[i] != '\\' && s[i] != ':' && s[i] != ';') {
                        // Only '\\', '\:' and '\;' are allowed 
                        throw new ArgumentException(); 
                    }
                    escape = false; 
                    continue;
                }

                if (s[i] == '\\') { 
                    if (i+1 == s.Length) {
                        // No character following escape char 
                        throw new ArgumentException(); 
                    }
                    escape = true; 
                    s = s.Remove(i, 1);
                    i--;
                }
            } 

            return s; 
        } 

        internal static ArrayList ParseSql7OutputCacheDependency(string outputCacheString) { 
            // The database and the table name are separated by a ":".  If the name
            // contains a ":" character, specify it by doing "\:"
            // Pairs of entries are separated by a ";"
            bool        escape = false; 
            int         iDatabaseStart = 0;
            int         iTableStart = -1; 
            string      database = null;        // The database portion of the pair 
            ArrayList   dependencies = null;
            int         len; 
            Sql7DependencyInfo   info;

            try {
                for (int i = 0; i < outputCacheString.Length+1; i++) { 
                    if (escape) {
                        escape = false; 
                        continue; 
                    }
 
                    if (i != outputCacheString.Length && outputCacheString[i] == '\\') {
                        escape = true;
                        continue;
                    } 

                    // We have reached ';' or the end of the string 
                    if (i == outputCacheString.Length || outputCacheString[i] == ';' ) { 
                        if (database==null) {
                            // No database name 
                            throw new ArgumentException();
                        }

                        // Get the lenght of the table portion 
                        len = i - iTableStart;
                        if (len == 0) { 
                            // No table name 
                            throw new ArgumentException();
                        } 

                        info = new Sql7DependencyInfo();
                        info._database = VerifyAndRemoveEscapeCharacters(database);
                        info._table = VerifyAndRemoveEscapeCharacters(outputCacheString.Substring(iTableStart, len)); 

                        if (dependencies == null) { 
                            dependencies = new ArrayList(1); 
                        }
 
                        dependencies.Add(info);

                        // Reset below values.  We are searching for the next pair.
                        iDatabaseStart = i+1; 
                        database = null;
                    } 
 
                    // Have we reached the end of the string?
                    if (i == outputCacheString.Length) { 
                        break;
                    }

                    if (outputCacheString[i] == ':') { 
                        if (database != null) {
                            // We have already got the database portion 
                            throw new ArgumentException(); 
                        }
 
                        // Do we get the database part?
                        len = i - iDatabaseStart;
                        if (len == 0) {
                            // No database name 
                            throw new ArgumentException();
                        } 
 
                        database = outputCacheString.Substring(iDatabaseStart, len);
                        iTableStart = i+1; 
                        continue;
                    }
                }
 
                return dependencies;
 
            } 
            catch (ArgumentException) {
                throw new ArgumentException(SR.GetString(SR.Invalid_sqlDependency_argument, outputCacheString)); 
            }
        }
    }
 

    [Serializable()] 
    public sealed class DatabaseNotEnabledForNotificationException : SystemException { 

        public DatabaseNotEnabledForNotificationException() { 
        }


        public DatabaseNotEnabledForNotificationException(String message) 
        : base(message) {
        } 
 

        public DatabaseNotEnabledForNotificationException(string message, Exception innerException) 
        : base (message, innerException) {
        }

 
        internal DatabaseNotEnabledForNotificationException(SerializationInfo info, StreamingContext context)
        : base(info, context) { 
        } 

    } 


    [Serializable()]
    public sealed class TableNotEnabledForNotificationException : SystemException { 

        public TableNotEnabledForNotificationException() { 
        } 

 
        public TableNotEnabledForNotificationException(String message)
        : base(message) {
        }
 

        public TableNotEnabledForNotificationException(string message, Exception innerException) 
        : base (message, innerException) { 
        }
 

        internal TableNotEnabledForNotificationException(SerializationInfo info, StreamingContext context)
        : base(info, context) {
        } 

    } 
 
    // A class to store the state of a timer for a specific database
    internal class DatabaseNotifState : IDisposable { 
        internal string         _database;
        internal string         _connectionString;
        internal int            _rqInCallback;
        internal bool           _notifEnabled;     // true means the ChangeNotif table was found in the database 
        internal bool           _init;      // true means timer callback was called at least once
        internal Timer          _timer; 
        internal Hashtable      _tables; // Names of all the tables registered for notification 
        internal Exception      _pollExpt;
        internal int            _pollSqlError; 
        internal SqlConnection  _sqlConn;
        internal SqlCommand     _sqlCmd;
        internal bool           _poolConn;
        internal DateTime       _utcTablesUpdated; // Time when _tables was last updated 
        internal int            _refCount = 0;
 
        public void Dispose() { 
            if (_sqlConn != null) {
                _sqlConn.Close(); 
                _sqlConn = null;
            }

            if (_timer != null) { 
                _timer.Dispose();
                _timer = null; 
            } 
        }
 
        internal DatabaseNotifState(string database, string connection, int polltime) {
            _database = database;
            _connectionString = connection;
            _timer = null; 
            _tables = new Hashtable();
            _pollExpt = null; 
            _utcTablesUpdated = DateTime.MinValue; 

            // We will pool the connection if the polltime is less than 5 s. 
            if (polltime <= 5000) {
                _poolConn = true;
            }
 
        }
 
        internal void GetConnection(out SqlConnection sqlConn, out SqlCommand sqlCmd) { 
            sqlConn = null;
            sqlCmd = null; 

            // !!! Please note that GetConnection and ReleaseConnection does NOT support
            // multithreading.  The caller must do the locking.
 
            if (_sqlConn != null) {
                // We already have a pooled connection. 
                Debug.Assert(_poolConn, "_poolConn"); 
                Debug.Assert(_sqlCmd != null, "_sqlCmd != null");
 
                sqlConn = _sqlConn;
                sqlCmd = _sqlCmd;

                _sqlConn = null; 
                _sqlCmd = null;
            } 
            else { 
                SqlConnectionHolder holder = null;
 
                try {
                    holder = SqlConnectionHelper.GetConnection(_connectionString, true);

                    sqlCmd = new SqlCommand(SqlCacheDependencyManager.SQL_POLLING_SP_DBO, holder.Connection); 

                    sqlConn = holder.Connection; 
                } 
                catch {
                    if (holder != null) { 
                        holder.Close();
                        holder = null;
                    }
 
                    sqlCmd = null;
 
                    throw; 
                }
            } 
        }

        internal void ReleaseConnection(ref SqlConnection sqlConn, ref SqlCommand sqlCmd, bool error) {
            // !!! Please note that GetConnection and ReleaseConnection does NOT support 
            // multithreading.  The caller must do the locking.
 
            if (sqlConn == null) { 
                Debug.Assert(sqlCmd == null, "sqlCmd == null");
                return; 
            }

            Debug.Assert(sqlCmd != null, "sqlCmd != null");
 
            if (_poolConn && !error) {
                _sqlConn = sqlConn; 
                _sqlCmd = sqlCmd; 
            }
            else { 
                sqlConn.Close();
            }

            sqlConn = null; 
            sqlCmd = null;
        } 
    } 

    internal static class SqlCacheDependencyManager{ 

        internal const bool     ENABLED_DEFAULT = true;
        internal const int      POLLTIME_DEFAULT = 60000;
        internal const int      TABLE_NAME_LENGTH = 128; 

        internal const int      SQL_EXCEPTION_SP_NOT_FOUND = 2812; 
        internal const int      SQL_EXCEPTION_PERMISSION_DENIED_ON_OBJECT = 229; 
        internal const int      SQL_EXCEPTION_PERMISSION_DENIED_ON_DATABASE = 262;
        internal const int      SQL_EXCEPTION_PERMISSION_DENIED_ON_USER = 2760; 
        internal const int      SQL_EXCEPTION_NO_GRANT_PERMISSION = 4613;
        internal const int      SQL_EXCEPTION_ADHOC = 50000;

        const char              CacheKeySeparatorChar = ':'; 
        const string            CacheKeySeparator = ":";
        const string            CacheKeySeparatorEscaped = "\\:"; 
 
        internal const string   SQL_CUSTOM_ERROR_TABLE_NOT_FOUND = "00000001";
 
        internal const string   SQL_NOTIF_TABLE =
                                "AspNet_SqlCacheTablesForChangeNotification";

        internal const string   SQL_POLLING_SP = 
                                "AspNet_SqlCachePollingStoredProcedure";
 
        internal const string   SQL_POLLING_SP_DBO = 
                                "dbo.AspNet_SqlCachePollingStoredProcedure";
 
        internal static TimeSpan    OneSec = new TimeSpan(0, 0, 1);

        internal static Hashtable   s_DatabaseNotifStates = new Hashtable();
        static TimerCallback        s_timerCallback = new TimerCallback(PollCallback); 
        static int                  s_activePolling = 0;
        static bool                 s_shutdown = false; 
 
        static internal string GetMoniterKey(string database, string table) {
            if (database.IndexOf(CacheKeySeparatorChar) != -1) { 
                database = database.Replace(CacheKeySeparator, CacheKeySeparatorEscaped);
            }

            if (table.IndexOf(CacheKeySeparatorChar) != -1) { 
                table = table.Replace(CacheKeySeparator, CacheKeySeparatorEscaped);
            } 
 
            // If we don't escape our separator char (':') in database and table,
            // these two pairs of inputs will then generate the same key: 
            // 1. database = "b", table = "b:b"
            // 2. database = "b:b", table = "b"
            return CacheInternal.PrefixSqlCacheDependency + database + CacheKeySeparator + table;
        } 

        static internal void Dispose(int waitTimeoutMs) { 
            try { 
                DateTime waitLimit = DateTime.UtcNow.AddMilliseconds(waitTimeoutMs);
 
                Debug.Assert(s_shutdown != true, "s_shutdown != true");
                Debug.Trace("SqlCacheDependencyManager", "Dispose is called");

                s_shutdown = true; 

                if (s_DatabaseNotifStates != null && s_DatabaseNotifStates.Count > 0) { 
                    // Lock it because InitPolling could be modifying it. 
                    lock(s_DatabaseNotifStates) {
                        foreach(DictionaryEntry entry in s_DatabaseNotifStates) { 
                            object  obj = entry.Value;
                            if (obj != null) {
                                ((DatabaseNotifState)obj).Dispose();
                            } 
                        }
                    } 
 
                    for (;;) {
                        if (s_activePolling == 0) 
                            break;

                        Thread.Sleep(250);
 
                        // only apply timeout if a managed debugger is not attached
                        if (!System.Diagnostics.Debugger.IsAttached && DateTime.UtcNow > waitLimit) { 
                            break; // give it up 
                        }
                    } 
                }
            }
            catch {
                // It's called by HttpRuntime.Dispose. It can't throw anything. 
                return;
            } 
        } 

        internal static SqlCacheDependencyDatabase GetDatabaseConfig(string database) { 
            SqlCacheDependencySection config = RuntimeConfig.GetAppConfig().SqlCacheDependency;
            object  obj;

            obj =  config.Databases[database]; 
            if (obj == null) {
                throw new HttpException(SR.GetString(SR.Database_not_found, database)); 
            } 

            return  (SqlCacheDependencyDatabase)obj; 
        }

        // Initialize polling for a database.  It will:
        // 1. Create the DatabaseNotifState that holds the polling status about this database. 
        // 2. Create the timer to poll.
        internal static void InitPolling(string database) { 
            SqlCacheDependencySection config = RuntimeConfig.GetAppConfig().SqlCacheDependency;; 
            SqlCacheDependencyDatabase  sqlDepDB;
            string connectionString; 

            Debug.Trace("SqlCacheDependencyManager",
                                "InitPolling is called.  Database=" + database);
 
            // Return if polling isn't even enabled.
            if (!config.Enabled) { 
                throw new ConfigurationErrorsException( 
                    SR.GetString(SR.Polling_not_enabled_for_sql_cache),
                    config.ElementInformation.Properties["enabled"].Source, config.ElementInformation.Properties["enabled"].LineNumber); 
            }

            // Return if the polltime is zero.  It means polling is disabled for this database.
            sqlDepDB = GetDatabaseConfig(database); 
            if (sqlDepDB.PollTime == 0) {
                throw new ConfigurationErrorsException( 
                    SR.GetString(SR.Polltime_zero_for_database_sql_cache, database), 
                    sqlDepDB.ElementInformation.Properties["pollTime"].Source, sqlDepDB.ElementInformation.Properties["pollTime"].LineNumber);
            } 

            if (s_DatabaseNotifStates.ContainsKey(database)) {
                // Someone has already started the timer for this database.
                Debug.Trace("SqlCacheDependencyManager", 
                                "InitPolling: Timer already started for " + database);
 
                return; 
            }
 
            connectionString = SqlConnectionHelper.GetConnectionString(sqlDepDB.ConnectionStringName, true, true);
            if (connectionString == null || connectionString.Length < 1) {
                throw new ConfigurationErrorsException(
                    SR.GetString(SR.Connection_string_not_found, sqlDepDB.ConnectionStringName), 
                    sqlDepDB.ElementInformation.Properties["connectionStringName"].Source, sqlDepDB.ElementInformation.Properties["connectionStringName"].LineNumber);
            } 
 
            lock(s_DatabaseNotifStates) {
                DatabaseNotifState   state; 

                if (s_DatabaseNotifStates.ContainsKey(database)) {
                    // Someone has already started the timer for this database.
                    Debug.Trace("SqlCacheDependencyManager", 
                                "InitPolling: Timer already started for " + database);
 
                    return; 
                }
 
                Debug.Trace("SqlCacheDependencyManager",
                                "InitPolling: Creating timer for " + database);

                state = new DatabaseNotifState(database, connectionString, sqlDepDB.PollTime); 
                state._timer = new Timer(s_timerCallback, state, 0 /* dueTime */, sqlDepDB.PollTime /* period */);
 
                s_DatabaseNotifStates.Add(database, state); 
            }
        } 

        // Timer callback function.
        static void PollCallback(object state) {
            using (new ApplicationImpersonationContext()) { 
                PollDatabaseForChanges((DatabaseNotifState)state, true /*fromTimer*/);
            } 
        } 

        // Query all the entries from the AspNet_SqlCacheTablesForChangeNotification 
        // table and update the values in the cache accordingly.
        //
        // This is mainly called by the timer callback.  But will also be called by
        // UpdateDatabaseNotifState, which polls for changes on demand. 
        internal static void PollDatabaseForChanges(DatabaseNotifState dbState, bool fromTimer) {
            SqlDataReader       sqlReader = null; 
            SqlConnection       sqlConn = null; 
            SqlCommand          sqlCmd = null;
            int                 changeId; 
            string              tableName;
            CacheInternal       cacheInternal = HttpRuntime.CacheInternal;
            string              monitorKey;
            object              obj; 
            bool                notifEnabled = false;
            Exception           pollExpt = null; 
            SqlException        sqlExpt = null; 

            Debug.Trace("SqlCacheDependencyManagerPolling", 
                "PollCallback called; connection=" + dbState._connectionString);

            if (s_shutdown) {
                return; 
            }
 
            // If this call is from a timer, and if the refcount for this database is zero, 
            // we will ignore it. The exception is if dbState._init == false,
            // which means the timer is polling it for the first time. 
            if (dbState._refCount == 0 && fromTimer && dbState._init  ) {
                Debug.Trace("SqlCacheDependencyManagerPolling",
                    "PollCallback ignored for " + dbState._database + " because refcount is 0");
                return; 
            }
 
            // Grab the lock, which allows only one thread to enter this method. 
            if (Interlocked.CompareExchange(ref dbState._rqInCallback, 1, 0) != 0) {
 
                // We can't get the lock.

                if (!fromTimer) {
                    // A non-timer caller will really want to make a call to SQL and 
                    // get the result.  So if another thread is calling this, we'll
                    // wait for it to be done. 
                    int         timeout; 
                    HttpContext context = HttpContext.Current;
 
                    if (context == null) {
                        timeout = 30;
                    }
                    else { 
                        timeout = Math.Max(context.Timeout.Seconds / 3, 30);
                    } 
                    DateTime waitLimit = DateTime.UtcNow.Add(new TimeSpan(0, 0, timeout)); 

                    for (;;) { 
                        if (Interlocked.CompareExchange(ref dbState._rqInCallback, 1, 0) == 0) {
                            break;
                        }
 
                        Thread.Sleep(250);
 
                        if (s_shutdown) { 
                            return;
                        } 

                        // only apply timeout if a managed debugger is not attached
                        if (!System.Diagnostics.Debugger.IsAttached && DateTime.UtcNow > waitLimit) {
                            // We've waited and retried for 5 seconds. 
                            // Somehow PollCallback haven't finished its first call for this database
                            // Assume we cannot connect to SQL. 
                            throw new HttpException( 
                                SR.GetString(SR.Cant_connect_sql_cache_dep_database_polling, dbState._database));
                        } 
                    }
                }
                else {
                    // For a timer callback, if another thread is updating the data for 
                    // this database, this thread will just leave and let that thread
                    // finish the update job. 
                    Debug.Trace("SqlCacheDependencyManagerPolling", 
                        "PollCallback returned because another thread is updating the data");
                    return; 
                }
            }

            try { 
                try {
                    // Keep a count on how many threads are polling right now 
                    // This counter is used by Dispose() 
                    Interlocked.Increment(ref s_activePolling);
 
                    // The below assert was commented out because this method is either
                    // called by a timer thread, or thru the SqlCacheDependencyAdmin APIs.
                    // In the latter case, the caller should have the permissions.
                    //(new SqlClientPermission(PermissionState.Unrestricted)).Assert(); 

                    dbState.GetConnection(out sqlConn, out sqlCmd); 
                    sqlReader = sqlCmd.ExecuteReader(); 

                    // If we got stuck for a long time in the ExecuteReader above, 
                    // Dispose() may have given up already while waiting for this thread to finish
                    if (s_shutdown) {
                        return;
                    } 

                    // ExecuteReader() succeeded, and that means we at least have found the notification table. 
                    notifEnabled = true; 

                    // Remember the original list of tables that are enabled 
                    Hashtable   originalTables = (Hashtable)dbState._tables.Clone();

                    while(sqlReader.Read()) {
                        tableName = sqlReader.GetString(0); 
                        changeId = sqlReader.GetInt32(1);
 
                        Debug.Trace("SqlCacheDependencyManagerPolling", 
                                "Database=" + dbState._database+ "; tableName=" + tableName + "; changeId=" + changeId);
 
                        monitorKey = GetMoniterKey(dbState._database, tableName);
                        obj = cacheInternal[monitorKey];

                        if (obj == null) { 
                            Debug.Assert(!dbState._tables.ContainsKey(tableName),
                                        "DatabaseNotifStae._tables and internal cache keys should be in-[....]"); 
 
                            Debug.Trace("SqlCacheDependencyManagerPolling",
                                "Add Database=" + dbState._database+ "; tableName=" + tableName + "; changeId=" + changeId); 

                            cacheInternal.UtcAdd(monitorKey, changeId, null,
                                        Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration,
                                        CacheItemPriority.NotRemovable, null); 

                            dbState._tables.Add(tableName, null); 
                        } 
                        else if (changeId != (int)obj) {
                            Debug.Assert(dbState._tables.ContainsKey(tableName), 
                                        "DatabaseNotifStae._tables and internal cache keys should be in-[....]");

                            Debug.Trace("SqlCacheDependencyManagerPolling",
                                    "Change Database=" + dbState._database+ "; tableName=" + tableName + "; old=" + (int)obj + "; new=" + changeId); 

                            // ChangeId is different. It means some table changes have happened. 
                            // Update local cache value 
                            cacheInternal.UtcInsert(monitorKey, changeId, null,
                                        Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, 
                                        CacheItemPriority.NotRemovable, null);
                        }

                        originalTables.Remove(tableName); 
                    }
 
                    // What's left in originalTables are the ones that're no longer 
                    // contained in the AspNet_SqlCacheTablesForChangeNotification
                    // table in the database. 

                    // Remove tables which are no longer enabled for notification
                    foreach(object key in originalTables.Keys) {
                        dbState._tables.Remove((string)key); 
                        cacheInternal.Remove(GetMoniterKey(dbState._database, (string)key));
 
                        Debug.Trace("SqlCacheDependencyManagerPolling", 
                                "Remove Database=" + dbState._database+ "; key=" + key);
                    } 

                    // Clear old error, if any.
                    if (dbState._pollSqlError != 0) {
                        dbState._pollSqlError = 0; 
                    }
                } 
                catch (Exception e) { 
                    pollExpt = e;
 
                    sqlExpt = e as SqlException;
                    if (sqlExpt != null) {
                        Debug.Trace("SqlCacheDependencyManagerPolling", "Error reading rows.  SqlException:"+
                            "\nMessage=" + sqlExpt.Message + 
                            "\nNumber=" + sqlExpt.Number);
 
                        dbState._pollSqlError = sqlExpt.Number; 
                    }
                    else { 
                        dbState._pollSqlError = 0;
                        Debug.Trace("SqlCacheDependencyManagerPolling", "Error reading rows.  Exception:"+ pollExpt);
                    }
                } 
                finally {
                    try { 
                        if (sqlReader != null) { 
                            sqlReader.Close();
                        } 

                        dbState.ReleaseConnection(ref sqlConn, ref sqlCmd, pollExpt != null);
                    }
                    catch { 
                    }
 
                    // Need locking because EnsureTableIsRegisteredAndPolled() assumes 
                    // the fields in a dbState are set atomically.
                    lock(dbState) { 
                        dbState._pollExpt = pollExpt;

                        // If we have changed from being enabled to disabled, and
                        // it's because we cannot find the SP for polling, it means 
                        // the database is no longer enabled for sql cache dependency.
                        // we should invalidate all cache items depending on any 
                        // table on this database 
                        if (dbState._notifEnabled && !notifEnabled &&
                            pollExpt != null && dbState._pollSqlError == SQL_EXCEPTION_SP_NOT_FOUND) { 
                            foreach(object key in dbState._tables.Keys) {
                                try {
                                    cacheInternal.Remove(GetMoniterKey(dbState._database, (string)key));
                                } 
                                catch {}
 
                                Debug.Trace("SqlCacheDependencyManagerPolling", 
                                    "Changed to disabled.  Remove Database=" + dbState._database+ "; key=" + key);
                            } 

                            // Since we have removed all the cache items related to this database,
                            // the _refCount of this database will drop to zero, and thus the timer
                            // callback will not poll this database. 
                            // So we have to cleanup _tables now.
                            dbState._tables.Clear(); 
                        } 

                        dbState._notifEnabled = notifEnabled; 
                        dbState._utcTablesUpdated = DateTime.UtcNow;

                        Debug.Trace("SqlCacheDependencyManagerPolling", "dbState:_pollExpt="+ dbState._pollExpt +
                                "; _pollSqlError=" + dbState._pollSqlError + "; _notifEnabled=" + dbState._notifEnabled + 
                                "; __utcTablesUpdated=" + dbState._utcTablesUpdated);
                    } 
 
                    // Mark dbState as initialized by PollCallback for the first time.
                    // EnsureTableIsRegisteredAndPolled() depends on this. 
                    if (dbState._init != true) {
                        dbState._init = true;
                    }
 
                    Interlocked.Decrement(ref s_activePolling);
 
                    // Release the lock 
                    Interlocked.Exchange(ref dbState._rqInCallback, 0);
                } 
            }
            catch { throw; }    // Prevent Exception Filter Security Issue (ASURT 122835)
        }
 
        // Called by SqlCacheDependency.GetDependKey
        static internal void EnsureTableIsRegisteredAndPolled(string database, string table) { 
            bool    doubleChecked = false; 

            // First check.  If the cache key exists, that means the first poll request 
            // for this table has successfully completed
            Debug.Trace("SqlCacheDependencyManagerCheck",
                                "Check is called.  Database=" + database+ "; table=" + table);
 
            if (HttpRuntime.CacheInternal[GetMoniterKey(database, table)] != null) {
                return; 
            } 

            // Initilize polling for this database, if needed. 
            InitPolling(database);

            // Wait until this database is initialized by PollCallback for the first time
            DatabaseNotifState  dbState = (DatabaseNotifState)s_DatabaseNotifStates[database]; 

            if (!dbState._init) { 
                int         timeout; 
                HttpContext context = HttpContext.Current;
 
                if (context == null) {
                    timeout = 30;
                }
                else { 
                    timeout = Math.Max(context.Timeout.Seconds / 3, 30);
                } 
                DateTime waitLimit = DateTime.UtcNow.Add(new TimeSpan(0, 0, timeout)); 

                Debug.Trace("SqlCacheDependencyManagerCheck", "Waiting for intialization: timeout=" + timeout + "s"); 

                for (;;) {
                    if (dbState._init)
                        break; 

                    Thread.Sleep(250); 
 
                    // only apply timeout if a managed debugger is not attached
                    if (!System.Diagnostics.Debugger.IsAttached && DateTime.UtcNow > waitLimit) { 
                        // We've waited and retried for waitLimit amount of time.
                        // Still PollCallback haven't finished its first call for this database
                        // Assume we cannot connect to SQL.
                        throw new HttpException( 
                            SR.GetString(SR.Cant_connect_sql_cache_dep_database_polling, database));
                    } 
                } 
            }
 
            while(true) {
                DateTime    utcTablesLastUpdated;
                bool        dbRegistered;
                Exception   pollException; 
                int         pollSqlError = 0;
 
                lock(dbState) { 
                     Debug.Trace("SqlCacheDependencyManagerCheck", "dbState:_pollExpt="+ dbState._pollExpt +
                                "; _pollSqlError=" + dbState._pollSqlError + "; _notifEnabled=" + dbState._notifEnabled ); 

                    pollException = dbState._pollExpt;
                    if (pollException != null) {
                        pollSqlError = dbState._pollSqlError; 
                    }
 
                    utcTablesLastUpdated = dbState._utcTablesUpdated; 
                    dbRegistered = dbState._notifEnabled;
                } 

                if (pollException == null &&    // No exception from polling
                    dbRegistered &&             // The database is registered
                    dbState._tables.ContainsKey(table)) {   // The table is also registered 
                    Debug.Trace("SqlCacheDependencyManagerCheck", "The table is registered too.  Exit now!");
                    return; 
                } 

                // Either we hit an error in the last polling, or the database or the table 
                // isn't registered.
                //
                // See if we can double check.  Double checking is needed because the
                // results we just looked at might be collected only at last poll time, 
                // which could be quite old, depending on the pollTime setting.
                // 
                // The scenario we try to solve is: 
                // 1. Let's say polling is configured to happen every 1 minute, and we just poll.
                // 2. A page then registers a table for notification. 
                // 3. The page then try to use SqlCacheDependency on that table.
                // 4. If we don't call UpdateDatabaseNotifStat to query the database now,
                //    we'll have to wait for a whole minute before we can use that table.
                // 

                // To prevent the SQL server from being bombarded by this kind of per-client-request 
                // adhoc check, we only allow a max of one double check per second per database 
                if (!doubleChecked &&
                    DateTime.UtcNow - utcTablesLastUpdated >= OneSec) { 

                    Debug.Trace("SqlCacheDependencyManagerCheck", "Double check...");
                    UpdateDatabaseNotifState(database);
                    doubleChecked = true; 
                    continue;
                } 
 
                if (pollSqlError == SQL_EXCEPTION_SP_NOT_FOUND) {
                    // This error happens if the database isn't enabled for notification. 
                    // This doesn't count as a real Sql error
                    Debug.Assert(dbRegistered == false, "When this error happened, we shouldn't be able to poll the database");
                    pollException = null;
                } 

                // Report any error if we failed in the last PollCallback 
                if (pollException != null) { 
                    string  error;
 
                    if (pollSqlError == SQL_EXCEPTION_PERMISSION_DENIED_ON_OBJECT ||
                        pollSqlError == SQL_EXCEPTION_PERMISSION_DENIED_ON_DATABASE) {
                        error = SR.Permission_denied_database_polling;
                    } 
                    else {
                        error = SR.Cant_connect_sql_cache_dep_database_polling; 
                    } 

                    HttpException outerException = new HttpException( 
                           SR.GetString(error, database), pollException);

                    outerException.SetFormatter(new UseLastUnhandledErrorFormatter(outerException));
 
                    throw outerException;
                } 
 
                // If we don't get any error, then either the database or the table isn't registered.
                if (dbRegistered == false) { 
                    throw new DatabaseNotEnabledForNotificationException(
                            SR.GetString(SR.Database_not_enabled_for_notification, database));
                }
                else { 
                    throw new TableNotEnabledForNotificationException(
                            SR.GetString(SR.Table_not_enabled_for_notification, table, database)); 
                } 
            }
        } 

        // Do a on-demand polling of the database in order to obtain the latest
        // data.
        internal static void UpdateDatabaseNotifState(string database) { 
            using (new ApplicationImpersonationContext()) {
                Debug.Trace("SqlCacheDependencyManager", "UpdateDatabaseNotifState called for database " + database + 
                    "; running as " + WindowsIdentity.GetCurrent().Name); 

                // Make sure we have initialized the polling of this database 
                InitPolling(database);
                Debug.Assert(s_DatabaseNotifStates[database] != null, "s_DatabaseNotifStates[database] != null");

                PollDatabaseForChanges((DatabaseNotifState)s_DatabaseNotifStates[database], false /*fromTimer*/); 
            }
        } 
 
        // Update all initialized databases
        internal static void UpdateAllDatabaseNotifState() { 
            lock(s_DatabaseNotifStates) {
                foreach(DictionaryEntry entry in s_DatabaseNotifStates) {
                    DatabaseNotifState  state = (DatabaseNotifState)entry.Value;
                    if (state._init) { 
                        UpdateDatabaseNotifState((string)entry.Key);
                    } 
                } 
            }
        } 

        internal static DatabaseNotifState AddRef(string database) {
            DatabaseNotifState dbState = (DatabaseNotifState)s_DatabaseNotifStates[database];
            Debug.Assert(dbState != null, "AddRef: s_DatabaseNotifStates[database] != null"); 

#if DBG 
            int res = 
#endif
            Interlocked.Increment(ref dbState._refCount); 
#if DBG
            Debug.Trace("SqlCacheDependencyManager", "AddRef called for " + database + "; res=" + res);
            Debug.Assert(res > 0, "AddRef result for " + database + " must be > 0");
#endif 
            return dbState;
        } 
 
        internal static void Release(DatabaseNotifState dbState) {
#if DBG 
            int res =
#endif
            Interlocked.Decrement(ref dbState._refCount);
#if DBG 
            Debug.Trace("SqlCacheDependencyManager", "Release called for " + dbState._database + "; res=" + res);
            Debug.Assert(res >= 0, "Release result for " + dbState._database + " must be >= 0"); 
#endif 
        }
    } 

    [AspNetHostingPermission(SecurityAction.LinkDemand, Level=AspNetHostingPermissionLevel.High)]
    public static class SqlCacheDependencyAdmin {
 
        // Note:
        // In all the SQL statements below, we will do an unlocking 
        // SELECT, followed by a locking SELECT.  This is to avoid 
        // duplication operation.
 
        //
        // {0} = SQL_NOTIF_TABLE
        // {1} = SQL_POLLING_SP
        // {2} = SQL_REGISTER_TABLE_SP 
        // {3} = SQL_TRIGGER_NAME_POSTFIX
        // {4} = SQL_UNREGISTER_TABLE_SP 
        // {5} = SQL_QUERY_REGISTERED_TABLES_SP 
        // {6} = SQL_UPDATE_CHANGE_ID_SP
        // 
        internal const string   SQL_CREATE_ENABLE_DATABASE_SP =
        "/* Create notification table */ \n" +
        "IF 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 */\n" +
        "IF 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. */ \n" + 
        "IF 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 (''" + SqlCacheDependencyManager.SQL_CUSTOM_ERROR_TABLE_NOT_FOUND + "'', 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. */ \n" +
        "IF 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. */ \n" + 
        "IF 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 */ \n" + 
        "IF 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  */ \n" +
        "IF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole = 1 AND name = N'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess') \n" + 
        "    EXEC sp_addrole N'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess' \n" +
        "\n" +
        "GRANT EXECUTE ON dbo.{1} to aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess\n" +
        "\n"; 

 
        // 
        // {0} = SQL_NOTIF_TABLE
        // {1} = SQL_POLLING_SP 
        // {2} = SQL_REGISTER_TABLE_SP
        // {3} = SQL_UNREGISTER_TABLE_SP
        // {4} = SQL_QUERY_REGISTERED_TABLES_SP
        // {5} = SQL_UPDATE_CHANGE_ID_SP 
        //
        internal const string   SQL_DISABLE_DATABASE = 
        "/* Remove 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" + 
        "    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 */ \n" + 
        "IF 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 */ \n" +
        "IF 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 */ \n" +
        "IF 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 */ \n" + 
        "IF 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. */ \n" +
        "IF 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 */ \n" + 
        "IF EXISTS ( SELECT name FROM sysusers WHERE issqlrole = 1 AND name = 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess') BEGIN\n" +
        DROP_MEMBERS + 
        "    EXEC sp_droprole 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess'\n" +
        "END\n";

        internal const string   DROP_MEMBERS = 
        "CREATE TABLE #aspnet_RoleMembers \n" +
        "( \n" + 
        "    Group_name      sysname, \n" + 
        "    Group_id        smallint, \n" +
        "    Users_in_group  sysname, \n" + 
        "    User_id         smallint \n" +
        ") \n" +
        "INSERT INTO #aspnet_RoleMembers \n" +
        "EXEC sp_helpuser 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess' \n" + 
        " \n" +
        "DECLARE @user_id smallint \n" + 
        "DECLARE @cmd nvarchar(500) \n" + 
        "DECLARE c1 CURSOR FORWARD_ONLY FOR  \n" +
        "    SELECT User_id FROM #aspnet_RoleMembers \n" + 
        "  \n" +
        "OPEN c1 \n" +
        "  \n" +
        "FETCH c1 INTO @user_id \n" + 
        "WHILE (@@fetch_status = 0)  \n" +
        "BEGIN \n" + 
        "    SET @cmd = 'EXEC sp_droprolemember ''aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess'',''' + USER_NAME(@user_id) + '''' \n" + 
        "    EXEC (@cmd) \n" +
        "    FETCH c1 INTO @user_id \n" + 
        "END \n" +
        " \n" +
        "close c1 \n" +
        "deallocate c1 \n"; 

 
        internal const string   SQL_REGISTER_TABLE_SP = 
                                "AspNet_SqlCacheRegisterTableStoredProcedure";
 
        internal const string   SQL_REGISTER_TABLE_SP_DBO =
                                "dbo.AspNet_SqlCacheRegisterTableStoredProcedure";

        internal const string   SQL_UNREGISTER_TABLE_SP = 
                                "AspNet_SqlCacheUnRegisterTableStoredProcedure";
 
        internal const string   SQL_UNREGISTER_TABLE_SP_DBO = 
                                "dbo.AspNet_SqlCacheUnRegisterTableStoredProcedure";
 
        internal const string   SQL_TRIGGER_NAME_POSTFIX =
                                "_AspNet_SqlCacheNotification_Trigger";

        internal const string   SQL_QUERY_REGISTERED_TABLES_SP = 
                                "AspNet_SqlCacheQueryRegisteredTablesStoredProcedure";
 
        internal const string   SQL_QUERY_REGISTERED_TABLES_SP_DBO = 
                                "dbo.AspNet_SqlCacheQueryRegisteredTablesStoredProcedure";
 
        internal const string   SQL_UPDATE_CHANGE_ID_SP=
                                "AspNet_SqlCacheUpdateChangeIdStoredProcedure";

        const int   SETUP_TABLE =           0x00000001; 
        const int   SETUP_DISABLE =         0x00000002;
        const int   SETUP_HTTPREQUEST =     0x00000004; 
        const int   SETUP_TABLES =          0x00000008; // We're called in a loop to setup an array of tables. 

        internal static void SetupNotifications(int flags, string table, string connectionString) { 
            SqlConnection   sqlConnection = null;
            SqlCommand      sqlCmd = null;
            bool            tableOp = (flags & (SETUP_TABLES|SETUP_TABLE)) != 0;
            bool            disable = (flags & SETUP_DISABLE) != 0; 

            if (tableOp) { 
                bool    tables = (flags & SETUP_TABLES) != 0; 
                if (table == null) {
                    if (tables) { 
                        throw new ArgumentException(SR.GetString(SR.Cache_null_table_in_tables),
                                            "tables");
                    }
                    else { 
                        throw new ArgumentNullException("table");
                    } 
                } 
                else if (table.Length == 0) {
                    if (tables) { 
                        throw new ArgumentException(SR.GetString(SR.Cache_null_table_in_tables),
                                    "tables");
                    }
                    else { 
                        throw new ArgumentException(SR.GetString(SR.Cache_null_table),
                                    "table"); 
                    } 
                }
            } 

            try {
                sqlConnection = new SqlConnection(connectionString);
                sqlConnection.Open(); 

                sqlCmd = new SqlCommand(null, sqlConnection); 
 
                if (tableOp) {
                    sqlCmd.CommandText = !disable ? SQL_REGISTER_TABLE_SP_DBO : SQL_UNREGISTER_TABLE_SP_DBO; 
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.Parameters.Add(new SqlParameter("@tableName", SqlDbType.NVarChar, table.Length));
                    sqlCmd.Parameters[0].Value = table;
                } 
                else {
                    if (!disable) { 
                        // Enable the database 
                        sqlCmd.CommandText = String.Format(CultureInfo.InvariantCulture,
                                                SQL_CREATE_ENABLE_DATABASE_SP, 
                                                SqlCacheDependencyManager.SQL_NOTIF_TABLE,
                                                SqlCacheDependencyManager.SQL_POLLING_SP,
                                                SQL_REGISTER_TABLE_SP,
                                                SQL_TRIGGER_NAME_POSTFIX, 
                                                SQL_UNREGISTER_TABLE_SP,
                                                SQL_QUERY_REGISTERED_TABLES_SP, 
                                                SQL_UPDATE_CHANGE_ID_SP); 
                        sqlCmd.CommandType = CommandType.Text;
                    } 
                    else {
                        // Disable the database
                        sqlCmd.CommandText = String.Format(CultureInfo.InvariantCulture,
                                                SQL_DISABLE_DATABASE, 
                                                SqlCacheDependencyManager.SQL_NOTIF_TABLE,
                                                SqlCacheDependencyManager.SQL_POLLING_SP, 
                                                SQL_REGISTER_TABLE_SP, 
                                                SQL_UNREGISTER_TABLE_SP,
                                                SQL_QUERY_REGISTERED_TABLES_SP, 
                                                SQL_UPDATE_CHANGE_ID_SP);
                        sqlCmd.CommandType = CommandType.Text;
                    }
                } 

                Debug.Trace("SqlCacheDependencyAdmin", "\n" + 
                    sqlCmd.CommandText); 

                sqlCmd.ExecuteNonQuery(); 

                // Clear CommandText so that error handling won't mistakenly
                // report it as a SQL error
                sqlCmd.CommandText = String.Empty; 

                // If we are being called as part of an ASP.NET Http request 
                if (HttpRuntime.IsAspNetAppDomain) { 
                    // Need to update the status of all initialized databases
                    // 
                    // Note: we can actually try to figure out which database we want
                    // to update based on the connectionString.  But updating
                    // all initialized ones are good enough.
                    SqlCacheDependencyManager.UpdateAllDatabaseNotifState(); 
                }
            } 
            catch (Exception e) { 
                SqlException sqlExpt = e as SqlException;
                bool throwError = true; 

                if (sqlExpt != null) {
                    Debug.Trace("SqlCacheDependencyAdmin", "SqlException:"+
                        "\nMessage=" + sqlExpt.Message + 
                        "\nNumber=" + sqlExpt.Number);
 
                    if (sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_SP_NOT_FOUND) { 
                        if (!disable) {
                            if (table != null) { 
                                throw new DatabaseNotEnabledForNotificationException(
                                    SR.GetString(SR.Database_not_enabled_for_notification,
                                                                    sqlConnection.Database));
                            } 
                            else {
                                throw; 
                            } 
                        }
                        else { 
                            if (table != null) {
                                throw new DatabaseNotEnabledForNotificationException(
                                    SR.GetString(SR.Cant_disable_table_sql_cache_dep));
                            } 
                            else {
                                // If we cannot find the SP for disabling the database, it maybe because 
                                // SQL cache dep is already disabled, or the SP is missing. 
                                // In either case, we just exit silently.
                                throwError = false; 
                            }
                        }
                    }
                    else if (sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_PERMISSION_DENIED_ON_OBJECT || 
                            sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_PERMISSION_DENIED_ON_DATABASE ||
                            sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_PERMISSION_DENIED_ON_USER || 
                            sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_NO_GRANT_PERMISSION) { 
                        string error;
 
                        if (!disable) {
                            if (table != null) {
                                error = SR.Permission_denied_table_enable_notification;
                            } 
                            else {
                                error = SR.Permission_denied_database_enable_notification; 
                            } 
                        }
                        else { 
                            if (table != null) {
                                error = SR.Permission_denied_table_disable_notification;
                            }
                            else { 
                                error = SR.Permission_denied_database_disable_notification;
                            } 
                        } 

                        if (table != null) { 
                            throw new HttpException(
                                SR.GetString(error, table));
                        }
                        else { 
                            throw new HttpException(
                                SR.GetString(error)); 
                        } 
                    }
                    else if (sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_ADHOC && 
                            sqlExpt.Message == SqlCacheDependencyManager.SQL_CUSTOM_ERROR_TABLE_NOT_FOUND) {
                        Debug.Assert(!disable && table != null, "disable && table != null");
                        throw new HttpException(SR.GetString(SR.Cache_dep_table_not_found, table));
                    } 
                }
 
                string  errString; 

                if (sqlCmd != null && sqlCmd.CommandText.Length != 0) { 
                    errString = SR.GetString(SR.Cant_connect_sql_cache_dep_database_admin_cmdtxt,
                                    sqlCmd.CommandText);
                }
                else { 
                    errString = SR.GetString(SR.Cant_connect_sql_cache_dep_database_admin);
                } 
 
                if (throwError) {
                    throw new HttpException(errString, e); 
                }
            }
            finally {
                if (sqlConnection != null) { 
                    sqlConnection.Close();
                } 
            } 
        }
 
        public static void EnableNotifications(string connectionString) {
            SetupNotifications(0, null, connectionString);
        }
 
        public static void DisableNotifications(string connectionString) {
            SetupNotifications(SETUP_DISABLE, null, connectionString); 
        } 

        public static void EnableTableForNotifications(string connectionString, string table) { 
            SetupNotifications(SETUP_TABLE, table, connectionString);
        }

        public static void EnableTableForNotifications(string connectionString, string[] tables) { 
            if (tables == null) {
                throw new ArgumentNullException("tables"); 
            } 

            foreach (string table in tables) { 
                SetupNotifications(SETUP_TABLES, table, connectionString);
            }
        }
 
        public static void DisableTableForNotifications(string connectionString, string table) {
            SetupNotifications(SETUP_TABLE|SETUP_DISABLE, table, connectionString); 
        } 

        public static void DisableTableForNotifications(string connectionString, string[] tables) { 
            if (tables == null) {
                throw new ArgumentNullException("tables");
            }
 
            foreach (string table in tables) {
                SetupNotifications(SETUP_TABLES|SETUP_DISABLE, table, connectionString); 
            } 
        }
 
        static string[] GetEnabledTables(string connectionString) {

            SqlDataReader       sqlReader = null;
            SqlConnection       sqlConn = null; 
            SqlCommand          sqlCmd = null;
            ArrayList           tablesObj = new ArrayList(); 
 
            try {
                sqlConn = new SqlConnection(connectionString); 
                sqlConn.Open();

                sqlCmd = new SqlCommand(SQL_QUERY_REGISTERED_TABLES_SP_DBO, sqlConn);
                sqlCmd.CommandType = CommandType.StoredProcedure; 

                sqlReader = sqlCmd.ExecuteReader(); 
 
                while(sqlReader.Read()) {
                    tablesObj.Add(sqlReader.GetString(0)); 
                }
            }
            catch (Exception e) {
                SqlException sqlExpt = e as SqlException; 

                if (sqlExpt != null && 
                    sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_SP_NOT_FOUND) { 

                        throw new DatabaseNotEnabledForNotificationException( 
                                SR.GetString(SR.Database_not_enabled_for_notification,
                                                                sqlConn.Database));
                }
                else { 
                    throw new HttpException(SR.GetString(SR.Cant_get_enabled_tables_sql_cache_dep), e);
                } 
            } 
            finally {
                try { 
                    if (sqlReader != null) {
                        sqlReader.Close();
                    }
 
                    if (sqlConn != null) {
                        sqlConn.Close(); 
                    } 
                }
                catch { 
                }
            }

            return (string[])tablesObj.ToArray(Type.GetType("System.String")); 
        }
 
        public static string[] GetTablesEnabledForNotifications(string connectionString) { 
            return GetEnabledTables(connectionString);
        } 
    }
}

 

// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
                        

Link Menu

Network programming in C#, Network Programming in VB.NET, Network Programming in .NET
This book is available now!
Buy at Amazon US or
Buy at Amazon UK