Getting the Weather Forecast Using SharePoint Workflows

Introduction

I already wrote a post on how to use SharePoint 2013 workflows. This time, I’ll get back to this topic, with a slightly more interesting (IMO) use case: a recurring workflow that runs once a day and emails users the weather forecast for the next day!

Let’s get started! This will work on both SharePoint 2013 on premises or SharePoint Online.

Weather Forecast

I needed a web service for retrieving the weather forecast that was:

  • Free (of course!)
  • REST (not SOAP!)
  • Using JSON (not XML!)

I ended up using Yahoo’s excellent weather web service. It does all this and a lot more, and I suggest you have a look at it.

image

I had to find out the WOEID for the city I was interested in – Coimbra, Portugal, of course – which was as simple as running this YQL query:

select woeid from geo.places(1) where text="coimbra, pt"

Then, all I had to do was pass the returned value (739672) to the query passed in the query string of the REST web service:

https://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20weather.forecast%20where%20woeid%20%3D%20739672&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys

The result is this:

image

Or, in a formatted way, much easier to read:

{

   "query": {

      "count": 1,

      "created": "2016-05-14T10:13:08Z",

      "lang": "en-US",

      "results": {

         "channel": {

            "units": {

               "distance": "mi",

               "pressure": "in",

               "speed": "mph",

               "temperature": "F"

            },

            "title": "Yahoo! Weather - Coimbra, Coimbra, PT",

            "link": "http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-739672/",

            "description": "Yahoo! Weather for Coimbra, Coimbra, PT",

            "language": "en-us",

            "lastBuildDate": "Sat, 14 May 2016 11:13 AM WEST",

            "ttl": "60",

            "location": {

               "city": "Coimbra",

               "country": "Portugal",

               "region": " Coimbra"

            },

            "wind": {

               "chill": "57",

               "direction": "325",

               "speed": "7"

            },

            "atmosphere": {

               "humidity": "90",

               "pressure": "1004.0",

               "rising": "0",

               "visibility": "16.0"

            },

            "astronomy": {

               "sunrise": "6:17 am",

               "sunset": "8:43 pm"

            },

            "image": {

               "title": "Yahoo! Weather",

               "width": "142",

               "height": "18",

               "link": "http://weather.yahoo.com",

               "url": "http://l.yimg.com/a/i/brand/purplelogo//uh/us/news-wea.gif"

            },

            "item": {

               "title": "Conditions for Coimbra, Coimbra, PT at 10:00 AM WEST",

               "lat": "40.194672",

               "long": "-8.40737",

               "link": "http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-739672/",

               "pubDate": "Sat, 14 May 2016 10:00 AM WEST",

               "condition": {

                  "code": "26",

                  "date": "Sat, 14 May 2016 10:00 AM WEST",

                  "temp": "57",

                  "text": "Cloudy"

               },

               "forecast": [

                  {

                     "code": "39",

                     "date": "14 May 2016",

                     "day": "Sat",

                     "high": "61",

                     "low": "54",

                     "text": "Scattered Showers"

                  },

                  {

                     "code": "28",

                     "date": "15 May 2016",

                     "day": "Sun",

                     "high": "71",

                     "low": "54",

                     "text": "Mostly Cloudy"

                  },

                  {

                     "code": "30",

                     "date": "16 May 2016",

                     "day": "Mon",

                     "high": "76",

                     "low": "52",

                     "text": "Partly Cloudy"

                  },

                  {

                     "code": "28",

                     "date": "17 May 2016",

                     "day": "Tue",

                     "high": "77",

                     "low": "53",

                     "text": "Mostly Cloudy"

                  },

                  {

                     "code": "30",

                     "date": "18 May 2016",

                     "day": "Wed",

                     "high": "78",

                     "low": "52",

                     "text": "Partly Cloudy"

                  },

                  {

                     "code": "30",

                     "date": "19 May 2016",

                     "day": "Thu",

                     "high": "77",

                     "low": "52",

                     "text": "Partly Cloudy"

                  },

                  {

                     "code": "30",

                     "date": "20 May 2016",

                     "day": "Fri",

                     "high": "71",

                     "low": "55",

                     "text": "Partly Cloudy"

                  },

                  {

                     "code": "28",

                     "date": "21 May 2016",

                     "day": "Sat",

                     "high": "72",

                     "low": "55",

                     "text": "Mostly Cloudy"

                  },

                  {

                     "code": "30",

                     "date": "22 May 2016",

                     "day": "Sun",

                     "high": "72",

                     "low": "53",

                     "text": "Partly Cloudy"

                  },

                  {

                     "code": "30",

                     "date": "23 May 2016",

                     "day": "Mon",

                     "high": "75",

                     "low": "52",

                     "text": "Partly Cloudy"

                  }

               ],

               "description": "<![CDATA[<img src=\"http://l.yimg.com/a/i/us/we/52/26.gif\"/>\n<BR />\n<b>Current Conditions:<\/b>\n<BR />Cloudy\n<BR />\n<BR />\n<b>Forecast:<\/b>\n<BR /> Sat - Scattered Showers. High: 61Low: 54\n<BR /> Sun - Mostly Cloudy. High: 71Low: 54\n<BR /> Mon - Partly Cloudy. High: 76Low: 52\n<BR /> Tue - Mostly Cloudy. High: 77Low: 53\n<BR /> Wed - Partly Cloudy. High: 78Low: 52\n<BR />\n<BR />\n<a href=\"http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-739672/\">Full Forecast at Yahoo! Weather<\/a>\n<BR />\n<BR />\n(provided by <a href=\"http://www.weather.com\" >The Weather Channel<\/a>)\n<BR />\n]]>",

               "guid": {

                  "isPermaLink": "false"

               }

            }

         }

      }

   }

}

