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!

Selecting top rows or random rows

We all come across the need to select top rows and/or random rows from our tables. I wrote a blog about Dynamic Paging in the past and in that showed a way of selecting a range of rows both in SQL Server and Oracle. Let's extend the thought and select either top rows or random rows. Take a look:

--SQL Server: Top 10 of SomeColumn

Select Top 10 * From myTable Where MyColumn = 'MyCondition' Order By SomeColumn Desc

--Oracle: Top 10 of SomeColumn

Select *

From (Select Row_Number() Over (Partition By 'kigo' Order By SomeColumn Desc) myTops,

Column1, Column2, Column3

From myTable Where MyColumn = 'MyCondition')

Where myTops < 10

--Oracle: Gets you 1% records

Select * From myTable Sample(1)

--SQL Server: Random 10

Select Top 10 * From myTable Where MyColumn = 'MyCondition' order by NewID()

--Oracle: Random 10

Select *

From (Select Row_Number() Over (Partition By 'kigo' Order By dbms_random.Value()) myTops,

Column1, Column2, Column3

From myTable Where MyColumn = 'MyCondition')

Where myTops < 10

Love coding!