SharePoint 2013/Online: Implement Client Caching for SharePoint Properties

SharePoint Pages are intended to act as data presentation layer to the end Users for the data stored in SharePoint List. In the rich interactive Client Centric Applications it is expected to have quick response to different User Actions like Read Data from or Save Data to SharePoint List.

Any delays due to read/write operations to SharePoint can negatively impact the User experience and could lead to denial of acceptance on developed features.

Using HTML5 Local Storage API, Web Applications can store data locally in user’s browser. Local Storage is more secure and large data can be stored without impacting overall Web Application Performance.

This new API allows large amount of data (approx. 5 MB) to be cached locally which is far larger than cookies which were commonly used for caching data.

Local Storage is maintained per domain & protocol (Origin of Request) and all Web Pages of same origin can use the same Cache.

In this article we will see HTML5 Local Storage API in action and utilize it to cache SharePoint Properties of primitive data type like String, Integer, Boolean and in one of the upcoming articles in the same series I will explain the caching of some of the complex SharePoint Objects.

Let’s kick start this demo with a new SharePoint App Project in Visual Studio 2013

• Choose “App for SharePoint” Project Template

• Specify SharePoint Site URL (On Premise or Online). Here I am using SharePoint Online Site to go with this demonstration
• Select hosting model as “SharePoint-Hosted”

• Enter SharePoint Online Account Credentials

• Wait till Visual Studio configure the project for you

I have added HTML code to default.aspx page that will display following elements to the UI

• Result Panel: This is a div that will display the output of the operations performed
• Get Web Title: This HTML Button will retrieve the Title of the Host Web and add it to the Cache on click event
• Clear Cache: This HTML Button will clear the local cache whenever needed on click event

• Add little CSS to App.css to make UI elements more intuitive

With this our UI is a go.

Now let’s explore the code that that we have added to App.js

• Step 1: Hooking up the event handlers to the click event for “Get Web Title” & “Clear Cache” button
• Step2: This function first checks if Local Storage API is supported in the current browser and if it is supported then clear the specific key by specify the key name or all the keys from local cache by calling Clear() methods that you can see as commented in this function.
• Step 3: This is the helper method that checks if current browser supports the HTML5 Local Storage API traditionally by querying JavaScript “window” object

• Step 4: This method is a helper function that helps to retrieve Query String Parameters out of the requested URL

• Step 5: Verifying if the current browser supports the HTML5 Local Storage API
• Step 6: Verifying if the current browser supports the HTML5 Local Storage API by utilizing Feature Detection capabilities of Modernizr.js Framework. For more details on Modernizr.js you can visit https://modernizr.com/
• Step 7: Instantiate the local storage object
• Step 8: Reading Web Title value out of Local Storage Cache by specifying the key
• Step 9: If the Web Title exists in the Local Cache then the value is retrieve out of it without any need of issuing call to SharePoint else we call another helper method “getWebTitle()” that will issue the request to SharePoint and get the Web Title, display it to result Panel and add the value to the Local Cache and make it available to server any subsequent request.

• Step 10: In this method we are making CSOM call to SharePoint in usual way, once the call gets succeed the Web Title will get displayed in the Result Panel and also adding the value to local storage by calling setItem() method. This value will be available to serve from Cache in response to any future request for Web Title.

With this we are done with the code.

Before we start execution we need to allow Read Permission to the App on Site Collection

• Launch AppManifest.xml
• Go to “Permissions” Tab
• Select “Site Collection” from Scope Dropdown
• Select “Read” from Permission Dropdown

• Run the App
• Enter the SharePoint Online Credential when prompted

•  Trust the App when prompted

When App lunches we can see the UI based on the HTML we have added earlier to the default.aspx page

We can see Result Panel where we will see the results of the operations and “Get Web Title”, “Clear Cache” buttons as well

Now when we click on “Get Web Title” button we can see two actions performed & logged in the Result Panel as follows-

• Since the Web Title is not available in the Local Cache, it is retrieved from SharePoint and saved back to the local cache
• Displaying Web Title in the Result Panel

If we investigate the Client – Server Request cycle using Fiddler we can see that there is actually a request issued to SharePoint and the response received from the server in form of requested data.

When we click “Get Web Title” button again we will see following two actions performed & logged in the Result Panel

• Since the Web Title is available in the Local Cache this time , so no call has been issued to SharePoint for data and Web Title has been retrieved from the Local Cache
• Displaying Web Title in the Result Panel retrieved out of Local Cache.

If we look into Fiddler again we can see that no request has been issued to SharePoint since we got the requested data available in Local Cache.

That’s it for this article.

In the upcoming article on Client Caching I will explain the caching scenarios related to bit more complex SharePoint Object like “SPListItemCollection” which is quite common return type when we query data from SharePoint Lists and deserve special attention as long as the caching is concerned.

Hope you find it helpful.

SharePoint 2013: Important SQL Queries on SharePoint Content Databases – Part II

