Caching Oracle data for ASP.NET applications

asp.net|oracle| Cache | Data to create scalable, high-performance web-based applications, ASP. NET provides an attribute called data caching (Caching). Data caching supports the programmatic storage of frequently accessed data objects in memory. This feature can be extended to extensively improve the performance of ASP.net applications that query data in Oracle databases. This article describes a strategy that can be used to cache Oracle database data using the ASP.net Web application in a Web farm environment. This technique allows you to cache frequently accessed Oracle database data in memory, rather than accessing the database frequently to fetch data. This can help avoid unnecessary walks to the Oracle database server. Further, the article proposes a implementation that maintains cached data so that it always synchronizes with Oracle data.

Asp. NET in the data cache

Asp. NET data caching is supported by the cache class and the CacheDependency class in the System.Web.Caching namespace. The cache class provides methods for inserting and extracting data from the cache. The CacheDependency class allows you to specify its dependencies for data items in the cache. When we add a project to the cache with the insert and add methods, you can specify an expiration (expiration) policy for a project. We can use the Absoluteexpiration property of the Insert method to define the lifetime of an item in the cache. This property allows you to specify the exact time that the corresponding data item expires. You can also use the SlidingExpiration property to specify how long a project expires (based on the time it is accessed). Once an item expires, it is purged from the cache. Unless it is added to the cache again, otherwise attempting to access, a null value is returned.

Set cache dependencies

Asp. NET allows us to define the dependencies of a cache item based on an external file, directory, or another cache entry, which is called file dependency and key dependency. If a dependency changes, the cache entry is automatically invalidated and purged from the cache. When the corresponding data source changes, we can use this method to remove items from the cache. For example, if our application takes data from an XML file and displays it in a table (grid), we can store the data in the file in the cache and set the cache to depend on that XML file. When an XML file is updated, the data item is purged from the cache. When this event occurs, the application reads back into the XML file, and the most recent copy of the data item is again inserted into the cache. Further, the callback event handler can be set as a listener and notified when the cached item is deleted. This allows us to not repeatedly poll the cache to determine if the data item is invalid.

asp.net cache dependencies on Oracle databases

Now consider a scenario where the data is stored in an Oracle database and a asp.net application is accessed through ado.net. Further, we assume that the data in the database table is generally static and is accessed frequently by this Web application. The DML operation on the table is very small and there are many select data. This is an ideal application of data caching techniques. But unfortunately, ASP. NET does not allow you to set a cache entry that depends on the data stored in the database table. Further, in the real world, web-based systems, Web servers, and Oracle database servers are always running on different machines, making caching invalid operations more challenging. In addition, most web-based applications use Web farms, with an instance of the same application running on different Web servers for load balancing. This situation makes the database caching problem slightly more complex.

To further study the solution to the above problem, we give an example of a Web application to illustrate how to implement it. In the example, we use the ASP.net application implemented by vb.net to access the Oracle 9i database through Oracle Data Provider for. NET (ODP).

This example uses a table named employee in the Oracle database. We set the trigger for INSERT, UPDATE, and delete on the table. These triggers call a pl/sql function that encapsulates a Java stored procedure. This Java stored procedure is responsible for updating cache-dependent files.

vb.net realization of asp.net tier

We designed a listener class with a callback method to handle notification of invalid cache entries. This callback method RemovedCallback is registered with a proxy (delegate) function. The declaration of a callback method OnRemove must have the same signature as the CacheItemRemovedCallback proxy declaration.

Dim OnRemove as CacheItemRemovedCallback = Nothing

OnRemove = New CacheItemRemovedCallback (AddressOf removedcallback)

The Listener event handling method, RemovedCallback, is responsible for handling the notification of database triggers, which is defined as follows. If the cache entry fails, the available database method calls Getrecordfromdatabase () to fetch the data from the database. The parameter "key" refers to the index position of an item that is deleted from the cache. Parameter ' value ' refers to a data object that is deleted from the cache. Parameter "CacheItemRemovedReason" refers to the reason for deleting data items from the cache.

Publicsub removedcallback (ByVal key asstring, ByVal value Asobject, ByVal Reason as CacheItemRemovedReason)

Dim Source as DataView

Source = Getrecordfromdatabase ()

Cache.Insert ("EmployeeTable", Source, New

System.Web.Caching.CacheDependency ("D:\download\tblemployee.txt"),

Cache.noabsoluteexpiration, Cache.noslidingexpiration,

Cacheitempriority.normal, OnRemove)