SharePoint Workflow

Next, I wanted to use this information in a SharePoint workflow. This workflow has to run every day automatically, and this was a challenge, because it is not supported out of the box. But, first things first, let’s see how I designed it the Weather Forecast workflow.

Initialization

Open SharePoint Designer and create a new Site workflow, make sure it is using the SharePoint 2013 settings. I first added a stage called Initialization, where I created a string workflow variable named url, to which I assigned the web service URL:

image

Next, I also created a dictionary variable named requestHeaders, to which I added a string entry called Accept, with value application/json (I don’t think this is necessary, but it is always good to tell a REST web service what we’re expecting):

image

Get Weather Forecast

On a new stage named Get Weather Forecast, I add the call to the web service itself, by adding a Call HTTP Web Service action, pointing to the contents of the url variable:

image

After I do that, I access the properties of Call HTTP Web Service action and I set the request headers as coming from the requestHeaders variable I just created:

image

Make sure you set the response to go to a new dictionary variable, responseContent. The rest is really irrelevant, but if you run into problems, it may be useful to look at the responseCode (string) and responseHeaders (dictionary) variables.

Process Response

I then added a new stage named Process Response, where I added an action of type Get an Item from a Dictionary, from which I extracted value query/results/channel/item/forecast(1)/text from the responseContent variable, which contains the result of the web service invocation, into a new string variable called result:

image

This needs some explanation. The value I’m extracting from dictionary is similar to a JSONPath – the JavaScript dotted-path to traverse an object – with two differences:

  • Instead of dots (.), we use slashes (/)
  • Instead of square brackets ([]) for indexed properties, we use parenthesis

The path we are extracting is so that we can jump directly to the textual description of the forecast for the next day, according to the schema that the Yahoo web service returns. If you use another one, you will need to change this path, of course.

Send Email

The next stage I added is called Send Email, and it’s where I send the weather forecast to the interested users. In it, I add a single action of type Send an Email,

image

In the To field, we should use a group instead of explicitly named users, something like “Weather Forecast Users”. In the body of the email, I have a mix of text and the result variable, which, if you remember, contains the portion of the web service response that has the textual description of the forecast.

Sleep for a Day

The last stage I’m adding is called Sleep for a Day, and, again, only has a single activity, Pause for Duration, configured to sleep for 1 day.


Wrap Up

This is what the resulting workflow looks like:

imageIn each stage, we need to add a transition to the next one, except in Sleep for a Day, on which the transition is going to Initialization. So, I have the following stages and transitions:

Stage Transition
Initialization Get Weather Forecast
Get Weather Forecast Process Response
Process Response Send Email
Send Email Sleep for a Day
Sleep for a Day Initialization

As for variables, I have:

image

Starting the Workflow

Now, all we have to do is, after publishing the workflow, is starting it. We do that through Site contentsSite workflows, normally available at URL /_layouts/15/workflow.aspx, and clicking the Weather Forecast link.

image

This workflow, it should be noted, shouldn’t stop, because after it reaches the final stage, it jumps to the first one, don’t forget about it.

Debugging the Workflow

The debugging experience is not great. What you can do is log stuff to the workflow history, using a Log to History List action, but be warned that you cannot log large contents: for example, logging the result of the web service call won’t work. What you can do is see where the workflow stopped and see if any exception was thrown, by clicking at the Status field of the running or completed workflow and inspecting the messages produced:

image

You also have the Workflow Health page (/_layouts/15/WorkflowServiceHealth.aspx), on which you can also see some information and stop running workflows that have gone rogue:

image

It is also worth pointing out that, unless you configured it otherwise, the output history for the workflows is stored in a hidden list called Workflow History (/Lists/Workflow%20History/AllItems.aspx):

image

Conclusion