In the previous article “SharePoint 2013: Important SQL Queries on SharePoint Content Databases – Part I”  on SQL Queries for SharePoint we have discussed some of the useful SharePoint Queries. This article is in continuation of Part-I and if you haven’t gone through the first article I would strongly recommend you to first go through it.

In this article I have included some more queries that are useful while working with SharePoint 2013 as follows-

Returns Documents Size based on Document Type

Query

 SELECT TOP 100 WEBS.FULLURL AS SITEURL, WEBS.TITLE AS [TITLE], DIRNAME + ‘/’ + LEAFNAME AS [DOCUMENT NAME], CAST((CAST(CAST(SIZE AS DECIMAL(10,2))/1024 AS DECIMAL(10,2))/1024) AS DECIMAL(10,2)) AS “SIZE IN MB” FROM DOCS INNER JOIN WEBS ON DOCS.WEBID = WEBS.ID INNER JOIN SITES ON WEBS.SITEID = SITES.ID WHERE DOCS.TYPE <> 1 AND (LEAFNAME IS NOT NULL) AND (LEAFNAME <> ”) AND (LEAFNAME NOT LIKE ‘%.STP’) AND (LEAFNAME NOT LIKE ‘%.ASPX’) AND (LEAFNAME NOT LIKE ‘%.XFP’) AND (LEAFNAME NOT LIKE ‘%.DWP’) AND (LEAFNAME NOT LIKE ‘%TEMPLATE%’) AND (LEAFNAME NOT LIKE ‘%.INF’) AND (LEAFNAME NOT LIKE ‘%.CSS’) ORDER BY “SIZE IN MB” DESC

 

Output

Returns Totals No. of Document of type (.Docx)

Query

SELECT COUNT(*) AS ‘# OF .DOCX’ FROM DOCS INNER JOIN WEBS ON DOCS.WEBID = WEBS.ID INNER JOIN SITES ON WEBS.SITEID = SITES.ID WHERE DOCS.TYPE <> 1 AND (LEAFNAME LIKE ‘%.DOCX’) AND (LEAFNAME NOT LIKE ‘%TEMPLATE%’)

 

Output

Returns Totals No. of Document of type (.PPTX)

Query

SELECT COUNT(*) AS ‘# OF .PPTX’ FROM DOCS INNER JOIN WEBS ON DOCS.WEBID = WEBS.ID INNER JOIN SITES ON WEBS.SITEID = SITES.ID WHERE DOCS.TYPE <> 1 AND (LEAFNAME LIKE ‘%.PPTX’) AND (LEAFNAME NOT LIKE ‘%TEMPLATE%’)

 

Output

Returns Totals No. of Document of type (.XLSX)

Query

SELECT COUNT(*) AS ‘# OF .XLSX’ FROM DOCS INNER JOIN WEBS ON DOCS.WEBID = WEBS.ID INNER JOIN SITES ON WEBS.SITEID = SITES.ID WHERE DOCS.TYPE <> 1 AND (LEAFNAME LIKE ‘%.XLSX’) AND (LEAFNAME NOT LIKE ‘%TEMPLATE%’)

 

Output

Returns Totals No. of Document of type (.HTML)

Query

SELECT COUNT(*) AS ‘# OF .HTML’ FROM DOCS INNER JOIN WEBS ON DOCS.WEBID = WEBS.ID INNER JOIN SITES ON WEBS.SITEID = SITES.ID WHERE DOCS.TYPE <> 1 AND (LEAFNAME LIKE ‘%.HTML’) AND (LEAFNAME NOT LIKE ‘%TEMPLATE%’)

 

Output

Returns all the top level site collections

Query

SELECT SITEID AS SITEGUID, ID AS WEBGUID, FULLURL AS URL, TITLE, AUTHOR, TIMECREATED FROM DBO.WEBS WHERE (PARENTWEBID IS NULL)

Output

Returns all the child sites in a site collection

Query

SELECT SITEID AS SITEGUID, ID AS WEBGUID, FULLURL AS URL, TITLE, AUTHOR, TIMECREATED FROM DBO.WEBS WHERE (NOT (PARENTWEBID IS NULL))

 

Output

Returns all the SharePoint groups in a site collection

Query

SELECT DBO.WEBS.SITEID, DBO.WEBS.ID, DBO.WEBS.FULLURL, DBO.WEBS.TITLE, DBO.GROUPS.ID AS EXPR1, DBO.GROUPS.TITLE AS EXPR2, DBO.GROUPS.DESCRIPTION FROM DBO.GROUPS INNER JOIN DBO.WEBS ON DBO.GROUPS.SITEID = DBO.WEBS.SITEID

 

Output

Returns all the users in a site collection

Query

SELECT DBO.WEBS.SITEID, DBO.WEBS.ID, DBO.WEBS.FULLURL, DBO.WEBS.TITLE, DBO.USERINFO.TP_ID, DBO.USERINFO.TP_DOMAINGROUP, DBO.USERINFO.TP_SITEADMIN, DBO.USERINFO.TP_TITLE, DBO.USERINFO.TP_EMAIL FROM DBO.USERINFO INNER JOIN DBO.WEBS ON DBO.USERINFO.TP_SITEID = DBO.WEBS.SITEID

 

