sharepoint guidance v2 – drop 6 video

Hi Guys,

Last week, we have released drop 6 of the Sharepoint guidance V2. In this drop, we have tackled the following subjects:

  • Several ways of doing list access
  • Caching BDC data and published pages
  • Using powershell to query sharepoint workflow associations

Here’s a video where Francis and I are talking about what’s in this drop


List access

There are a lot of ways to query list data. The most common way is to use the Content Query WebPart (CQWP). This webpart allows you to create a query against most sharepoint data. In previous drops, we’ve already demonstrated how to use the CQWP to query data. However, there are things the CQWP can’t be used for. In this drop, we’ve demonstrated some of those techniques:

Using the Portal Sitemap Provider to access list properties

The CQWP cannot access additional properties that can be stored on sharepoint objects such as lists and sites. You can use the Portal Sitemap Provider (PSMP) to get access to some of these items.

From MSDN: The Portal Sitemap Provider provides PortalSiteMapNode objects that represent a merging of the Windows SharePoint Services 3.0SPNavigation store and dynamic site structure, including sites and pages.

Since the PSMP also caches it’s data, it’s much more performant than querying all SPSite objects directly.

In our Reference Implementation, partners can create sites to collaborate in resolving issues with some of Contoso’s products. These sites are linked to a specific incident and we’d like to get an overview of the status of the incident.  So we are using the PSMP to get a list of all incident sites, with their status. The following codesnippet shows how we are doing that:

    1: PortalSiteMapProvider provider = PortalSiteMapProvider.CombinedNavSiteMapProvider;
    2: if (provider.CurrentNode != null && provider.CurrentNode.HasChildNodes)
    3: {
    4:     var nodes = provider.GetChildNodes(provider.CurrentNode).OfType<PortalWebSiteMapNode>();
    5:     var incidentNodes = from node in nodes
    6:                         where node.GetProperty("businessevent") != null &&
    7:                               node.GetProperty("businessevent").ToString().ToLower() == "incident"
    8:                         select new
    9:                         {
   10:                             Title = node.Title,
   11:                             Url = node.Url,
   12:                             Status = node.GetProperty("status"),
   13:                             CreatedDate = node.CreatedDate,
   14:                             LastModifiedDate = node.LastModifiedDate
   15:                         };
   17:     view.Data = incidentNodes;


Using the Object Model to access targetted lists across site collections

You can only use the CQWP and PSMP within a Site Collection. But what if you want to access data across site collections? If you want to access a specific list in a different sitecollection, the easiest way to do this is to use the Sharepoint object model.

In our Reference Implementation, each partner has it’s own sitecollection. On some pages, we’d like to show promotions, which are stored in a central SiteCollection.  Since we know which list to query, using the OM is the easiest approach. The following codesnippet shows how to open the site  that contains the list. The URL we are passing in determines which site in which sitecollection we are targeting. Note that we have to dispose the SPWeb that we have opened again.

    1: using (SPSite site = new SPSite(siteUrl))
    2: {
    3:     using (SPWeb web = site.OpenWeb())
    4:     {
    5:         if (web != null)
    6:         {
    7:             partnerPromotions = this.QueryForPages(web);
    8:         }
    9:     }
   10: }

Now once we have hold of the SPWeb, we can fire a query against it. That’s fairly straigtforward. Just fire a CAML query against it. We’re then populating a list of strongly typed objects that we can show in our Views:

    1: private List<PartnerPromotionEntity> QueryForPages(SPWeb web)
    2:  {
    3:      List<PartnerPromotionEntity> partnerPromotions = new List<PartnerPromotionEntity>();
    5:      SPList pagesLibrary = web.Lists["Pages"];
    6:      if (pagesLibrary != null)
    7:      {
    8:          SPQuery query = new SPQuery();
    9:          query.Query = string.Format("<Where><Eq><FieldRef Name='ContentType'/><Value Type='Text'>{0}</Value></Eq></Where>", "PromotionPage");
   10:          SPListItemCollection items = pagesLibrary.GetItems(query);
   11:          foreach (SPListItem item in items)
   12:          {
   13:              partnerPromotions.Add(new PartnerPromotionEntity
   14:                                         {
   15:                                             ImageTag = item["ProductImageField"] == null ? string.Empty : item["ProductImageField"].ToString(),
   16:                                             PromotionName = item["PromotionNameField"] == null ? string.Empty : item["PromotionNameField"].ToString(),
   17:                                             PromotionUrl = string.Format("{0}/Pages/{1}", web.ServerRelativeUrl, item["LinkFilename"])
   18:                                         });
   19:          }
   20:      }
   22:      return partnerPromotions;
   23:  }

Note that we’re using the GetItems(query) method. This is MUCH more performant than using the Items property. Usually, it’s much better to use the GetItems method than the Items property, because the items property will return ALL the data for that item. For large lists, with many columns, this becomes very slow!

Using search to query a lot of lists across site collections

In the previous example, we knew we wanted to target a single list in a different site collection. However, what would happen if you want to query infomation scattered around several sitecollections. For example, we want an overview of all incident tasks in the Contoso RI. However, these incident tasks are scattered around the incident sites for different partners (in different site collections).

If you wanted to query something like that using the Object Model, you’d need to iterate over all sites and lists to find the information. Then you’d better invest in a LOT more hardware ;) A better approach is using Sharepoint Search to find this information. The nice thing about Search is, that it is VERY fast. However, it will not give you a real time overview the data. Search uses a search crawler to index it’s data. So depending on your configuration of the search crawler, some of the information can be out of date.

