Здесь описана простейшая настройка новой техники ASP2 для поддержания всегда актуального рекордсета в CAСHE c применением зависимости от SQL-рекордсета и возможностью получения уведомлений об изменениях в рекордсете.

• В приложении составляем вот такой конфигурационный файл (Здесь: VS2005 - имя SQL-сервера, SH - имя базы в которой ведется работа. Табла, на которую мы подписываемся, называется MyGroup)

  00001:     <connectionStrings>
  00002:         <add name="SH_Price" connectionString="Data Source=VS2005;Initial Catalog=SH;Integrated Security=True" providerName="System.Data.SqlClient"/>
  00003:     </connectionStrings>
  00004:     <system.web>
  00005:         <caching>
  00006:             <sqlCacheDependency enabled="true" pollTime="1000"  >
  00007:                 <databases>
  00008:                     <add name="SH" connectionStringName="SH_Price" />
  00009:                 </databases> 
  00010:             </sqlCacheDependency>
  00011:         </caching>

• В Application_Start выдаем:

  00001:         'это запускается ОДИН раз чтобы создать в базе нужную таблу и триггера
  00002:         System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(System.Configuration.ConfigurationManager.ConnectionStrings("SH_Price").ConnectionString)
  00003:         System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(System.Configuration.ConfigurationManager.ConnectionStrings("SH_Price").ConnectionString, "MyGroup")
  00004:         'это контроль
  00005:         'System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(System.Configuration.ConfigurationManager.ConnectionStrings("SH_Price").ConnectionString)
  