SharePoint offers some mechanisms for integrating outside data, in the form of REST web services returning JSON, into workflows. While more complex scenarios require more advanced techniques and APIs (Business Connectivity Services, for example, or custom workflow actions), it is still possible to do some interesting stuff with what is available out of the box. Variations to this example might include conditional checks, like, only sending a warning email if the weather forecast is not good, or store the results for the next days, etc. I leave that as an exercise to you, dear reader! Smile In the meantime, do let me know how it goes!

SharePoint Pitfalls: Master Pages in Page Layouts

When you deploy a page layout, you may want to add your own master page as well. I find it useful to add my own custom master pages and reference them directly, in the same or a dependent feature. You might be surprised, however, that it doesn’t work exactly how you’d expect!

The issue is, page layouts will ignore silently anything in the MasterPageFile attribute if it isn’t one of the standard tokens for the system or custom master pages. ~masterurl/default.master and ~masterurl/custom.master. The solution is to have a code behind class and specify the master page in the OnPreInit method (anywhere else won’t work):

protected override void OnPreInit(EventArgs e)

{

    base.OnPreInit(e);

 

    this.MasterPageFile = "~site/_catalogs/masterpage/CustomMasterPage.master":

}

SharePoint Pitfalls Index

This page will list all of my posts dedicated to SharePoint pitfalls. It will be updated regularly.

  1. Creating a Visual Studio Project Without SharePoint Locally Installed
  2. Save Publishing Site as Template Option Missing
  3. Publishing Pages in Document Libraries Other Than Pages

SharePoint Pitfalls: Publishing Pages in Document Libraries Other Than Pages

This one is a classic: the SharePoint Publishing feature in a Publishing Site creates a document library by the name of Pages; this is where you can store your publishing pages.

A common request is to have more of these document libraries, that is because we cannot create publishing pages anywhere else. The problem is, it is unsupported, God knows why!

What you can do is rename the document library to something else. SharePoint will look for the library id in the site’s property bag, under __PagesListId, so only need to update this value accordingly:

web.AllProperties["__PagesListId"] = newDocLibId.ToString();

web.Update();

Now, there are some solutions over the Internet that claim to go around this limitation, but none of them is supported, so proceed with care!

SharePoint Pitfalls: Save Publishing Site as Template Option Missing

If you want to save a publishing site as a template, so that it can be used to create other sites, you may find it surprising that the option is missing from the site settings page:

image

I don’t know exactly why, but publishing sites hide this option, however, it’s not difficult to get around it: just navigate to /_layouts/15/savetmpl.aspx. But, wait, what if you get this?

image

Easy, easy. Open your site in SharePoint Designer, click Site Options:

image

and change the value of property SaveSiteAsTemplateEnabled from false to true:

image

And now you will be able to access savetmpl.aspx and save your site.

SharePoint Pitfalls: Creating a Visual Studio Project Without SharePoint Locally Installed

This is the first on a (huge) collection of posts on SharePoint pitfalls. Hope you enjoy, and, please, do send me your feedback!

If you do not have SharePoint locally installed, Visual Studio will not let you create a SharePoint project:

sp-not-installed

Fortunately, it is easy to go around this in two simple steps:

  1. RegistryIn the Registry, add the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\15.0\SharePoint

    In it, add two string values: Location=C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ and SharePoint=Installed

  2. Global Assembly CacheCopy the following assemblies from a machine where SharePoint is installed (C:\Windows\Microsoft.NET\Assembly\GAC_32) into your development machine and add them to the GAC (gacutil):
    • Microsoft.SharePoint.dll
    • Microsoft.SharePoint.Client.dll
    • Microsoft.SharePoint.Client.Publishing.dll
    • Microsoft.SharePoint.Client.Runtime.dll
    • Microsoft.SharePoint.Client.ServerRuntime.dll
    • Microsoft.SharePoint.Library.dll
    • Microsoft.SharePoint.Linq.dll
    • Microsoft.SharePoint.Portal.dll
    • Microsoft.SharePoint.Publishing.dll

    Of course, you may need others as well.

Enjoy!

SharePoint Reference Document Updated

Just updated my SharePoint reference document Excel (references: here and here) with the default content placeholders.

It now contains:

  • Fields: all the built-in SharePoint field types;
  • Content types;
  • List templates;
  • Site templates;
  • SPDataSource returned fields;
  • List fields;
  • Document library fields;
  • Search content classes;
  • Content placeholders in default master pages.

If you have any corrections or if I missed something, please let me know!

SharePoint Filter Web Part

SharePoint includes a couple of useful web parts that can be used to filter other web parts. The problem is, they are only available in the Enterprise edition. It is easy, however, to build our own filters.

The key interface here is ITransformableFilterValues; it defines the contract for passing one or more values into another web part for the purpose of filtering it. Another useful interface is IDefaultFilterValue, which, guess what, defines a default value to be returned in case no one exists to be returned. Let’s see an example that retrieves values from the query string:

