15 seconds: cache Oracle data for ASP.NET applications
Original: Narayan Veeramani
time:
12/29 / 2003
In order to create scalable, high-performance web-based applications, ASP.NET provides a feature called data caching. The data cache supports data objects that are frequently accessed can be programmatically stored in memory. This feature is extended to extensively improve the performance of the ASP.NET application in the Oracle database. This article describes a policy that can be used to cache Oracle database data in an ASP.NET web application in the Web Farm environment. This tip allows for frequent access to the Oracle database data that is frequently accessed in memory instead of frequent access to the database to take data. This can help avoid unnecessary far away from the Oracle database server. Further, the article proposes an implementation to keep cache data to always synchronize with Oracle data.
ASP.NET data cache
The data cache in the ASP.NET is supported by the Cache class and the system.Web.Caching namespace. CACHEDEPENDENCY. The Cache class provides a method of retrofitting data to cache. The CacheDependency class allows you to depends on the specified items of the data items in the cache. When we use Insert and Add methods to add items to the cache, you can specify an expiration policy. We can use the INSERT method's AbsoluteExpiration property to define a life period of a project in the cache. This property allows you to specify the exact time of the corresponding data item expire. You can also use the SlidingExpiration property to specify the overdue time of the project (based on the time it is accessible). Once an item expires, it is cleared from the cache. Unless it is added to the cache again, you will try to access it again, and you will return an null value.
Set the cache dependency
ASP.NET enables us to define a cache item based on an external file, directory, or another cache item, that is, the so-called file dependence and key dependence. If a dependency change is changed, the cache item is automatically invalidated and cleared from the cache. When the corresponding data source changes, we can use this method to remove the project from the cache. For example, if our application takes data from an XML file and appears in a table (Grid), we can store the data in the file into the cache and set the cache depending on the XML file. When the XML file is updated, the data item is cleared from the cache. When this event occurs, the application re-reads the XML file, the latest data item replica is again inserted into the cache. Further, the callback event processor can be set to a listener that is notified when the cache item is deleted. This makes we do not need to repeated polling cache to determine if the data item is invalid.
ASP.NET cache on the Oracle database
Now consider such a scenario: Data is stored in the Oracle database, an ASP.NET app is accessed via ADO.NET. Further, we assume that the data in the database table is generally static and is frequently accessed by this web application. There are few DML operations on the table and there are many SELECTs on the data. This situation is ideal for data cache technology. But unfortunately, ASP.NET does not allow setting a cache item to depends on 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 cache invalid operations more challenging. In addition, most Web-based applications use web farms, and instances of the same application run with load balancing on different web servers. This situation makes the database cache problem is more complex.
In order to further study the solutions of the above problems, we will give an example of a web application to explain how to implement it. In the example, we use the VB.NET implementation ASP.NET app to access the Oracle 9i database via Oracle Data Provider for .NET (ODP). This example uses a table called Employee in the Oracle database. We set triggers for INSERT, UPDATE, and DELETE for this table. These triggers call a PL / SQL function encapsulated a Java stored procedure. This Java stored procedure is responsible for updating the cache-dependent file.
ASP.NET TIER VB.NET implementation
We have designed a notification that contains a listening class of a callback method to handle the cache item invalid. This callback method RemovedCallback is registered with a Delegate function. The declaration of the callback method onremove must be the same signature as the CacheItemRemovedCallback proxy.
Dim onRemove as cacheItem / portback = Nothing
OnRemove = New CacheItemRemovedCallback (Addressof RemovedCallback)
The listening event processing method RemovedCallback is responsible for handling the notification of the database trigger, which is defined below. If the cached item is invalid, you can remove the data from the database to get the data from the database method. The parameter "key" refers to the index position of the item removed from the cache. The parameter "value" refers to the data object removed from the cache. The parameter "cacheItemRemoveDreason" refers to the reason why the data item is deleted from the cache.
Publicsub RemovedCallback (Byval Key Asstring, Byval Value Asbobject, Byval Reason As CacheItem Moredreason)
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 database table Employee and returns a DataView object reference. It uses a stored procedure called getEmployee to abstract SQL from the EMPLOYEE table. This method has a parameter named p_empid, indicating the primary key of 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.Ind64)). 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 exception
Thrownew Exception ("Exception in Database Tier Method getRecordfromDatabase ()" ex.Message, EX)
Finally
Try
cmd.dispose ()
Catch exception
Finally
CMD = Nothing
EndTry
Try
Con. close ()
Catch exception
Finally
Con = Nothing
EndTry
EndTry
ENDFUNCTION
The function getDatabaseConnection accepts a connection string (Connection Stirng) as a parameter and returns an 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 exception
Thrownew Exception ("Exception in Database Tier Method getoraclect ()" EX.MESSAGE, EX)
EndTry
ENDFUNCTION
Oracle Database Tier Realization
The trigger body of the DML event on the Employee table is as follows. This trigger simply invokes a PL / SQL package function to update the operating system file called Tblemployee.txt. The file copy is updated on two machines (machine 1 and machine 2). Two machines run different instances of the same web application to balance the load. Here the administrator refers to the Oracle database (Schema) object owner.
Begin
Administrator.plfile ('Machine1 // Download // Tblemployee.txt'); Administrator.plfile ('Machine2 // Download // TbleMPloye.txt');
END;
To update the cache dependency file, we need to write a C function or Java stored procedure. In our example, the Java stored procedure is selected because the Oracle Database Server has a built-in JVM that makes it easy to write the Java stored procedure. There must be sufficient memory to allocate the Java pool in the system global zone (SGA) of the Oracle instance. Static method UpdateFile accepts an absolute path as a parameter and creates a cache dependencies in the appropriate directory. If the file already exists, then first delete and then created.
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 calls the UPDATEFILE method during the Java store in the file name.
(p_filename in varchar2)
As Language Java
Name 'Updfile.Updatefile (java.lang.string)';
Oracle data cache in Web Farm deployment
As we discuss, the web server 1 and machine 2 constitute a Web Farm to provide load balancing for our web application. Each machine runs an instance of the same web application. In this case, each instance can have a copy of the cache data stored in the Cache object. When the Employee table changes, the corresponding database trigger updates the file Tblemploye.txt on the two machines. Each instance specifies a cache dependency to TBLEMPLOYE.TXT. The two instances of Web Farm can be updated correctly, so that the data cache on the two instances can be synchronized with the database table Employee.
in conclusion
The data cache is an effective technique for optimizing the ASP.NET app on the Oracle database. Although ASP.NET does not allow database dependence of the cached, the Oracle trigger coordinated Java stored procedures can extend the power of ASP.NET cache to allow Oracle databases. This trick can also be applied to Web Farm deployment.
