Tuesday, February 14, 2017

Set initial value to Sharepoint people picker via javascript

Suppose that in our Sharepoint Online site we want to use Azure groups for configuring permissions on securable objects (sub sites, document libraries, etc.) and that all Azure groups’ names start with common prefix “azure_” which is set as part of naming convention used in the company. In this case users will always need to type this prefix in people picker each time they want to grant permissions to some Azure group. Is it possible to help them and set common prefix as initial value of people picker? Yes, it is possible with using the following trick:

   1: var peoplePickerUtility = {
   3:     isPeoplePickerOpened: false,
   4:     isPeoplePickerChanged: false,
   6:     addPrefixToPeoplePicker: function() {
   7:         if (window.location.href.toLowerCase().indexOf("/user.aspx") < 0) {
   8:             return;
   9:         }
  11:         setTimeout(function(){
  12:             try {
  13:                 if (typeof(SPClientPeoplePicker) != "undefined" &&
  14: $("#" + SPClientPeoplePicker.SPClientPeoplePickerDict.peoplePicker_TopSpan.EditorElementId).is(":visible")) {
  15:                     peoplePickerUtility.isPeoplePickerOpened = true;                    
  16:                 }
  17:                 else {
  18:                     peoplePickerUtility.isPeoplePickerOpened = false;
  19:                     peoplePickerUtility.isPeoplePickerChanged = false;
  20:                 }
  22:                 if (peoplePickerUtility.isPeoplePickerOpened &&
  23:                         !peoplePickerUtility.isPeoplePickerChanged) {
  24: $("#" + SPClientPeoplePicker.SPClientPeoplePickerDict.peoplePicker_TopSpan.EditorElementId).val("azure_");
  25:                     peoplePickerUtility.isPeoplePickerChanged = true;
  26:                 }
  28:                 peoplePickerUtility.addPrefixToPeoplePicker();
  29:             }
  30:             catch(e) {
  31:                 console.log("Error in peoplePickerUtility.addPrefixToPeoplePicker: " + e.message);
  32:             }
  33:         }, 1000);
  34:     }
  35: };
  37: $(function() {
  38:     peoplePickerUtility.addPrefixToPeoplePicker();
  39: });

The idea is that we run function the loop each 1 sec (line 11) which checks whether people picker is opened via SPClientPeoplePicker.SPClientPeoplePickerDict.peoplePicker_TopSpan object. If it is visible it sets its initial value (“azure_”) and sets flag which says that it is already changed (line 25), so it won’t change it twice once it is opened and override value added by user. If people picker will be closed and opened again, it will set initial value again. This code works only on granting permissions page (user.aspx) in order to not affect whole site. Of course this is not perfect solution, but it works. After that people picker will have “azure_” text as initial value:

Hope that this information will help someone.

Friday, February 3, 2017

Improve Yammer embed feed performance on Sharepoint Online

Yammer embed feed is often added to the front page of the company intranet in Sharepoint. From one side it adds value to customers as users may see corporate feed together with other important content on the same page. From other side it adds drawback: increase of the total page load time (by total time I mean time needed for both server and client side components. Yammer adds more time to the client part). We measured that Yammers adds ~3-4 seconds to the scripting part of the page load time. Is there a way to improve situation?

Let’s check how Yammer embed feed is added on the page. In most cases it is added via Content editor web part and the following code:

   1: <!DOCTYPE HTML>                                          
   2: <html>
   3:     <head></head>
   4:     <body>
   5:         <script type="text/javascript"
   6: src="https://c64.assets-yammer.com/assets/platform_embed.js"></script>
   2:         <div id="embedded-feed" style="height:1000px;width:400px;"></div>
   3:         <script>
   4:             yam.connect.embedFeed({"config": { "header": false },
   5:                 "container": "#embedded-feed",
   6:                 network: 'example.com' });
   7:             $(window).load(function () {
   8:                 $('.frontpage #embedded-feed').width('100%');
   9:             });
   7:     </body>
   8: </html>

As you can see script is loaded from Yammer CDN https://c64.assets-yammer.com/assets/platform_embed.js. So the first thing I tried was to move this script to the Sharepoint doclib, but it didn’t give visible results (internally this script loads a lot of other scripts and css files from CDN and also adds iframe dynamically).

After that I tried to use another approach which gave some results. First of all create new page (e.g. yammer.aspx) in Pages doclib and detach it from Page layout via Sharepoint Designer. After that remove all code from there and add only Yammer code shown above.

