A voyage with the SharePoint Connector

Last week I had to set up a demo to a potential partner to show how EPiServer can be used to pull information together within an Enterprise and expose it in different ways. After giving it some thought, I decided that the best way to show this would be to grab the latest version of the SharePoint Connector and hook it up – after all, many if not most Enterprises now have SharePoint deployed in some way. Of course, me being me, I was never going to want to do this the easy way and so I decided to really funk up my demo. More on that a little bit later.

I was only interested in two parts of the SharePoint Connector, although there is much more. I just wanted to be able to share documents between EPiServer and SharePoint and also expose some EPiServer content in SharePoint. The Connector also supports exposing SharePoint list data through EPiServer using a Content Channel, but I wasn’t bothered too much about that and so I haven’t covered it in this blog post. Is is covered elsewhere though, for example by Mattias Nordgren in this article.

So, first, just to recap on my experiences getting the SharePoint connector working. To use this connector I had to have a SharePoint environment running somewhere, and so the easiest way to do that was with a Virtual PC. I won’t bore you with the detail of getting that running (there’s umpteen blogs on the web about it) but in summary I set up the following:

Loopback adapter on the host with static IP

Virtual PC with 1Gb of RAM and 20Gb expanding VHD

- Windows Server 2008

- SharePoint Server 2007 (MOSS) with SP1 ‘streamlined’ in (it won’t install on Win 2008 unless you streamline the SP1 into MOSS RTM or download the all-in-one MOSS+SP1 package)

- Two network adapters – one bound to the host’s main network adapter and one to the loopback adapter

Onto this Virtual PC I set up a single team site to be used for the demo. Be persistent in getting all this set up – MOSS is a bit of a pain to configure and you may find that you are battling timeouts and things in getting it all set up. It is a bit of a resource hog and when running in a VPC then even on a laptop like mine (dual-core 2.5Ghz with 4Gb of RAM) it’s painful to use. Keep plugging away and you should succeed.

On the host PC I set up a standard run-of-the-mill EPiServer 5 RC2 SP1 installation with both Public and Demo templates (well, they are prettier). I then installed the EPiServer SharePoint Connector and ran the wizard on my new site. I connected it to my new SharePoint team site and completed the wizard.

Exposing a document library in EPiServer

At this point you can see the first part of the connectivity working – the ability for EPiServer to be able to browse a SharePoint document library. On my SharePoint team site I created a new document library called ‘Demo Document Library’, and then logged onto my EPiServer site to browse it:

You can upload documents both in SharePoint or EPiServer and it’s a 2-way communication, so the documents are immediately visible on both ends of the connection:

Exposing EPiServer content in SharePoint

The next stage is to get EPiServer content exposed in SharePoint. This is a little trickier. You need to follow the steps outlined in the SharePoint connector ZIP file to deploy the .wsp file to SharePoint. For some reason when I copy-pasted the ‘deploysolution’ line from the installation instructions, it wouldn’t run and kept saying the usage was wrong. Bizarrely, when I actually typed the line in by hand it worked fine. Maybe there’s a dodgy character or something when you copy-paste, so be aware of that gotcha if it gets you :)

Once the SharePoint solution is deployed successfully, you can follow the rest of the instructions to set up the web parts and make them available. Once added to your Team Site, you can set it up to look something like this:

OK, so it’s not that pretty and I haven’t set up things like web part titles, and the images don’t show, but it illustrates how you can pull through EPiServer content into a web part. Note that I have connected up the two web parts so selection in the tree view will populate the page view web part.

So far everything I have talked about is covered by the documentation, and is ‘standard’ functionality. However, as I said at the start of this blog post, nothing in life is ever easy with me (and if it is, I’ll find a way of making it harder). I decided to take this to the next level.

Exposing SQL content in EPiServer

