I've been having a bit of a problem with caching of an excel file that I'm
hoping someone here will be able to help with. Essentially, what seems to be
happening is that the file dependency I am using seems to become locked
whenever the "CacheItemRemovedCallback" for it is called. This is probably
better explained using code, so here goes:


'From global.asax - Generates cache on application start

Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
cachehandle.RefreshCache("dtMainExcelData", "AppStart")
End Sub


'From one of the pages using the data that is cached

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then

' Make sure Cache item isn't empty, if it is, fill it up
If Cache("dtMainExcelData") Is Nothing Then
cachehandle.RefreshCache("dtMainExcelData", "PageLoad")
End If

Dim dtMainExcelData As DataTable

dtMainExcelData = CType(Cache("dtMainExcelData"), DataTable)

dgExcelData.DataSource = dtMainExcelData
dgExcelData.DataBind()
End If
End Sub

'The caching methods from my code module: "cachehandle"

Public Sub RefreshCache(ByVal strObjectName As String, ByVal strMode As
String)
'strMode not used. It can be used to handle refresh differently based on
where it is called from
'strObjectName enables handling of multiple cache items with identical
dependencies

Dim onRemove As New CacheItemRemovedCallback(AddressOf RemovedCallback)
Dim myCacheDependency As New
CacheDependency(System.Configuration.Configuration Settings.AppSettings("dependencyFile").ToString())

Dim dtExcelData As DataTable = GetExcelData(strObjectName)

System.Web.HttpContext.Current.Cache.Insert(strObj ectName, dtExcelData,
myCacheDependency, _
Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration,
CacheItemPriority.NotRemovable, onRemove)
End Sub

Public Function GetExcelData(ByVal strObjectName As String) As DataTable
Dim myConnection As OleDbConnection
Dim strServerPath, strConnection As String
Dim dtExcelData As DataTable

strServerPath = "C:\cachetest\cachefile.xls"

Dim myOleDBCommand As OleDbCommand
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
strServerPath.ToString & ";Extended Properties=Excel 8.0;"
myConnection = New OleDbConnection(strConnection)

myOleDBCommand = New OleDbCommand("SELECT [Product],[Part
No],[DESCRIPTION],[Q'TY],[TCO],[UK COST],[Date IN] FROM [Sheet1$] WHERE
[Product] <> '';", myConnection)

Try
myConnection.Open()

'ConvertDataReaderToDataTable does exactly as named.
'Locking problem was occuring before I started to use this function.
dtExcelData =
ConvertDataReaderToDataTable(myOleDBCommand.Execut eReader(CommandBehavior.CloseConnection))
Return dtExcelData

Catch e As OleDbException
'This is the exception that occurs.

Finally
If Not myConnection Is Nothing Then
If myConnection.State = ConnectionState.Open Then
myConnection.Close()
End If

myConnection.Dispose()
myConnection = Nothing
End If

'Clutching at straws here
OleDbConnection.ReleaseObjectPool()
myOleDBCommand = Nothing
End Try
End Function

Public Sub RemovedCallback(ByVal key As String, ByVal value As Object,
ByVal reason As CacheItemRemovedReason)
If Not reason.ToString = "Removed" Then
RefreshCache(key.ToString, "CallBack")
End If
End Sub

To explain the process a bit, the RefreshCache call in the Application_Start
method runs without any problems. When I change the file (Either by editing
then saving, or overwriting), the RemovedCallback routine runs and as long as
the reason for removal isn't "Removed", the RefreshCache routine is called.
RefreshCache then calls GetExcelData, which then fails with the following
exception:

System.Data.OleDb.OleDbException: The Microsoft Jet database engine cannot
open the file ''. It is already opened exclusively by another user, or you
need permission to view its data.
at System.Data.OleDb.OleDbConnection.ProcessResults(I nt32 hr)
at System.Data.OleDb.OleDbConnection.InitializeProvid er()
at System.Data.OleDb.OleDbConnection.Open()
at ProgramName.Global.GetExcelData(String strObjectName) in
C:\cachetest\cachehandle.vb:line 99
at ProgramName.Global.RefreshCache(String strObjectName, String strMode)
in C:\cachetest\cachehandle.vb:line 20
at ProgramName.Global.RemovedCallback(String key, Object value,
CacheItemRemovedReason reason) in C:\cachetest\cachehandle.vb:line 151
at
System.Web.Caching.CacheEntry.CallCacheItemRemoved Callback(CacheItemRemovedCallback callback, CacheItemRemovedReason reason)

This I initially put down to not closing the connections properly, but if I
then allow the page_load event to call the RefreshCache routine it succeeds
with no exceptions. Can anyone help me on this? Is this a bug / feature in
..Net that stops the code accessing the dependency file whilst in the
"CacheItemRemovedCallback" loop?

Thanks in advance.

That post looks terrible in Firefox (Fine in IE). For a formatted version
(With tabs), you can look at this text file:
http://www.tacticaladvantage.co.uk/caching_post.txt