Sunday, September 27, 2015

Camlex 4.1 and Camlex.Client 2.1 are released: reverse version for lists joins and fields projections

Over year ago new feature was announced in Camlex library: lists joins and fields projections. This feature allowed to create lists joins in CAML syntax via C# (I will repeat example from the mentioned post here):

   1: var query = new SPQuery();
   3: query.Query = Camlex.Query().Where(x => (string)x["CustomerCity"] == "London" &&
   4:     (string)x["CustomerCityState"] == "UK").ToString();
   6: query.Joins = Camlex.Query().Joins()
   7:     .Left(x => x["CustomerName"].ForeignList("Customers"))
   8:     .Left(x => x["CityName"].PrimaryList("Customers").ForeignList("CustomerCities"))
   9:     .Left(x => x["StateName"].PrimaryList("CustomerCities").ForeignList("CustomerCityStates"))
  10:     .ToString();
  12: query.ProjectedFields = Camlex.Query().ProjectedFields()
  13:     .Field(x => x["CustomerCity"].List("CustomerCities").ShowField("Title"))
  14:     .Field(x => x["CustomerCityState"].List("CustomerCityStates").ShowField("Title"))
  15:     .ToString();
  17: query.ViewFields = Camlex.Query().ViewFields(x => new[] {x["CustomerCity"],
  18:     x["CustomerCityState"]});

It will give the following query:

   1: <Where>
   2:   <And>
   3:     <Eq>
   4:       <FieldRef Name="CustomerCity" />
   5:       <Value Type="Text">London</Value>
   6:     </Eq>
   7:     <Eq>
   8:       <FieldRef Name="CustomerCityState" />
   9:       <Value Type="Text">UK</Value>
  10:     </Eq>
  11:   </And>
  12: </Where>


   1: <Join Type="LEFT" ListAlias="Customers">
   2:   <Eq>
   3:     <FieldRef Name="CustomerName" RefType="Id" />
   4:     <FieldRef List="Customers" Name="Id" />
   5:   </Eq>
   6: </Join>
   7: <Join Type="LEFT" ListAlias="CustomerCities">
   8:   <Eq>
   9:     <FieldRef List="Customers" Name="CityName" RefType="Id" />
  10:     <FieldRef List="CustomerCities" Name="Id" />
  11:   </Eq>
  12: </Join>
  13: <Join Type="LEFT" ListAlias="CustomerCityStates">
  14:   <Eq>
  15:     <FieldRef List="CustomerCities" Name="StateName" RefType="Id" />
  16:     <FieldRef List="CustomerCityStates" Name="Id" />
  17:   </Eq>
  18: </Join>


   1: <Field Name="CustomerCity" Type="Lookup" List="CustomerCities" ShowField="Title" />
   2: <Field Name="CustomerCityState" Type="Lookup" List="CustomerCityStates" ShowField="Title" />

and view fields:

   1: <FieldRef Name="CustomerCity" />
   2: <FieldRef Name="CustomerCityState" />

In order to make previous release faster I didn’t include reverse version of this C# to CAML transformation there. I.e. in online service (this service allows to perform transformation in different direction from CAML to C# in order to simplify using Camlex for developers which didn’t use it yet) it was not possible to transform joins and projected fields from CAML to C#. With new release of Camlex 4.1 and Camlex.Client 2.1 it became possible. Now if e.g. the following CAML will be passed there:

   1: <Query>
   2:   <Joins>
   3:     <Join Type="LEFT" ListAlias="Customers">
   4:       <Eq>
   5:         <FieldRef Name="CustomerName" RefType="Id" />
   6:         <FieldRef List="Customers" Name="Id" />
   7:       </Eq>
   8:     </Join>
   9:     <Join Type="LEFT" ListAlias="CustomerCities">
  10:       <Eq>
  11:         <FieldRef List="Customers" Name="CityName" RefType="Id" />
  12:         <FieldRef List="CustomerCities" Name="Id" />
  13:       </Eq>
  14:     </Join>
  15:   </Joins>
  16: </Query>