public class QueryStringFilter : WebPart, ITransformableFilterValues, IDefaultFilterValue

{

    public QueryStringFilter()

    {

        this.ChromeType = PartChromeType.None;

        this.Hidden = true;

        this.MultiValueHandling = MultiValueHandling.First;

        this.MultiValueSeparator = String.Empty;

    }

 

    [WebDisplayName("Default Value")]

    [WebPartStorage(Storage.Shared)]

    [WebBrowsable(true)]

    [FriendlyName("Default Value")]

    [DefaultValue("")]

    [Description("")]

    [Personalizable(PersonalizationScope.Shared)]

    public String DefaultValue { get; set; }

 

    [WebDisplayName("Allow All Value")]

    [WebPartStorage(Storage.Shared)]

    [WebBrowsable(true)]

    [FriendlyName("Allow All Value")]

    [Personalizable(PersonalizationScope.Shared)]

    [DefaultValue(false)]

    [Description("")]

    public Boolean AllowAllValue { get; set; }

 

    [WebDisplayName("Allow Empty Value")]

    [WebPartStorage(Storage.Shared)]

    [WebBrowsable(true)]

    [FriendlyName("Allow Empty Value")]

    [Personalizable(PersonalizationScope.Shared)]

    [DefaultValue(false)]

    [Description("")]

    public Boolean AllowEmptyValue { get; set; }

 

    [WebDisplayName("Allow Multiple Values")]

    [WebPartStorage(Storage.Shared)]

    [WebBrowsable(true)]

    [FriendlyName("Allow Multiple Values")]

    [Personalizable(PersonalizationScope.Shared)]

    [DefaultValue(false)]

    [Description("")]

    public Boolean AllowMultipleValues { get; set; }

 

    [WebDisplayName("Query String Parameter Name")]

    [WebPartStorage(Storage.Shared)]

    [WebBrowsable(true)]

    [FriendlyName("Query String Parameter Name")]

    [Personalizable(PersonalizationScope.Shared)]

    [DefaultValue("")]

    [Description("")]

    public String ParameterName { get; set; }

 

    [WebDisplayName("Multi Value Handling")]

    [WebPartStorage(Storage.Shared)]

    [WebBrowsable(true)]

    [FriendlyName("Multi Value Handling")]

    [Personalizable(PersonalizationScope.Shared)]

    [DefaultValue(MultiValueHandling.First)]

    [Description("")]

    public MultiValueHandling MultiValueHandling { get; set; }

 

    [WebDisplayName("Multi Value Separator")]

    [WebPartStorage(Storage.Shared)]

    [WebBrowsable(true)]

    [FriendlyName("Multi Value Separator")]

    [Personalizable(PersonalizationScope.Shared)]

    [DefaultValue("")]

    [Description("")]

    public String MultiValueSeparator { get; set; }

 

    ReadOnlyCollection<String> ITransformableFilterValues.ParameterValues

    {

        get

        {

            var list = new List<String>();

 

            if (String.IsNullOrWhiteSpace(this.ParameterName) == false)

            {

                if (this.AllowMultipleValues == false)

                {

                    list.Add(this.Context.Request.QueryString[this.ParameterName]);

                }

                else

                {

                    var index =

                        Array.IndexOf(

                            this.Context.Request.QueryString.AllKeys.Select(x => (x ?? String.Empty).ToLowerInvariant()).ToArray(),

                            this.ParameterName.ToLowerInvariant());

 

                    if (index >= 0)

                    {

                        if (this.MultiValueHandling == MultiValueHandling.First)

                        {

                            list.Add(this.Context.Request.QueryString.GetValues(index).First());

                        }

                        else if (this.MultiValueHandling == MultiValueHandling.All)

                        {

                            list.AddRange(this.Context.Request.QueryString.GetValues(index));

                        }

                        else

                        {

                            list.Add(String.Join(this.MultiValueSeparator, this.Context.Request.QueryString.GetValues(index)));

                        }

                    }

                }

 

                if (list.Count == 0)

                {

                    if (String.IsNullOrWhiteSpace(this.DefaultValue) == false)

                    {

                        list.Add(this.DefaultValue);

                    }

                    else

                    {

                        if (this.AllowAllValue == false)

                        {

                            if (this.AllowEmptyValue == true)

                            {

                                list.Add(String.Empty);

                            }

                        }

                        else

                        {

                            list.Add(null);

                        }

                    }

                }

            }

 

            return new ReadOnlyCollection<String>(list);

        }

    }

 

    [ConnectionProvider("Query String Filter", "ITransformableFilterValues", AllowsMultipleConnections = true)]

    public ITransformableFilterValues GetFilterValues()

    {

        return this;

    }

}