Allan Thraen wrote a great blog post on a custom Virtual Path Provider that used the sample ‘Northwind’ database as it’s sample source. I downloaded his sample code and located the Northwind database for SQL, and installed both. Note that you will possibly only find the Northwind database for SQL 2000. It comes with both a database file and also SQL scripts. I couldn’t get the database to install on SQL 2008 but the scripts worked just fine, so I suggest you use those.

Once I’d set up Allan’s code as he described, the provider worked fine and I could see the custom pages in EPiServer:

This got my demo to a nice point. I could show documents being shared between MOSS and EPiServer, I could show content being pulled from EPiServer to MOSS, and I could show content being pulled from SQL into EPiServer.

Exposing SQL content in SharePoint via EPiServer

There was just one piece of the jigsaw left – la pièce de résistance. And this is to show the Northwind database pulled from SQL into EPiServer and then displayed in the MOSS Web Part. The first thought I had was to just put the page ID of the ‘Northwind’ page into the MOSS Web Part, which seemed at first to work. The Web Part showed the ‘Northwind’ top level and I could expand it to show the items within it. However, as soon as I clicked any of the options, MOSS threw an ‘Unexpected Error’. After turning on debugging and stack tracing I could see that the EPiServer PageList Web Part was throwing an error ‘Input string was not in a correct format.’ To understand the reason for this, we need to take a step back and understand how page links work for custom page providers work in EPiServer.

A page link in EPiServer consists of three parts; the page ID, the work ID and the remote site ID. Only the page ID is mandatory, as the work ID and remote site ID will use defaults. The page ID is numeric. Custom page providers also make use of the remote site ID, which is a string. By setting a value in here, it is then looked up against the page providers in the web.config and if a relevant page provider is located then that is the one used. In Allan’s example, the name ‘NW’ is used for the page provider. This is then pulled through as the remote site ID in page links. When a page link is rendered as a string, then if a remote site ID is present then the default formatter puts in in this structure; page ID, underscore, work ID, underscore, remote site ID. For example, in the screenshot above, the page with page ID of 1 within the Northwind page provider is ‘1__NW’.

Now we can understand why the Web Part was failing. When it renders the tree it simply renders out the page IDs from the page links. That’s fine for rendering it, but when you try to select one of the string-based page IDs it tries to read it as an integer and hence it breaks. (out of interest, the reason you get a incorrect format error is because the code is actually doing a Convert.ToInt32 and it can’t make any sense of the page link with underscores). What this tells us is that the Web Part only supports numeric page IDs, and hence doesn’t support pages coming through from a custom page provider. This is a bit of a limitation and I’m not entirely sure why it’s there. I know that the web parts supplied are just for an example and it would make it more complex for sure, but I think maybe this is something for the next version. I’ll suggest it :)

This limitation applies to both the Page List and Page View web part. In fact, if you try to change the settings for the Web Part and enter a custom page provider ID-based page id then it won’t save it and will give you an error ‘page ID is not specified’ when rendering the web part (although this will be just a ‘soft’ error rather than the full-on error caused before).

Thankfully, this isn’t the end of the story and there is a way to work around it. It is a bit technical and does require some coding, but isn’t overly complex and doesn’t take long. There are two things we need to do – firstly update our web parts to support a full page link and secondly to update our web service calls (I’ll explain why later).

Getting custom page providers to work with the EPiServer Web Parts

The source code for the two web parts is supplied as part of the EPiServer Connector download, and so the first thing to do is to load it into a project and take a look at the code. You will see immediately that the page ID’s are always referred to as integers. There is no concept that they could be a string. That is easy to fix though, and it is as simple as running through all the page ID references and changing it to a string. Make sure you also remove any .ToString method calls on the page ID too. This is because you may get object exceptions if you .ToString a null string. The web part editor panels need to be updated too so that they treat the page ID as a string. I didn’t wrap any format checking around those so in theory you could really mess it up by entering rubbish strings, but I wanted to get it working rather than make it foolproof. That’s for someone else to play with :)

I also noticed that the inheritance from the web part base classes seems to have changed, as it now takes a parameterless constructor. Again, that is easily changed.

