Caching and Asynchronous Excel UDFs

This sample shows how the result of an Excel-DNA async UDF call can be cached using the .NET 4 MemoryCache class.

PS: Apparently there is a bug in the memory management of the .NET MemoryCache class. See the StackOverflow discussion and the Connect bug report. The SharpMemoryCache NuGet package might be an alternative, though I've not tried it.

<DnaLibrary Name="CachedAsyncSample" RuntimeVersion="v4.0" Language="C#">

  <Reference Name="System.Runtime.Caching" />
  <![CDATA[
    using System;
    using System.Threading;
    using System.Runtime.Caching;
    using ExcelDna.Integration;
    
    public static class dnaFunctions
    {
        public static object dnaCachedAsync(string input)
        {
            // First check the cache, and return immediately 
            // if we found something.
            // (We also need a unique key to identify the cache item)
            string key = "dnaCachedAsync:" + input;
            ObjectCache cache = MemoryCache.Default; 
            string cachedItem = cache[key] as string;
            if (cachedItem != null) 
                return cachedItem;
    
            // Not in the cache - make the async call 
            // to retrieve the item. (The second parameter here should identify 
            // the function call, so would usually be an array of the input parameters, 
            // but here we have the identifying key already.)
            object asyncResult = ExcelAsyncUtil.Run("dnaCachedAsync", key, () => 
            {
                // Here we fetch the data from far away....
                // This code will run on a ThreadPool thread.
    
                // To simulate a slow calculation or web service call,
                // Just sleep for a few seconds...
                Thread.Sleep(5000);
    
                // Then return the result
                return "The calculation with input " 
                        + input + " completed at " 
                        + DateTime.Now.ToString("HH:mm:ss");
            });
    
            // Check the asyncResult to see if we're still busy
            if (asyncResult.Equals(ExcelError.ExcelErrorNA))
                return "!!! Fetching data";
    
            // OK, we actually got the result this time.
            // Add to the cache and return
            // (keeping the cached entry valid for 1 minute)
            // Note that the function won't recalc automatically after 
            //    the cache expires. For this we need to go the 
            //    RxExcel route with an IObservable.
            cache.Add(key, asyncResult, DateTime.Now.AddMinutes(1), null);
            return asyncResult;
        }
    
        public static string dnaTest()
        {
            return "Hello from CachedAsyncSample";
        }
    }

  ]]>
</DnaLibrary>