There are a couple of public properties:

  • ParameterName: the query string key whose value is to be returned;
  • DefaultValue: the default value to be returned, in case the query string does not contain a value for the ParameterName key;
  • AllowAllValue: whether to allow the all (null) value;
  • AllowEmptyValue: whether to allow the empty (“”) value;
  • AllowMultipleValues: whether to allow multiple values or not;
  • MultiValueHandling: what to do if multiple values are found for the ParameterName key: select the first only, return all or combine them all into one;
  • MultiValueSeparator: the separator for combining multiple values.

The web part is invisible and will only show the chrome when the page is in edit mode. After you add it to a page, you can add a connection of type filter to another web part and select the field on the other web part that you want to filter by. The actual

ITransformableFilterValues implementation is returned by the GetFilterValues method, which is marked with the ASP.NET ConnectionProvider attribute so as to make it a connection provider, and can feed several other web parts. The logic inside ParameterValues is a bit tricky because of the AllowAllValue and AllowEmptyValue properties but I think you’ll have no problems following it.

You would normally apply a filter using the browser interface, but you can also do it in markup:

<WebPartPages:SPProxyWebPartManager runat="server">

    <SPWebPartConnections>

        <WebPartPages:SPWebPartConnection ConsumerConnectionPointID="DFWP Filter Consumer ID" ConsumerID="listViewWebPart" ProviderConnectionPointID="ITransformableFilterValues" ProviderID="queryStringFilterWebPart">

            <WebPartPages:TransformableFilterValuesToParametersTransformer ConsumerFieldNames="LinkTitle" ProviderFieldNames="TaskName"/>

        </WebPartPages:SPWebPartConnection>

    </SPWebPartConnections>

</WebPartPages:SPProxyWebPartManager>

In this example, I am binding the LinkTitle field of a list view web part to the TaskName query string parameter provided by the QueryStringFilter web part. If the query string contains a TaskName parameter that matches the list view’s LinkTitle, it will show these records.

SharePoint XSLT Web Part

After my previous post on XSLT processing, what else could follow? Of course, an XSLT web part for SharePoint! Smile

Here I want to solve a couple of problems:

  • Allow the usage of XSLT 2.0;
  • Have a more flexible parameter passing mechanism than <ParameterBindings>;
  • Make the XSLT extension mechanism (parameters, functions) more usable.

Similar to XsltListViewWebPart and the others, this web part will query SharePoint and return the results processed by a XSLT style sheet. I am going to built on top of the classes introduced in the last post. Here is the SPCustomXsltWebPart (please, do give it a better name…):

public enum XsltVersion

{

    Xslt1 = 1,

    Xslt2 = 2

}

 

public class SPCustomXsltWebPart : WebPart, IWebPartTable

{

    private static readonly Regex parametersRegex = new Regex(@"@(\w+)\b", RegexOptions.IgnoreCase);

 

    [NonSerialized]

    private DataTable table;

    [NonSerialized]

    private IOrderedDictionary parameters;

 

    public SPCustomXsltWebPart()

    {

        this.AddDefaultExtensions = true;

        this.RowLimit = Int32.MaxValue;

        this.Parameters = new ParameterCollection();

        this.XsltVersion = XsltVersion.Xslt1;

    }

 

    [Category("XSLT")]

    [Personalizable(PersonalizationScope.Shared)]

    [WebBrowsable(true)]

    [WebDisplayName("XSL Version")]

    [WebDescription("The XSLT version")]

    [DefaultValue(XsltVersion.Xslt1)]

    public XsltVersion XsltVersion { get; set; }

 

    [Category("XSLT")]

    [Personalizable(PersonalizationScope.Shared)]

    [WebBrowsable(true)]

    [WebDisplayName("XSL Link")]

    [WebDescription("The URL of a file containing XSLT")]

    [DefaultValue("")]

    public String XslLink { get; set; }

 

    [Category("XSLT")]

    [Personalizable(PersonalizationScope.Shared)]

    [WebBrowsable(true)]

    [WebDisplayName("XSL")]

    [WebDescription("The XSLT content")]

    [DefaultValue("")]

    [PersistenceMode(PersistenceMode.InnerProperty)]

    public String Xsl { get; set; }

 

    [Category("Query")]

    [Personalizable(PersonalizationScope.Shared)]

    [WebBrowsable(true)]

    [WebDisplayName("Query")]

    [WebDescription("The CAML query")]

    [DefaultValue("")]

    [PersistenceMode(PersistenceMode.InnerProperty)]

    public String Query { get; set; }

 

    [Category("Query")]

    [Personalizable(PersonalizationScope.Shared)]

    [WebBrowsable(true)]

    [WebDisplayName("Row Limit")]

    [WebDescription("The row limit")]

    [DefaultValue(Int32.MaxValue)]

    public UInt32 RowLimit { get; set; }

 

    [Category("Query")]