We figured however, that for getting an overview of the Incident Tasks, It’s not really a problem if the results are not real time.

The following code example shows how we’ve implemented this. We are creating a Sharepoint Search Query and firing it at the Sharepoint Search Engine. The results, we’re putting into a list of objects and showing them in our View.

    1: StringBuilder query = new StringBuilder();
    2:  query.Append("SELECT Path, Title, Description, ContentType FROM SCOPE() ");
    3:  query.Append("WHERE \"SCOPE\"='All Sites' AND ContentType = 'Incident Task' ");
    4:  query.Append("ORDER BY Path ASC");
    6:  FullTextSqlQuery sqlQuery = new FullTextSqlQuery(ServerContext.Current)
    7:                                  {
    8:                                      QueryText = query.ToString(),
    9:                                      ResultTypes = ResultType.RelevantResults
   10:                                  };
   12:  ResultTableCollection queryResults = sqlQuery.Execute();
   13:  DataTable resultsTable = new DataTable();
   14:  resultsTable.Load(queryResults[ResultType.RelevantResults]);
   16:  var results = from DataRow datarow in resultsTable.Rows
   17:                select new
   18:                {
   19:                    Path = datarow[0] == DBNull.Value ? string.Empty : (string)datarow[0],
   20:                    Title = datarow[1] == DBNull.Value ? string.Empty : (string)datarow[1],
   21:                    Description = datarow[2] == DBNull.Value ?string.Empty : (string)datarow[2]
   22:                };
   24:  this.view.Data = results.ToList();


In this drop, we’ve looked at some caching scenario’s. Apart from the normal ASP.Net caching guidance, there are several specific things you could do with regards to caching in sharepoint.

Caching Business Data Catalog Data

The Business Data Catalog (BDC) is a way of exposing data from several data sources (for example databases or webservices) to sharepoint. Then, this data can be queried using out of the box webparts or indexed using Sharepoint Search. This is a very powerful concept. However, the BDC doesn’t support caching.

From the perspective of a webpart, the BDC is a datasource. It’s usually recommended to encapsulate data access logic in separate classes, for example by using the repository pattern.  So what we’ve done is to encapsulate the calls to the BDC in a repository. Now this becomes a handy and centralized place to implement caching.

Note. All of your custom webparts are using the repository, but of course, the out of the box webparts are still calling the BDC directly.

Caching published pages

We’re also demonstrating how to cache published pages.  This is done through configuration and has to be turned on in a feature receiver.

Using Powershell to query sharpoint object model

Once you have deployed your application, with stuff like webparts and workflows in it, people will start using them in production. They’ll take the sharepoint designer and start building the functionality they need. But now if you want to upgrade your functionality, they run the risk of loosing their customizations.

Unfortunately, there is no way to package those customizations that are made in a production environment. But there are tools available to at least detect for example where your webparts are being used. What we’re demonstrating in this drop is how to do the same for workflow assiciations.


I hope you find what we’re demonstrating in our RI useful. In the next couple of drops, we’re going to demonstrate how to do stuff like logging and exception handling, so keep tuned!

As always, any feedback is welcome :)