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.ConfigurationSettings.AppSettings("dependencyFile").ToString())

        Dim dtExcelData As DataTable = GetExcelData(strObjectName)

        System.Web.HttpContext.Current.Cache.Insert(strObjectName, 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.ExecuteReader(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(Int32 hr)
  at System.Data.OleDb.OleDbConnection.InitializeProvider()
  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.CallCacheItemRemovedCallback(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.