The only real coding I had to do was in the WebPartBase where I had to add some code in order that the web part could understand how to separate out the string-based page reference. There might be an EPiServer call to do it, I don’t know, but here’s my quick and dirty method to do it:

   1:  

   2:         private PageReference GetPageRefFromComplex(string pageref)

   3:         {

   4:             PageReference oPageRef = new PageReference();

   5:  

   6:             string [] pagerefparts = pageref.Split('_');

   7:  

   8:             if (pagerefparts.Length > 0)

   9:             {

  10:                 oPageRef.ID = int.Parse(pagerefparts[0]);

  11:             }

  12:  

  13:             if (pagerefparts.Length > 1)

  14:             {

  15:                 if (pagerefparts[1].Length > 0)

  16:                 {

  17:                     oPageRef.WorkID = int.Parse(pagerefparts[1]);

  18:                 }

  19:             }

  20:             

  21:             if (pagerefparts.Length > 2)

  22:             {

  23:                 if (pagerefparts[2].Length > 0)

  24:                 {

  25:                     oPageRef.RemoteSite = pagerefparts[2];

  26:                 }

  27:             }

  28:  

  29:             return oPageRef;

  30:         }

I then call it from both GetChildPages and GetPage a little like this:

   1: PageReference parentPageRef = new PageReference();;

   2:  

   3: if(parentId.IndexOf("_") >= 0)

   4: {

   5:     parentPageRef = GetPageRefFromComplex(parentId);

   6: }

   7: else

   8: {

   9:     parentPageRef.ID = int.Parse(parentId);

  10: }

Now the code is almost ready to compile. There are a couple of maintenance tasks to do first though. Firstly, you will probably need to re-add a reference to the Microsoft SharePoint library. You can find the DLL on your Virtual PC image with SharePoint on. Secondly, the sample code include the public key for signing EPiServer code. This is fine, except that SharePoint will only work with web parts that are strongly signed (that is, fully signed with both public and private key parts). Unless you fancy trying to persuade EPiServer to give you the private key part for their code (not likely!) then I suggest you just replace the EPiServer key with your own signed key. Personally, I was lazy and used a non-password protected combined key pair just to get it signed. You probably wouldn’t do this in a production environment. The only consequence of using our own key is that we’ll need to tell SharePoint that our new web parts are ‘safe’ – more on that in a moment.

Once the web parts are compiled you should be take them and deploy them into the GAC on your SharePoint box. You will notice that they are installed alongside the original ones, with a new Public Key Token:

The new one in the screenshot above starts ‘d27…’. We now need to tell SharePoint that it can use the new web parts. To do this, open the web.config of the web site that you deployed your Team Site to (probably C:\inetpub\wwwroot\wss\VirtualDirectories\80). Find the line that declares the SafeControl for the EPiServer web parts. You will notice that it refers to the Public Key Token. We just need to change it with our new Public Key Token or, I would suggest, copy and comment the existing one and then amend your copy:

   1: <SafeControl Assembly="EpiServer.SharePointWebParts, Version=2.1.0.0, Culture=neutral, PublicKeyToken=8fe83dea738b45b7" Namespace="EpiServer.SharePointWebParts" TypeName="*" Safe="True" />

   2: <!-- <SafeControl Assembly="EpiServer.SharePointWebParts, Version=2.1.0.0, Culture=neutral, PublicKeyToken=d27c8c750c68e499" Namespace="EpiServer.SharePointWebParts" TypeName="*" Safe="True" /> –>

In order that SharePoint can update the web parts, some of the SharePoint system needs to restart. Saving web.config doesn’t seem to do it. You can probably recycle the app pool or something but I like to use sledgehammers to crack nuts, and so I just use iisreset. It works :)

You will now need to remove the old web parts from the page and add the new ones to the web part gallery. You follow exactly the same procedure as before, except that before you add them to the web part gallery, make sure you remove the old ones (edit properties, delete).

