ASP.Net Data Cache & Oracle Database Change Notification

A good web application design has data caching built to it. It is always a fine balance to find out what’s the optimal level to keep the cache and the refresh intervals. In most of my designs I compartmentalize these into various types like 24 hour windows, specified number of hour windows, user types with specified number of hours… etc.

The question that follows naturally is how to force a cache refresh. Again, multiple ways to go about doing it and here I wanted to bring forward the concept with Oracle 10g called Database Change Notification methodology. Read this ODP.Net article from Oracle that goes in detail about this.

To highlight this concept in simple terms it is done with some sort of notification (typically an event) built in our applications. An example might look like this:

OracleConnection con = new OracleConnection("Your Connection String");

OracleCommand cmd = new OracleCommand("Your SQL", con);

con.Open();

// Register notification with command object if result changes.

// When an OracleDependency instance is bound to an OracleCommand

// instance, an OracleNotificationRequest is created and is set in the

// OracleCommand's Notification property. This indicates subsequent

// execution of command will register the notification.

OracleDependency dep = new OracleDependency(cmd);

// Allow the change notification handler in the database to persist

// even after the first database change

cmd.Notification.IsNotifiedOnce = false;

// Add the event handler to handle the notification.

// The OnDatabaseNotification method will be invoked when a notification

// message is sent from the database

dep.OnChange +=

new OnChangeEventHandler(OnDatabaseNotification);

OracleDataAdapter da = new OracleDataAdapter(cmd);

// Do your data stuff here...

// This event gets triggered on Database Change Notification

public static void OnDatabaseNotification(object src, OracleNotificationEventArgs args)

{

// Call your cache refresh method here...

}

Love coding!