Martin Hatch – Tales of Tech

This has been a mission of mine for a while now (before I went on holiday and took a 2 week hiatus from all things SharePoint :)).

One of the clients I’ve been working with has been trying to replicate a pretty simple operation (by normal development standards). They have a SharePoint list with a LOT of items in it (we are talking 200,000 list items and above) and includes some Choice fields.

They want to return a count of how often each choice value is being used. Now, if you were using SQL Server you would simply do the following pseudo-SQL:

select count(*) from myList group by myChoiceField

At first look in SharePoint this is not possible:

• There is no “count” operation in CAML, nor any other kind of aggregation function
• SharePoint Search “full text query” does not support the count(*) operator (or anything similar)
• The only reference to aggregations is in the SPView.Aggregations property .. this is only used by the rendered HTML and the values are not returned in the result set.

Now .. I know that you can get count values on a list, if you create a View with a Group By then it shows you the number of items in each group, so it MUST be possible! So my mission started

List view with groups We want to replicate this behaviour, but programmatically!

First.. we need a test environment
The first thing I did was create a really big list. We are talking about 200,000 list items, so you can’t just pull all the items out in an SPQuery (as it would be far too slow!).

I generated a simple custom list. I add a choice field (with optional values of 1-20) and then generated 200,000 list items with a randomly assigned choice value (and a bunch of them without any choice value at all .. just for laughs).

Now I could play with my code

Attempt Number 1 – Retrieve all list and programmatically calculate the counts (fail)
I kinda knew this wouldn’t work .. but I needed a sounding board to know HOW bad it really was. There are 200,000 items after all, so this was never going to be fast.

• Use SPQuery to retrieve 2 fields (the ID, and my “choice” field).
• Retrieve the result set, and iterate through them, incremementing an integer value to get each “group” count value

This was a definite #fail.To retrieve all 200,000 list items in a single SPQuery took about 25 seconds to execute … FAR too slow.

Attempt Number 2 – Execute separate query for each “group” (fail)
I was a little more positive with this one … smaller queries execute much faster so this had some legs (and this is certainly a viable option if you only want the count for a SINGLE group).

• Create an SPQuery for each of the “choice” values we want to group by (there are 20 of them!)
• Execute each query, and use SPListItemCollection.Count to get the value

Unfortunately this was another spectacular #fail. Each query executed in around 2 seconds .. which would be fine if we didn’t have to do it 20 times! 🙁 (i.e. 40 second page load!!)

Attempt Number 3 – Use the SPView object (success!)
Ok .. so I know that the SPView can render extremely fast. With my sample list, and creating a streamlined “group by” view it was rendering in about 2 seconds (and thats on my laptop VM! I’m sure a production box would be much much quicker).

The main problem is … how do you get these values programmatically?

The SPView class contains a “RenderAsHtml” method which returns the full HTML output of the List View (including all of the group values, javascript functions, the lot). My main question was how did it actually work? (and how on earth did it get those values so quickly!)

I started off poking into the SPView object using Reflector (tsk tsk). The chain I ended up following was this:

• SPView.RenderAsHtml() –>
• SPList.RenderAsHtml() (obfuscated … arghhhh)

So that was a dead end .. I did some more poking around and found out that SPContext also has a view render method …

• SPContext.RenderViewAsHtml() –>
• SPContextInternalClass.RenderViewAsHtml() –>
• COM object ! (arghhhh)

Now .. the fact that we just hit a COM object suggests that we are starting to wander towards the SQL queries that get executed to retrieve the view data .. I didn’t want to go anywhere NEAR that one, so I decided to leave it there and perhaps try using the output HTML instead (nasty .. but not much of a choice left!).