Output

Returns all the members of the SharePoint Groups

Query

SELECT DBO.GROUPS.ID, DBO.GROUPS.TITLE, DBO.USERINFO.TP_TITLE, DBO.USERINFO.TP_LOGIN FROM DBO.GROUPMEMBERSHIP INNER JOIN DBO.GROUPS ON DBO.GROUPMEMBERSHIP.SITEID = DBO.GROUPS.SITEID INNER JOIN DBO.USERINFO ON DBO.GROUPMEMBERSHIP.MEMBERID = DBO.USERINFO.TP_ID

 

Output

Returns all the sites where a specific feature is activated

Query

–Feature ID of Announcements List : ’00BFEA71-D1CE-42de-9C63-A44004CE0104′

SELECT DBO.WEBS.ID AS WEBGUID, DBO.WEBS.TITLE AS WEBTITLE, DBO.WEBS.FULLURL AS WEBURL, DBO.FEATURES.FEATUREID, DBO.FEATURES.TIMEACTIVATED FROM DBO.FEATURES INNER JOIN DBO.WEBS ON DBO.FEATURES.SITEID = DBO.WEBS.SITEID AND DBO.FEATURES.WEBID = DBO.WEBS.ID WHERE (DBO.FEATURES.FEATUREID = ’00BFEA71-D1CE-42DE-9C63-A44004CE0104′)

Output

Returns all the users assigned to roles

Query

SELECT DBO.WEBS.ID, DBO.WEBS.TITLE, DBO.WEBS.FULLURL, DBO.ROLES.ROLEID, DBO.ROLES.TITLE AS ROLETITLE, DBO.USERINFO.TP_TITLE, DBO.USERINFO.TP_LOGIN FROM DBO.ROLEASSIGNMENT INNER JOIN DBO.ROLES ON DBO.ROLEASSIGNMENT.SITEID = DBO.ROLES.SITEID AND DBO.ROLEASSIGNMENT.ROLEID = DBO.ROLES.ROLEID INNER JOIN DBO.WEBS ON DBO.ROLES.SITEID = DBO.WEBS.SITEID AND DBO.ROLES.WEBID = DBO.WEBS.ID INNER JOIN DBO.USERINFO ON DBO.ROLEASSIGNMENT.PRINCIPALID = DBO.USERINFO.TP_ID

Output

Returns all the SharePoint groups assigned to roles

Query

SELECT DBO.WEBS.ID, DBO.WEBS.TITLE, DBO.WEBS.FULLURL, DBO.ROLES.ROLEID, DBO.ROLES.TITLE AS ROLETITLE, DBO.GROUPS.TITLE AS GROUPNAME FROM DBO.ROLEASSIGNMENT INNER JOIN DBO.ROLES ON DBO.ROLEASSIGNMENT.SITEID = DBO.ROLES.SITEID AND DBO.ROLEASSIGNMENT.ROLEID = DBO.ROLES.ROLEID INNER JOIN DBO.WEBS ON DBO.ROLES.SITEID = DBO.WEBS.SITEID AND DBO.ROLES.WEBID = DBO.WEBS.ID INNER JOIN DBO.GROUPS ON DBO.ROLEASSIGNMENT.SITEID = DBO.GROUPS.SITEID AND DBO.ROLEASSIGNMENT.PRINCIPALID = DBO.GROUPS.ID

Output

Returns list of unhosted pages in the SharePoint solution

Query

SELECT WEBS.FULLURL AS SITEURL, CASE WHEN [DIRNAME] = ” THEN ‘/’+[LEAFNAME] ELSE ‘/’+[DIRNAME]+’/’+[LEAFNAME] END AS [PAGE URL], CAST((CAST(CAST(SIZE AS DECIMAL(10,2))/1024 AS DECIMAL(10,2))/1024) AS DECIMAL(10,2))   AS ‘FILE SIZE IN MB’ FROM     DOCS INNER JOIN WEBS ON DOCS.WEBID = WEBS.ID WHERE [TYPE]=0 AND [LEAFNAME] LIKE ‘%.ASPX’) AND [DIRNAME] NOT LIKE (‘%_CATALOGS/%’) AND [DIRNAME] NOT LIKE (‘%/FORMS’) AND [DIRNAME] NOT LIKE (‘%LISTS/%’) AND [SETUPPATH] IS NOT NULL ORDER BY [PAGE URL]

Output

Returns list of Site Title and total number of users associated with it

Query

SELECT WEBS.FULLURL, WEBS.TITLE,COUNT(WEBMEMBERS.USERID) AS ‘TOTAL USER’ FROM WEBS INNER JOIN WEBMEMBERS ON WEBS.ID = WEBMEMBERS.WEBID WHERE FULLURL NOT LIKE ‘%SITES%’ AND FULLURL <> ‘MYSITE’ AND FULLURL <> ‘PERSONAL’ GROUP BY WEBS.FULLURL, WEBS.TITLE ORDER BY ‘TOTAL USER’ DESC

Output

That is all for this article.

Hope you find it helpful.