NCache 4.3 - Online Documentation

Oracle Cache Dependency

 
NOTE: This feature is not available in NCache Express and Professional edition.
 
While working with databases in a multiuser environment, updates in the databases are very frequent. When cache is involved, you need to keep cached items synchronized with the database. One possible solution is that, on every update, database itself notifies the cache and cache expires the out of synch items. Oracle Dependency is provided by NCache for this purpose. Item expires if result of the command (based on command text) changes. Oracle Dependency is only available for Oracle database 10g release 2 or later. Also make sure that Oracle Data Providers for .NET (version 10.1.0.2.0 or later) is installed.
 
Following is the step-by-step guide for using OracleCacheDependency.
 
Enabling Database Notification:
 
Grant CHANGE NOTIFICATION privilege to the user to create a notification registration. Following statement grants the CHANGE NOTIFICATION privilege to the user.
 
[SQL]
"grant change notification to scott"
 
Using Dependency:
 
  1. Create a .Net test application.
  2. Add the following namespaces in your project:
     
    using Alachisoft.NCache.Web.Caching;
    using Alachisoft.NCache.Runtime.Dependencies;
    using Alachisoft.NCache.Runtime;
     
  3. Declare a variable "_cache" in the code and initialize it with "sqlDependencyCache", and clear it if this cache was used previously.
     
    Cache _cache = NCache.InitializeCache("oracleDependencyCache");
    _cache.Clear();
     
  4. Create a String "connectionString" containing information to connect with Oracle database e.g.
     
    String connectionString = "User Id=scott;Password=tiger;Data Source=oracle";
     
  5. Create a dependency and add it to the cache.
     
    CacheDependency orclSync = new OracleCacheDependency(connectionString, "SELECT CustomerID FROM Customers WHERE CustomerID = 006");
    _cache.Insert("Customer:David:006", "my Item", orclSync, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Normal);
     
  6. Now update the key "6" of the Database table using the following Oracle command.--[SQL]
     
    UPDATE Customers SET Customer Name = 'Modified Customer' WHERE CustomerID =6
     
  7. This invalidates cacheKey " Dependant-CustomerID6 " and is removed by NCache.
 
 
Note: In Oracle 10g, database change notifications are only object based. This means that change notifications will be fired if any row is modified in object. Therefore we have to check rowID to confirm if the row changed is the one for which the event is registered. RowID's cannot be retrieved unless explicitly included in query. Therefore customer has to include rowID in query that he/she is registering with OracleDependecy, otherwise change notification will be fired if any row is modified in table.
 
When rowID is included in query like: select rowID, productID, productname, unitprice from Products where ProductID = 220; NCache will save the rowID of rows for which change notification is registered. When it receives any change notification, NCache will compare the rowIDs to determine whether the row changed is the one for which the rowID is registered. Otherwise NCache will have no way to check this and items for which change notification is registered will be removed if any row in table changes.
 
In Oracle 11g both object based and query based (default) notifications are provided. In query based notifications; change of the modified row will be notified only if the change notification is registered for it.
 
 
 
See Also
 
© Copyright  2014 Alachisoft. All rights reserved.