    [Personalizable(PersonalizationScope.Shared)]

    [WebBrowsable(true)]

    [WebDisplayName("Lists")]

    [WebDescription("The target lists")]

    [DefaultValue("")]

    public String Lists { get; set; }

 

    [Category("Query")]

    [Personalizable(PersonalizationScope.Shared)]

    [WebBrowsable(true)]

    [WebDisplayName("Webs")]

    [WebDescription("The target webs")]

    [DefaultValue("")]

    public String Webs { get; set; }

 

    [Category("Query")]

    [Personalizable(PersonalizationScope.Shared)]

    [WebBrowsable(true)]

    [WebDisplayName("View Fields")]

    [WebDescription("The view fields")]

    [DefaultValue("")]

    public String ViewFields { get; set; }

 

    [Category("Query")]

    [Personalizable(PersonalizationScope.Shared)]

    [WebBrowsable(true)]

    [WebDisplayName("Query Throttle Mode")]

    [WebDescription("The query throttle mode")]

    [DefaultValue(SPQueryThrottleOption.Default)]

    public SPQueryThrottleOption QueryThrottleMode { get; set; }

 

    [Category("General")]

    [Personalizable(PersonalizationScope.Shared)]

    [WebBrowsable(true)]

    [WebDisplayName("Add Default Extensions")]

    [WebDescription("Adds the default extensions")]

    [DefaultValue(true)]

    public Boolean AddDefaultExtensions { get; set; }

 

    [PersistenceModeAttribute(PersistenceMode.InnerProperty)]

    public ParameterCollection Parameters { get; private set; }

 

    public event EventHandler<XsltExtensionEventArgs> XsltExtension;

 

    protected XsltProvider XsltProvider

    {

        get

        {

            return this.XsltVersion == XsltVersion.Xslt1 ? DefaultXsltProvider.Instance : SaxonXsltProvider.Instance;

        }

    }

    protected virtual void OnXsltExtension(XsltExtensionEventArgs e)

    {

        var handler = this.XsltExtension;

 

        if (handler != null)

        {

            handler(this, e);

        }

    }

 

    protected override void CreateChildControls()

    {

        var xml = this.GetXml();

        var html = this.Render(xml);

        var literal = new LiteralControl(html);

 

        this.Controls.Add(literal);

 

        base.CreateChildControls();

    }

 

    private String GetXslt()

    {

        var xslt = String.Empty;

 

        if (String.IsNullOrWhiteSpace(this.Xsl) == false)

        {

            xslt = this.Xsl;

        }

        else if (String.IsNullOrWhiteSpace(this.XslLink) == false)

        {

            var doc = new XmlDocument();

            doc.Load(this.XslLink);

 

            xslt = doc.InnerXml;

        }

 

        return xslt;

    }

 

    private DataTable GetTable()

    {

        if (this.table == null)

        {

            var query = new SPSiteDataQuery();

            query.Query = this.ApplyParameters(this.Query);

            query.QueryThrottleMode = this.QueryThrottleMode;

            query.RowLimit = this.RowLimit;

            query.Lists = this.Lists;

            query.Webs = this.Webs;

            query.ViewFields = this.ViewFields;

 

            this.table = SPContext.Current.Site.RootWeb.GetSiteData(query);

            this.table.TableName = "Row";

 

            foreach (var column in this.table.Columns.OfType<DataColumn>())

            {

                column.ColumnMapping = MappingType.Attribute;

            }

        }

 

        return this.table;

    }

 

    private String ApplyParameters(String value)

    {

        var parameters = this.GetParameters();

 

        value = parametersRegex.Replace(value, x => this.GetFormattedValue(parameters[x.Value.Substring(1)]));

 

        return value;

    }

 

    private String GetFormattedValue(Object value)

    {

        if (value == null)

        {

            return String.Empty;

        }

 

        if (value is Enum)

        {

            return ((Int32)value).ToString();

        }

 

        if (value is DateTime)

        {

            return SPUtility.CreateISO8601DateTimeFromSystemDateTime((DateTime)value);

        }

 

        if (value is IFormattable)

        {

            return (value as IFormattable).ToString(String.Empty, CultureInfo.InvariantCulture);

        }

 

        return value.ToString();

    }

 

    private IOrderedDictionary GetParameters()

    {

        if (this.parameters == null)

        {

            this.parameters = this.Parameters.GetValues(this.Context, this);

        }

 

        return this.parameters;

    }

 

    private String GetXml()

    {

        var sb = new StringBuilder();

        var table = this.GetTable();

 

        using (var writer = new StringWriter(sb))

        {

            table.WriteXml(writer);

        }

 

        sb

            .Replace("<DocumentElement>", "<dsQueryResponse RowLimit='" + this.RowLimit + "'><Rows>")

            .Replace("</DocumentElement>", "</Rows></dsQueryResponse>");

 

        return sb.ToString();

    }

 

