Viacheslav Eremin | SQL-зависимости кеша

(ASP.NET) ASP.NET (2005 год)

SQL-зависимости кеша

Здесь описана простейшая настройка новой техники 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 ( )

Link to this page: //www.vb-net.com/asp2/5/index.htm

< THANKS ME>