mcs/class/referencesource/System.Web/Cache/SqlCacheDependency.cs

   1 //------------------------------------------------------------------------------
   2 // <copyright file="SqlCacheDepModule.cs" company="Microsoft">
   3 //     Copyright (c) Microsoft Corporation.  All rights reserved.
   4 // </copyright>
   5 //------------------------------------------------------------------------------
   6
   7 /*
   8  * SqlCacheDepModule
   9  *
  10  * Copyright (c) 1998-1999, Microsoft Corporation
  11  *
  12  */
  13
  14 namespace System.Web.Caching {
  15
  16     using System;
  17     using System.Threading;
  18     using System.Collections;
  19     using System.Configuration;
  20     using System.IO;
  21     using System.Web.Caching;
  22     using System.Web.Util;
  23     using System.Web.Configuration;
  24     using System.Xml;
  25     using System.Data;
  26     using System.Data.SqlClient;
  27     using System.Globalization;
  28     using System.Security.Permissions;
  29     using System.Text;
  30     using System.Runtime.InteropServices;
  31     using System.EnterpriseServices;
  32     using System.Web.UI;
  33     using System.Web.DataAccess;
  34     using System.Security.Principal;
  35     using System.Web.Hosting;
  36     using System.Runtime.Serialization;
  37     using System.Web.Management;
  38     using System.Security;
  39
  40     public sealed class SqlCacheDependency : CacheDependency {
  41
  42         internal static bool        s_hasSqlClientPermission;
  43         internal static bool        s_hasSqlClientPermissionInited;
  44
  45         const string SQL9_CACHE_DEPENDENCY_DIRECTIVE = "CommandNotification";
  46         internal const string SQL9_OUTPUT_CACHE_DEPENDENCY_COOKIE = "MS.SqlDependencyCookie";
  47
  48         SqlDependency       _sqlYukonDep;       // SqlDependency for Yukon
  49         DatabaseNotifState  _sql7DatabaseState;     // Database state for SQL7/2000
  50         string              _uniqueID;              // used by HttpCachePolicy for the ETag
  51 #if DBG
  52         bool                _isUniqueIDInitialized;
  53 #endif
  54
  55         struct Sql7DependencyInfo {
  56             internal string  _database;
  57             internal string  _table;
  58         }
  59
  60         // For generating Unique Id
  61         Sql7DependencyInfo  _sql7DepInfo;
  62         int                 _sql7ChangeId;
  63
  64
  65         // For Non-SQL9 SQL servers, we create a dependency based on an internal cached item.
  66
  67         public SqlCacheDependency(string databaseEntryName, string tableName)
  68         :base(0, null, new string[1] {GetDependKey(databaseEntryName, tableName)})
  69         {
  70             Debug.Trace("SqlCacheDependency",
  71                             "Depend on key=" + GetDependKey(databaseEntryName, tableName) + "; value=" +
  72                             HttpRuntime.CacheInternal[GetDependKey(databaseEntryName, tableName)]);
  73
  74             // Permission checking is done in GetDependKey()
  75
  76             _sql7DatabaseState = SqlCacheDependencyManager.AddRef(databaseEntryName);
  77             _sql7DepInfo._database = databaseEntryName;
  78             _sql7DepInfo._table = tableName;
  79
  80             object o = HttpRuntime.CacheInternal[GetDependKey(databaseEntryName, tableName)];
  81             if (o == null) {
  82                 // If the cache entry can't be found, this cache dependency will be set to CHANGED already.
  83                 _sql7ChangeId = -1;
  84             }
  85             else {
  86                 // Note that if the value in the cache changed between the base ctor and here, even though
  87                 // we get a wrong unqiue Id, but it's okay because that change will cause the CacheDependency's
  88                 // state to become CHANGED and any cache operation using this CacheDependency will fail anyway.
  89                 _sql7ChangeId = (int)o;
  90             }
  91
  92             // The ctor of every class derived from CacheDependency must call this.
  93             FinishInit();
  94
  95             InitUniqueID();
  96         }
  97
  98         protected override void DependencyDispose() {
  99             if (_sql7DatabaseState != null) {
 100                 SqlCacheDependencyManager.Release(_sql7DatabaseState);
 101             }
 102         }
 103
 104         // For SQL9, we use SqlDependency
 105         public SqlCacheDependency(SqlCommand sqlCmd) {
 106             HttpContext context = HttpContext.Current;
 107
 108             if (sqlCmd == null) {
 109                 throw new ArgumentNullException("sqlCmd");
 110             }
 111
 112             // Prevent a conflict between using SQL9 outputcache and an explicit
 113             // SQL9 SqlCacheDependency at the same time.  See VSWhidey 396429 and
 114             // the attached email in the
 115             if (context != null && context.SqlDependencyCookie != null &&  // That means We have already setup SQL9 dependency for output cache
 116                 sqlCmd.NotificationAutoEnlist) {    // This command will auto-enlist in that output cache dependency
 117                 throw new HttpException(SR.GetString(SR.SqlCacheDependency_OutputCache_Conflict));
 118             }
 119
 120             CreateSqlDep(sqlCmd);
 121
 122             InitUniqueID();
 123         }
 124
 125         void InitUniqueID() {
 126             if (_sqlYukonDep != null) {
 127                 // Yukon does not provide us with an ID, so we'll use a Guid.
 128                 _uniqueID = Guid.NewGuid().ToString("N", CultureInfo.InvariantCulture);
 129             }
 130             else if (_sql7ChangeId == -1) {
 131                 // The database/tablen entry can't be found in the cache.  That means SQL doesn't have
 132                 // this database/table registered for sql cache dependency.  In this case, we can't
 133                 // generate a unique id.
 134                 _uniqueID = null;
 135             }
 136             else {
 137                 _uniqueID = _sql7DepInfo._database + ":" + _sql7DepInfo._table + ":" + _sql7ChangeId.ToString(CultureInfo.InvariantCulture);
 138             }
 139 #if DBG
 140             _isUniqueIDInitialized = true;
 141 #endif
 142         }
 143
 144         public override string GetUniqueID() {
 145 #if DBG
 146             Debug.Assert(_isUniqueIDInitialized == true, "_isUniqueIDInitialized == true");
 147 #endif
 148             return _uniqueID;
 149         }
 150
 151         private static void CheckPermission() {
 152             if (!s_hasSqlClientPermissionInited) {
 153                 if (!System.Web.Hosting.HostingEnvironment.IsHosted) {
 154                     try {
 155                         new SqlClientPermission(PermissionState.Unrestricted).Demand();
 156                         s_hasSqlClientPermission = true;
 157                     }
 158                     catch (SecurityException) {}
 159                 }
 160                 else {
 161                     s_hasSqlClientPermission = Permission.HasSqlClientPermission();
 162                 }
 163
 164                 s_hasSqlClientPermissionInited = true;
 165             }
 166
 167             if (!s_hasSqlClientPermission) {
 168                 throw new HttpException(SR.GetString(SR.SqlCacheDependency_permission_denied));
 169             }
 170         }
 171
 172         void OnSQL9SqlDependencyChanged(Object sender, SqlNotificationEventArgs e) {
 173             Debug.Trace("SqlCacheDependency", "SQL9 dependency changed: depId=" + _sqlYukonDep.Id);
 174             NotifyDependencyChanged(sender, e);
 175         }
 176
 177         private SqlCacheDependency() {
 178             CreateSqlDep(null);
 179             InitUniqueID();
 180         }
 181
 182         void CreateSqlDep(SqlCommand sqlCmd) {
 183             _sqlYukonDep = new SqlDependency();
 184
 185             // Note: sqlCmd is null in output cache case.
 186
 187             if (sqlCmd != null) {
 188                 Debug.Trace("SqlCacheDependency", "SqlCmd added to SqlDependency object");
 189                 _sqlYukonDep.AddCommandDependency(sqlCmd);
 190             }
 191
 192             _sqlYukonDep.OnChange += new OnChangeEventHandler(OnSQL9SqlDependencyChanged);
 193
 194             Debug.Trace("SqlCacheDependency", "SQL9 dependency created: depId=" + _sqlYukonDep.Id);
 195         }
 196
 197         internal static void ValidateOutputCacheDependencyString(string depString, bool page) {
 198             if (depString == null) {
 199                 throw new HttpException(SR.GetString(SR.Invalid_sqlDependency_argument, depString));
 200             }
 201
 202             if (StringUtil.EqualsIgnoreCase(depString, SQL9_CACHE_DEPENDENCY_DIRECTIVE)) {
 203                 if (!page) {
 204                     // It's impossible for only a page, but not its controls, to use Yukon Cache Dependency; neither
 205                     // can the opposite scenario possible.  It's because once we create a SqlDependency and
 206                     // stick it to the context, it's complicated (but not impossible) to clear it when rendering
 207                     // the parts (either a page or a control) that doesn't depend on Yukon.
 208                     // To keep things simple, we restrict Yukon Cache Dependency only to page.
 209                     throw new HttpException(
 210                         SR.GetString(SR.Attrib_Sql9_not_allowed));
 211                 }
 212             }
 213             else {
 214                 // It's for non-SQL 9 scenario.
 215                 ParseSql7OutputCacheDependency(depString);
 216             }
 217         }
 218
 219         public static CacheDependency CreateOutputCacheDependency(string dependency) {
 220             if (dependency == null) {
 221                 throw new HttpException(SR.GetString(SR.Invalid_sqlDependency_argument, dependency));
 222             }
 223
 224             if (StringUtil.EqualsIgnoreCase(dependency, SQL9_CACHE_DEPENDENCY_DIRECTIVE)) {
 225                 HttpContext context = HttpContext.Current;
 226                 Debug.Assert(context != null);
 227
 228                 SqlCacheDependency  dep = new SqlCacheDependency();
 229
 230                 Debug.Trace("SqlCacheDependency", "Setting depId=" + dep._sqlYukonDep.Id);
 231                 context.SqlDependencyCookie = dep._sqlYukonDep.Id;
 232
 233                 return dep;
 234             }
 235             else {
 236                 ArrayList                   sqlDependencies;
 237                 AggregateCacheDependency    aggr = null;
 238                 Sql7DependencyInfo           info;
 239
 240                 sqlDependencies = ParseSql7OutputCacheDependency(dependency);
 241
 242                 // ParseSql7OutputCacheDependency will throw if we cannot find a single entry
 243                 Debug.Assert(sqlDependencies.Count > 0, "sqlDependencies.Count > 0");
 244
 245                 Debug.Trace("SqlCacheDependency", "Creating SqlCacheDependency for SQL8 output cache");
 246
 247                 if (sqlDependencies.Count == 1) {
 248                     info = (Sql7DependencyInfo)sqlDependencies[0];
 249                     return CreateSql7SqlCacheDependencyForOutputCache(info._database, info._table, dependency);
 250                 }
 251
 252                 aggr = new AggregateCacheDependency();
 253
 254                 for(int i=0; i < sqlDependencies.Count; i++) {
 255                     info = (Sql7DependencyInfo)sqlDependencies[i];
 256                     aggr.Add(CreateSql7SqlCacheDependencyForOutputCache(info._database, info._table, dependency));
 257                 }
 258
 259                 return aggr;
 260             }
 261         }
 262
 263         static SqlCacheDependency CreateSql7SqlCacheDependencyForOutputCache(string database, string table, string depString) {
 264             try {
 265                 return new SqlCacheDependency(database, table);
 266             }
 267             catch (HttpException e) {
 268                 HttpException outerException = new HttpException(
 269                        SR.GetString(SR.Invalid_sqlDependency_argument2, depString, e.Message), e);
 270
 271                 outerException.SetFormatter(new UseLastUnhandledErrorFormatter(outerException));
 272
 273                 throw outerException;
 274             }
 275         }
 276
 277         static string GetDependKey(string database, string tableName) {
 278
 279             // This is called by ctor SqlCacheDependency(string databaseEntryName, string tableName)
 280             // before the body of that ctor is executed.  So we have to make sure the app has
 281             // the right permission here.
 282             CheckPermission();
 283
 284             // First is to check whether Sql cache polling is enabled in config or not.
 285             if (database == null) {
 286                 throw new ArgumentNullException("database");
 287             }
 288
 289             if (tableName == null) {
 290                 throw new ArgumentNullException("tableName");
 291             }
 292
 293             if (tableName.Length == 0) {
 294                 throw new ArgumentException(SR.GetString(SR.Cache_null_table));
 295             }
 296
 297             string  monitorKey = SqlCacheDependencyManager.GetMoniterKey(database, tableName);
 298
 299             // Make sure the table is already registered with the database and
 300             // we've polled the database at least once, so that there is an
 301             // entry in the cache.
 302             SqlCacheDependencyManager.EnsureTableIsRegisteredAndPolled(database, tableName);
 303             return monitorKey;
 304         }
 305
 306         static string VerifyAndRemoveEscapeCharacters(string s) {
 307             int     i;
 308             bool    escape = false;
 309
 310             for (i=0; i < s.Length; i++) {
 311                 if (escape) {
 312                     if (s[i] != '\\' && s[i] != ':' && s[i] != ';') {
 313                         // Only '\\', '\:' and '\;' are allowed
 314                         throw new ArgumentException();
 315                     }
 316                     escape = false;
 317                     continue;
 318                 }
 319
 320                 if (s[i] == '\\') {
 321                     if (i+1 == s.Length) {
 322                         // No character following escape char
 323                         throw new ArgumentException();
 324                     }
 325                     escape = true;
 326                     s = s.Remove(i, 1);
 327                     i--;
 328                 }
 329             }
 330
 331             return s;
 332         }
 333
 334         internal static ArrayList ParseSql7OutputCacheDependency(string outputCacheString) {
 335             // The database and the table name are separated by a ":".  If the name
 336             // contains a ":" character, specify it by doing "\:"
 337             // Pairs of entries are separated by a ";"
 338             bool        escape = false;
 339             int         iDatabaseStart = 0;
 340             int         iTableStart = -1;
 341             string      database = null;        // The database portion of the pair
 342             ArrayList   dependencies = null;
 343             int         len;
 344             Sql7DependencyInfo   info;
 345
 346             try {
 347                 for (int i = 0; i < outputCacheString.Length+1; i++) {
 348                     if (escape) {
 349                         escape = false;
 350                         continue;
 351                     }
 352
 353                     if (i != outputCacheString.Length && outputCacheString[i] == '\\') {
 354                         escape = true;
 355                         continue;
 356                     }
 357
 358                     // We have reached ';' or the end of the string
 359                     if (i == outputCacheString.Length || outputCacheString[i] == ';' ) {
 360                         if (database==null) {
 361                             // No database name
 362                             throw new ArgumentException();
 363                         }
 364
 365                         // Get the lenght of the table portion
 366                         len = i - iTableStart;
 367                         if (len == 0) {
 368                             // No table name
 369                             throw new ArgumentException();
 370                         }
 371
 372                         info = new Sql7DependencyInfo();
 373                         info._database = VerifyAndRemoveEscapeCharacters(database);
 374                         info._table = VerifyAndRemoveEscapeCharacters(outputCacheString.Substring(iTableStart, len));
 375
 376                         if (dependencies == null) {
 377                             dependencies = new ArrayList(1);
 378                         }
 379
 380                         dependencies.Add(info);
 381
 382                         // Reset below values.  We are searching for the next pair.
 383                         iDatabaseStart = i+1;
 384                         database = null;
 385                     }
 386
 387                     // Have we reached the end of the string?
 388                     if (i == outputCacheString.Length) {
 389                         break;
 390                     }
 391
 392                     if (outputCacheString[i] == ':') {
 393                         if (database != null) {
 394                             // We have already got the database portion
 395                             throw new ArgumentException();
 396                         }
 397
 398                         // Do we get the database part?
 399                         len = i - iDatabaseStart;
 400                         if (len == 0) {
 401                             // No database name
 402                             throw new ArgumentException();
 403                         }
 404
 405                         database = outputCacheString.Substring(iDatabaseStart, len);
 406                         iTableStart = i+1;
 407                         continue;
 408                     }
 409                 }
 410
 411                 return dependencies;
 412
 413             }
 414             catch (ArgumentException) {
 415                 throw new ArgumentException(SR.GetString(SR.Invalid_sqlDependency_argument, outputCacheString));
 416             }
 417         }
 418     }
 419
 420
 421     [Serializable()]
 422     public sealed class DatabaseNotEnabledForNotificationException : SystemException {
 423
 424         public DatabaseNotEnabledForNotificationException() {
 425         }
 426
 427
 428         public DatabaseNotEnabledForNotificationException(String message)
 429         : base(message) {
 430         }
 431
 432
 433         public DatabaseNotEnabledForNotificationException(string message, Exception innerException)
 434         : base (message, innerException) {
 435         }
 436
 437
 438         internal DatabaseNotEnabledForNotificationException(SerializationInfo info, StreamingContext context)
 439         : base(info, context) {
 440         }
 441
 442     }
 443
 444
 445     [Serializable()]
 446     public sealed class TableNotEnabledForNotificationException : SystemException {
 447
 448         public TableNotEnabledForNotificationException() {
 449         }
 450
 451
 452         public TableNotEnabledForNotificationException(String message)
 453         : base(message) {
 454         }
 455
 456
 457         public TableNotEnabledForNotificationException(string message, Exception innerException)
 458         : base (message, innerException) {
 459         }
 460
 461
 462         internal TableNotEnabledForNotificationException(SerializationInfo info, StreamingContext context)
 463         : base(info, context) {
 464         }
 465
 466     }
 467
 468     // A class to store the state of a timer for a specific database
 469     internal class DatabaseNotifState : IDisposable {
 470         internal string         _database;
 471         internal string         _connectionString;
 472         internal int            _rqInCallback;
 473         internal bool           _notifEnabled;     // true means the ChangeNotif table was found in the database
 474         internal bool           _init;      // true means timer callback was called at least once
 475         internal Timer          _timer;
 476         internal Hashtable      _tables; // Names of all the tables registered for notification
 477         internal Exception      _pollExpt;
 478         internal int            _pollSqlError;
 479         internal SqlConnection  _sqlConn;
 480         internal SqlCommand     _sqlCmd;
 481         internal bool           _poolConn;
 482         internal DateTime       _utcTablesUpdated; // Time when _tables was last updated
 483         internal int            _refCount = 0;
 484
 485         public void Dispose() {
 486             if (_sqlConn != null) {
 487                 _sqlConn.Close();
 488                 _sqlConn = null;
 489             }
 490
 491             if (_timer != null) {
 492                 _timer.Dispose();
 493                 _timer = null;
 494             }
 495         }
 496
 497         internal DatabaseNotifState(string database, string connection, int polltime) {
 498             _database = database;
 499             _connectionString = connection;
 500             _timer = null;
 501             _tables = new Hashtable();
 502             _pollExpt = null;
 503             _utcTablesUpdated = DateTime.MinValue;
 504
 505             // We will pool the connection if the polltime is less than 5 s.
 506             if (polltime <= 5000) {
 507                 _poolConn = true;
 508             }
 509
 510         }
 511
 512         internal void GetConnection(out SqlConnection sqlConn, out SqlCommand sqlCmd) {
 513             sqlConn = null;
 514             sqlCmd = null;
 515
 516             // !!! Please note that GetConnection and ReleaseConnection does NOT support
 517             // multithreading.  The caller must do the locking.
 518
 519             if (_sqlConn != null) {
 520                 // We already have a pooled connection.
 521                 Debug.Assert(_poolConn, "_poolConn");
 522                 Debug.Assert(_sqlCmd != null, "_sqlCmd != null");
 523
 524                 sqlConn = _sqlConn;
 525                 sqlCmd = _sqlCmd;
 526
 527                 _sqlConn = null;
 528                 _sqlCmd = null;
 529             }
 530             else {
 531                 SqlConnectionHolder holder = null;
 532
 533                 try {
 534                     holder = SqlConnectionHelper.GetConnection(_connectionString, true);
 535
 536                     sqlCmd = new SqlCommand(SqlCacheDependencyManager.SQL_POLLING_SP_DBO, holder.Connection);
 537
 538                     sqlConn = holder.Connection;
 539                 }
 540                 catch {
 541                     if (holder != null) {
 542                         holder.Close();
 543                         holder = null;
 544                     }
 545
 546                     sqlCmd = null;
 547
 548                     throw;
 549                 }
 550             }
 551         }
 552
 553         internal void ReleaseConnection(ref SqlConnection sqlConn, ref SqlCommand sqlCmd, bool error) {
 554             // !!! Please note that GetConnection and ReleaseConnection does NOT support
 555             // multithreading.  The caller must do the locking.
 556
 557             if (sqlConn == null) {
 558                 Debug.Assert(sqlCmd == null, "sqlCmd == null");
 559                 return;
 560             }
 561
 562             Debug.Assert(sqlCmd != null, "sqlCmd != null");
 563
 564             if (_poolConn && !error) {
 565                 _sqlConn = sqlConn;
 566                 _sqlCmd = sqlCmd;
 567             }
 568             else {
 569                 sqlConn.Close();
 570             }
 571
 572             sqlConn = null;
 573             sqlCmd = null;
 574         }
 575     }
 576
 577     internal static class SqlCacheDependencyManager{
 578
 579         internal const bool     ENABLED_DEFAULT = true;
 580         internal const int      POLLTIME_DEFAULT = 60000;
 581         internal const int      TABLE_NAME_LENGTH = 128;
 582
 583         internal const int      SQL_EXCEPTION_SP_NOT_FOUND = 2812;
 584         internal const int      SQL_EXCEPTION_PERMISSION_DENIED_ON_OBJECT = 229;
 585         internal const int      SQL_EXCEPTION_PERMISSION_DENIED_ON_DATABASE = 262;
 586         internal const int      SQL_EXCEPTION_PERMISSION_DENIED_ON_USER = 2760;
 587         internal const int      SQL_EXCEPTION_NO_GRANT_PERMISSION = 4613;
 588         internal const int      SQL_EXCEPTION_ADHOC = 50000;
 589
 590         const char              CacheKeySeparatorChar = ':';
 591         const string            CacheKeySeparator = ":";
 592         const string            CacheKeySeparatorEscaped = "\\:";
 593
 594         internal const string   SQL_CUSTOM_ERROR_TABLE_NOT_FOUND = "00000001";
 595
 596         internal const string   SQL_NOTIF_TABLE =
 597                                 "AspNet_SqlCacheTablesForChangeNotification";
 598
 599         internal const string   SQL_POLLING_SP =
 600                                 "AspNet_SqlCachePollingStoredProcedure";
 601
 602         internal const string   SQL_POLLING_SP_DBO =
 603                                 "dbo.AspNet_SqlCachePollingStoredProcedure";
 604
 605         internal static TimeSpan    OneSec = new TimeSpan(0, 0, 1);
 606
 607         internal static Hashtable   s_DatabaseNotifStates = new Hashtable();
 608         static TimerCallback        s_timerCallback = new TimerCallback(PollCallback);
 609         static int                  s_activePolling = 0;
 610         static bool                 s_shutdown = false;
 611
 612         static internal string GetMoniterKey(string database, string table) {
 613             if (database.IndexOf(CacheKeySeparatorChar) != -1) {
 614                 database = database.Replace(CacheKeySeparator, CacheKeySeparatorEscaped);
 615             }
 616
 617             if (table.IndexOf(CacheKeySeparatorChar) != -1) {
 618                 table = table.Replace(CacheKeySeparator, CacheKeySeparatorEscaped);
 619             }
 620
 621             // If we don't escape our separator char (':') in database and table,
 622             // these two pairs of inputs will then generate the same key:
 623             // 1. database = "b", table = "b:b"
 624             // 2. database = "b:b", table = "b"
 625             return CacheInternal.PrefixSqlCacheDependency + database + CacheKeySeparator + table;
 626         }
 627
 628         static internal void Dispose(int waitTimeoutMs) {
 629             try {
 630                 DateTime waitLimit = DateTime.UtcNow.AddMilliseconds(waitTimeoutMs);
 631
 632                 Debug.Assert(s_shutdown != true, "s_shutdown != true");
 633                 Debug.Trace("SqlCacheDependencyManager", "Dispose is called");
 634
 635                 s_shutdown = true;
 636
 637                 if (s_DatabaseNotifStates != null && s_DatabaseNotifStates.Count > 0) {
 638                     // Lock it because InitPolling could be modifying it.
 639                     lock(s_DatabaseNotifStates) {
 640                         foreach(DictionaryEntry entry in s_DatabaseNotifStates) {
 641                             object  obj = entry.Value;
 642                             if (obj != null) {
 643                                 ((DatabaseNotifState)obj).Dispose();
 644                             }
 645                         }
 646                     }
 647
 648                     for (;;) {
 649                         if (s_activePolling == 0)
 650                             break;
 651
 652                         Thread.Sleep(250);
 653
 654                         // only apply timeout if a managed debugger is not attached
 655                         if (!System.Diagnostics.Debugger.IsAttached && DateTime.UtcNow > waitLimit) {
 656                             break; // give it up
 657                         }
 658                     }
 659                 }
 660             }
 661             catch {
 662                 // It's called by HttpRuntime.Dispose. It can't throw anything.
 663                 return;
 664             }
 665         }
 666
 667         internal static SqlCacheDependencyDatabase GetDatabaseConfig(string database) {
 668             SqlCacheDependencySection config = RuntimeConfig.GetAppConfig().SqlCacheDependency;
 669             object  obj;
 670
 671             obj =  config.Databases[database];
 672             if (obj == null) {
 673                 throw new HttpException(SR.GetString(SR.Database_not_found, database));
 674             }
 675
 676             return  (SqlCacheDependencyDatabase)obj;
 677         }
 678
 679         // Initialize polling for a database.  It will:
 680         // 1. Create the DatabaseNotifState that holds the polling status about this database.
 681         // 2. Create the timer to poll.
 682         internal static void InitPolling(string database) {
 683             SqlCacheDependencySection config = RuntimeConfig.GetAppConfig().SqlCacheDependency;;
 684             SqlCacheDependencyDatabase  sqlDepDB;
 685             string connectionString;
 686
 687             Debug.Trace("SqlCacheDependencyManager",
 688                                 "InitPolling is called.  Database=" + database);
 689
 690             // Return if polling isn't even enabled.
 691             if (!config.Enabled) {
 692                 throw new ConfigurationErrorsException(
 693                     SR.GetString(SR.Polling_not_enabled_for_sql_cache),
 694                     config.ElementInformation.Properties["enabled"].Source, config.ElementInformation.Properties["enabled"].LineNumber);
 695             }
 696
 697             // Return if the polltime is zero.  It means polling is disabled for this database.
 698             sqlDepDB = GetDatabaseConfig(database);
 699             if (sqlDepDB.PollTime == 0) {
 700                 throw new ConfigurationErrorsException(
 701                     SR.GetString(SR.Polltime_zero_for_database_sql_cache, database),
 702                     sqlDepDB.ElementInformation.Properties["pollTime"].Source, sqlDepDB.ElementInformation.Properties["pollTime"].LineNumber);
 703             }
 704
 705             if (s_DatabaseNotifStates.ContainsKey(database)) {
 706                 // Someone has already started the timer for this database.
 707                 Debug.Trace("SqlCacheDependencyManager",
 708                                 "InitPolling: Timer already started for " + database);
 709
 710                 return;
 711             }
 712
 713             connectionString = SqlConnectionHelper.GetConnectionString(sqlDepDB.ConnectionStringName, true, true);
 714             if (connectionString == null || connectionString.Length < 1) {
 715                 throw new ConfigurationErrorsException(
 716                     SR.GetString(SR.Connection_string_not_found, sqlDepDB.ConnectionStringName),
 717                     sqlDepDB.ElementInformation.Properties["connectionStringName"].Source, sqlDepDB.ElementInformation.Properties["connectionStringName"].LineNumber);
 718             }
 719
 720             lock(s_DatabaseNotifStates) {
 721                 DatabaseNotifState   state;
 722
 723                 if (s_DatabaseNotifStates.ContainsKey(database)) {
 724                     // Someone has already started the timer for this database.
 725                     Debug.Trace("SqlCacheDependencyManager",
 726                                 "InitPolling: Timer already started for " + database);
 727
 728                     return;
 729                 }
 730
 731                 Debug.Trace("SqlCacheDependencyManager",
 732                                 "InitPolling: Creating timer for " + database);
 733
 734                 state = new DatabaseNotifState(database, connectionString, sqlDepDB.PollTime);
 735                 state._timer = new Timer(s_timerCallback, state, 0 /* dueTime */, sqlDepDB.PollTime /* period */);
 736
 737                 s_DatabaseNotifStates.Add(database, state);
 738             }
 739         }
 740
 741         // Timer callback function.
 742         static void PollCallback(object state) {
 743             using (new ApplicationImpersonationContext()) {
 744                 PollDatabaseForChanges((DatabaseNotifState)state, true /*fromTimer*/);
 745             }
 746         }
 747
 748         // Query all the entries from the AspNet_SqlCacheTablesForChangeNotification
 749         // table and update the values in the cache accordingly.
 750         //
 751         // This is mainly called by the timer callback.  But will also be called by
 752         // UpdateDatabaseNotifState, which polls for changes on demand.
 753         internal static void PollDatabaseForChanges(DatabaseNotifState dbState, bool fromTimer) {
 754             SqlDataReader       sqlReader = null;
 755             SqlConnection       sqlConn = null;
 756             SqlCommand          sqlCmd = null;
 757             int                 changeId;
 758             string              tableName;
 759             CacheInternal       cacheInternal = HttpRuntime.CacheInternal;
 760             string              monitorKey;
 761             object              obj;
 762             bool                notifEnabled = false;
 763             Exception           pollExpt = null;
 764             SqlException        sqlExpt = null;
 765
 766             Debug.Trace("SqlCacheDependencyManagerPolling",
 767                 "PollCallback called; connection=" + dbState._connectionString);
 768
 769             if (s_shutdown) {
 770                 return;
 771             }
 772
 773             // If this call is from a timer, and if the refcount for this database is zero,
 774             // we will ignore it. The exception is if dbState._init == false,
 775             // which means the timer is polling it for the first time.
 776             if (dbState._refCount == 0 && fromTimer && dbState._init  ) {
 777                 Debug.Trace("SqlCacheDependencyManagerPolling",
 778                     "PollCallback ignored for " + dbState._database + " because refcount is 0");
 779                 return;
 780             }
 781
 782             // Grab the lock, which allows only one thread to enter this method.
 783             if (Interlocked.CompareExchange(ref dbState._rqInCallback, 1, 0) != 0) {
 784
 785                 // We can't get the lock.
 786
 787                 if (!fromTimer) {
 788                     // A non-timer caller will really want to make a call to SQL and
 789                     // get the result.  So if another thread is calling this, we'll
 790                     // wait for it to be done.
 791                     int         timeout;
 792                     HttpContext context = HttpContext.Current;
 793
 794                     if (context == null) {
 795                         timeout = 30;
 796                     }
 797                     else {
 798                         timeout = Math.Max(context.Timeout.Seconds / 3, 30);
 799                     }
 800                     DateTime waitLimit = DateTime.UtcNow.Add(new TimeSpan(0, 0, timeout));
 801
 802                     for (;;) {
 803                         if (Interlocked.CompareExchange(ref dbState._rqInCallback, 1, 0) == 0) {
 804                             break;
 805                         }
 806
 807                         Thread.Sleep(250);
 808
 809                         if (s_shutdown) {
 810                             return;
 811                         }
 812
 813                         // only apply timeout if a managed debugger is not attached
 814                         if (!System.Diagnostics.Debugger.IsAttached && DateTime.UtcNow > waitLimit) {
 815                             // We've waited and retried for 5 seconds.
 816                             // Somehow PollCallback haven't finished its first call for this database
 817                             // Assume we cannot connect to SQL.
 818                             throw new HttpException(
 819                                 SR.GetString(SR.Cant_connect_sql_cache_dep_database_polling, dbState._database));
 820                         }
 821                     }
 822                 }
 823                 else {
 824                     // For a timer callback, if another thread is updating the data for
 825                     // this database, this thread will just leave and let that thread
 826                     // finish the update job.
 827                     Debug.Trace("SqlCacheDependencyManagerPolling",
 828                         "PollCallback returned because another thread is updating the data");
 829                     return;
 830                 }
 831             }
 832
 833             try {
 834                 try {
 835                     // Keep a count on how many threads are polling right now
 836                     // This counter is used by Dispose()
 837                     Interlocked.Increment(ref s_activePolling);
 838
 839                     // The below assert was commented out because this method is either
 840                     // called by a timer thread, or thru the SqlCacheDependencyAdmin APIs.
 841                     // In the latter case, the caller should have the permissions.
 842                     //(new SqlClientPermission(PermissionState.Unrestricted)).Assert();
 843
 844                     dbState.GetConnection(out sqlConn, out sqlCmd);
 845                     sqlReader = sqlCmd.ExecuteReader();
 846
 847                     // If we got stuck for a long time in the ExecuteReader above,
 848                     // Dispose() may have given up already while waiting for this thread to finish
 849                     if (s_shutdown) {
 850                         return;
 851                     }
 852
 853                     // ExecuteReader() succeeded, and that means we at least have found the notification table.
 854                     notifEnabled = true;
 855
 856                     // Remember the original list of tables that are enabled
 857                     Hashtable   originalTables = (Hashtable)dbState._tables.Clone();
 858
 859                     while(sqlReader.Read()) {
 860                         tableName = sqlReader.GetString(0);
 861                         changeId = sqlReader.GetInt32(1);
 862
 863                         Debug.Trace("SqlCacheDependencyManagerPolling",
 864                                 "Database=" + dbState._database+ "; tableName=" + tableName + "; changeId=" + changeId);
 865
 866                         monitorKey = GetMoniterKey(dbState._database, tableName);
 867                         obj = cacheInternal[monitorKey];
 868
 869                         if (obj == null) {
 870                             Debug.Assert(!dbState._tables.ContainsKey(tableName),
 871                                         "DatabaseNotifStae._tables and internal cache keys should be in-sync");
 872
 873                             Debug.Trace("SqlCacheDependencyManagerPolling",
 874                                 "Add Database=" + dbState._database+ "; tableName=" + tableName + "; changeId=" + changeId);
 875
 876                             cacheInternal.UtcAdd(monitorKey, changeId, null,
 877                                         Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration,
 878                                         CacheItemPriority.NotRemovable, null);
 879
 880                             dbState._tables.Add(tableName, null);
 881                         }
 882                         else if (changeId != (int)obj) {
 883                             Debug.Assert(dbState._tables.ContainsKey(tableName),
 884                                         "DatabaseNotifStae._tables and internal cache keys should be in-sync");
 885
 886                             Debug.Trace("SqlCacheDependencyManagerPolling",
 887                                     "Change Database=" + dbState._database+ "; tableName=" + tableName + "; old=" + (int)obj + "; new=" + changeId);
 888
 889                             // ChangeId is different. It means some table changes have happened.
 890                             // Update local cache value
 891                             cacheInternal.UtcInsert(monitorKey, changeId, null,
 892                                         Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration,
 893                                         CacheItemPriority.NotRemovable, null);
 894                         }
 895
 896                         originalTables.Remove(tableName);
 897                     }
 898
 899                     // What's left in originalTables are the ones that're no longer
 900                     // contained in the AspNet_SqlCacheTablesForChangeNotification
 901                     // table in the database.
 902
 903                     // Remove tables which are no longer enabled for notification
 904                     foreach(object key in originalTables.Keys) {
 905                         dbState._tables.Remove((string)key);
 906                         cacheInternal.Remove(GetMoniterKey(dbState._database, (string)key));
 907
 908                         Debug.Trace("SqlCacheDependencyManagerPolling",
 909                                 "Remove Database=" + dbState._database+ "; key=" + key);
 910                     }
 911
 912                     // Clear old error, if any.
 913                     if (dbState._pollSqlError != 0) {
 914                         dbState._pollSqlError = 0;
 915                     }
 916                 }
 917                 catch (Exception e) {
 918                     pollExpt = e;
 919
 920                     sqlExpt = e as SqlException;
 921                     if (sqlExpt != null) {
 922                         Debug.Trace("SqlCacheDependencyManagerPolling", "Error reading rows.  SqlException:"+
 923                             "\nMessage=" + sqlExpt.Message +
 924                             "\nNumber=" + sqlExpt.Number);
 925
 926                         dbState._pollSqlError = sqlExpt.Number;
 927                     }
 928                     else {
 929                         dbState._pollSqlError = 0;
 930                         Debug.Trace("SqlCacheDependencyManagerPolling", "Error reading rows.  Exception:"+ pollExpt);
 931                     }
 932                 }
 933                 finally {
 934                     try {
 935                         if (sqlReader != null) {
 936                             sqlReader.Close();
 937                         }
 938
 939                         dbState.ReleaseConnection(ref sqlConn, ref sqlCmd, pollExpt != null);
 940                     }
 941                     catch {
 942                     }
 943
 944                     // Need locking because EnsureTableIsRegisteredAndPolled() assumes
 945                     // the fields in a dbState are set atomically.
 946                     lock(dbState) {
 947                         dbState._pollExpt = pollExpt;
 948
 949                         // If we have changed from being enabled to disabled, and
 950                         // it's because we cannot find the SP for polling, it means
 951                         // the database is no longer enabled for sql cache dependency.
 952                         // we should invalidate all cache items depending on any
 953                         // table on this database
 954                         if (dbState._notifEnabled && !notifEnabled &&
 955                             pollExpt != null && dbState._pollSqlError == SQL_EXCEPTION_SP_NOT_FOUND) {
 956                             foreach(object key in dbState._tables.Keys) {
 957                                 try {
 958                                     cacheInternal.Remove(GetMoniterKey(dbState._database, (string)key));
 959                                 }
 960                                 catch {}
 961
 962                                 Debug.Trace("SqlCacheDependencyManagerPolling",
 963                                     "Changed to disabled.  Remove Database=" + dbState._database+ "; key=" + key);
 964                             }
 965
 966                             // Since we have removed all the cache items related to this database,
 967                             // the _refCount of this database will drop to zero, and thus the timer
 968                             // callback will not poll this database.
 969                             // So we have to cleanup _tables now.
 970                             dbState._tables.Clear();
 971                         }
 972
 973                         dbState._notifEnabled = notifEnabled;
 974                         dbState._utcTablesUpdated = DateTime.UtcNow;
 975
 976                         Debug.Trace("SqlCacheDependencyManagerPolling", "dbState:_pollExpt="+ dbState._pollExpt +
 977                                 "; _pollSqlError=" + dbState._pollSqlError + "; _notifEnabled=" + dbState._notifEnabled +
 978                                 "; __utcTablesUpdated=" + dbState._utcTablesUpdated);
 979                     }
 980
 981                     // Mark dbState as initialized by PollCallback for the first time.
 982                     // EnsureTableIsRegisteredAndPolled() depends on this.
 983                     if (dbState._init != true) {
 984                         dbState._init = true;
 985                     }
 986
 987                     Interlocked.Decrement(ref s_activePolling);
 988
 989                     // Release the lock
 990                     Interlocked.Exchange(ref dbState._rqInCallback, 0);
 991                 }
 992             }
 993             catch { throw; }    // Prevent Exception Filter Security Issue (ASURT 122835)
 994         }
 995
 996         // Called by SqlCacheDependency.GetDependKey
 997         static internal void EnsureTableIsRegisteredAndPolled(string database, string table) {
 998             bool    doubleChecked = false;
 999
1000             // First check.  If the cache key exists, that means the first poll request
1001             // for this table has successfully completed
1002             Debug.Trace("SqlCacheDependencyManagerCheck",
1003                                 "Check is called.  Database=" + database+ "; table=" + table);
1004
1005             if (HttpRuntime.CacheInternal[GetMoniterKey(database, table)] != null) {
1006                 return;
1007             }
1008
1009             // Initilize polling for this database, if needed.
1010             InitPolling(database);
1011
1012             // Wait until this database is initialized by PollCallback for the first time
1013             DatabaseNotifState  dbState = (DatabaseNotifState)s_DatabaseNotifStates[database];
1014
1015             if (!dbState._init) {
1016                 int         timeout;
1017                 HttpContext context = HttpContext.Current;
1018
1019                 if (context == null) {
1020                     timeout = 30;
1021                 }
1022                 else {
1023                     timeout = Math.Max(context.Timeout.Seconds / 3, 30);
1024                 }
1025                 DateTime waitLimit = DateTime.UtcNow.Add(new TimeSpan(0, 0, timeout));
1026
1027                 Debug.Trace("SqlCacheDependencyManagerCheck", "Waiting for intialization: timeout=" + timeout + "s");
1028
1029                 for (;;) {
1030                     if (dbState._init)
1031                         break;
1032
1033                     Thread.Sleep(250);
1034
1035                     // only apply timeout if a managed debugger is not attached
1036                     if (!System.Diagnostics.Debugger.IsAttached && DateTime.UtcNow > waitLimit) {
1037                         // We've waited and retried for waitLimit amount of time.
1038                         // Still PollCallback haven't finished its first call for this database
1039                         // Assume we cannot connect to SQL.
1040                         throw new HttpException(
1041                             SR.GetString(SR.Cant_connect_sql_cache_dep_database_polling, database));
1042                     }
1043                 }
1044             }
1045
1046             while(true) {
1047                 DateTime    utcTablesLastUpdated;
1048                 bool        dbRegistered;
1049                 Exception   pollException;
1050                 int         pollSqlError = 0;
1051
1052                 lock(dbState) {
1053                      Debug.Trace("SqlCacheDependencyManagerCheck", "dbState:_pollExpt="+ dbState._pollExpt +
1054                                 "; _pollSqlError=" + dbState._pollSqlError + "; _notifEnabled=" + dbState._notifEnabled );
1055
1056                     pollException = dbState._pollExpt;
1057                     if (pollException != null) {
1058                         pollSqlError = dbState._pollSqlError;
1059                     }
1060
1061                     utcTablesLastUpdated = dbState._utcTablesUpdated;
1062                     dbRegistered = dbState._notifEnabled;
1063                 }
1064
1065                 if (pollException == null &&    // No exception from polling
1066                     dbRegistered &&             // The database is registered
1067                     dbState._tables.ContainsKey(table)) {   // The table is also registered
1068                     Debug.Trace("SqlCacheDependencyManagerCheck", "The table is registered too.  Exit now!");
1069                     return;
1070                 }
1071
1072                 // Either we hit an error in the last polling, or the database or the table
1073                 // isn't registered.
1074                 //
1075                 // See if we can double check.  Double checking is needed because the
1076                 // results we just looked at might be collected only at last poll time,
1077                 // which could be quite old, depending on the pollTime setting.
1078                 //
1079                 // The scenario we try to solve is:
1080                 // 1. Let's say polling is configured to happen every 1 minute, and we just poll.
1081                 // 2. A page then registers a table for notification.
1082                 // 3. The page then try to use SqlCacheDependency on that table.
1083                 // 4. If we don't call UpdateDatabaseNotifStat to query the database now,
1084                 //    we'll have to wait for a whole minute before we can use that table.
1085                 //
1086
1087                 // To prevent the SQL server from being bombarded by this kind of per-client-request
1088                 // adhoc check, we only allow a max of one double check per second per database
1089                 if (!doubleChecked &&
1090                     DateTime.UtcNow - utcTablesLastUpdated >= OneSec) {
1091
1092                     Debug.Trace("SqlCacheDependencyManagerCheck", "Double check...");
1093                     UpdateDatabaseNotifState(database);
1094                     doubleChecked = true;
1095                     continue;
1096                 }
1097
1098                 if (pollSqlError == SQL_EXCEPTION_SP_NOT_FOUND) {
1099                     // This error happens if the database isn't enabled for notification.
1100                     // This doesn't count as a real Sql error
1101                     Debug.Assert(dbRegistered == false, "When this error happened, we shouldn't be able to poll the database");
1102                     pollException = null;
1103                 }
1104
1105                 // Report any error if we failed in the last PollCallback
1106                 if (pollException != null) {
1107                     string  error;
1108
1109                     if (pollSqlError == SQL_EXCEPTION_PERMISSION_DENIED_ON_OBJECT ||
1110                         pollSqlError == SQL_EXCEPTION_PERMISSION_DENIED_ON_DATABASE) {
1111                         error = SR.Permission_denied_database_polling;
1112                     }
1113                     else {
1114                         error = SR.Cant_connect_sql_cache_dep_database_polling;
1115                     }
1116
1117                     HttpException outerException = new HttpException(
1118                            SR.GetString(error, database), pollException);
1119
1120                     outerException.SetFormatter(new UseLastUnhandledErrorFormatter(outerException));
1121
1122                     throw outerException;
1123                 }
1124
1125                 // If we don't get any error, then either the database or the table isn't registered.
1126                 if (dbRegistered == false) {
1127                     throw new DatabaseNotEnabledForNotificationException(
1128                             SR.GetString(SR.Database_not_enabled_for_notification, database));
1129                 }
1130                 else {
1131                     throw new TableNotEnabledForNotificationException(
1132                             SR.GetString(SR.Table_not_enabled_for_notification, table, database));
1133                 }
1134             }
1135         }
1136
1137         // Do a on-demand polling of the database in order to obtain the latest
1138         // data.
1139         internal static void UpdateDatabaseNotifState(string database) {
1140             using (new ApplicationImpersonationContext()) {
1141                 Debug.Trace("SqlCacheDependencyManager", "UpdateDatabaseNotifState called for database " + database +
1142                     "; running as " + WindowsIdentity.GetCurrent().Name);
1143
1144                 // Make sure we have initialized the polling of this database
1145                 InitPolling(database);
1146                 Debug.Assert(s_DatabaseNotifStates[database] != null, "s_DatabaseNotifStates[database] != null");
1147
1148                 PollDatabaseForChanges((DatabaseNotifState)s_DatabaseNotifStates[database], false /*fromTimer*/);
1149             }
1150         }
1151
1152         // Update all initialized databases
1153         internal static void UpdateAllDatabaseNotifState() {
1154             lock(s_DatabaseNotifStates) {
1155                 foreach(DictionaryEntry entry in s_DatabaseNotifStates) {
1156                     DatabaseNotifState  state = (DatabaseNotifState)entry.Value;
1157                     if (state._init) {
1158                         UpdateDatabaseNotifState((string)entry.Key);
1159                     }
1160                 }
1161             }
1162         }
1163
1164         internal static DatabaseNotifState AddRef(string database) {
1165             DatabaseNotifState dbState = (DatabaseNotifState)s_DatabaseNotifStates[database];
1166             Debug.Assert(dbState != null, "AddRef: s_DatabaseNotifStates[database] != null");
1167
1168 #if DBG
1169             int res =
1170 #endif
1171             Interlocked.Increment(ref dbState._refCount);
1172 #if DBG
1173             Debug.Trace("SqlCacheDependencyManager", "AddRef called for " + database + "; res=" + res);
1174             Debug.Assert(res > 0, "AddRef result for " + database + " must be > 0");
1175 #endif
1176             return dbState;
1177         }
1178
1179         internal static void Release(DatabaseNotifState dbState) {
1180 #if DBG
1181             int res =
1182 #endif
1183             Interlocked.Decrement(ref dbState._refCount);
1184 #if DBG
1185             Debug.Trace("SqlCacheDependencyManager", "Release called for " + dbState._database + "; res=" + res);
1186             Debug.Assert(res >= 0, "Release result for " + dbState._database + " must be >= 0");
1187 #endif
1188         }
1189     }
1190
1191     [AspNetHostingPermission(SecurityAction.LinkDemand, Level=AspNetHostingPermissionLevel.High)]
1192     public static class SqlCacheDependencyAdmin {
1193
1194         // Note:
1195         // In all the SQL statements below, we will do an unlocking
1196         // SELECT, followed by a locking SELECT.  This is to avoid
1197         // duplication operation.
1198
1199         //
1200         // {0} = SQL_NOTIF_TABLE
1201         // {1} = SQL_POLLING_SP
1202         // {2} = SQL_REGISTER_TABLE_SP
1203         // {3} = SQL_TRIGGER_NAME_POSTFIX
1204         // {4} = SQL_UNREGISTER_TABLE_SP
1205         // {5} = SQL_QUERY_REGISTERED_TABLES_SP
1206         // {6} = SQL_UPDATE_CHANGE_ID_SP
1207         //
1208         internal const string   SQL_CREATE_ENABLE_DATABASE_SP =
1209         "/* Create notification table */ \n" +
1210         "IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{0}' AND type = 'U') \n" +
1211         "   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{0}' AND type = 'U') \n" +
1212         "      CREATE TABLE dbo.{0} (\n" +
1213         "      tableName             NVARCHAR(450) NOT NULL PRIMARY KEY,\n" +
1214         "      notificationCreated   DATETIME NOT NULL DEFAULT(GETDATE()),\n" +
1215         "      changeId              INT NOT NULL DEFAULT(0)\n" +
1216         "      )\n" +
1217         "\n" +
1218         "/* Create polling SP */\n" +
1219         "IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{1}' AND type = 'P') \n" +
1220         "   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{1}' AND type = 'P') \n" +
1221         "   EXEC('CREATE PROCEDURE dbo.{1} AS\n" +
1222         "         SELECT tableName, changeId FROM dbo.{0}\n" +
1223         "         RETURN 0')\n" +
1224         "\n" +
1225         "/* Create SP for registering a table. */ \n" +
1226         "IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{2}' AND type = 'P') \n" +
1227         "   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{2}' AND type = 'P') \n" +
1228         "   EXEC('CREATE PROCEDURE dbo.{2} \n" +
1229         "             @tableName NVARCHAR(450) \n" +
1230         "         AS\n" +
1231         "         BEGIN\n" +
1232         "\n" +
1233         "         DECLARE @triggerName AS NVARCHAR(3000) \n" +
1234         "         DECLARE @fullTriggerName AS NVARCHAR(3000)\n" +
1235         "         DECLARE @canonTableName NVARCHAR(3000) \n" +
1236         "         DECLARE @quotedTableName NVARCHAR(3000) \n" +
1237         "\n" +
1238         "         /* Create the trigger name */ \n" +
1239         "         SET @triggerName = REPLACE(@tableName, ''['', ''__o__'') \n" +
1240         "         SET @triggerName = REPLACE(@triggerName, '']'', ''__c__'') \n" +
1241         "         SET @triggerName = @triggerName + ''{3}'' \n" +
1242         "         SET @fullTriggerName = ''dbo.['' + @triggerName + '']'' \n" +
1243         "\n" +
1244         "         /* Create the cannonicalized table name for trigger creation */ \n" +
1245         "         /* Do not touch it if the name contains other delimiters */ \n" +
1246         "         IF (CHARINDEX(''.'', @tableName) <> 0 OR \n" +
1247         "             CHARINDEX(''['', @tableName) <> 0 OR \n" +
1248         "             CHARINDEX('']'', @tableName) <> 0) \n" +
1249         "             SET @canonTableName = @tableName \n" +
1250         "         ELSE \n" +
1251         "             SET @canonTableName = ''['' + @tableName + '']'' \n" +
1252         "\n" +
1253         "         /* First make sure the table exists */ \n" +
1254         "         IF (SELECT OBJECT_ID(@tableName, ''U'')) IS NULL \n" +
1255         "         BEGIN \n" +
1256         "             RAISERROR (''" + SqlCacheDependencyManager.SQL_CUSTOM_ERROR_TABLE_NOT_FOUND + "'', 16, 1) \n" +
1257         "             RETURN \n" +
1258         "         END \n" +
1259         "\n" +
1260         "         BEGIN TRAN\n" +
1261         "         /* Insert the value into the notification table */ \n" +
1262         "         IF NOT EXISTS (SELECT tableName FROM dbo.{0} WITH (NOLOCK) WHERE tableName = @tableName) \n" +
1263         "             IF NOT EXISTS (SELECT tableName FROM dbo.{0} WITH (TABLOCKX) WHERE tableName = @tableName) \n" +
1264         "                 INSERT  dbo.{0} \n" +
1265         "                 VALUES (@tableName, GETDATE(), 0)\n" +
1266         "\n" +
1267         "         /* Create the trigger */ \n" +
1268         "         SET @quotedTableName = QUOTENAME(@tableName, '''''''') \n" +
1269         "         IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = ''TR'') \n" +
1270         "             IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = ''TR'') \n" +
1271         "                 EXEC(''CREATE TRIGGER '' + @fullTriggerName + '' ON '' + @canonTableName +''\n" +
1272         "                       FOR INSERT, UPDATE, DELETE AS BEGIN\n" +
1273         "                       SET NOCOUNT ON\n" +
1274         "                       EXEC dbo.{6} N'' + @quotedTableName + ''\n" +
1275         "                       END\n" +
1276         "                       '')\n" +
1277         "         COMMIT TRAN\n" +
1278         "         END\n" +
1279         "   ')\n" +
1280         "\n" +
1281         "/* Create SP for updating the change Id of a table. */ \n" +
1282         "IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{6}' AND type = 'P') \n" +
1283         "   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{6}' AND type = 'P') \n" +
1284         "   EXEC('CREATE PROCEDURE dbo.{6} \n" +
1285         "             @tableName NVARCHAR(450) \n" +
1286         "         AS\n" +
1287         "\n" +
1288         "         BEGIN \n" +
1289         "             UPDATE dbo.{0} WITH (ROWLOCK) SET changeId = changeId + 1 \n" +
1290         "             WHERE tableName = @tableName\n" +
1291         "         END\n" +
1292         "   ')\n" +
1293         "\n" +
1294         "/* Create SP for unregistering a table. */ \n" +
1295         "IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{4}' AND type = 'P') \n" +
1296         "   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{4}' AND type = 'P') \n" +
1297         "   EXEC('CREATE PROCEDURE dbo.{4} \n" +
1298         "             @tableName NVARCHAR(450) \n" +
1299         "         AS\n" +
1300         "         BEGIN\n" +
1301         "\n" +
1302         "         BEGIN TRAN\n" +
1303         "         DECLARE @triggerName AS NVARCHAR(3000) \n" +
1304         "         DECLARE @fullTriggerName AS NVARCHAR(3000)\n" +
1305         "         SET @triggerName = REPLACE(@tableName, ''['', ''__o__'') \n" +
1306         "         SET @triggerName = REPLACE(@triggerName, '']'', ''__c__'') \n" +
1307         "         SET @triggerName = @triggerName + ''{3}'' \n" +
1308         "         SET @fullTriggerName = ''dbo.['' + @triggerName + '']'' \n" +
1309         "\n" +
1310         "         /* Remove the table-row from the notification table */ \n" +
1311         "         IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = ''{0}'' AND type = ''U'') \n" +
1312         "             IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = ''{0}'' AND type = ''U'') \n" +
1313         "             DELETE FROM dbo.{0} WHERE tableName = @tableName \n" +
1314         "\n" +
1315         "         /* Remove the trigger */ \n" +
1316         "         IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = ''TR'') \n" +
1317         "             IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = ''TR'') \n" +
1318         "             EXEC(''DROP TRIGGER '' + @fullTriggerName) \n" +
1319         "\n" +
1320         "         COMMIT TRAN\n" +
1321         "         END\n" +
1322         "   ')\n" +
1323         "\n" +
1324         "/* Create SP for querying all registered table */ \n" +
1325         "IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{5}' AND type = 'P') \n" +
1326         "   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{5}' AND type = 'P') \n" +
1327         "   EXEC('CREATE PROCEDURE dbo.{5} \n" +
1328         "         AS\n" +
1329         "         SELECT tableName FROM dbo.{0}" +
1330         "   ')\n" +
1331         "\n" +
1332         "/* Create roles and grant them access to SP  */ \n" +
1333         "IF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole = 1 AND name = N'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess') \n" +
1334         "    EXEC sp_addrole N'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess' \n" +
1335         "\n" +
1336         "GRANT EXECUTE ON dbo.{1} to aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess\n" +
1337         "\n";
1338
1339
1340         //
1341         // {0} = SQL_NOTIF_TABLE
1342         // {1} = SQL_POLLING_SP
1343         // {2} = SQL_REGISTER_TABLE_SP
1344         // {3} = SQL_UNREGISTER_TABLE_SP
1345         // {4} = SQL_QUERY_REGISTERED_TABLES_SP
1346         // {5} = SQL_UPDATE_CHANGE_ID_SP
1347         //
1348         internal const string   SQL_DISABLE_DATABASE =
1349         "/* Remove notification table */ \n" +
1350         "IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{0}' AND type = 'U') \n" +
1351         "    IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{0}' AND type = 'U') \n" +
1352         "    BEGIN\n" +
1353         "      /* First, unregister all registered tables */ \n" +
1354         "      DECLARE tables_cursor CURSOR FOR \n" +
1355         "      SELECT tableName FROM dbo.{0} \n" +
1356         "      DECLARE @tableName AS NVARCHAR(450) \n" +
1357         "\n" +
1358         "      OPEN tables_cursor \n" +
1359         "\n" +
1360         "      /* Perform the first fetch. */ \n" +
1361         "      FETCH NEXT FROM tables_cursor INTO @tableName \n" +
1362         "\n" +
1363         "      /* Check @@FETCH_STATUS to see if there are any more rows to fetch. */ \n" +
1364         "      WHILE @@FETCH_STATUS = 0 \n" +
1365         "      BEGIN \n" +
1366         "          EXEC {3} @tableName \n" +
1367         "\n" +
1368         "          /* This is executed as long as the previous fetch succeeds. */ \n" +
1369         "          FETCH NEXT FROM tables_cursor INTO @tableName \n" +
1370         "      END \n" +
1371         "      CLOSE tables_cursor \n" +
1372         "      DEALLOCATE tables_cursor \n" +
1373         "\n" +
1374         "      /* Drop the table */\n" +
1375         "      DROP TABLE dbo.{0} \n" +
1376         "    END\n" +
1377         "\n" +
1378         "/* Remove polling SP */ \n" +
1379         "IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{1}' AND type = 'P') \n" +
1380         "    IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{1}' AND type = 'P') \n" +
1381         "      DROP PROCEDURE dbo.{1} \n" +
1382         "\n" +
1383         "/* Remove SP that registers a table */ \n" +
1384         "IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{2}' AND type = 'P') \n" +
1385         "    IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{2}' AND type = 'P') \n" +
1386         "      DROP PROCEDURE dbo.{2} \n" +
1387         "\n" +
1388         "/* Remove SP that unregisters a table */ \n" +
1389         "IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{3}' AND type = 'P') \n" +
1390         "    IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{3}' AND type = 'P') \n" +
1391         "      DROP PROCEDURE dbo.{3} \n"+
1392         "\n" +
1393         "/* Remove SP that querys the registered table */ \n" +
1394         "IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{4}' AND type = 'P') \n" +
1395         "    IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{4}' AND type = 'P') \n" +
1396         "      DROP PROCEDURE dbo.{4} \n" +
1397         "\n" +
1398         "/* Remove SP that updates the change Id of a table. */ \n" +
1399         "IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{5}' AND type = 'P') \n" +
1400         "    IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{5}' AND type = 'P') \n" +
1401         "      DROP PROCEDURE dbo.{5} \n" +
1402         "\n" +
1403         "/* Drop roles */ \n" +
1404         "IF EXISTS ( SELECT name FROM sysusers WHERE issqlrole = 1 AND name = 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess') BEGIN\n" +
1405         DROP_MEMBERS +
1406         "    EXEC sp_droprole 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess'\n" +
1407         "END\n";
1408
1409         internal const string   DROP_MEMBERS =
1410         "CREATE TABLE #aspnet_RoleMembers \n" +
1411         "( \n" +
1412         "    Group_name      sysname, \n" +
1413         "    Group_id        smallint, \n" +
1414         "    Users_in_group  sysname, \n" +
1415         "    User_id         smallint \n" +
1416         ") \n" +
1417         "INSERT INTO #aspnet_RoleMembers \n" +
1418         "EXEC sp_helpuser 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess' \n" +
1419         " \n" +
1420         "DECLARE @user_id smallint \n" +
1421         "DECLARE @cmd nvarchar(500) \n" +
1422         "DECLARE c1 CURSOR FORWARD_ONLY FOR  \n" +
1423         "    SELECT User_id FROM #aspnet_RoleMembers \n" +
1424         "  \n" +
1425         "OPEN c1 \n" +
1426         "  \n" +
1427         "FETCH c1 INTO @user_id \n" +
1428         "WHILE (@@fetch_status = 0)  \n" +
1429         "BEGIN \n" +
1430         "    SET @cmd = 'EXEC sp_droprolemember ''aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess'',''' + USER_NAME(@user_id) + '''' \n" +
1431         "    EXEC (@cmd) \n" +
1432         "    FETCH c1 INTO @user_id \n" +
1433         "END \n" +
1434         " \n" +
1435         "close c1 \n" +
1436         "deallocate c1 \n";
1437
1438
1439         internal const string   SQL_REGISTER_TABLE_SP =
1440                                 "AspNet_SqlCacheRegisterTableStoredProcedure";
1441
1442         internal const string   SQL_REGISTER_TABLE_SP_DBO =
1443                                 "dbo.AspNet_SqlCacheRegisterTableStoredProcedure";
1444
1445         internal const string   SQL_UNREGISTER_TABLE_SP =
1446                                 "AspNet_SqlCacheUnRegisterTableStoredProcedure";
1447
1448         internal const string   SQL_UNREGISTER_TABLE_SP_DBO =
1449                                 "dbo.AspNet_SqlCacheUnRegisterTableStoredProcedure";
1450
1451         internal const string   SQL_TRIGGER_NAME_POSTFIX =
1452                                 "_AspNet_SqlCacheNotification_Trigger";
1453
1454         internal const string   SQL_QUERY_REGISTERED_TABLES_SP =
1455                                 "AspNet_SqlCacheQueryRegisteredTablesStoredProcedure";
1456
1457         internal const string   SQL_QUERY_REGISTERED_TABLES_SP_DBO =
1458                                 "dbo.AspNet_SqlCacheQueryRegisteredTablesStoredProcedure";
1459
1460         internal const string   SQL_UPDATE_CHANGE_ID_SP=
1461                                 "AspNet_SqlCacheUpdateChangeIdStoredProcedure";
1462
1463         const int   SETUP_TABLE =           0x00000001;
1464         const int   SETUP_DISABLE =         0x00000002;
1465         const int   SETUP_HTTPREQUEST =     0x00000004;
1466         const int   SETUP_TABLES =          0x00000008; // We're called in a loop to setup an array of tables.
1467
1468         internal static void SetupNotifications(int flags, string table, string connectionString) {
1469             SqlConnection   sqlConnection = null;
1470             SqlCommand      sqlCmd = null;
1471             bool            tableOp = (flags & (SETUP_TABLES|SETUP_TABLE)) != 0;
1472             bool            disable = (flags & SETUP_DISABLE) != 0;
1473
1474             if (tableOp) {
1475                 bool    tables = (flags & SETUP_TABLES) != 0;
1476                 if (table == null) {
1477                     if (tables) {
1478                         throw new ArgumentException(SR.GetString(SR.Cache_null_table_in_tables),
1479                                             "tables");
1480                     }
1481                     else {
1482                         throw new ArgumentNullException("table");
1483                     }
1484                 }
1485                 else if (table.Length == 0) {
1486                     if (tables) {
1487                         throw new ArgumentException(SR.GetString(SR.Cache_null_table_in_tables),
1488                                     "tables");
1489                     }
1490                     else {
1491                         throw new ArgumentException(SR.GetString(SR.Cache_null_table),
1492                                     "table");
1493                     }
1494                 }
1495             }
1496
1497             try {
1498                 sqlConnection = new SqlConnection(connectionString);
1499                 sqlConnection.Open();
1500
1501                 sqlCmd = new SqlCommand(null, sqlConnection);
1502
1503                 if (tableOp) {
1504                     sqlCmd.CommandText = !disable ? SQL_REGISTER_TABLE_SP_DBO : SQL_UNREGISTER_TABLE_SP_DBO;
1505                     sqlCmd.CommandType = CommandType.StoredProcedure;
1506                     sqlCmd.Parameters.Add(new SqlParameter("@tableName", SqlDbType.NVarChar, table.Length));
1507                     sqlCmd.Parameters[0].Value = table;
1508                 }
1509                 else {
1510                     if (!disable) {
1511                         // Enable the database
1512                         sqlCmd.CommandText = String.Format(CultureInfo.InvariantCulture,
1513                                                 SQL_CREATE_ENABLE_DATABASE_SP,
1514                                                 SqlCacheDependencyManager.SQL_NOTIF_TABLE,
1515                                                 SqlCacheDependencyManager.SQL_POLLING_SP,
1516                                                 SQL_REGISTER_TABLE_SP,
1517                                                 SQL_TRIGGER_NAME_POSTFIX,
1518                                                 SQL_UNREGISTER_TABLE_SP,
1519                                                 SQL_QUERY_REGISTERED_TABLES_SP,
1520                                                 SQL_UPDATE_CHANGE_ID_SP);
1521                         sqlCmd.CommandType = CommandType.Text;
1522                     }
1523                     else {
1524                         // Disable the database
1525                         sqlCmd.CommandText = String.Format(CultureInfo.InvariantCulture,
1526                                                 SQL_DISABLE_DATABASE,
1527                                                 SqlCacheDependencyManager.SQL_NOTIF_TABLE,
1528                                                 SqlCacheDependencyManager.SQL_POLLING_SP,
1529                                                 SQL_REGISTER_TABLE_SP,
1530                                                 SQL_UNREGISTER_TABLE_SP,
1531                                                 SQL_QUERY_REGISTERED_TABLES_SP,
1532                                                 SQL_UPDATE_CHANGE_ID_SP);
1533                         sqlCmd.CommandType = CommandType.Text;
1534                     }
1535                 }
1536
1537                 Debug.Trace("SqlCacheDependencyAdmin", "\n" +
1538                     sqlCmd.CommandText);
1539
1540                 sqlCmd.ExecuteNonQuery();
1541
1542                 // Clear CommandText so that error handling won't mistakenly
1543                 // report it as a SQL error
1544                 sqlCmd.CommandText = String.Empty;
1545
1546                 // If we are being called as part of an ASP.NET Http request
1547                 if (HttpRuntime.IsAspNetAppDomain) {
1548                     // Need to update the status of all initialized databases
1549                     //
1550                     // Note: we can actually try to figure out which database we want
1551                     // to update based on the connectionString.  But updating
1552                     // all initialized ones are good enough.
1553                     SqlCacheDependencyManager.UpdateAllDatabaseNotifState();
1554                 }
1555             }
1556             catch (Exception e) {
1557                 SqlException sqlExpt = e as SqlException;
1558                 bool throwError = true;
1559
1560                 if (sqlExpt != null) {
1561                     Debug.Trace("SqlCacheDependencyAdmin", "SqlException:"+
1562                         "\nMessage=" + sqlExpt.Message +
1563                         "\nNumber=" + sqlExpt.Number);
1564
1565                     if (sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_SP_NOT_FOUND) {
1566                         if (!disable) {
1567                             if (table != null) {
1568                                 throw new DatabaseNotEnabledForNotificationException(
1569                                     SR.GetString(SR.Database_not_enabled_for_notification,
1570                                                                     sqlConnection.Database));
1571                             }
1572                             else {
1573                                 throw;
1574                             }
1575                         }
1576                         else {
1577                             if (table != null) {
1578                                 throw new DatabaseNotEnabledForNotificationException(
1579                                     SR.GetString(SR.Cant_disable_table_sql_cache_dep));
1580                             }
1581                             else {
1582                                 // If we cannot find the SP for disabling the database, it maybe because
1583                                 // SQL cache dep is already disabled, or the SP is missing.
1584                                 // In either case, we just exit silently.
1585                                 throwError = false;
1586                             }
1587                         }
1588                     }
1589                     else if (sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_PERMISSION_DENIED_ON_OBJECT ||
1590                             sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_PERMISSION_DENIED_ON_DATABASE ||
1591                             sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_PERMISSION_DENIED_ON_USER ||
1592                             sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_NO_GRANT_PERMISSION) {
1593                         string error;
1594
1595                         if (!disable) {
1596                             if (table != null) {
1597                                 error = SR.Permission_denied_table_enable_notification;
1598                             }
1599                             else {
1600                                 error = SR.Permission_denied_database_enable_notification;
1601                             }
1602                         }
1603                         else {
1604                             if (table != null) {
1605                                 error = SR.Permission_denied_table_disable_notification;
1606                             }
1607                             else {
1608                                 error = SR.Permission_denied_database_disable_notification;
1609                             }
1610                         }
1611
1612                         if (table != null) {
1613                             throw new HttpException(
1614                                 SR.GetString(error, table));
1615                         }
1616                         else {
1617                             throw new HttpException(
1618                                 SR.GetString(error));
1619                         }
1620                     }
1621                     else if (sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_ADHOC &&
1622                             sqlExpt.Message == SqlCacheDependencyManager.SQL_CUSTOM_ERROR_TABLE_NOT_FOUND) {
1623                         Debug.Assert(!disable && table != null, "disable && table != null");
1624                         throw new HttpException(SR.GetString(SR.Cache_dep_table_not_found, table));
1625                     }
1626                 }
1627
1628                 string  errString;
1629
1630                 if (sqlCmd != null && sqlCmd.CommandText.Length != 0) {
1631                     errString = SR.GetString(SR.Cant_connect_sql_cache_dep_database_admin_cmdtxt,
1632                                     sqlCmd.CommandText);
1633                 }
1634                 else {
1635                     errString = SR.GetString(SR.Cant_connect_sql_cache_dep_database_admin);
1636                 }
1637
1638                 if (throwError) {
1639                     throw new HttpException(errString, e);
1640                 }
1641             }
1642             finally {
1643                 if (sqlConnection != null) {
1644                     sqlConnection.Close();
1645                 }
1646             }
1647         }
1648
1649         public static void EnableNotifications(string connectionString) {
1650             SetupNotifications(0, null, connectionString);
1651         }
1652
1653         public static void DisableNotifications(string connectionString) {
1654             SetupNotifications(SETUP_DISABLE, null, connectionString);
1655         }
1656
1657         public static void EnableTableForNotifications(string connectionString, string table) {
1658             SetupNotifications(SETUP_TABLE, table, connectionString);
1659         }
1660
1661         public static void EnableTableForNotifications(string connectionString, string[] tables) {
1662             if (tables == null) {
1663                 throw new ArgumentNullException("tables");
1664             }
1665
1666             foreach (string table in tables) {
1667                 SetupNotifications(SETUP_TABLES, table, connectionString);
1668             }
1669         }
1670
1671         public static void DisableTableForNotifications(string connectionString, string table) {
1672             SetupNotifications(SETUP_TABLE|SETUP_DISABLE, table, connectionString);
1673         }
1674
1675         public static void DisableTableForNotifications(string connectionString, string[] tables) {
1676             if (tables == null) {
1677                 throw new ArgumentNullException("tables");
1678             }
1679
1680             foreach (string table in tables) {
1681                 SetupNotifications(SETUP_TABLES|SETUP_DISABLE, table, connectionString);
1682             }
1683         }
1684
1685         static string[] GetEnabledTables(string connectionString) {
1686
1687             SqlDataReader       sqlReader = null;
1688             SqlConnection       sqlConn = null;
1689             SqlCommand          sqlCmd = null;
1690             ArrayList           tablesObj = new ArrayList();
1691
1692             try {
1693                 sqlConn = new SqlConnection(connectionString);
1694                 sqlConn.Open();
1695
1696                 sqlCmd = new SqlCommand(SQL_QUERY_REGISTERED_TABLES_SP_DBO, sqlConn);
1697                 sqlCmd.CommandType = CommandType.StoredProcedure;
1698
1699                 sqlReader = sqlCmd.ExecuteReader();
1700
1701                 while(sqlReader.Read()) {
1702                     tablesObj.Add(sqlReader.GetString(0));
1703                 }
1704             }
1705             catch (Exception e) {
1706                 SqlException sqlExpt = e as SqlException;
1707
1708                 if (sqlExpt != null &&
1709                     sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_SP_NOT_FOUND) {
1710
1711                         throw new DatabaseNotEnabledForNotificationException(
1712                                 SR.GetString(SR.Database_not_enabled_for_notification,
1713                                                                 sqlConn.Database));
1714                 }
1715                 else {
1716                     throw new HttpException(SR.GetString(SR.Cant_get_enabled_tables_sql_cache_dep), e);
1717                 }
1718             }
1719             finally {
1720                 try {
1721                     if (sqlReader != null) {
1722                         sqlReader.Close();
1723                     }
1724
1725                     if (sqlConn != null) {
1726                         sqlConn.Close();
1727                     }
1728                 }
1729                 catch {
1730                 }
1731             }
1732
1733             return (string[])tablesObj.ToArray(Type.GetType("System.String"));
1734         }
1735
1736         public static string[] GetTablesEnabledForNotifications(string connectionString) {
1737             return GetEnabledTables(connectionString);
1738         }
1739     }
1740 }
1741
1742