    private String ApplyXslt(String xml, String xslt, XsltExtensionEventArgs args)

    {

        return this.XsltProvider.Transform(xml, xslt, args);

    }

 

    private String Render(String xml)

    {

        if (String.IsNullOrWhiteSpace(xml) == true)

        {

            return String.Empty;

        }

 

        var xslt = this.GetXslt();

 

        if (String.IsNullOrWhiteSpace(xslt) == true)

        {

            return String.Empty;

        }

 

        var extensions = new XsltExtensionEventArgs();

 

        this.OnXsltExtension(extensions);

 

        if (this.AddDefaultExtensions == true)

        {

            var defaultExtensions = Activator.CreateInstance(typeof(Microsoft.SharePoint.WebPartPages.DataFormWebPart).Assembly.GetType("Microsoft.SharePoint.WebPartPages.DataFormDdwRuntime"));

            extensions.AddExtension("http://schemas.microsoft.com/WebParts/v2/DataView/runtime", defaultExtensions);

        }

 

        foreach (var ext in extensions.Extensions)

        {

            extensions.AddExtension(ext.Key, ext.Value);

        }

 

        var parameters = this.GetParameters();

 

        foreach (var key in parameters.Keys.OfType<String>())

        {

            extensions.AddParameter(key, String.Empty, parameters[key].ToString());

        }

 

        foreach (var param in extensions.Parameters)

        {

            extensions.AddParameter(param.Name, param.NamespaceUri, param.Parameter.ToString());

        }

 

        return this.ApplyXslt(xml, xslt, extensions);

    }

 

    void IWebPartTable.GetTableData(TableCallback callback)

    {

        callback(this.GetTable().DefaultView);

    }

 

    PropertyDescriptorCollection IWebPartTable.Schema

    {

        get { return TypeDescriptor.GetProperties(this.GetTable().DefaultView); }

    }

}

This class extends the basic WebPart class and adds a couple of properties:

  • XsltVersion: the XSLT version to use, which will result in either my DefaultXsltProvider or the SaxonXsltProvider being used;
  • XslLink: the URL of a file containing XSLT;
  • Xsl: in case you prefer to have the XSLT inline;
  • Query: a CAML query;
  • Webs: the webs to query;
  • Lists: the lists to query;
  • ViewFields: the fields to return;
  • RowLimit: maximum number of rows to return;
  • QueryThrottleMode: the query throttle mode;
  • AddDefaultExtensions: whether to add the default extension functions and parameters;
  • Parameters: a standard collection of ASP.NET parameter controls.

The web part uses SPSiteDataQuery to execute a CAML query. Before the query is executed, any parameters it may have, in the form @ParameterName,  are replaced by actual values evaluated from the Parameters collection. This gives some flexibility to the queries, because, not only ASP.NET includes parameters for all the common sources, it’s very easy to add new ones. The web part knows how to format strings, enumerations, DateTime objects and in general any object implementing IFormattable; if you wish, you can extend it to support other types, but I don’t think it will be necessary.

An example usage:

<web:SPCustomXsltWebPart runat="server" XslLink="~/Style.xslt">

    <Query>

        <Where><Eq><FieldRef Name='Id'/><Value Type='Number'>@Id</Value></Eq></Where>

    </Query>

    <Parameters>

        <asp:QueryStringParameter Name="Id" QueryStringField="Id" Type="Int32" />

    </Parameters>

</web:SPCustomXsltWebPart>

Notice that one of the Xsl or the XslLink properties must be set, and the same goes for the Query.

