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.

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.

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.

Update 2017-03-07: see also how to provision embedded local UI resources from App_LocalResources folder in this post: Provision and automatic update of embedded resources to App_LocalResources folder under Sharepoint Template/Layouts or Template/ControlTemplates sub folders

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();

   6:  

   7: var camlQuery = new SP.CamlQuery();

   8: camlQuery.set_viewXml("<View><Query>" + query + "</Query></View>");

   9:  

  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:     },

   8:  

   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();

   5:  

   6: var camlQuery = new SP.CamlQuery();

   7: camlQuery.set_viewXml("<View><Query>" + query + "</Query></View>");

   8:  

   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.

Use specific version of Sharepoint client object model in PowerShell via assembly binding redirection

Recently we faced with interesting problem: in PowerShell script we needed to use OfficeDevPnP.Core.dll of version 16.1.8.1115.0 which was built with client-side object model (CSOM) version 16.1.3912.1204. However in our basic app we used newer CSOM version 16.1.5625.1200 and since its dlls were added to source control together with other source files we wanted to reuse it in PowerShell scripts as well. In application it was easy to fix issue with different referenced CSOM version by specifying assembly binding redirection in app.config. But in PowerShell it was not so simple. By default all attempts to call methods from OfficeDevPnP.Core.dll threw exception that Microsoft.SharePoint.Client.dll of version 16.1.3912.1204 is not found.

Error is logical because as I wrote above our version of OfficeDevPnP.Core.dll was built with older CSOM version. But how to solve it in PowerShell? In order to avoid this error we can use code-based assembly redirection binding:

   1: $currentDir = Convert-Path(Get-Location)

   2: $dllsDir = resolve-path($currentDir + "\..\csom16.1.5625.1200\")

   3: $officeDevPnPDir = resolve-path($currentDir + "\..\OfficeDevPnPCore16.1.8.1115.0\lib\net45\")

   4:  

   5: $AssemblyClientRuntime =

   6:     [System.Reflection.Assembly]::LoadFile(

   7:         [System.IO.Path]::Combine($dllsDir, "Microsoft.SharePoint.Client.Runtime.dll"))

   8: $AssemblyClient =

   9:     [System.Reflection.Assembly]::LoadFile(

  10:         [System.IO.Path]::Combine($dllsDir, "Microsoft.SharePoint.Client.dll"))

  11:  

  12: $OnAssemblyResolve = [System.ResolveEventHandler] {

  13:     param($sender, $e)

  14:  

  15:     if ($e.Name -eq "Microsoft.SharePoint.Client.Runtime, Version=16.1.3912.1204, " +

  16:         "Culture=neutral, PublicKeyToken=71e9bce111e9429c")

  17:     {

  18:         return $AssemblyClientRuntime

  19:     }

  20:     if ($e.Name -eq "Microsoft.SharePoint.Client, Version=16.1.3912.1204, " +

  21:         "Culture=neutral, PublicKeyToken=71e9bce111e9429c")

  22:     {

  23:         return $AssemblyClient

  24:     }

  25:  

  26:     foreach($a in [System.AppDomain]::CurrentDomain.GetAssemblies())

  27:     {

  28:         if ($a.FullName -eq $e.Name)

  29:         {

  30:             return $a

  31:         }

  32:     }

  33:     return $null

  34: }

  35: [System.AppDomain]::CurrentDomain.add_AssemblyResolve($OnAssemblyResolve)

In this code at first we load specific versions of CSOM and OfficeDevPnP.Core (lines 1-10). Then we define custom assembly binding resolver (lines 12-34) and add it to current AppDomain’s assembly resolvers (line 35). In custom resolver we check assembly name and if it corresponds to older version of CSOM 16.1.3912.1204 we resolve it with current newer version 16.1.5625.1200. This approach allows to use specific CSOM version in PowerShell.

Also need to mention that when you launch Sharepoint Management Shell it loads currently installed CSOM from the GAC and in .Net there is no way to unload assembly once it is already loaded. I.e. if you need to use specific CSOM version in PowerShell which differs from the version installed into your GAC, run Windows PowerShell console instead of Sharepoint Management Shell.