It will produce the following C# code:

   1: Camlex.Query().Joins()
   2:     .Left(x => x["CustomerName"].ForeignList("Customers"))
   3:     .Left(x => x["CityName"].PrimaryList("Customers").ForeignList("CustomerCities"))

The same with projected fields:

   2: <Query>
   3:   <ProjectedFields>
   4:     <Field Name="CustomerCity" Type="Lookup" List="CustomerCities"
   5:         ShowField="Title" />
   6:     <Field Name="CustomerCityState" Type="Lookup" List="CustomerCityStates"
   7:         ShowField="Title" />
   8:   </ProjectedFields>
   9: </Query>

will produce

   1: Camlex.Query()
   2:     .ProjectedFields().Field(x => x["CustomerCity"].List("CustomerCities").ShowField("Title"))
   3:     .Field(x => x["CustomerCityState"].List("CustomerCityStates").ShowField("Title"))

With new version Camlex and Camlex.Client became complete and fully bidirectional again.

Friday, September 25, 2015

One problem with Restore-SPSite cmdlet

When you use Restore-SPSite cmdlet for restoring site collection

Restore-SPSite -Path c:/temp/siteA.bak

you may get the following error:

Restore-SPSite : The operation that you are attempting to perform cannot be completed successfully.  No content databases in the web application were available to store your site collection.  The existing content databases may have reached the maximum number of site collections, or be set to read-only, or be offline, or may already contain a copy of this site collection.  Create another content database for the Web application and then try the operation again.


