Function Caching

Projects > Caching


Using Dictionary

This is not thread safe. Exceptions will occur when used from multiple threads.
Cached items will stay in memory forever until the session is closed.

static System.Collections.Generic.Dictionary<string, string> cache = new System.Collections.Generic.Dictionary<string, string>(); 

public static object udf_SOMETEXT(string sArgument1)
{
    string sKey = "cachedValue-" + sArgument1;

    if (cache.ContainsKey(sKey) == true)
    {
        return cache[sKey];
    }
    else
    {
        //item is not in the cache
        object result = ExcelDna.Integration.ExcelAsyncUtil.Run("udf_SOMETEXT",
                                                                        sKey,
        delegate
        {
            System.Threading.Thread.Sleep(3000);
            return "completed at: " + System.DateTime.Now.ToString("HH:mm:ss");
        });

        if (result.Equals(ExcelDna.Integration.ExcelError.ExcelErrorNA))
        {
            return "Please wait ...";
        }
        else
        {
            cache[sKey] = (string)result;
            return result;
        }
    }
}

Using MemoryCache

This function will cache the results just in the current session.
If you close Excel then the cache will be cleared.
Add a Reference to the System.Runtime.Caching assembly.
Assemblies, Framework: "System.Runtime.Caching"
This uses the MemoryCache Class available in .NET 4.0

public static object udf_SOMETEXT(string sArgument1) 
{
    string sKey = "cachedValue-" + sArgument1;
    System.Runtime.Caching.ObjectCache cache = System.Runtime.Caching.MemoryCache.Default;
    string cachedItem = cache[sKey] as string;

    if (cachedItem != null)
    {
        return cachedItem;
    }
    else
    {
       object result = ExcelDna.Integration.ExcelAsyncUtil.Run("udf_SOMETEXT",
                                                               sKey,
        delegate
        {
            System.Threading.Thread.Sleep(3000);
            return "completed at: " + System.DateTime.Now.ToString("HH:mm:ss");
        });

        if (result.Equals(ExcelDna.Integration.ExcelError.ExcelErrorNA))
        {
            return "Please wait ...";
        }
        else
        {
            // pass in the fixed date and time the cache entry will expire - 1 minute
            cache.Add(sKey, result, System.DateTime.Now.AddMinutes(1));
            return result;
        }
    }
}

If you request the same item with multiple threads at the same time, the requests do not wait for the first one to finish. The item will be requested multiple times.



Persistent Cache

This is when the cache is backed up outside of the process memory.
Typically the cache is backed up in a file.
link - docs.microsoft.com/en-us/dotnet/desktop/wpf/advanced/walkthrough-caching-application-data-in-a-wpf-application?view=netframeworkdesktop-4.8




© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext