SharePoint CSOM

After I realised it was a not a very good idea to host my C# program at our SharePoint service provider, I googled around for options, I had only seen the Server Side Object Model, which require you develop your code on the SP server, or you install SP on your developer machine and you deploy your fully trusted code on the SP server. From an application developer's viewpoint the SSOM concept is horrible, and should only be used if there is no other viable alternatives. Actually it’s a horrible concept plain and simple and should be avoided.  I googled for options and almost immediately came across this MSDN video.

I also found discussions on other options JSOM (JavaScript) and REST interface. (And how to use Powershell scripts in visual studio utilizing the CSOM library.)  
With lots of help from a consultant I created my C# program using the CSOM model in visual studio on my PC with remote connection to SharePoint only. It was pretty straightforward, there are peculiarities with SP I do not like but it might be to my lack of SP experience. E.g. the custom lists are crap as a database, the CSOM calls are very very slow (I find them so in our environment), but for very small amounts of simple data SP is alright.
There is no truncate function for SP lists, if you want to empty a list you have to delete all the rows one by one and this is excruciatingly slow. The data I import to SP in my C# program is split into 4 lists, for a complete refresh I had to do this slow delete process one list after another. But after some googling I found the Parallel.ForEach capabilities in C#  which made it possible to delete rows for all lists in parallel effectively cutting the delete time down to just one fourth.
Here is the main section of my program with the Parallel.ForEach in the red square.

Next SP development project will probably be more visual including JavaScript, it will be similar to this application but in SP.
I am no longer a developer and haven’t worked as one for the last +15 years, but I believe if you do not know software development you cannot do a good job as an information or application IT architect. And if you do not regularly practice programming you do not understand software development well enough. But more important after some time with no development work I become a


Euro2016 Football Championship - the result

Portugal and Corporate IT won the Euro2016 Football Championship.
Portugal played the best and Corporate IT due to some advanced predictive statistical analysis supported by The Data Warehouse won the Company Football tips.

Corporate IT scored well in the individual football tips too with me in 4th place.
The winner of this years football tips Viktor Sjölinder who happens to be a Business Intelligence Developer, has promised to improve the Data Warehouse Tips app for the 2018 Football World Championship bringing the legacy of Henrik forward.


Hello, World!

I assist a cloud project, storing company public data in a cloud app. Some of this public data should be imported into the company Intranet for convenient access of the company's employees. We will call a webservice requesting updates and then import them into the intranet. Since the cloud app provider has not yet created this web service I asked them to provide us with a web service responding with the classical ‘Hello, World!” phrase. With this webservice I could write a PoC, this was necessary since we and especially I had not done such a program before. I started by writing a Powershell script, but I could not find any good documentation how to access our intranet (I didn’t try that hard to be honest). I got the impression my Powershell script had to be executed on the intranet server, which I do not have access to. I switched to C# and .Net and wrote my first C# program (I got much help from a consultant).
Setting up the dummy ‘Hello, World!’ was a real smart move since it is rather complex to find your way through proxies and firewalls calling the right service provider with right parameters, receiving and interpreting the result updating the intranet, logging the event, do error checking and sending notifications if something goes wrong. Now when I had this complex dummy program up and running, the project leader suggest I should put this program into production. This was really clever, the intranet was hosted by another external service provider and we have not set up a similar program before. By moving the dummy program into production we would learn this process as well and when we have the final program ready we only have to substitute the program, this should be a quick operation.
So I sent my program to our internal development guys and asked them to help us move the program into production, After a quick quality review they handed over my program to the intra net service provider. Their response in italic:

First and foremost, what is provided is an executable and not a script so we cannot continue with the provided info/executable. - I was a bit surprised since I had supplied the source code and I’m happy to share the entire .net project.

There is no guarantee that what is provided/described in the post below is actually inserted into the executable. Moreover, it is a bad idea to stab "parameters" such as email addresses, mail servers and URLs in compiled code. Normally a programmer puts these in resource or configuration files so that it should not be recompiled whenever you need to adjust the parameters. - I was surprised they questioned the supplied executable was the right one, but since I supplied the source code they should be able to compile themselves. This was a first version of a dummy program with all hard coded. The current version I use the configuration manager e.g.
string info = ConfigurationManager.AppSettings["webRequest"];

Secondary we prefer this would be a PowerShell Script . It is easy to convert such into a PowerShell script because the syntax of C # and PowerShell are similar. But as the "developer" indicates "I have no knowledge of C # .NET or Visual Studio" ....Nevertheless we can provide help to convert from C # into PowerShell. If needed please inform me so I can request a quote to provide this kind of support. - I could not see any point in converting my C# to PowerShell script. (If you are afraid of .Net upward compatibility issues, I’m pretty certain that is nothing against PowerShell version hell.)

Furthermore, you will need to approve this request for the three different environments since the script for the three environments will be different. This because the parameters for each environment are different  (cfr. URLs) and you such cannot follow the DTAP model and so therefore you might face different behavior in production that has not been tested in ITE and staging. - ?

