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 SqlCacheDependency(string databaseEntryName, string tableName)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.Cache.InternalCache.Get(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.Cache.InternalCache.Get(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 DependencyDispose()98 protected override void DependencyDispose() { 99 if (_sql7DatabaseState != null) { 100 SqlCacheDependencyManager.Release(_sql7DatabaseState); 101 } 102 } 103 104 // For SQL9, we use SqlDependency SqlCacheDependency(SqlCommand sqlCmd)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 bug. 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 InitUniqueID()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 GetUniqueID()144 public override string GetUniqueID() { 145 #if DBG 146 Debug.Assert(_isUniqueIDInitialized == true, "_isUniqueIDInitialized == true"); 147 #endif 148 return _uniqueID; 149 } 150 CheckPermission()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 OnSQL9SqlDependencyChanged(Object sender, SqlNotificationEventArgs e)172 void OnSQL9SqlDependencyChanged(Object sender, SqlNotificationEventArgs e) { 173 Debug.Trace("SqlCacheDependency", "SQL9 dependency changed: depId=" + _sqlYukonDep.Id); 174 NotifyDependencyChanged(sender, e); 175 } 176 SqlCacheDependency()177 private SqlCacheDependency() { 178 CreateSqlDep(null); 179 InitUniqueID(); 180 } 181 CreateSqlDep(SqlCommand sqlCmd)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 ValidateOutputCacheDependencyString(string depString, bool page)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 CreateOutputCacheDependency(string dependency)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 CreateSql7SqlCacheDependencyForOutputCache(string database, string table, string depString)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 GetDependKey(string database, string tableName)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 VerifyAndRemoveEscapeCharacters(string s)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 ParseSql7OutputCacheDependency(string outputCacheString)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 DatabaseNotEnabledForNotificationException()424 public DatabaseNotEnabledForNotificationException() { 425 } 426 427 DatabaseNotEnabledForNotificationException(String message)428 public DatabaseNotEnabledForNotificationException(String message) 429 : base(message) { 430 } 431 432 DatabaseNotEnabledForNotificationException(string message, Exception innerException)433 public DatabaseNotEnabledForNotificationException(string message, Exception innerException) 434 : base (message, innerException) { 435 } 436 437 DatabaseNotEnabledForNotificationException(SerializationInfo info, StreamingContext context)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 TableNotEnabledForNotificationException()448 public TableNotEnabledForNotificationException() { 449 } 450 451 TableNotEnabledForNotificationException(String message)452 public TableNotEnabledForNotificationException(String message) 453 : base(message) { 454 } 455 456 TableNotEnabledForNotificationException(string message, Exception innerException)457 public TableNotEnabledForNotificationException(string message, Exception innerException) 458 : base (message, innerException) { 459 } 460 461 TableNotEnabledForNotificationException(SerializationInfo info, StreamingContext context)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 Dispose()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 DatabaseNotifState(string database, string connection, int polltime)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 GetConnection(out SqlConnection sqlConn, out SqlCommand sqlCmd)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 ReleaseConnection(ref SqlConnection sqlConn, ref SqlCommand sqlCmd, bool error)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 GetMoniterKey(string database, string table)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 Dispose(int waitTimeoutMs)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 GetDatabaseConfig(string database)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. InitPolling(string database)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. PollCallback(object state)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. PollDatabaseForChanges(DatabaseNotifState dbState, bool fromTimer)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 CacheStoreProvider cacheInternal = HttpRuntime.Cache.InternalCache; 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.Get(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.Add(monitorKey, changeId, new CacheInsertOptions() { Priority = CacheItemPriority.NotRemovable }); 877 dbState._tables.Add(tableName, null); 878 } 879 else if (changeId != (int)obj) { 880 Debug.Assert(dbState._tables.ContainsKey(tableName), 881 "DatabaseNotifStae._tables and internal cache keys should be in-sync"); 882 883 Debug.Trace("SqlCacheDependencyManagerPolling", 884 "Change Database=" + dbState._database+ "; tableName=" + tableName + "; old=" + (int)obj + "; new=" + changeId); 885 886 // ChangeId is different. It means some table changes have happened. 887 // Update local cache value 888 cacheInternal.Insert(monitorKey, changeId, new CacheInsertOptions() { Priority = CacheItemPriority.NotRemovable }); 889 } 890 891 originalTables.Remove(tableName); 892 } 893 894 // What's left in originalTables are the ones that're no longer 895 // contained in the AspNet_SqlCacheTablesForChangeNotification 896 // table in the database. 897 898 // Remove tables which are no longer enabled for notification 899 foreach(object key in originalTables.Keys) { 900 dbState._tables.Remove((string)key); 901 cacheInternal.Remove(GetMoniterKey(dbState._database, (string)key)); 902 903 Debug.Trace("SqlCacheDependencyManagerPolling", 904 "Remove Database=" + dbState._database+ "; key=" + key); 905 } 906 907 // Clear old error, if any. 908 if (dbState._pollSqlError != 0) { 909 dbState._pollSqlError = 0; 910 } 911 } 912 catch (Exception e) { 913 pollExpt = e; 914 915 sqlExpt = e as SqlException; 916 if (sqlExpt != null) { 917 Debug.Trace("SqlCacheDependencyManagerPolling", "Error reading rows. SqlException:"+ 918 "\nMessage=" + sqlExpt.Message + 919 "\nNumber=" + sqlExpt.Number); 920 921 dbState._pollSqlError = sqlExpt.Number; 922 } 923 else { 924 dbState._pollSqlError = 0; 925 Debug.Trace("SqlCacheDependencyManagerPolling", "Error reading rows. Exception:"+ pollExpt); 926 } 927 } 928 finally { 929 try { 930 if (sqlReader != null) { 931 sqlReader.Close(); 932 } 933 934 dbState.ReleaseConnection(ref sqlConn, ref sqlCmd, pollExpt != null); 935 } 936 catch { 937 } 938 939 // Need locking because EnsureTableIsRegisteredAndPolled() assumes 940 // the fields in a dbState are set atomically. 941 lock(dbState) { 942 dbState._pollExpt = pollExpt; 943 944 // If we have changed from being enabled to disabled, and 945 // it's because we cannot find the SP for polling, it means 946 // the database is no longer enabled for sql cache dependency. 947 // we should invalidate all cache items depending on any 948 // table on this database 949 if (dbState._notifEnabled && !notifEnabled && 950 pollExpt != null && dbState._pollSqlError == SQL_EXCEPTION_SP_NOT_FOUND) { 951 foreach(object key in dbState._tables.Keys) { 952 try { 953 cacheInternal.Remove(GetMoniterKey(dbState._database, (string)key)); 954 } 955 catch {} 956 957 Debug.Trace("SqlCacheDependencyManagerPolling", 958 "Changed to disabled. Remove Database=" + dbState._database+ "; key=" + key); 959 } 960 961 // Since we have removed all the cache items related to this database, 962 // the _refCount of this database will drop to zero, and thus the timer 963 // callback will not poll this database. 964 // So we have to cleanup _tables now. 965 dbState._tables.Clear(); 966 } 967 968 dbState._notifEnabled = notifEnabled; 969 dbState._utcTablesUpdated = DateTime.UtcNow; 970 971 Debug.Trace("SqlCacheDependencyManagerPolling", "dbState:_pollExpt="+ dbState._pollExpt + 972 "; _pollSqlError=" + dbState._pollSqlError + "; _notifEnabled=" + dbState._notifEnabled + 973 "; __utcTablesUpdated=" + dbState._utcTablesUpdated); 974 } 975 976 // Mark dbState as initialized by PollCallback for the first time. 977 // EnsureTableIsRegisteredAndPolled() depends on this. 978 if (dbState._init != true) { 979 dbState._init = true; 980 } 981 982 Interlocked.Decrement(ref s_activePolling); 983 984 // Release the lock 985 Interlocked.Exchange(ref dbState._rqInCallback, 0); 986 } 987 } 988 catch { throw; } // Prevent Exception Filter Security Issue (ASURT 122835) 989 } 990 991 // Called by SqlCacheDependency.GetDependKey EnsureTableIsRegisteredAndPolled(string database, string table)992 static internal void EnsureTableIsRegisteredAndPolled(string database, string table) { 993 bool doubleChecked = false; 994 995 // First check. If the cache key exists, that means the first poll request 996 // for this table has successfully completed 997 Debug.Trace("SqlCacheDependencyManagerCheck", 998 "Check is called. Database=" + database+ "; table=" + table); 999 1000 if (HttpRuntime.Cache.InternalCache.Get(GetMoniterKey(database, table)) != null) { 1001 return; 1002 } 1003 1004 // Initilize polling for this database, if needed. 1005 InitPolling(database); 1006 1007 // Wait until this database is initialized by PollCallback for the first time 1008 DatabaseNotifState dbState = (DatabaseNotifState)s_DatabaseNotifStates[database]; 1009 1010 if (!dbState._init) { 1011 int timeout; 1012 HttpContext context = HttpContext.Current; 1013 1014 if (context == null) { 1015 timeout = 30; 1016 } 1017 else { 1018 timeout = Math.Max(context.Timeout.Seconds / 3, 30); 1019 } 1020 DateTime waitLimit = DateTime.UtcNow.Add(new TimeSpan(0, 0, timeout)); 1021 1022 Debug.Trace("SqlCacheDependencyManagerCheck", "Waiting for intialization: timeout=" + timeout + "s"); 1023 1024 for (;;) { 1025 if (dbState._init) 1026 break; 1027 1028 Thread.Sleep(250); 1029 1030 // only apply timeout if a managed debugger is not attached 1031 if (!System.Diagnostics.Debugger.IsAttached && DateTime.UtcNow > waitLimit) { 1032 // We've waited and retried for waitLimit amount of time. 1033 // Still PollCallback haven't finished its first call for this database 1034 // Assume we cannot connect to SQL. 1035 throw new HttpException( 1036 SR.GetString(SR.Cant_connect_sql_cache_dep_database_polling, database)); 1037 } 1038 } 1039 } 1040 1041 while(true) { 1042 DateTime utcTablesLastUpdated; 1043 bool dbRegistered; 1044 Exception pollException; 1045 int pollSqlError = 0; 1046 1047 lock(dbState) { 1048 Debug.Trace("SqlCacheDependencyManagerCheck", "dbState:_pollExpt="+ dbState._pollExpt + 1049 "; _pollSqlError=" + dbState._pollSqlError + "; _notifEnabled=" + dbState._notifEnabled ); 1050 1051 pollException = dbState._pollExpt; 1052 if (pollException != null) { 1053 pollSqlError = dbState._pollSqlError; 1054 } 1055 1056 utcTablesLastUpdated = dbState._utcTablesUpdated; 1057 dbRegistered = dbState._notifEnabled; 1058 } 1059 1060 if (pollException == null && // No exception from polling 1061 dbRegistered && // The database is registered 1062 dbState._tables.ContainsKey(table)) { // The table is also registered 1063 Debug.Trace("SqlCacheDependencyManagerCheck", "The table is registered too. Exit now!"); 1064 return; 1065 } 1066 1067 // Either we hit an error in the last polling, or the database or the table 1068 // isn't registered. 1069 // 1070 // See if we can double check. Double checking is needed because the 1071 // results we just looked at might be collected only at last poll time, 1072 // which could be quite old, depending on the pollTime setting. 1073 // 1074 // The scenario we try to solve is: 1075 // 1. Let's say polling is configured to happen every 1 minute, and we just poll. 1076 // 2. A page then registers a table for notification. 1077 // 3. The page then try to use SqlCacheDependency on that table. 1078 // 4. If we don't call UpdateDatabaseNotifStat to query the database now, 1079 // we'll have to wait for a whole minute before we can use that table. 1080 // 1081 1082 // To prevent the SQL server from being bombarded by this kind of per-client-request 1083 // adhoc check, we only allow a max of one double check per second per database 1084 if (!doubleChecked && 1085 DateTime.UtcNow - utcTablesLastUpdated >= OneSec) { 1086 1087 Debug.Trace("SqlCacheDependencyManagerCheck", "Double check..."); 1088 UpdateDatabaseNotifState(database); 1089 doubleChecked = true; 1090 continue; 1091 } 1092 1093 if (pollSqlError == SQL_EXCEPTION_SP_NOT_FOUND) { 1094 // This error happens if the database isn't enabled for notification. 1095 // This doesn't count as a real Sql error 1096 Debug.Assert(dbRegistered == false, "When this error happened, we shouldn't be able to poll the database"); 1097 pollException = null; 1098 } 1099 1100 // Report any error if we failed in the last PollCallback 1101 if (pollException != null) { 1102 string error; 1103 1104 if (pollSqlError == SQL_EXCEPTION_PERMISSION_DENIED_ON_OBJECT || 1105 pollSqlError == SQL_EXCEPTION_PERMISSION_DENIED_ON_DATABASE) { 1106 error = SR.Permission_denied_database_polling; 1107 } 1108 else { 1109 error = SR.Cant_connect_sql_cache_dep_database_polling; 1110 } 1111 1112 HttpException outerException = new HttpException( 1113 SR.GetString(error, database), pollException); 1114 1115 outerException.SetFormatter(new UseLastUnhandledErrorFormatter(outerException)); 1116 1117 throw outerException; 1118 } 1119 1120 // If we don't get any error, then either the database or the table isn't registered. 1121 if (dbRegistered == false) { 1122 throw new DatabaseNotEnabledForNotificationException( 1123 SR.GetString(SR.Database_not_enabled_for_notification, database)); 1124 } 1125 else { 1126 throw new TableNotEnabledForNotificationException( 1127 SR.GetString(SR.Table_not_enabled_for_notification, table, database)); 1128 } 1129 } 1130 } 1131 1132 // Do a on-demand polling of the database in order to obtain the latest 1133 // data. UpdateDatabaseNotifState(string database)1134 internal static void UpdateDatabaseNotifState(string database) { 1135 using (new ApplicationImpersonationContext()) { 1136 Debug.Trace("SqlCacheDependencyManager", "UpdateDatabaseNotifState called for database " + database + 1137 "; running as " + WindowsIdentity.GetCurrent().Name); 1138 1139 // Make sure we have initialized the polling of this database 1140 InitPolling(database); 1141 Debug.Assert(s_DatabaseNotifStates[database] != null, "s_DatabaseNotifStates[database] != null"); 1142 1143 PollDatabaseForChanges((DatabaseNotifState)s_DatabaseNotifStates[database], false /*fromTimer*/); 1144 } 1145 } 1146 1147 // Update all initialized databases UpdateAllDatabaseNotifState()1148 internal static void UpdateAllDatabaseNotifState() { 1149 lock(s_DatabaseNotifStates) { 1150 foreach(DictionaryEntry entry in s_DatabaseNotifStates) { 1151 DatabaseNotifState state = (DatabaseNotifState)entry.Value; 1152 if (state._init) { 1153 UpdateDatabaseNotifState((string)entry.Key); 1154 } 1155 } 1156 } 1157 } 1158 AddRef(string database)1159 internal static DatabaseNotifState AddRef(string database) { 1160 DatabaseNotifState dbState = (DatabaseNotifState)s_DatabaseNotifStates[database]; 1161 Debug.Assert(dbState != null, "AddRef: s_DatabaseNotifStates[database] != null"); 1162 1163 #if DBG 1164 int res = 1165 #endif 1166 Interlocked.Increment(ref dbState._refCount); 1167 #if DBG 1168 Debug.Trace("SqlCacheDependencyManager", "AddRef called for " + database + "; res=" + res); 1169 Debug.Assert(res > 0, "AddRef result for " + database + " must be > 0"); 1170 #endif 1171 return dbState; 1172 } 1173 Release(DatabaseNotifState dbState)1174 internal static void Release(DatabaseNotifState dbState) { 1175 #if DBG 1176 int res = 1177 #endif 1178 Interlocked.Decrement(ref dbState._refCount); 1179 #if DBG 1180 Debug.Trace("SqlCacheDependencyManager", "Release called for " + dbState._database + "; res=" + res); 1181 Debug.Assert(res >= 0, "Release result for " + dbState._database + " must be >= 0"); 1182 #endif 1183 } 1184 } 1185 1186 [AspNetHostingPermission(SecurityAction.LinkDemand, Level=AspNetHostingPermissionLevel.High)] 1187 public static class SqlCacheDependencyAdmin { 1188 1189 // Note: 1190 // In all the SQL statements below, we will do an unlocking 1191 // SELECT, followed by a locking SELECT. This is to avoid 1192 // duplication operation. 1193 1194 // 1195 // {0} = SQL_NOTIF_TABLE 1196 // {1} = SQL_POLLING_SP 1197 // {2} = SQL_REGISTER_TABLE_SP 1198 // {3} = SQL_TRIGGER_NAME_POSTFIX 1199 // {4} = SQL_UNREGISTER_TABLE_SP 1200 // {5} = SQL_QUERY_REGISTERED_TABLES_SP 1201 // {6} = SQL_UPDATE_CHANGE_ID_SP 1202 // 1203 internal const string SQL_CREATE_ENABLE_DATABASE_SP = 1204 "/* Create notification table */ \n" + 1205 "IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{0}' AND type = 'U') \n" + 1206 " IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{0}' AND type = 'U') \n" + 1207 " CREATE TABLE dbo.{0} (\n" + 1208 " tableName NVARCHAR(450) NOT NULL PRIMARY KEY,\n" + 1209 " notificationCreated DATETIME NOT NULL DEFAULT(GETDATE()),\n" + 1210 " changeId INT NOT NULL DEFAULT(0)\n" + 1211 " )\n" + 1212 "\n" + 1213 "/* Create polling SP */\n" + 1214 "IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{1}' AND type = 'P') \n" + 1215 " IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{1}' AND type = 'P') \n" + 1216 " EXEC('CREATE PROCEDURE dbo.{1} AS\n" + 1217 " SELECT tableName, changeId FROM dbo.{0}\n" + 1218 " RETURN 0')\n" + 1219 "\n" + 1220 "/* Create SP for registering a table. */ \n" + 1221 "IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{2}' AND type = 'P') \n" + 1222 " IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{2}' AND type = 'P') \n" + 1223 " EXEC('CREATE PROCEDURE dbo.{2} \n" + 1224 " @tableName NVARCHAR(450) \n" + 1225 " AS\n" + 1226 " BEGIN\n" + 1227 "\n" + 1228 " DECLARE @triggerName AS NVARCHAR(3000) \n" + 1229 " DECLARE @fullTriggerName AS NVARCHAR(3000)\n" + 1230 " DECLARE @canonTableName NVARCHAR(3000) \n" + 1231 " DECLARE @quotedTableName NVARCHAR(3000) \n" + 1232 "\n" + 1233 " /* Create the trigger name */ \n" + 1234 " SET @triggerName = REPLACE(@tableName, ''['', ''__o__'') \n" + 1235 " SET @triggerName = REPLACE(@triggerName, '']'', ''__c__'') \n" + 1236 " SET @triggerName = @triggerName + ''{3}'' \n" + 1237 " SET @fullTriggerName = ''dbo.['' + @triggerName + '']'' \n" + 1238 "\n" + 1239 " /* Create the cannonicalized table name for trigger creation */ \n" + 1240 " /* Do not touch it if the name contains other delimiters */ \n" + 1241 " IF (CHARINDEX(''.'', @tableName) <> 0 OR \n" + 1242 " CHARINDEX(''['', @tableName) <> 0 OR \n" + 1243 " CHARINDEX('']'', @tableName) <> 0) \n" + 1244 " SET @canonTableName = @tableName \n" + 1245 " ELSE \n" + 1246 " SET @canonTableName = ''['' + @tableName + '']'' \n" + 1247 "\n" + 1248 " /* First make sure the table exists */ \n" + 1249 " IF (SELECT OBJECT_ID(@tableName, ''U'')) IS NULL \n" + 1250 " BEGIN \n" + 1251 " RAISERROR (''" + SqlCacheDependencyManager.SQL_CUSTOM_ERROR_TABLE_NOT_FOUND + "'', 16, 1) \n" + 1252 " RETURN \n" + 1253 " END \n" + 1254 "\n" + 1255 " BEGIN TRAN\n" + 1256 " /* Insert the value into the notification table */ \n" + 1257 " IF NOT EXISTS (SELECT tableName FROM dbo.{0} WITH (NOLOCK) WHERE tableName = @tableName) \n" + 1258 " IF NOT EXISTS (SELECT tableName FROM dbo.{0} WITH (TABLOCKX) WHERE tableName = @tableName) \n" + 1259 " INSERT dbo.{0} \n" + 1260 " VALUES (@tableName, GETDATE(), 0)\n" + 1261 "\n" + 1262 " /* Create the trigger */ \n" + 1263 " SET @quotedTableName = QUOTENAME(@tableName, '''''''') \n" + 1264 " IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = ''TR'') \n" + 1265 " IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = ''TR'') \n" + 1266 " EXEC(''CREATE TRIGGER '' + @fullTriggerName + '' ON '' + @canonTableName +''\n" + 1267 " FOR INSERT, UPDATE, DELETE AS BEGIN\n" + 1268 " SET NOCOUNT ON\n" + 1269 " EXEC dbo.{6} N'' + @quotedTableName + ''\n" + 1270 " END\n" + 1271 " '')\n" + 1272 " COMMIT TRAN\n" + 1273 " END\n" + 1274 " ')\n" + 1275 "\n" + 1276 "/* Create SP for updating the change Id of a table. */ \n" + 1277 "IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{6}' AND type = 'P') \n" + 1278 " IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{6}' AND type = 'P') \n" + 1279 " EXEC('CREATE PROCEDURE dbo.{6} \n" + 1280 " @tableName NVARCHAR(450) \n" + 1281 " AS\n" + 1282 "\n" + 1283 " BEGIN \n" + 1284 " UPDATE dbo.{0} WITH (ROWLOCK) SET changeId = changeId + 1 \n" + 1285 " WHERE tableName = @tableName\n" + 1286 " END\n" + 1287 " ')\n" + 1288 "\n" + 1289 "/* Create SP for unregistering a table. */ \n" + 1290 "IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{4}' AND type = 'P') \n" + 1291 " IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{4}' AND type = 'P') \n" + 1292 " EXEC('CREATE PROCEDURE dbo.{4} \n" + 1293 " @tableName NVARCHAR(450) \n" + 1294 " AS\n" + 1295 " BEGIN\n" + 1296 "\n" + 1297 " BEGIN TRAN\n" + 1298 " DECLARE @triggerName AS NVARCHAR(3000) \n" + 1299 " DECLARE @fullTriggerName AS NVARCHAR(3000)\n" + 1300 " SET @triggerName = REPLACE(@tableName, ''['', ''__o__'') \n" + 1301 " SET @triggerName = REPLACE(@triggerName, '']'', ''__c__'') \n" + 1302 " SET @triggerName = @triggerName + ''{3}'' \n" + 1303 " SET @fullTriggerName = ''dbo.['' + @triggerName + '']'' \n" + 1304 "\n" + 1305 " /* Remove the table-row from the notification table */ \n" + 1306 " IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = ''{0}'' AND type = ''U'') \n" + 1307 " IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = ''{0}'' AND type = ''U'') \n" + 1308 " DELETE FROM dbo.{0} WHERE tableName = @tableName \n" + 1309 "\n" + 1310 " /* Remove the trigger */ \n" + 1311 " IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = ''TR'') \n" + 1312 " IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = ''TR'') \n" + 1313 " EXEC(''DROP TRIGGER '' + @fullTriggerName) \n" + 1314 "\n" + 1315 " COMMIT TRAN\n" + 1316 " END\n" + 1317 " ')\n" + 1318 "\n" + 1319 "/* Create SP for querying all registered table */ \n" + 1320 "IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{5}' AND type = 'P') \n" + 1321 " IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{5}' AND type = 'P') \n" + 1322 " EXEC('CREATE PROCEDURE dbo.{5} \n" + 1323 " AS\n" + 1324 " SELECT tableName FROM dbo.{0}" + 1325 " ')\n" + 1326 "\n" + 1327 "/* Create roles and grant them access to SP */ \n" + 1328 "IF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole = 1 AND name = N'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess') \n" + 1329 " EXEC sp_addrole N'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess' \n" + 1330 "\n" + 1331 "GRANT EXECUTE ON dbo.{1} to aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess\n" + 1332 "\n"; 1333 1334 1335 // 1336 // {0} = SQL_NOTIF_TABLE 1337 // {1} = SQL_POLLING_SP 1338 // {2} = SQL_REGISTER_TABLE_SP 1339 // {3} = SQL_UNREGISTER_TABLE_SP 1340 // {4} = SQL_QUERY_REGISTERED_TABLES_SP 1341 // {5} = SQL_UPDATE_CHANGE_ID_SP 1342 // 1343 internal const string SQL_DISABLE_DATABASE = 1344 "/* Remove notification table */ \n" + 1345 "IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{0}' AND type = 'U') \n" + 1346 " IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{0}' AND type = 'U') \n" + 1347 " BEGIN\n" + 1348 " /* First, unregister all registered tables */ \n" + 1349 " DECLARE tables_cursor CURSOR FOR \n" + 1350 " SELECT tableName FROM dbo.{0} \n" + 1351 " DECLARE @tableName AS NVARCHAR(450) \n" + 1352 "\n" + 1353 " OPEN tables_cursor \n" + 1354 "\n" + 1355 " /* Perform the first fetch. */ \n" + 1356 " FETCH NEXT FROM tables_cursor INTO @tableName \n" + 1357 "\n" + 1358 " /* Check @@FETCH_STATUS to see if there are any more rows to fetch. */ \n" + 1359 " WHILE @@FETCH_STATUS = 0 \n" + 1360 " BEGIN \n" + 1361 " EXEC {3} @tableName \n" + 1362 "\n" + 1363 " /* This is executed as long as the previous fetch succeeds. */ \n" + 1364 " FETCH NEXT FROM tables_cursor INTO @tableName \n" + 1365 " END \n" + 1366 " CLOSE tables_cursor \n" + 1367 " DEALLOCATE tables_cursor \n" + 1368 "\n" + 1369 " /* Drop the table */\n" + 1370 " DROP TABLE dbo.{0} \n" + 1371 " END\n" + 1372 "\n" + 1373 "/* Remove polling SP */ \n" + 1374 "IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{1}' AND type = 'P') \n" + 1375 " IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{1}' AND type = 'P') \n" + 1376 " DROP PROCEDURE dbo.{1} \n" + 1377 "\n" + 1378 "/* Remove SP that registers a table */ \n" + 1379 "IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{2}' AND type = 'P') \n" + 1380 " IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{2}' AND type = 'P') \n" + 1381 " DROP PROCEDURE dbo.{2} \n" + 1382 "\n" + 1383 "/* Remove SP that unregisters a table */ \n" + 1384 "IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{3}' AND type = 'P') \n" + 1385 " IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{3}' AND type = 'P') \n" + 1386 " DROP PROCEDURE dbo.{3} \n"+ 1387 "\n" + 1388 "/* Remove SP that querys the registered table */ \n" + 1389 "IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{4}' AND type = 'P') \n" + 1390 " IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{4}' AND type = 'P') \n" + 1391 " DROP PROCEDURE dbo.{4} \n" + 1392 "\n" + 1393 "/* Remove SP that updates the change Id of a table. */ \n" + 1394 "IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{5}' AND type = 'P') \n" + 1395 " IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{5}' AND type = 'P') \n" + 1396 " DROP PROCEDURE dbo.{5} \n" + 1397 "\n" + 1398 "/* Drop roles */ \n" + 1399 "IF EXISTS ( SELECT name FROM sysusers WHERE issqlrole = 1 AND name = 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess') BEGIN\n" + 1400 DROP_MEMBERS + 1401 " EXEC sp_droprole 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess'\n" + 1402 "END\n"; 1403 1404 internal const string DROP_MEMBERS = 1405 "CREATE TABLE #aspnet_RoleMembers \n" + 1406 "( \n" + 1407 " Group_name sysname, \n" + 1408 " Group_id smallint, \n" + 1409 " Users_in_group sysname, \n" + 1410 " User_id smallint \n" + 1411 ") \n" + 1412 "INSERT INTO #aspnet_RoleMembers \n" + 1413 "EXEC sp_helpuser 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess' \n" + 1414 " \n" + 1415 "DECLARE @user_id smallint \n" + 1416 "DECLARE @cmd nvarchar(500) \n" + 1417 "DECLARE c1 CURSOR FORWARD_ONLY FOR \n" + 1418 " SELECT User_id FROM #aspnet_RoleMembers \n" + 1419 " \n" + 1420 "OPEN c1 \n" + 1421 " \n" + 1422 "FETCH c1 INTO @user_id \n" + 1423 "WHILE (@@fetch_status = 0) \n" + 1424 "BEGIN \n" + 1425 " SET @cmd = 'EXEC sp_droprolemember ''aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess'',''' + USER_NAME(@user_id) + '''' \n" + 1426 " EXEC (@cmd) \n" + 1427 " FETCH c1 INTO @user_id \n" + 1428 "END \n" + 1429 " \n" + 1430 "close c1 \n" + 1431 "deallocate c1 \n"; 1432 1433 1434 internal const string SQL_REGISTER_TABLE_SP = 1435 "AspNet_SqlCacheRegisterTableStoredProcedure"; 1436 1437 internal const string SQL_REGISTER_TABLE_SP_DBO = 1438 "dbo.AspNet_SqlCacheRegisterTableStoredProcedure"; 1439 1440 internal const string SQL_UNREGISTER_TABLE_SP = 1441 "AspNet_SqlCacheUnRegisterTableStoredProcedure"; 1442 1443 internal const string SQL_UNREGISTER_TABLE_SP_DBO = 1444 "dbo.AspNet_SqlCacheUnRegisterTableStoredProcedure"; 1445 1446 internal const string SQL_TRIGGER_NAME_POSTFIX = 1447 "_AspNet_SqlCacheNotification_Trigger"; 1448 1449 internal const string SQL_QUERY_REGISTERED_TABLES_SP = 1450 "AspNet_SqlCacheQueryRegisteredTablesStoredProcedure"; 1451 1452 internal const string SQL_QUERY_REGISTERED_TABLES_SP_DBO = 1453 "dbo.AspNet_SqlCacheQueryRegisteredTablesStoredProcedure"; 1454 1455 internal const string SQL_UPDATE_CHANGE_ID_SP= 1456 "AspNet_SqlCacheUpdateChangeIdStoredProcedure"; 1457 1458 const int SETUP_TABLE = 0x00000001; 1459 const int SETUP_DISABLE = 0x00000002; 1460 const int SETUP_HTTPREQUEST = 0x00000004; 1461 const int SETUP_TABLES = 0x00000008; // We're called in a loop to setup an array of tables. 1462 SetupNotifications(int flags, string table, string connectionString)1463 internal static void SetupNotifications(int flags, string table, string connectionString) { 1464 SqlConnection sqlConnection = null; 1465 SqlCommand sqlCmd = null; 1466 bool tableOp = (flags & (SETUP_TABLES|SETUP_TABLE)) != 0; 1467 bool disable = (flags & SETUP_DISABLE) != 0; 1468 1469 if (tableOp) { 1470 bool tables = (flags & SETUP_TABLES) != 0; 1471 if (table == null) { 1472 if (tables) { 1473 throw new ArgumentException(SR.GetString(SR.Cache_null_table_in_tables), 1474 "tables"); 1475 } 1476 else { 1477 throw new ArgumentNullException("table"); 1478 } 1479 } 1480 else if (table.Length == 0) { 1481 if (tables) { 1482 throw new ArgumentException(SR.GetString(SR.Cache_null_table_in_tables), 1483 "tables"); 1484 } 1485 else { 1486 throw new ArgumentException(SR.GetString(SR.Cache_null_table), 1487 "table"); 1488 } 1489 } 1490 } 1491 1492 try { 1493 sqlConnection = new SqlConnection(connectionString); 1494 sqlConnection.Open(); 1495 1496 sqlCmd = new SqlCommand(null, sqlConnection); 1497 1498 if (tableOp) { 1499 sqlCmd.CommandText = !disable ? SQL_REGISTER_TABLE_SP_DBO : SQL_UNREGISTER_TABLE_SP_DBO; 1500 sqlCmd.CommandType = CommandType.StoredProcedure; 1501 sqlCmd.Parameters.Add(new SqlParameter("@tableName", SqlDbType.NVarChar, table.Length)); 1502 sqlCmd.Parameters[0].Value = table; 1503 } 1504 else { 1505 if (!disable) { 1506 // Enable the database 1507 sqlCmd.CommandText = String.Format(CultureInfo.InvariantCulture, 1508 SQL_CREATE_ENABLE_DATABASE_SP, 1509 SqlCacheDependencyManager.SQL_NOTIF_TABLE, 1510 SqlCacheDependencyManager.SQL_POLLING_SP, 1511 SQL_REGISTER_TABLE_SP, 1512 SQL_TRIGGER_NAME_POSTFIX, 1513 SQL_UNREGISTER_TABLE_SP, 1514 SQL_QUERY_REGISTERED_TABLES_SP, 1515 SQL_UPDATE_CHANGE_ID_SP); 1516 sqlCmd.CommandType = CommandType.Text; 1517 } 1518 else { 1519 // Disable the database 1520 sqlCmd.CommandText = String.Format(CultureInfo.InvariantCulture, 1521 SQL_DISABLE_DATABASE, 1522 SqlCacheDependencyManager.SQL_NOTIF_TABLE, 1523 SqlCacheDependencyManager.SQL_POLLING_SP, 1524 SQL_REGISTER_TABLE_SP, 1525 SQL_UNREGISTER_TABLE_SP, 1526 SQL_QUERY_REGISTERED_TABLES_SP, 1527 SQL_UPDATE_CHANGE_ID_SP); 1528 sqlCmd.CommandType = CommandType.Text; 1529 } 1530 } 1531 1532 Debug.Trace("SqlCacheDependencyAdmin", "\n" + 1533 sqlCmd.CommandText); 1534 1535 sqlCmd.ExecuteNonQuery(); 1536 1537 // Clear CommandText so that error handling won't mistakenly 1538 // report it as a SQL error 1539 sqlCmd.CommandText = String.Empty; 1540 1541 // If we are being called as part of an ASP.NET Http request 1542 if (HttpRuntime.IsAspNetAppDomain) { 1543 // Need to update the status of all initialized databases 1544 // 1545 // Note: we can actually try to figure out which database we want 1546 // to update based on the connectionString. But updating 1547 // all initialized ones are good enough. 1548 SqlCacheDependencyManager.UpdateAllDatabaseNotifState(); 1549 } 1550 } 1551 catch (Exception e) { 1552 SqlException sqlExpt = e as SqlException; 1553 bool throwError = true; 1554 1555 if (sqlExpt != null) { 1556 Debug.Trace("SqlCacheDependencyAdmin", "SqlException:"+ 1557 "\nMessage=" + sqlExpt.Message + 1558 "\nNumber=" + sqlExpt.Number); 1559 1560 if (sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_SP_NOT_FOUND) { 1561 if (!disable) { 1562 if (table != null) { 1563 throw new DatabaseNotEnabledForNotificationException( 1564 SR.GetString(SR.Database_not_enabled_for_notification, 1565 sqlConnection.Database)); 1566 } 1567 else { 1568 throw; 1569 } 1570 } 1571 else { 1572 if (table != null) { 1573 throw new DatabaseNotEnabledForNotificationException( 1574 SR.GetString(SR.Cant_disable_table_sql_cache_dep)); 1575 } 1576 else { 1577 // If we cannot find the SP for disabling the database, it maybe because 1578 // SQL cache dep is already disabled, or the SP is missing. 1579 // In either case, we just exit silently. 1580 throwError = false; 1581 } 1582 } 1583 } 1584 else if (sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_PERMISSION_DENIED_ON_OBJECT || 1585 sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_PERMISSION_DENIED_ON_DATABASE || 1586 sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_PERMISSION_DENIED_ON_USER || 1587 sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_NO_GRANT_PERMISSION) { 1588 string error; 1589 1590 if (!disable) { 1591 if (table != null) { 1592 error = SR.Permission_denied_table_enable_notification; 1593 } 1594 else { 1595 error = SR.Permission_denied_database_enable_notification; 1596 } 1597 } 1598 else { 1599 if (table != null) { 1600 error = SR.Permission_denied_table_disable_notification; 1601 } 1602 else { 1603 error = SR.Permission_denied_database_disable_notification; 1604 } 1605 } 1606 1607 if (table != null) { 1608 throw new HttpException( 1609 SR.GetString(error, table)); 1610 } 1611 else { 1612 throw new HttpException( 1613 SR.GetString(error)); 1614 } 1615 } 1616 else if (sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_ADHOC && 1617 sqlExpt.Message == SqlCacheDependencyManager.SQL_CUSTOM_ERROR_TABLE_NOT_FOUND) { 1618 Debug.Assert(!disable && table != null, "disable && table != null"); 1619 throw new HttpException(SR.GetString(SR.Cache_dep_table_not_found, table)); 1620 } 1621 } 1622 1623 string errString; 1624 1625 if (sqlCmd != null && sqlCmd.CommandText.Length != 0) { 1626 errString = SR.GetString(SR.Cant_connect_sql_cache_dep_database_admin_cmdtxt, 1627 sqlCmd.CommandText); 1628 } 1629 else { 1630 errString = SR.GetString(SR.Cant_connect_sql_cache_dep_database_admin); 1631 } 1632 1633 if (throwError) { 1634 throw new HttpException(errString, e); 1635 } 1636 } 1637 finally { 1638 if (sqlConnection != null) { 1639 sqlConnection.Close(); 1640 } 1641 } 1642 } 1643 EnableNotifications(string connectionString)1644 public static void EnableNotifications(string connectionString) { 1645 SetupNotifications(0, null, connectionString); 1646 } 1647 DisableNotifications(string connectionString)1648 public static void DisableNotifications(string connectionString) { 1649 SetupNotifications(SETUP_DISABLE, null, connectionString); 1650 } 1651 EnableTableForNotifications(string connectionString, string table)1652 public static void EnableTableForNotifications(string connectionString, string table) { 1653 SetupNotifications(SETUP_TABLE, table, connectionString); 1654 } 1655 EnableTableForNotifications(string connectionString, string[] tables)1656 public static void EnableTableForNotifications(string connectionString, string[] tables) { 1657 if (tables == null) { 1658 throw new ArgumentNullException("tables"); 1659 } 1660 1661 foreach (string table in tables) { 1662 SetupNotifications(SETUP_TABLES, table, connectionString); 1663 } 1664 } 1665 DisableTableForNotifications(string connectionString, string table)1666 public static void DisableTableForNotifications(string connectionString, string table) { 1667 SetupNotifications(SETUP_TABLE|SETUP_DISABLE, table, connectionString); 1668 } 1669 DisableTableForNotifications(string connectionString, string[] tables)1670 public static void DisableTableForNotifications(string connectionString, string[] tables) { 1671 if (tables == null) { 1672 throw new ArgumentNullException("tables"); 1673 } 1674 1675 foreach (string table in tables) { 1676 SetupNotifications(SETUP_TABLES|SETUP_DISABLE, table, connectionString); 1677 } 1678 } 1679 GetEnabledTables(string connectionString)1680 static string[] GetEnabledTables(string connectionString) { 1681 1682 SqlDataReader sqlReader = null; 1683 SqlConnection sqlConn = null; 1684 SqlCommand sqlCmd = null; 1685 ArrayList tablesObj = new ArrayList(); 1686 1687 try { 1688 sqlConn = new SqlConnection(connectionString); 1689 sqlConn.Open(); 1690 1691 sqlCmd = new SqlCommand(SQL_QUERY_REGISTERED_TABLES_SP_DBO, sqlConn); 1692 sqlCmd.CommandType = CommandType.StoredProcedure; 1693 1694 sqlReader = sqlCmd.ExecuteReader(); 1695 1696 while(sqlReader.Read()) { 1697 tablesObj.Add(sqlReader.GetString(0)); 1698 } 1699 } 1700 catch (Exception e) { 1701 SqlException sqlExpt = e as SqlException; 1702 1703 if (sqlExpt != null && 1704 sqlExpt.Number == SqlCacheDependencyManager.SQL_EXCEPTION_SP_NOT_FOUND) { 1705 1706 throw new DatabaseNotEnabledForNotificationException( 1707 SR.GetString(SR.Database_not_enabled_for_notification, 1708 sqlConn.Database)); 1709 } 1710 else { 1711 throw new HttpException(SR.GetString(SR.Cant_get_enabled_tables_sql_cache_dep), e); 1712 } 1713 } 1714 finally { 1715 try { 1716 if (sqlReader != null) { 1717 sqlReader.Close(); 1718 } 1719 1720 if (sqlConn != null) { 1721 sqlConn.Close(); 1722 } 1723 } 1724 catch { 1725 } 1726 } 1727 1728 return (string[])tablesObj.ToArray(Type.GetType("System.String")); 1729 } 1730 GetTablesEnabledForNotifications(string connectionString)1731 public static string[] GetTablesEnabledForNotifications(string connectionString) { 1732 return GetEnabledTables(connectionString); 1733 } 1734 } 1735 } 1736 1737 1738