caching datatables

Hi,

I have a datatable that I get back from an oracle table that gets used heavily
in my web application. I was wondering if there is any way I could store the
datatable in cache. I know in Sql Server you can setup a cache invalidation at a
table level, I was wondering if we could do something similar at a table level
with an Oracle table. meaning if the table contents change only then the cache
should be refreshed.

 

Any ideas.

thanks

For that you have to write your own custom cache validation, check this article
below

 

http://www.15seconds.com/issue/031229.htm 

http://www.oracle.com/technology/oramag/oracle/06-mar/o26odpnet.html

http://www.oracle.com/technology/oramag/oracle/06-may/o36odp.html

Thank you. I went ahead and used ODP.NET oracle dependency to achieve oracle
change notification. I have the following function to execute the first time
when the page loads when the cache is null. the idea is to refresh the cached
datatable only when there is a change in the mytable. For some reason the
odep_OnChange is not firing. The change notification is not triggering what
could be the reason ?

   

private void PopulateCachedDataTable()
    {
        string strsql = "";
        DataTable mappingSchemaTable = null;
        DataTable cachedDataTable = new DataTable(); strsql = "SELECT *
FROM mytable"; OracleConnection oc = new
OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings[&qu
ot;prodString"].ConnectionString); oc.Open(); OracleCommand ocmd = new
OracleCommand(strsql, oc); OracleDependency.Port = 1521; OracleDependency odep =
new OracleDependency(ocmd); ocmd.Notification.IsNotifiedOnce = false;
odep.OnChange +=new OnChangeEventHandler(odep_OnChange);

  

 OracleDataReader odata = ocmd.ExecuteReader();
        if (odata != null)
        {
            mappingSchemaTable = odata.GetSchemaTable();

            for (int i = 0; i < mappingSchemaTable.Rows.Count; i++)
            {
                cachedDataTable.Columns.Add(odata.GetName(i),
odata.GetFieldType(i));
            }
            cachedDataTable.BeginLoadData();
            object[] values = new object[mappingSchemaTable.Rows.Count];

            while (odata.Read())
            {
                odata.GetValues(values);
                cachedDataTable.LoadDataRow(values, true);

            }
            cachedDataTable.EndLoadData();
           
            Cache["MappingData"] = cachedDataTable;
        } }

  

void  odep_OnChange(object sender, OracleNotificationEventArgs eventArgs)
{
//refresh the cache datatable 	
PopulateCachedDataTable();
}