Caching a SharePoint list as a datatable

19-Dec-2008
SharePoint 2007

For one of my current projects I needed to show a SPGridView with data from a small list on the frontpage of the Intranet.

The list wasn’t very small so didn’t want to read if from the database on every hit of the frontpage, but on the hand it was so big that I couldn’t cache it.

Now the big question was how to cache it.

If I used PortalSiteMapProvider then it’ll always be refreshed when there was a change, but it would be hard to get the data into the SPGridView for displaying, sorting, paging and filtering.

If I on the other hand justed cached a DataTable, then it’ll be easy to use in the SPGridView, but then I’d have to figure out when to invalidate the cache. It should be often enough that users wasn’t annoyed with out of date data, but seldom enoughtthat it didn’t annoy them due to the performance hit.

So I decided to combine the two and use the PortalSiteMapProvider to figure out when an update was needed and the DataTable for the real caching of the list items.

If you need something similar then here is my class, it doesn’t deal with item level permissions, so if you need that you’ll have to implement something different:

class DataTableCacheObject
{
    DateTime? lastModified;
    DataTable dataTable;
 
    public class Result
    {
        public DataTable dataTable { private set; get; }
        public SPList list { private set; get; }
        internal Result(DataTable dataTable, SPList list)
        {
            this.dataTable = dataTable;
            this.list = list;
        }
    }
 
    public static Result GetDataTable(string webUrl, string listName)
    {
        Result result = null;
  
        if (string.IsNullOrEmpty(listName))
            return null;
 
        Guid webId = new Guid();
        if (string.IsNullOrEmpty(webUrl))
            webId = SPContext.Current.Web.ID;
        else
        {
            using (SPWeb web = SPContext.Current.Site.AllWebs[webUrl])
            {
                webId = web.ID;
            }
        }
 
        SPSecurity.RunWithElevatedPrivileges(delegate()
        {
            using (SPSite site = new SPSite(SPContext.Current.Site.ID))
            {
                using (SPWeb web = site.AllWebs[webId])
                {
                    // Check if user has view rights to list
                    //
                    SPList list = web.Lists[listName];
                    if (!list.DoesUserHavePermissions(SPContext.Current.Web.CurrentUser,SPBasePermissions.ViewListItems))
                        return;
  
                    // Get Last Modified datetime
                    //
                    PortalSiteMapProvider psmp = PortalSiteMapProvider.GlobalNavSiteMapProvider;
                    PortalWebSiteMapNode node = psmp.FindSiteMapNode(web.ServerRelativeUrl) as PortalWebSiteMapNode;
                    SPQuery query = new SPQuery();
                    query.Query = "<OrderBy><FieldRef Ascending=\"FALSE\" Name=\"Modified\" /></OrderBy>";
                    query.RowLimit = 1;
                    SiteMapNodeCollection smnc = psmp.GetCachedListItemsByQuery(node, listName, query, web);
                    DateTime? lastModified = null;
                    foreach (SiteMapNode smn in smnc)
                    {
                        lastModified = (DateTime)((PortalListItemSiteMapNode)smn)[SPBuiltInFieldId.Modified];
                    }
 
                    // Look up DataTable in Cache
                    //
                    string cacheKey = String.Format("DataTable:{0}:{1}", web.ID, listName);
                    DataTableCacheObject dtco = HttpContext.Current.Cache[cacheKey] as DataTableCacheObject;
                    if (dtco != null)
                    {
                        // If not modified use DataTable from Cache
                        //
                        if (dtco.lastModified.Equals(lastModified))
                        {
                            result = new Result(dtco.dataTable.Copy(),list);
                            return;
                        }
 
                        // Modified => Replace
                        //
                        dtco.dataTable.Dispose();
                    }
  
                    // Get new DataTable and put into Cache
                    //
                    dtco = new DataTableCacheObject();
                    dtco.lastModified = lastModified;
                    dtco.dataTable = list.Items.GetDataTable();
                    HttpContext.Current.Cache[cacheKey] = dtco;
                    result = new Result(dtco.dataTable.Copy(), list);
                }
            }
        });
        return result;
    }
}

The Black Knight Sings

The Black Knight Sings

Caching a SharePoint list as a datatable