Endsub

Method Getrecordfromdatabase () is responsible for querying the database table employee and returning a DataView object reference. It uses a stored procedure called GetEmployee to abstract the SQL that takes data from the employee table. This method has a parameter named P_empid that represents the primary key of the employee.

Publicfunction getrecordfromdatabase (ByVal p_empid as Int32) as DataView

Dim con as OracleConnection = Nothing

Dim cmd as OracleCommand = Nothing

Dim ds as DataSet = Nothing

Try

con = getdatabaseconnection ("Userid=scott; Password=tiger;data source=testingdb; ")

cmd = New OracleCommand ("Administrator.getemployee", con)

Cmd.commandtype = CommandType.StoredProcedure

Cmd. Parameters.Add (New oracleparameter ("EmployeeId", Oracledbtype.int64)). Value = P_empid

Dim param asnew OracleParameter ("RC1", Oracledbtype.refcursor)

Cmd. Parameters.Add (param). Direction = ParameterDirection.Output

Dim mycommand asnew oracledataadapter (cmd)

ds = New DataSet

Mycommand.fill (DS)

Dim table as DataTable = ds. Tables (0)

Dim index as Int32 = table. Rows.Count

Return DS. Tables (0). DefaultView

Catch ex as Exception

Thrownew Exception ("Exception in Database Tier method Getrecordfromdatabase ()" + ex. Message, ex)

Finally

Try

Cmd. Dispose ()

Catch ex as Exception

Finally

cmd = Nothing

Endtry

Try

Con. Close ()

Catch ex as Exception

Finally

con = Nothing

Endtry

Endtry

Endfunction

Function Getdatabaseconnection takes a connection string (connection stirng) as a parameter and returns a OracleConnection object reference.

Publicfunction getdatabaseconnection (ByVal strconnection As String) as OracleConnection

Dim con as Oracle.DataAccess.Client.OracleConnection = Nothing

Try

con = New Oracle.DataAccess.Client.OracleConnection

Con. ConnectionString = strconnection

Con. Open ()

Return con

Catch ex as Exception

Thrownew Exception ("Exception in Database Tier method Getoracleconnection ()" + ex. Message, ex)

Endtry

Endfunction

Oracle Database Tier Implementation

The trigger body that defines the DML event on the employee table is as follows. This trigger simply invokes a pl/sql wrapping function to update the operating system file named Tblemployee.txt. The copy of the file is updated on both machines (Machine 1 and Machine 2). The two machines run a different instance of the same Web application to balance the load. Here the Administrator refers to the schema object owner of the Oracle database.

Begin

Administrator.plfile (' machine1\\download\\ tblemployee.txt ');

Administrator.plfile (' machine2\\download\\ tblemployee.txt ');

End

To update a cached dependency file, we need to write a C function or a Java stored procedure. The Java stored procedure is selected in our example because the Oracle database server has a built-in JVM that makes it easy to write Java stored procedures. There must be enough memory allocated to the Java pool in the system global Area (SGA) of the Oracle instance. The static method Updatefile takes an absolute path as an argument and creates a cache-dependent file in the appropriate directory. If the file already exists, delete and then create it.

Import java.io.*;

public class Updfile {public static void Updatefile (String filename)

{

try {

File F = new file (filename);

F.delete ();

F.createnewfile ();

}

catch (IOException E)

{

Log exception

}

};

The Pl/sql package is implemented as follows. The package function invokes the Updatefile method in the Java stored procedure, using the file name as the parameter.

(P_filename in VARCHAR2)

As LANGUAGE JAVA

NAME ' Updfile.updatefile (java.lang.String) ';

Oracle data caching in Web farm deployments

As we discussed in the example, Web server 1 and Machine 2 form a Web farm to provide load balancing for our web applications. Each machine runs an instance of the same Web application. In this case, each instance can have its own copy of the cached data stored in the cache object. When the employee table changes, the corresponding database triggers update the file tblemployee.txt on both machines. Each instance specifies a cache dependency to Tblemployee.txt, and two instances of the Web farm can be updated correctly, allowing the data cache on two instances to be synchronized with the database table employee.

Conclusion

Data caching is an effective technique for optimizing ASP.NET applications on Oracle databases. Although ASP.net does not allow caching database dependencies to be set, Oracle triggers collaborate with Java stored procedures to extend the power of the ASP.net cache to allow Oracle database caching. This technique can also be applied to Web farm deployments.