After replying the best I could to the the service provider's letter, I received a response that made me think it was a bad idea to put my program into production. E.g. the service provider thought it was a bad idea to test the program in the test environment, which I found a bit ‘odd’, I usually test programs in test environments. Anyway I skip this idea, I’m pretty good at conceiving ideas so I’m confident I come up with one to everybody's liking. I’m fond of PowerShell and now also .Net, in the end I might convert my C# to PowerShell even though it is awkward, normally you start with a quick script prototype and convert it to an executable for production. Our service provider seem to favour the other way around. And they indicate test,staging and production environment are so different you need to modify your program when moving between these environments, I question the value of testing at all.


Good Luck Henrik

Henrik has “kicked his last bicycle” for the Company (at least for this time, as all other Data Warehouse developers he is more than welcome back).
It is never fun to say goodbye to a good colleague and a friend. Henrik has been with us for some plus two years, it is not a long time, but during that time Henrik has done a lot of brilliant work. Henrik is a nice likable guy who works hard and deliver outstanding solutions, and as such he has been a great goodwill ambassador for the Data Warehouse. Henrik is sorely missed by me and the rest of the company.
Henrik scores on the football pitch.

But the Data Warehouse must go on. Now when Henrik leaves Lucas will go on together with three newcomers. It is a tough task, a lot of work and great responsibility.  I wish the new team good luck and will of course assist to the best of my ability.


The importance of XML in web services

‘I want the output from your webservice in JSON not in XML format’ I said to the software vendor techie.
‘Yes of course’ he responded but ‘why not XML? It is simpler to work with and you can use XSLT’.
‘Yes’, I thought, ‘and that crappy XSL language, I never use that’.

We at the Läskeblask Brewery Inc. has bought a service supplying us with Alcohol Awareness Data sheets, required by law for the alcoholic beverages we sell. A data sheet accompanying the sales order when we ship alcoholic goods. When we create the sales order we call this web service with the product number, the web service responds with an XML list of links to Alcohol Awareness data sheets in different languages, which should be displayed in a menu from which the salesman could click on the right sheet.The  problem I had, we had forgot or not thought about this menu, I realised we needed an application in between the sales application and the web service, that’s why I called the vendor techie, to get more details about the web service.

A few hours later it hit me, a browser could probably do an XSL transformation, so I googled around, and sure enough modern browsers can do that. I went to w3schools.com and taught myself enough XSL for the task  in an hour.
I had this XML from the web service:
The first thing I realized I needed a proper XML header (is the place to specify the XSL script), so I added one:
<?xml-stylesheet version="1.0" type="text/xsl" href="xsl1.xml"?>

The href points to my XSL script (xsl1.xml). Next I found out XSL can only work with well formed XML data, and this XML was not well formed, If you take a close look at the <ExternalURL> tag above you see it contains solitary ampersands (&) and this is not allowed in XML ‘&’ is written ‘&amp;’ in well formed XML. For this PoC I manually edited my XML script :

Now I had well formed XML script pointing to my XSL script (I started with pinched snippets from w3school and of course googled around, it took me some hours). This is the XSL script:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>
<xsl:template match="/"> <!-- this template is for the entire XML file -->
<xsl:for-each select="ListResultOfMaterial">
   <h2>Alcohol Awareness Data Sheets for: <xsl:value-of select="userPartNumber"/><xsl:text> - </xsl:text><xsl:value-of select="userPartName"/></h2>
   <table border="1">
     <tr bgcolor="#9acd32">
       <th>ExternalUrl (for reference only, the URLs are used as links in the Country and Language cells) </th>
     <xsl:for-each select="Document">
<xsl:variable name="cmlink">
          <xsl:value-of select="ExternalUrl"/>
<xsl:variable name="greendt">
          <xsl:value-of select="substring(IssueDate,1,10)"/>
     <td><a href="{$cmlink}"><xsl:value-of select="CountryCode" /></a></td>
     <td><a href="{$cmlink}"><xsl:value-of select="LanguageCode" /></a></td>
         <td><xsl:value-of select="substring(IssueDate,1,10)"/></td>
<xsl:choose> <!-- if-then-else (if is only simple if statements) -->
  <xsl:when test="$greendt = '2008-03-04'">
     <xsl:attribute name="style">font-family: Helvetica; color: green; font-size: 8pt;</xsl:attribute>
     <xsl:attribute name="style">font-family: Helvetica; color: red; font-size: 8pt;</xsl:attribute>
  <xsl:value-of select="ExternalUrl"/>

And now I fired off


In Chrome.  Nothing happened, absolutely nothing. After a lot of swearing and a very long while and of course googling around, I found out some Chrome developer had decided it was unsafe to invoke an XSL script residing on my PC! So I invoked a better browser IE 10!!!
And there it was a beautifully transformed XML script:
Clicking any of the two first columns in the second row gives you the Alcohol Awareness Data Sheet for the Pan-Galactic Gargle Blaster drink.
I now have ‘the in between menu’ without any web whatsoever just a simple XSL script. Now I only have to persuade the software vendors to change the webservice and host my XSL script.

There is probably better ways to do this, if you know one please let me know. I happily buy you a tin of Pan-Galactic Gargle Blaster when you stop at the Läskeblask Brewery the next time.