• Вышеуказанные две строчки создают в заданной базе:

  00001: /* Create notification table */ 
  00002: IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = 'AspNet_SqlCacheTablesForChangeNotification' AND type = 'U') 
  00003:    IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = 'AspNet_SqlCacheTablesForChangeNotification' AND type = 'U') 
  00004:       CREATE TABLE dbo.AspNet_SqlCacheTablesForChangeNotification (
  00005:       tableName             NVARCHAR(450) NOT NULL PRIMARY KEY,
  00006:       notificationCreated   DATETIME NOT NULL DEFAULT(GETDATE()),
  00007:       changeId              INT NOT NULL DEFAULT(0)
  00008:       )
  00009: 
  00010: /* Create polling SP */
  00011: IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = 'AspNet_SqlCachePollingStoredProcedure' AND type = 'P') 
  00012:    IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = 'AspNet_SqlCachePollingStoredProcedure' AND type = 'P') 
  00013:    EXEC('CREATE PROCEDURE dbo.AspNet_SqlCachePollingStoredProcedure AS
  00014:          SELECT tableName, changeId FROM dbo.AspNet_SqlCacheTablesForChangeNotification
  00015:          RETURN 0')
  00016: 
  00017: /* Create SP for registering a table. */ 
  00018: IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = 'AspNet_SqlCacheRegisterTableStoredProcedure' AND type = 'P') 
  00019:    IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = 'AspNet_SqlCacheRegisterTableStoredProcedure' AND type = 'P') 
  00020:    EXEC('CREATE PROCEDURE dbo.AspNet_SqlCacheRegisterTableStoredProcedure 
  00021:              @tableName NVARCHAR(450) 
  00022:          AS
  00023:          BEGIN
  00024: 
  00025:          DECLARE @triggerName AS NVARCHAR(3000) 
  00026:          DECLARE @fullTriggerName AS NVARCHAR(3000)
  00027:          DECLARE @canonTableName NVARCHAR(3000) 
  00028:          DECLARE @quotedTableName NVARCHAR(3000) 
  00029: 
  00030:          %af_src_comm_3 
  00031:          SET @triggerName = REPLACE(@tableName, ''['', ''__o__'') 
  00032:          SET @triggerName = REPLACE(@triggerName, '']'', ''__c__'') 
  00033:          SET @triggerName = @triggerName + ''_AspNet_SqlCacheNotification_Trigger'' 
  00034:          SET @fullTriggerName = ''dbo.['' + @triggerName + '']'' 
  00035: 
  00036:          %af_src_comm_4 
  00037:          %af_src_comm_5 
  00038:          IF (CHARINDEX(''.'', @tableName) <> 0 OR 
  00039:              CHARINDEX(''['', @tableName) <> 0 OR 
  00040:              CHARINDEX('']'', @tableName) <> 0) 
  00041:              SET @canonTableName = @tableName 
  00042:          ELSE 
  00043:              SET @canonTableName = ''['' + @tableName + '']'' 
  00044: 
  00045:          %af_src_comm_6 
  00046:          IF (SELECT OBJECT_ID(@tableName, ''U'')) IS NULL 
  00047:          BEGIN 
  00048:              RAISERROR (''00000001'', 16, 1) 
  00049:              RETURN 
  00050:          END 
  00051: 
  00052:          BEGIN TRAN
  00053:          %af_src_comm_7 
  00054:          IF NOT EXISTS (SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH (NOLOCK) WHERE tableName = @tableName) 
  00055:              IF NOT EXISTS (SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH (TABLOCKX) WHERE tableName = @tableName) 
  00056:                  INSERT  dbo.AspNet_SqlCacheTablesForChangeNotification 
  00057:                  VALUES (@tableName, GETDATE(), 0)
  00058: 
  00059:          %af_src_comm_8 
  00060:          SET @quotedTableName = QUOTENAME(@tableName, '''''''') 
  00061:          IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = ''TR'') 
  00062:              IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = ''TR'') 
  00063:                  EXEC(''CREATE TRIGGER '' + @fullTriggerName + '' ON '' + @canonTableName +''
  00064:                        FOR INSERT, UPDATE, DELETE AS BEGIN
  00065:                        SET NOCOUNT ON
  00066:                        EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'' + @quotedTableName + ''
  00067:                        END
  00068:                        '')
  00069:          COMMIT TRAN
  00070:          END
  00071:    ')
  00072: 
  00073: /* Create SP for updating the change Id of a table. */ 
  00074: IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = 'AspNet_SqlCacheUpdateChangeIdStoredProcedure' AND type = 'P') 
  00075:    IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = 'AspNet_SqlCacheUpdateChangeIdStoredProcedure' AND type = 'P') 
  00076:    EXEC('CREATE PROCEDURE dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure 
  00077:              @tableName NVARCHAR(450) 
  00078:          AS
  00079: 
  00080:          BEGIN 
  00081:              UPDATE dbo.AspNet_SqlCacheTablesForChangeNotification WITH (ROWLOCK) SET changeId = changeId + 1 
  00082:              WHERE tableName = @tableName
  00083:          END
  00084:    ')
  00085: 
  00086: /* Create SP for unregistering a table. */ 
  00087: IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = 'AspNet_SqlCacheUnRegisterTableStoredProcedure' AND type = 'P') 
  00088:    IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = 'AspNet_SqlCacheUnRegisterTableStoredProcedure' AND type = 'P') 
  00089:    EXEC('CREATE PROCEDURE dbo.AspNet_SqlCacheUnRegisterTableStoredProcedure 
  00090:              @tableName NVARCHAR(450) 
  00091:          AS
  00092:          BEGIN
  00093: 
  00094:          BEGIN TRAN
  00095:          DECLARE @triggerName AS NVARCHAR(3000) 
  00096:          DECLARE @fullTriggerName AS NVARCHAR(3000)
  00097:          SET @triggerName = REPLACE(@tableName, ''['', ''__o__'') 
  00098:          SET @triggerName = REPLACE(@triggerName, '']'', ''__c__'') 
  00099:          SET @triggerName = @triggerName + ''_AspNet_SqlCacheNotification_Trigger'' 
  00100:          SET @fullTriggerName = ''dbo.['' + @triggerName + '']'' 
  00101: 
  00102:          %af_src_comm_11 
  00103:          IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = ''AspNet_SqlCacheTablesForChangeNotification'' AND type = ''U'') 
  00104:              IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = ''AspNet_SqlCacheTablesForChangeNotification'' AND type = ''U'') 
  00105:              DELETE FROM dbo.AspNet_SqlCacheTablesForChangeNotification WHERE tableName = @tableName 
  00106: 
  00107:          %af_src_comm_12 
  00108:          IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = ''TR'') 
  00109:              IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = ''TR'') 
  00110:              EXEC(''DROP TRIGGER '' + @fullTriggerName) 
  00111: 
  00112:          COMMIT TRAN
  00113:          END
  00114:    ')
  00115: 
  00116: /* Create SP for querying all registered table */ 
  00117: IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = 'AspNet_SqlCacheQueryRegisteredTablesStoredProcedure' AND type = 'P') 
  00118:    IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = 'AspNet_SqlCacheQueryRegisteredTablesStoredProcedure' AND type = 'P') 
  00119:    EXEC('CREATE PROCEDURE dbo.AspNet_SqlCacheQueryRegisteredTablesStoredProcedure 
  00120:          AS
  00121:          SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification   ')
  00122: 
  00123: /* Create roles and grant them access to SP  */ 
  00124: IF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole = 1 AND name = N'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess') 
  00125:     EXEC sp_addrole N'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess' 
  00126: 
  00127: GRANT EXECUTE ON dbo.AspNet_SqlCachePollingStoredProcedure to aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess
  

• Далее мы конфигурируем зависимость:

  00001:         Dim X As New SqlDataSource
  00002:         X.DataSourceMode = SqlDataSourceMode.DataReader
  00003:         X.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("SH_Price").ConnectionString
  00004:         X.SelectCommand = "GetMyGroupForXML"
  00005:         Dim DR As Data.SqlClient.SqlDataReader = X.Select(New DataSourceSelectArguments)
  00006:         If DR.Read Then
  00007:             Dim XML_string As String = DR.GetSqlString(0)
  00008:             Dim MySqlDependency = New SqlCacheDependency("SH", "MyGroup")
  00009:             Cache.Insert("MyXML", XML_string, MySqlDependency)
  00010:         End If
  00011:         DR.Close()

• Собственно все. Теперь в обьекте MyXML всегда актуальные - курсы валют, текущие остатки на складе, брак, товар со скидкой и так далее

  00001:        XmlDataSource1.Data = Cache("MyXML")
  00002:        XmlDataSource1.DataBind()

• Если все получилось правильно, то профайлер должен нам показать вот что:
  
  
  
  Comments ( )
  <00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21>  <22>  <23> 
  Link to this page: //www.vb-net.com/asp2/5/index.htm

  <SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>