You can now add the new web parts to your page and you should find that you no longer get any errors. However, you may well still get some weird behaviour. Notice the pages that supposedly live below ‘Beverages’:

Obviously there’s something funny going on here. The reason for this is because even though the web parts are now working correctly, the web service being used (PageStoreService.asmx) does not support page links with a remote site ID. Using Reflector  shows that the web service actually blanks out the remote site ID when a calls comes in before passing the request on to the EPiServer API to be served. This is probably by design although as before I can’t figure out why. Again, we can work around this though.

We need to create a web service that behaves like PageStoreService but doesn’t throw away the remote site ID. To do this, we can inherit from PageStoreService and then just change the bits we need to. It’s actually easier than you might think. First we create a new ‘web service definition’ in the EPiServer folder structure (C:\Program Files\EPiServer\CMS\5.2.375.133\Application\WebServices). I decided to call my updated web service PageProviderPageStoreService, so I created an .asmx with that name a single line of code:

   1: <%@ WebService language="c#" Codebehind="PageProviderPageStoreService.asmx.cs" Class="EPiServer.PageProviderPageStoreService.PageProviderPageStoreService,EPiServer.Templates.Demo" %>

You will notice that I am using the ‘EPiServer.Templates.Demo’ assembly, because I simply compiled my web service into my demo templates assembly. This isn’t really ideal and you’d probably use a separate class library which can go into the GAC, but I was in a hurry and it works as long as you only call that web service within the right web application :)

Inside the actual implementation of the web service you will need to implement a number of methods. I didn’t bother with all the ‘writing’ methods such as delete and save because they would be a lot of work and it might get fairly risky. More input on that would be appreciated. My skeleton implementation is shown below.

   1: namespace EPiServer.PageProviderPageStoreService

   2: {

   3:     [WebService(Namespace = "http://schemas.episerver.com/WebServices/v5/")]

   4:     public class PageProviderPageStoreService : EPiServer.WebServices.PageStoreService

   5:     {

   6:         [WebMethod(EnableSession = false, BufferResponse = true, Description = "Retrieve page listing.")]

   7:         public new RawPage[] GetChildren(PageReference pageLink, LanguageSelector selector, int startIndex, int maxRows)

   8:         {

   9:             ValidateWebServiceAccess();

  10:             PageDataCollection pages = DataFactory.Instance.GetChildren(pageLink, selector, startIndex, maxRows);

  11:             new FilterAccess().Filter(pages);

  12:             return pages.ToRawPageArray();

  13:         }

  14:  

  15:         [WebMethod(EnableSession = false, BufferResponse = true, Description = "Get a page with default values.")]

  16:         public new RawPage GetDefaultPageData(PageReference parentPageLink, int pageTypeID, LanguageSelector selector, AccessLevel access)

  17:         {

  18:             ValidateWebServiceAccess();

  19:             return DataFactory.Instance.GetDefaultPageData(parentPageLink, pageTypeID, selector).ToRawPage();

  20:         }

  21:  

  22:         [WebMethod(EnableSession = false, BufferResponse = true, Description = "Read a single page.")]

  23:         public new RawPage GetPage(PageReference pageLink, LanguageSelector selector, AccessLevel access)

  24:         {

  25:             ValidateWebServiceAccess();

  26:             return DataFactory.Instance.GetPage(pageLink, selector).ToRawPage();

  27:         }

  28:     }

  29: }

This can then be compiled and we should be ready to go. Remember to edit the Web Service settings for the web parts to reflect the new web service we just created. Our web part should now be working correctly and look something like the following (I’ve told the Page View web part to only show Page Name and Main Body):

So there we have it! We now have a SQL database being exposed through EPiServer’s custom page provider model straight through into SharePoint. It just shows how flexible and powerful this technology is.

The whole thing still needs tidying up and maybe if someone is interested in doing that I’ll post up my updated source code and let you have a crack at it? :)