Then we need to modify the page where we show embed Yammer feed: instead of Content editor web part which was used for Yammer initially we will use Page viewer web part and configure it to use newly created page yammer.aspx as a source (that’s why we needed to detach this page from page layout – it should not contain any UI elements like headers, navigation, etc. It should only contain Yammer widget). I.e. after that page will look the same, but technically there is difference how Yammer is shown there. Now Yammer is shown in sub page via iframe in separate http request and its scripts not affect load of basic page. At least for us after that our testing showed that scripting time was reduced on ~2 seconds. Hope that information will be helpful for you.

Thursday, January 19, 2017

Write output to the file in PowerShell

When we create PowerShell scripts we often add some useful info to the trace using Write-Host method:

   1: Write-Host "Check web" $web.Url

It will write trace to console. How to write output to the file from PowerShell? One way is of course use .Net System.IO.File class, but in PowerShell there is also more convenient native syntax:

   1: ("Check web" + $web.Url) | Out-File "log.txt" -Append

As result it will write the same string which we used in example with Write-Host to the log.txt. The difference with this syntax and Write-Host is that in last case you need to explicitly concatenate strings using + operator, while in Write-Host it is not necessary.

Friday, January 13, 2017

Export images from MS SQL Server database without programming

As you probably know images can be stored directly in database, e.g. in the following table:

   1: CREATE TABLE [dbo].[Images](
   2:     [id] [uniqueidentifier] NOT NULL,
   3:     [name] [nvarchar](255) NOT NULL,
   4:     [data] [image] NOT NULL
   5: )

where image itself is stored in data column of image type and its name in name column. Sometimes we need to export one or all images from there. How to do that? Of course it is possible to write utility or PowerShell script, but for such small tasks I prefer to use non-programming approach. In this post I will show how to export images from MS SQL Server database without programming.

We will use free LinqPad tool for that. The good thing in LinqPad is that it doesn’t only allows to execute Sql queries, but also allows to write C# code for manipulating data. First of all we need to add connection to our SQL Server instance:

After that specify exact database in Connection dropdown list on the top and choose Language = C# Statements:

Now we can write the following Linq 2 Sql code for exporting single specific image:

   1: var img = Images.First(i => i.Id == new Guid("..."));
   2: using (var ms = new MemoryStream(img.Data.ToArray()))
   3: {
   4:     System.Drawing.Image.FromStream(ms).Dump("c:/dump/" + img.Name);
   5: }

Alternatively we may preview image in LinqPad preview window by calling Dump() method:

   1: var img = Images.First(i => i.Id == new Guid("..."));
   2: using (var ms = new MemoryStream(img.Data.ToArray()))
   3: {
   4:     System.Drawing.Image.FromStream(ms).Dump();
   5: }

If you want to export all images from the database table use the following code:

   1: foreach (var img in Images)
   2: {
   3:     using (var ms = new MemoryStream(img.Data.ToArray()))
   4:     {
   5:         System.Drawing.Image.FromStream(ms).Save("c:/dump/" + img.Name);
   6:     }
   7: }

In this example all images will be exported to c:\dump folder (ensure that it exists before to run the code). As you can see LinqPad is convenient tool for performing small maintenance tasks over SQL Server.

Friday, January 6, 2017

Provision and automatic update of embedded resources to Resources folder in Sharepoint hive

Automatic provision of embedded resources may be quite annoying unless you know how to do it automatically. Surprisingly I didn’t find articles about this issue so decided to write this post. As you probably know in order to provision own resources (resx) files to Sharepoint Resources folder (e.g. 15/Resources in Sharepoint 2013) we add Resources Sharepoint mapped folder in Visual studio and add resx files there:

By default when we add resx file it is added with Build Action = Content:

If we will create wsp package with such resource file we will see that it will include resx file inside the package:

and manifest.xml will have reference on it:

   1: <?xml version="1.0" encoding="utf-8"?>
   2: <Solution xmlns="http://schemas.microsoft.com/sharepoint/"
   3:     SolutionId="c9c995d9-6cf3-422e-81fc-ffb0f7648610"
   4:     SharePointProductVersion="15.0">
   5:   <Assemblies>
   6:     <Assembly Location="SharePointProject4.dll" DeploymentTarget="GlobalAssemblyCache" />
   7:   </Assemblies>
   8:   <RootFiles>
   9:     <RootFile Location="Resources\Test.resx" />
  10:   </RootFiles>
  11: </Solution>

