SqlCacheDependency.cs source code in C# .NET

Source code for the .NET framework in C#

                        

Code:

/ DotNET / DotNET / 8.0 / untmp / whidbey / REDBITS / ndp / fx / src / xsp / System / Web / Cache / SqlCacheDependency.cs / 3 / 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;

    [AspNetHostingPermission(SecurityAction.LinkDemand, Level=AspNetHostingPermissionLevel.Minimal)]
    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);
            } 
        }

        internal static CacheDependency CreateOutputCacheDependency(string depString) {
            if (depString == null) { 
                throw new HttpException(SR.GetString(SR.Invalid_sqlDependency_argument, depString));
            } 
 
            if (StringUtil.EqualsIgnoreCase(depString, 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(depString); 
 
                // 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, depString); 
                } 

                aggr = new AggregateCacheDependency(); 

                for(int i=0; i < sqlDependencies.Count; i++) {
                    info = (Sql7DependencyInfo)sqlDependencies[i];
                    aggr.Add(CreateSql7SqlCacheDependencyForOutputCache(info._database, info._table, depString)); 
                }
 
                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()] 
    [AspNetHostingPermission(SecurityAction.LinkDemand, Level=AspNetHostingPermissionLevel.Minimal)]
    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()]
    [AspNetHostingPermission(SecurityAction.LinkDemand, Level=AspNetHostingPermissionLevel.Minimal)] 
    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-sync"); 

                            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-sync"); 

                            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);
        }
    } 
}
 
 


                        

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