using (SPSite site = new SPSite(https://myspsite))
{
SPList list = site.RootWeb.Lists[“TestList”];
string strViewHtml = list.Views[“GroupedView”].RenderAsHtml();
}

Having done this we now have the HTML output of our view (and this code takes about 2-3 seconds to execute … fast enough for my laptop .. we can always cache the value if needed).
 
Looking through the DOM output in the browser, it was possible to identify the “group” element by their attributes. It is a TBody node with both an ID attribute and a “groupString” attribute (the GroupString is the important one, as it tells us the view is configured to “Group By”).
 
What I needed next was a way of getting the actual values out of the HTML. For this I used the extremely awesome “HTML Agility Pack” from Codeplex. This is a set of libraries that allow you to parse DOM elements, including both plain “poorly formed” HTML as well as XHTML, and then use XPath queries to extract any values you want (much in the same way you would normally use the XML namespace for XHTML).
 
This gave me the TBODY node, and from there I could use string manipulation on the “InnerText” to pull out the group name and the count value 🙂

// Using HTML Agility Pack – Codeplex
// load the HTML into the HtmlDocument object

HtmlDocument doc = new HtmlDocument();
doc.LoadHtml(strViewHtml);

// retrieve all TBODY elements which have both
// an ID and groupString attribute
HtmlNodeCollection nodes = doc.DocumentNode.SelectNodes(“//tbody[@id][@groupstring]”);

if (nodes != null)
{
foreach (HtmlNode node in nodes)
{
// extract the Group Name
string strGroupName = node.InnerText.Substring(node.InnerText.LastIndexOf(“ ”)+6);
strGroupName = strGroupName.Substring(0, strGroupName.IndexOf(“&#”)-1);
Console.Write (“Group: ” + strGroupName + “, “);

// extract the number of items
string strValueText = node.InnerText.Substring(node.InnerText.LastIndexOf(“(“) + 1);
Console.WriteLine(“Number of Items: ” + strValueText.Substring(0, strValueText.Length – 1));
}
}

As you can see I’m doing some rather nasty SubString statements.. there may well be a quicker and cleaner way to do this using Regex .. this was more a proof of concept than anything else 🙂

Result!

Console output, showing group names and counts. 3 seconds isn’t bad, running on a “single server” laptop VM image 🙂

The end result was 2-3 second bit of code, retreiving Group By, Count values for a list with 200,000 list items.

Not bad for an afternoons work 🙂

Attempt 4 – Do the same thing in JQuery (kudos to Jaap Vossers)
This was actually the original solution, I asked Jaap if he could look at this if he had spare time, as I knew he had a lot of JQuery experience (and he blew me away by having it all working in under 30 minutes!).

Basically it uses pretty standard JQuery to go off and retrieve the HTML content from another page, scraping the HTML and pulling back the values. Same as the C# it grabs the group TBody, then walks down the DOM to retrieve the text value that it outputs.

The speed is roughly the same as the actual view itself. I’m sure some more JQuery could be employed to pull out the specific values and do more with them, but the concept appears to be sound:

<script type=”text/javascript” src=”https://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js”></script>

<script type=”text/javascript”>
$(document).ready(function(){

// you will need to change this URL
var url = https://myspsite/Lists/MyList/GroupedView.aspx;

var groupings = [];

$.get(url, function(data) {
$(data).find(“tbody[id^=titl][groupString] > tr > td”).each(

function(index, value){
groupings.push($(this).text());
}
);

$(“#placeholder”).append(“<ul></ul>”);

$.each(groupings, function(index, value){

$(“#placeholder ul”).append(“<li>” + value + “</li>”)
});
});
});

</script>
<div id=”placeholder”></div>

tada .. (thanks Jaap!)

Result from JQuery output, dropped into a Content Editor Web Part

Summary
Well .. I know doing HTML scraping isn’t pretty, but seeing as the code is MUCH faster than anything else I’ve seen (and is stuck in the middle of a COM object) there didn’t seem to be much choice.

By all means, feel free to let me know if you have any alternatives to this.

This one plagued me for quite some time.. you create a new site, and some pages. The navigation menus are all there but when you look in code the SPNavigationNodeCollections are null.

This is the same for both the standard WSS navigation collection (SPWeb.Navigation.QuickLaunch) and the publishing site collection (PublishingWeb.CurrentNavigationNodes).

However … you navigate the the “Modify Navigation” screen and make any change, click Ok and magically all the nodes appear in code!

Why does this happen?
the main thing is the role of the “SiteMap” providers in SharePoint. These are what really drive your navigation rendering, presenting an XML map of the “nodes” structure of your pages and using controls like the ASP.Net menu to render them.

The problem is, these don’t always match up with the data in the content database around navigation settings. I think this follows the similar grounds of “ghosting” in that (for performance reasons) the SPNavigationNodeCollection doesn’t get filled up with data until you “customise” something.

This is normally achieved by going to the “Modify Navigation Settings” page and clicking the “ok” button, at which point SharePoint conveniently goes and populates the nodes collection, and it becomes available to you in code.

What can I do about it?
Well, thankfully there is a fix for this.

You can manually create the nodes yourself, but you DO have to add some special node properties so that SharePoint recognises them as being “linked” to the existing pages and sites, otherwise you end up with duplicates all over the place!

The trick is that any node you add for pages you add a custom Property of NodeType set to the value of Page. For sub-sites you set this property to Area.

This way, SharePoint sees your custom nodes as being “Page” and “Site” nodes instead of new custom links!

Simple eh???

The code is listed below:

// use a Publishing Web object so we can access pages

PublishingWeb pWeb = PublishingWeb.GetPublishingWeb(web);

// use this to store the urls that exist in the current nav

List currentUrls = new List();

foreach (SPNavigationNode node in web.Navigation.QuickLaunch)

{

// collect all of the existing navigation nodes

// so that we don’t add them twice!

currentUrls.Add(node.Url.ToLower());

}

foreach(PublishingPage page in pWeb.GetPublishingPages())

{

// check to make sure we don’t add the page twice

if (!currentUrls.Contains(page.Uri.AbsolutePath.ToLower())

&& page.IncludeInCurrentNavigation)

{

// create the new node

SPNavigationNode newNode = new SPNavigationNode(page.Title, page.Url);

newNode = web.Navigation.QuickLaunch.AddAsFirst(newNode);

// IMPORTANT

// SET THE NodeType to “Page”

newNode.Properties[“NodeType”] = “Page”;

// save changes

newNode.Update();

}

}

foreach(SPWeb tempWeb in web.Webs)

{

// make sure we don’t add the sub-site twice

if (!currentUrls.Contains(tempWeb.ServerRelativeUrl.ToLower()))

{

// create the new node

SPNavigationNode newNode = new SPNavigationNode(tempWeb.Title, tempWeb.ServerRelativeUrl);

newNode = web.Navigation.QuickLaunch.AddAsLast(newNode);

// IMPORTANT

// SET THE NodeType to “Area”

// (this is a throwback to SPS 2003 where it used

// “Portal Area” instead of sub sites)

newNode.Properties[“NodeType”] = “Area”;

newNode.Update();

}

}

// save changes to the Publishing Web

pWeb.Update();

Timer jobs are wonderfully robust creatures. They effectively replace the "Windows Scheduled Task" for SharePoint servers, allowing you to run .Net code on a scheduled or "one off" basis.

However, there can be some confusion about running Timer Jobs on multiple servers, so this should clear it up a bit.

1. By default Timer Jobs will only execute on the server that they are called from (typically the Central Administration server)
2. Through code you can specify a specific box (SPServer) to run your Timer Job on.

3. It is possible to run a Timer Job on every server on the farm (although this can be dangerous!)

The crux of all this is based on the SPJobDefinition constructor (https://msdn.microsoft.com/en-us/library/microsoft.sharepoint.administration.spjobdefinition.spjobdefinition.aspx).

The constructor includes a parameter for an SPServer object, which allows you to specify which server the timer job will run on.

Example Code 1 – Add Job to single Server:

The code below is for a Web Application scoped which will register a job definition on a single server. As Web Application scoped features are activated from Central Administration, it will use the current SPServer (i.e. the Central Admin server).

This is useful for code which modified content in the database, as you only want it to execute a single time.

public override void FeatureActivated(SPFeatureReceiverProperties properties)

{

SPWebApplication webApp = properties.Feature.Parent as SPWebApplication;

SPJobDefinition job = new SPJobDefinition("CustomJobDef", webApp);

}

Example Code 2 – Add Job to All Servers:

The code below is for a Web Application scoped which will register a job definition on all web front end servers in the farm.

This is useful for code which modifies files or server settings, as you need it to execute separately for each server.

public override void FeatureActivated(SPFeatureReceiverProperties properties)

{

SPWebApplication webApp = properties.Feature.Parent as SPWebApplication;

foreach (SPServer server in SPFarm.Local.Servers)

{

if (server.Role == SPServerRole.WebFrontEnd)

{

SPJobDefinition job = new SPJobDefinition("CustomJobDef", webApp, server, SPJobLockType.None);

}

}

}

Hope this helps! Enjoy!

Answer … YES

It’s amazing how many people think that this is not the case. What is even more amazing is how many people blindly accept it without actually trying it out themselves.

If you attach an EventReceiver to your Content Type and then create a Child Content Type then the event receiver will still fire on child content types!

The lesson of the day?

A man who asks a question is a fool for 5 minutes ..
A man who asks no questions is a fool for life!

I wanted to introduce this topic before my break for the festive season, as I am going to be offline pretty much until a few weeks into 2009. And this topic is around the subject of Delegate Controls!

What are Delegate Controls?
Well .. delegates are basically a new control that can be used in SharePoint (both WSS 3.0 and MOSS 2007) which allow you to render different controls on different sites, using features to switch them on and off.

Sound interesting? Well let me whet your appetite further.

Apart from the fact that you can create your own delegates (hopefully your dev brain boggles already!) but Delegates are already in use in a large number of places in SharePoint:

• Top Navigation Menu Control
• Quick Launch Menu Control
• Search Controls
• My Links / Quick Links controls
• etc …

So .. in short, if you want to modify these controls you don’t need to modify the master page.
I’ll say that again, just in case you missed it. You don’t need to modify the master page.

The delegate controls are already in the default.master, so you just need to install and activate a feature which tells SharePoint to use your custom control instead of the standard ones!

Neat huh? (I knew you’d like it)

So what kind of controls can I use with Delegates?
You’ll like this answer too …. anything.

Yep, any control (either ASCX based user controls, or DLL based server controls) can be dropped into a delegate. All you need is a feature that registers it!

So how does it all work then?
Well .. delegates have 2 parts to them:

• ASP.Net Delegate Control (effectively a placeholder)
• Feature which registers a new ASP.Net control to use in place of the delegate

The structure of a Delegate Control is very simple:

You only have 1 attribute to worry about; ControlId is basically a unique "name" for your placeholder. The example shown here is for the standard Search control that appears on every page.

After you’ve got your Delegate Control, you need a feature to implement your actual ASP.Net controls. Luckily the feature is quite straightforward.

This post was prompted by the problems I’ve been having trying to get header nodes to be added to a site when it is provisioned, which have a custom URL pointing at a different site in the site collection.

Despite the NavBar element in the onet.xml having a URL attribute, this had no effect on my quick launch and it was always created using the current web’s URL (i.e. on click it just took me straight to the current web site home page).

So, I embarked on a simple feature which I could use to create those navigation headers.

The code is pretty simple:

public override void FeatureActivated(SPFeatureReceiverProperties properties)
        {
            // get the current "Web" object
            using (SPWeb web = (SPWeb)properties.Feature.Parent)
            {
                    // create the new navigation node
                    SPNavigationNode newNode =
                        new SPNavigationNode("Go to any sub site", "/anysite/anysubsite", true);

                    // add the node to the quicklaunch menu
                    web.Navigation.QuickLaunch.AddAsLast(newNode);

                    // save the changes
                    newNode.Update();
            }
        }

Now, the main thing to note is that when you create your SPNavigationNode object to use the true argument in the constructor!

This tells SharePoint that the URL is an External URL (and by External .. I mean "not in the current web"). If you don’t set this then you will get an error telling you that the URL is invalid, or that the page/file cannot be found.

Even if the URL is a relative URL to the same site collection, you still have to set this value to true! Not entirely obvious that one, so one to watch out for.

This post was prompted by a post in the MSDN Forums. Simply put … what order do things happen in the onet.xml?

This may seem like a trivial question .. but if you are writing custom features, it is important (to say vital) that you know whether or not your feature will activate before a list is created or after, or whether or not your files in modules will have been provisioned or not.

The order that I discovered is as follows:

1. *<SiteFeatures> in onet.xml
2. *Stapled Site Features (stapled using FeatureSiteTemplateAssociation)
3. <WebFeature> in onet.xml
4. Stapled Web Features (using FeatureSiteTemplateAssociation)
5. <Lists> in onet.xml
6. <Modules> in onet.xml

* note – obviously Site Features will only activate if you are creating a Site Collection, as opposed to a normal web site

How I worked this out

Well, you can copy the steps yourself, in 8 easy steps:

First off, create a new Custom Site Definition (a simple copy of STS). In my example, I called it MHSTS with a single configuration (#0) and created a WEBTEMP file to register it.

Second, create a new SPFeatureEventReceiver class (in an assembly from a class library). The code in the "FeatureActivated" should be something like this:

Third, create 5 features (the scope is in [ ])

• mhOnetSiteFeature [Site]
• mhStapledSiteFeature [Site]
• mhOnetWebFeature [WEB]
• mhStapledWebFeature [WEB
• mhManualWebFeature [WEB]

Each of these needs to be attached to the same SPFeatureEventReceiver class that we referenced earlier. This allows your debugger to work out which feature is activating … so each time your code steps into breakpoints, you can check the "Feature.Definition" properties and work out which feature is being activated.

Fourth step, create 2 more features which will act as Feature Staplers (using FeatureSiteTemplateAssociation elements).

• mhWebStapler [Site]
  • Staples the mhStapledWebFeature to the MHSTS#0 template (when used to create a new Site or Site Collection).
• mhSiteStapler [WebApplication]
  • Staples the mhStapledSiteFeature to the MHSTS#0 template (when used to create a Site Collection)

Fifth.. in your custom site definition (MHSTS) add the following features into the onet.xml:

• <SiteFeatures>
  • mhOnetSiteFeature
  • mhWebStapler
• <WebFeatures>
  • mhOnetWebFeature

Sixth; Install all of the features using STSADM, and (in Central Administration) activate the "mhSiteStapler" feature within a test Web Application.

Seventh; Within that Web Application, create a new Site Collection using your new site definition (MHSTS). This should fire off all of your features in the following order;

1. mhOnetSiteFeature
2. mhStapledSiteFeature
3. mhOnetWebFeature
4. mhStapledWebFeature

(you can check this by debugging your event handler, and setting a watch on the properties.Definition.DisplayName value)

Now .. if you are keeping a close eye on your debugging window, then you can also start to look at the state of the SPWeb itself. The main thing is that None of the Lists or pages will have been created … at ANY point in this process. This means that <Lists> and <Modules> will not be executed until ALL of your features have activated.

Eighth; Now you can double-check the theory … navigate to your new Site Collection .. and activate your remaining feature manually (mhManualWebFeature).

You should still have your debugging window open .. which should let you know that now (after the site is provisioned) all of the Lists and web pages are now accessible from Code.

Ok .. that’s great … errrr … so What ??

Well, this has quite a lot of importance for writing custom features. If you want to write features which automatically manipulate pages and lists then you are going to be a bit stuck, as the lists and pages won’t have been created until after your feature activates! (bummer eh? But what you gonna do?)

To get around this problem you really have 2 options:

1. Make your own custom definition. Strip the lists out of the onet.xml, and create ListInstance Features. These can then be created during the feature activation stage, allowing them to be modified by other features that are being activated / stapled.
2. Put some While Loops with Thread.Sleep() statements in your code, basically waiting until the lists have been created. This is not a very elegant solution but you don’t have much choice if you are trying to use staplers to modify out of the box definitions (or if you don’t want to touch the onet.xml).

Thats it … comments welcome as always 🙂