However we may want to reuse it for using localized strings also from C# code. In order to do that we need to open resx file in Visual Studio designer and set Access Modifier to Internal or Public. After that VS generates .designer.cs file with strongly-typed C# class which we then can use from the code. After that we also need to change Build Action to Embedded Resource:

But if then we will try to create wsp package we will see that resx file is not included to it anymore:

manifest.xml won’t have reference to resource file as well:

   1: <?xml version="1.0" encoding="utf-8"?>
   2: <Solution xmlns="http://schemas.microsoft.com/sharepoint/"
   3:     SolutionId="c9c995d9-6cf3-422e-81fc-ffb0f7648610"
   4:     SharePointProductVersion="15.0">
   5:   <Assemblies>
   6:     <Assembly Location="SharePointProject4.dll" DeploymentTarget="GlobalAssemblyCache" />
   7:   </Assemblies>
   8: </Solution>

And it means that our resx files won’t be automatically updated anymore when we will update wsp solution via Update-SPSolution cmdlet. Does it mean that with ability to use embedded resources from C# we lost automatic updates of resx files? Fortunately no, there is workaround which will allow to have both possibilities.

First thing which we need to do is to add new Empty element to the project:

If you didn’t have features in your project yet VS will create new feature automatically and will add new element there. We don’t need this feature so delete it. If you had already features in the project VS will add new element to one of them – this is also not needed so revert changes in features after new element has been added. Also we won’t need elements.xml file under new element – remove it as well.

Then edit SharePointProjectItem.spdata file under added element (you can see it in Solution explorer when will click Show all files there) and add reference to resx file like this:

   1: <?xml version="1.0" encoding="utf-8"?>
   2: <ProjectItem Type="Microsoft.VisualStudio.SharePoint.GenericElement"
   3:     SupportedTrustLevels="All"
   4:     SupportedDeploymentScopes="Web, Site, WebApplication, Farm, Package"
   5:     xmlns="http://schemas.microsoft.com/VisualStudio/2010/SharePointTools/SharePointProjectItemModel">
   6:   <Files>
   7:     <ProjectItemFile Source="..\Resources\Test.resx" Target="Resources" Type="RootFile" />
   8:   </Files>
   9: </ProjectItem>

Source attribute should contain correct relative path to resx from from SharePointProjectItem.spdata file. And add element to the package in VS designer:

After that if you will publish wsp package you will see that it again contains resx file and reference inside manifest.xml:

   1: <?xml version="1.0" encoding="utf-8"?>
   2: <Solution xmlns="http://schemas.microsoft.com/sharepoint/"
   3:     SolutionId="c9c995d9-6cf3-422e-81fc-ffb0f7648610"
   4:     SharePointProductVersion="15.0">
   5:   <Assemblies>
   6:     <Assembly Location="SharePointProject4.dll" DeploymentTarget="GlobalAssemblyCache" />
   7:   </Assemblies>
   8:   <RootFiles>
   9:     <RootFile Location="Resources\Test.resx" />
  10:   </RootFiles>
  11: </Solution>

Now if you will update wsp via Update-SPSolution your embedded resource files will be updated automatically. The only thing which you need to remember is that if you add new embedded resource to the project you will need to add reference on it to the SharePointProjectItem.spdata. But from my point of view this is not big price for possibility to use resources from the code and have automatic updates for them.

Wednesday, January 4, 2017

CAML query for datetime fields and current datetime in Sharepoint javascript object model: UTC, ISO8101, Today, Now, IncludeTimeValue and StorageTZ

Often we need to retrieve items from Sharepoint list using condition on datetime field comparing it with DateTime.Now including both date and time parts. In this post I will show several ways to achieve it using javascript object model.

Suppose that we need to query news which have PublishedDate field which is less or equal current datetime (item[“PublishedDate”] <= DateTime.Now). It is important to take into consideration time part in order to have correct listing for news published today (e.g. news which have PublishedDate set to 12:00 today should not be visible until 11:59, but should appear starting from 12:00).

In order to get such news we need to use the following CAML query:

   1: <Where>
   2:     <Leq>
   3:         <FieldRef Name="PublishedDate" />
   4:         <Value IncludeTimeValue="True" Type="DateTime">2017-01-04T12:00:00Z</Value>
   5:     </Leq>
   6: </Where>

