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.
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:
1 class DataTableCacheObject
2 {
3 DateTime? lastModified;
4 DataTable dataTable;
5
6 public class Result
7 {
8 public DataTable dataTable { private set; get; }
9 public SPList list { private set; get; }
10 internal Result(DataTable dataTable, SPList list)
11 {
12 this.dataTable = dataTable;
13 this.list = list;
14 }
15 }
16
17 public static Result GetDataTable(string webUrl, string listName)
18 {
19 Result result = null;
20
21 if (string.IsNullOrEmpty(listName))
22 return null;
23
24 Guid webId = new Guid();
25 if (string.IsNullOrEmpty(webUrl))
26 webId = SPContext.Current.Web.ID;
27 else
28 {
29 using (SPWeb web = SPContext.Current.Site.AllWebs[webUrl])
30 {
31 webId = web.ID;
32 }
33 }
34
35 SPSecurity.RunWithElevatedPrivileges(delegate()
36 {
37 using (SPSite site = new SPSite(SPContext.Current.Site.ID))
38 {
39 using (SPWeb web = site.AllWebs[webId])
40 {
41 // Check if user has view rights to list
42 //
43 SPList list = web.Lists[listName];
44 if (!list.DoesUserHavePermissions(SPContext.Current.Web.CurrentUser,SPBasePermissions.ViewListItems))
45 return;
46
47 // Get Last Modified datetime
48 //
49 PortalSiteMapProvider psmp = PortalSiteMapProvider.GlobalNavSiteMapProvider;
50 PortalWebSiteMapNode node = psmp.FindSiteMapNode(web.ServerRelativeUrl) as PortalWebSiteMapNode;
51 SPQuery query = new SPQuery();
52 query.Query = "<OrderBy><FieldRef Ascending=\"FALSE\" Name=\"Modified\" /></OrderBy>";
53 query.RowLimit = 1;
54 SiteMapNodeCollection smnc = psmp.GetCachedListItemsByQuery(node, listName, query, web);
55 DateTime? lastModified = null;
56 foreach (SiteMapNode smn in smnc)
57 {
58 lastModified = (DateTime)((PortalListItemSiteMapNode)smn)[SPBuiltInFieldId.Modified];
59 }
60
61 // Look up DataTable in Cache
62 //
63 string cacheKey = String.Format("DataTable:{0}:{1}", web.ID, listName);
64 DataTableCacheObject dtco = HttpContext.Current.Cache[cacheKey] as DataTableCacheObject;
65 if (dtco != null)
66 {
67 // If not modified use DataTable from Cache
68 //
69 if (dtco.lastModified.Equals(lastModified))
70 {
71 result = new Result(dtco.dataTable.Copy(),list);
72 return;
73 }
74
75 // Modified => Replace
76 //
77 dtco.dataTable.Dispose();
78 }
79
80 // Get new DataTable and put into Cache
81 //
82 dtco = new DataTableCacheObject();
83 dtco.lastModified = lastModified;
84 dtco.dataTable = list.Items.GetDataTable();
85 HttpContext.Current.Cache[cacheKey] = dtco;
86 result = new Result(dtco.dataTable.Copy(), list);
87 }
88 }
89 });
90 return result;
91 }
92 }