Hope you find this useful, and let me know how it works!

     

    Querying SharePoint

    Introduction

    SharePoint, being a content management system, of course, offers a couple of ways to query its contents programmatically. Here we will explore some of these options.

    Web Parts

    First, of course, there are the web parts. These allow us to configure queries visually on a page. The most important web parts are:

    ContentByQueryWebPart: use this for simple queries that do not return much contents, on a single site collection. Can be used in SharePoint Online. Can only be customized through XSLT.

    ContentBySearchWebPart (introduced in SharePoint 2013): more powerful, but does not exist in SharePoint Online. Can handle more complex queries that can span multiple site collections and multiple levels of sorting. Can only be customized through HTML and JavaScript templates. Results can be refined.

    XsltListViewWebPart/DataFormWebPart: can be used to view of a specific list. The display can be totally configured. XsltListViewWebPart is a bit more powerful.

    ListViewByQuery: requires that you pass an SPList and a SPQuery instance. Can only display pre-defined views.

    APIs

    There are several APIs for querying, either directly or using the search index.

    SPQuery: can only be applied to a single list:

    var query = new SPQuery();

    query.Query = "<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>Bla Bla</Value></Eq></Where>";

     

    var list = site.RootWeb.Lists["Some List"];

     

    var table = list.GetItems(query).GetDataTable();

    SPSiteDataQuery: can be applied to several lists across a site collection. Has some issues, for example, won’t return values for multi-value fields:

    var query = new SPSiteDataQuery();

    query.Lists = "<List Name='Tasks'/>";

    query.Query = "<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>Bla Bla</Value></Eq></Where>";

    query.Webs = "<Web Scope='SiteCollection'/>";

     

    var table = site.RootWeb.GetSiteData(query);

    KeywordQuery: uses the search index to search for keywords, so it requires that contents are indexed beforehand, and the search service is functioning:

    using (var query = new KeywordQuery(site))

    {

        query.QueryText = "Bla Bla";

        query.ResultsProvider = SearchProvider.Default;

     

        var searchExecutor = new SearchExecutor();

     

        var resultTableCollection = searchExecutor.ExecuteQuery(query);

     

        var searchResult = resultTableCollection.Filter("TableType", KnownTableTypes.RelevantResults).Single();

     

        var table = new DataTable();

        table.TableName = "Result";

        table.Load(searchResult, LoadOption.OverwriteChanges);

    }

    FullTextSqlQuery: uses SharePoint Search SQL to execute queries, which makes it generally more powerful than KeywordQuery:

    using (var query = new FullTextSqlQuery(site))

    {

        query.QueryText = "SELECT * FROM scope() WHERE Title = 'Teste'";

        query.ResultsProvider = SearchProvider.Default;

     

        var searchExecutor = new SearchExecutor();

     

        var resultTableCollection = searchExecutor.ExecuteQuery(query);

     

        var searchResult = resultTableCollection.Filter("TableType", KnownTableTypes.RelevantResults).Single();

     

        var table = new DataTable();

        table.TableName = "Result";

        table.Load(searchResult, LoadOption.OverwriteChanges);

    }

    CrossListQueryInfo and CrossListQueryCache: performs queries in a single site collection but multiple sites, with optional audience targeting. CrossListQueryCache caches the results for a period of time:

    var crossListQueryInfo = new CrossListQueryInfo();

    crossListQueryInfo.Query = "<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>Bla Bla</Value></Eq></Where>";

    crossListQueryInfo.Lists = "<List Name='Some List'/>";

    crossListQueryInfo.Webs = "<Webs Scope='SiteCollection' />";

    crossListQueryInfo.UseCache = true;

     

    var crossListQueryCache = new CrossListQueryCache(crossListQueryInfo);

     

    var table = crossListQueryCache.GetSiteData(site.RootWeb);

    Client KeywordQuery: this is the client counterpart to KeywordQuery. It uses the SharePoint Client Components, or Client Side Object Model (CSOM), which means, it accesses SharePoint through its web services. It is basically similar to the server KeywordQuery, but can be used from a remote machine:

    var keywordQuery = new Microsoft.SharePoint.Client.Search.Query.KeywordQuery(ctx);

    keywordQuery.QueryText = "SharePoint";

     

    var searchExecutor = new Microsoft.SharePoint.Client.Search.Query.SearchExecutor(ctx);

     

    var results = searchExecutor.ExecuteQuery(keywordQuery);

     

    ctx.ExecuteQuery();

     

    var searchResult = results.Value.Where(x => x.TableType == KnownTableTypes.RelevantResults.ToString()).Single();

    Web Services

    Search.asmx: SOAP web service that takes a SharePoint SQL query. Part of the SharePoint Foundation 2010 Web Services, now somewhat obsolete.

    Lists.asmx: Another SOAP web service that can return and update items in a list.

    ListData.svc: WCF Data Service REST/OData that can be used for querying (including OData queries) or modifying contents of lists.

    SharePoint 2013 REST Services: new REST/OData web services introduced in SharePoint 2013. Includes _api/web, _api/search, _api/web/lists, for searches, web or list operations.

    SharePoint Foundation RPC Protocol

    The SharePoint Foundation RPC Protocol, now obsolete, this allowed querying, exporting contents and performing a number of other operations through the OWSSVR.DLL handler. Although almost unused nowadays, still offers the only out of the box way to, for example, export a list in XML format.

    JavaScript

    In SharePoint 2010 the JavaScript Side Object Model (JSOM) was introduced and in version 2013 it was enhanced. It is now possible to do anything that the SharePoint Client API allows.

    Conclusion

    You see, lots of ways to get contents from SharePoint, as usual. Make sure you chose the one that best suits your needs.

    References

    SharePoint 2013 .NET Server, CSOM, JSOM, and REST API index

    When to use the Content Query Web Part or the Content Search Web Part in SharePoint

    Choose the right API set in SharePoint 2013

    Use OData query operations in SharePoint REST requests

    SharePoint Server 2013 Client Components SDK

    SharePoint Search SQL Syntax Reference