Several notes about datetime value. First of all pay attention on IncludeTimeValue="True" attribute which tells Sharepoint to use time part for comparison. At second, value itself 2017-01-04T12:00:00Z is in ISO8601 format (in basic Sharepoint server object model there is convenient method SPUtility.CreateISO8601DateTimeFromSystemDateTime which formats datetime into this format). Note that it shouldn’t include milliseconds part .000 after seconds, i.e. you can’t use standard Date.toISOString() javascript function as is because of that (this function returns also milliseconds which breaks Sharepoint query engine). At third, you also can’t use Date.toISOString() javascript function because it converts datetime object to UTC format, while in regular CAML queries dates should not be in UTC (there is a way to use UTC datetime in CAML query, but you need to do additional action for that - add StorageTZ=”TRUE” attribute like shown e.g. here: Timezone offset comparisons in CAML with UTC parameter. In our example we don’t use this attribute, i.e. need to use datetime in local timezone, not in UTC). And at fourth, as we want to get news which have item[“PublishedDate”] <= DateTime.Now we of course should not hardcode value to the CAML query. Instead we need to get current datetime from server and format it to ISO8601 non-UTC format. In one of my previous posts I showed how to get current server’s datetime (see Get current server date time via javascript object model in Sharepoint):

   1: var currentServerDateTime = new Date(new Date().getTime() +
   2:     _spPageContextInfo.clientServerTimeDelta);

Here is full code which creates above CAML query using Sharepoint javascript object model:

   1: var currentServerDateTime = new Date(new Date().getTime() +
   2:     _spPageContextInfo.clientServerTimeDelta);
   3: var query = new CamlBuilder().Where().DateTimeField("PublishedDate")
   4:     .LessThanOrEqualTo(Utility.CreateISO8601DateTimeFromSystemDateTime(currentServerDateTime))
   5:         .ToString();
   7: var camlQuery = new SP.CamlQuery();
   8: camlQuery.set_viewXml("<View><Query>" + query + "</Query></View>");
  10: var list = site.openWeb(webUrl).get_lists().getByTitle(listTitle);
  11: var items = list.getItems(camlQuery);

Here I used the following helper utility which works like SPUtility.CreateISO8601DateTimeFromSystemDateTime, but in javascript:

   1: var Utility = {
   2:     pad: function (number) {
   3:       if (number < 10) {
   4:         return '0' + number;
   5:       }
   6:       return number;
   7:     },
   9:     CreateISO8601DateTimeFromSystemDateTime: function(dt) {
  10:       return dt.getFullYear() +
  11:         '-' + Utility.pad(dt.getMonth() + 1) +
  12:         '-' + Utility.pad(dt.getDate()) +
  13:         'T' + Utility.pad(dt.getHours()) +
  14:         ':' + Utility.pad(dt.getMinutes()) +
  15:         ':' + Utility.pad(dt.getSeconds()) +
  16:         'Z';
  17:     },
  18: };

Also for creating CAML query itself I used convenient utility CamlJS, which is similar to Camlex, i.e. allows to built CAML queries dynamically, but works in javascript.

Shown above method uses direct approach with comparing to exact server’s current datetime. However for achieving the same result we may use one non-well documented Sharepoint feature: use CAML query with <Today /> keyword and IncludeTimeValue="True" attribute. It will return the same items as above query, i.e. news which have item[“PublishedDate”] <= DateTime.Now using time part in comparision. You may think that instead of <Today /> and IncludeTimeValue="True" you may use another CAML keyword <Now />, but this is not the case – <Now /> should be used only inside DateRangesOverlap element – see the following forum thread for details CAML Now Instead of Today - CAML Bug?. Here is code which uses this approach:

   1: var currentServerDateTime = new Date(new Date().getTime() +
   2:     _spPageContextInfo.clientServerTimeDelta);
   3: var query = new CamlBuilder().Where().DateTimeField("PublishedDate")
   4:     .LessThanOrEqualTo(CamlBuilder.CamlValues.Today).ToString();
   6: var camlQuery = new SP.CamlQuery();
   7: camlQuery.set_viewXml("<View><Query>" + query + "</Query></View>");
   9: var list = site.openWeb(webUrl).get_lists().getByTitle(listTitle);
  10: var items = list.getItems(camlQuery);

Resulting CAML will be:

   1: <Where>
   2:     <Leq>
   3:         <FieldRef Name="PublishedDate" />
   4:         <Value IncludeTimeValue="True" Type="DateTime">
   5:             <Today />
   6:         </Value>
   7:     </Leq>
   8: </Where>

Hope that this information will help you.