It may happen when you backuped site collection from the web application (e.g. and then try to restore it to the same web app using different managed path (e.g. In order to avoid this error specify DatabaseServer and DatabaseName parameters:

Restore-SPSite -Path c:/temp/siteA.bak -DatabaseServer servername -DatabaseName dbname

Error should disappear after that.

Wednesday, September 23, 2015

Provisioning ready for use OneNote notebook to Sharepoint site–part 2

In the previous part I showed how to provision ready for use OneNote notebook to Sharepoint site. Described method works but has one drawback: if you have many sites and need to create notebook in all of them, users who will work with several sites will always see the same notebook name and won’t know from which sites they are because all of them will have the same name “New Section 1” (remember that we provisioned 2 files in Notebook docset: “Open Notebook.onetoc2” and “New Section”). In this post we will show this issue.

User experience will be better if we will use different names for notebooks. E.g. we may use parent sites’ titles for notebooks. In this case users will be able to open notebooks from different sites in OneNote desktop application and will know from which site each notebook is:



It is quite simple to do. We need to rename “New Section” to {web title}.one in web provisioned handler or in feature receiver (depending how you implemented it) right after setting ProgId for the Notebook folder (changing of ProgId was described in previous part):

   1: var list = web.GetList(SPUrlUtility.CombineUrl(web.ServerRelativeUrl, "OneNote"));
   2: var folder = list.Folders.Cast<SPListItem>().FirstOrDefault(f =>
   3:     f.Url.EndsWith("Notebook"));
   4: folder[SPBuiltInFieldId.Title] = "Notebook";
   5: folder.ProgId = "OneNote.Notebook";
   6: folder.Update();
   8: foreach (SPFile file in folder.Folder.Files)
   9: {
  10:     if (string.Compare(file.Name, "New Section", true) == 0)
  11:     {
  12:         file.MoveTo(string.Format("OneNote/{Notebook/{0}.one", web.Title));
  13:     }
  14: }

After that notebook will have the same title as parent web and users will be able to distinguish notebooks in OneNote as shown on the picture above.

Friday, September 18, 2015

Localize web part titles via client object model in Sharepoint

Starting with 2010 version Sharepoint has Multilingual UI feature which allows to dynamically switch language used for UI elements on the site (depending on settings this language may be determined by language preferences set in user profile or language http header configured in browser). This feature is also supported in Sharepoint 2013 and Sharepoint Online. Programmatically you may add different translations using special properties. E.g. if you need to localize site title you need to use SPWeb.TitleResource property.

The problem is that client object model has limited support of localizations UI elements. E.g. Microsoft.SharePoint.Client.dll of version (on-premise) doesn’t have these properties at all. The same assembly, but of vesion (online) has the following properties:

  • Web.TitleResource
  • List.TitleResource
  • Field.TitleResource
  • ContentType.NameResource

These properties can be used for adding localizations to appropriate Sharepoint objects. Of course in order to take effect you need to enable MUI on your site. It can be done in Site settings > Language settings:


In this example we enabled alternate Finnish language (language id = 1035) for our site.

The problem however that there is no similar property for web part title. However in Sharepoint in most cases web parts are most often seen by end users than other elements. So it would be good to have a mechanism for localizing web part titles. It can be done by the following PowerShell script:

   1: $localeId = 1035
   3: function ProcessWebParts($context, $page) {
   4:   $manager = $page.GetLimitedWebPartManager(
   5: [Microsoft.SharePoint.Client.WebParts.PersonalizationScope]::Shared)
   6:   $pageWebParts = $manager.WebParts
   7:   $context.Load($pageWebParts)
   8:   ExecuteLocalizedQuery $context $localeId
  10:   $pageWebParts | foreach {
  11:     $properties = $_.WebPart.Properties
  12:     $context.Load($properties)
  13:     ExecuteLocalizedQuery $context $localeId
  15:     $properties["Title"] = $properties["Title"] + $localeId.ToString()
  16:     $_.SaveWebPartChanges()
  17:   }
  18: } 
  20: function ExecuteLocalizedQuery($context, $localeid) {
  21:     $context.PendingRequest.RequestExecutor.WebRequest.Headers["Accept-Language"] =
  22: (new-object System.Globalization.CultureInfo($localeid)).Name
  23:     $context.ExecuteQuery()
  24: }
  27: $currentDir = Convert-Path(Get-Location)
  28: $dllsDir = resolve-path($currentDir + "\dlls")
  30: [System.Reflection.Assembly]::LoadFile([System.IO.Path]::Combine($dllsDir,
  31: "Microsoft.SharePoint.Client.dll"))
  32: [System.Reflection.Assembly]::LoadFile([System.IO.Path]::Combine($dllsDir,
  33: "Microsoft.SharePoint.Client.Runtime.dll"))
  34: [System.Reflection.Assembly]::LoadFile([System.IO.Path]::Combine($dllsDir,
  35: "Microsoft.SharePoint.Client.Taxonomy.dll"))
  36: [System.Reflection.Assembly]::LoadFile([System.IO.Path]::Combine($dllsDir,
  37: "Microsoft.Online.SharePoint.Client.Tenant.dll"))
  39: $siteURL = ""
  40: $context = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)    
  41: $web = $context.Web
  42: $site = $context.Site
  43: $context.Load($web)
  44: $context.Load($site)
  45: ExecuteLocalizedQuery $context $localeId
  47: $page = $web.GetFileByServerRelativeUrl("/Pages/default.aspx")
  48: $context.Load($page)
  49: ExecuteLocalizedQuery $context $localeId
  51: ProcessWebParts $context $page

The key element here is ExecuteLocalizedQuery function (lines 20-24). In this function we pass locale id to ClientContext.PendingRequest.RequestExecutor.WebRequest.Headers[“Accept-Language”]. As result our code is executed as it would be called from UI with used Finnish language. Now if we open site with Finnish language all web parts on default.aspx page will have titles with “1035” suffix. But when we will switch to default English language they will have initial values. The only problem which I found at the moment is that if we will use this script with default English language (language id = 1033), it will override also all other translations regardless of the fact that in Site settings > Language settings Overwrite translations setting is set to No (see above). But this is not a big problem because this script is needed first of all for adding translations for alternate languages.

Hope that this information will be helpful.