How to check if a specific file exists in a folder structure of a SharePoint document library using the client object model

Recently we had to create a utility function that makes it us possible to check if a file having a specific name exists anywhere within a folder structure of a SharePoint document library.

As long as you know not only the title of the document library, but its server relative URL as well, it requires only a single round-trip to the server:

1. private bool FileExists(ClientContext clientContext, String docLibTitle, String fileName, String rootFolderServerRelativeUrl, String folderPath)
2. {
3.     var folderServerRelativeUrl = string.Format("{0}/{1}", rootFolderServerRelativeUrl, folderPath);
4.     // or use a helper method to combine URL parts
5.     //var folderServerRelativeUrl = JoinUrlParts(rootFolderServerRelativeUrl, folderPath);
6.  
7.     List DocumentsList = clientContext.Web.Lists.GetByTitle(docLibTitle);
8.  
9.     CamlQuery camlQuery = new CamlQuery();
10.     camlQuery.ViewXml = @"<View Scope='Recursive'>
11.                         <Query>
12.                             <Where>
13.                                 <Eq>
14.                                     <FieldRef Name='FileLeafRef'></FieldRef>
15.                                     <Value Type='Text'>" + fileName + @"</Value>
16.                                 </Eq>
17.                             </Where>
18.                         </Query>
19.                 </View>";
20.     camlQuery.FolderServerRelativeUrl = folderServerRelativeUrl;
21.     ListItemCollection listItems = DocumentsList.GetItems(camlQuery);
22.     clientContext.Load(listItems);
23.     clientContext.ExecuteQuery();
24.  
25.     return listItems.Count > 0;
26. }

Usage:

1. var webUrl = "http://YourSharePoint/site/subsite&quot;;
2. string docLibTitle = "Documents";
3. var rootFolderServerRelativeUrl = "/site/subsite/Shared Documents";
4. var folderPath = "folder/subfolder";
5.  
6. ClientContext clientContext = new ClientContext(webUrl);
7. var fileName = "document.docx";
8.  
9. bool fileFound = FileExists(clientContext, docLibTitle, fileName, rootFolderServerRelativeUrl, folderPath);

Note: If the folderServerRelativeUrl points to a location not within the document library (rootFolderServerRelativeUrl is wrong), the CAML query will ignore the FolderServerRelativeUrl and the entire library will be searched for a matching file. If  the folderPath part is wrong (not existing folder) then no matching item will be found, the query will return always false. Although the SPFolder server-side object model provides an Exists property to check if the folder at the given oath exists, there is no such property for the Folder object in the client object model. As a workaround, you can detect such mistakes by including these two lines of code in the FileExists method before invoking the ExecuteQuery method:

Folder folder = clientContext.Web.GetFolderByServerRelativeUrl(folderServerRelativeUrl);
clientContext.Load(folder);

If either part of the folderServerRelativeUrl is wrong, a File not found exception will be thrown on calling the ExecuteQuery method.

The helper method mentioned in the code is useful if you would not like to bother with leading and trailing slashes in the URL and in the folder path:

1. public static string JoinUrlParts(params string[] urlParts)
2. {
3.     return string.Join("/", urlParts.Where(up => !string.IsNullOrEmpty(up)).ToList().Select(up => up.Trim('/')).ToArray());            
4. }

If you, however, know only the title of the document library but not its server relative URL you need two round-trips:

1. private bool FileExists(ClientContext clientContext, String docLibTitle, String fileName, String folderPath)
2. {
3.     List docLib = clientContext.Web.Lists.GetByTitle(docLibTitle);
4.     Folder rootFolder = docLib.RootFolder;
5.     clientContext.Load(rootFolder, f => f.ServerRelativeUrl);
6.     clientContext.ExecuteQuery();
7.  
8.     string rootFolderServerRelativeUrl = rootFolder.ServerRelativeUrl;
9.     var folderServerRelativeUrl = string.Format("{0}/{1}", rootFolderServerRelativeUrl, folderPath);
10.     // or use a helper method to combine URL parts
11.     //var folderServerRelativeUrl = JoinUrlParts(rootFolderServerRelativeUrl, folderPath);
12.  
13.     Folder folder = clientContext.Web.GetFolderByServerRelativeUrl(folderServerRelativeUrl);
14.     clientContext.Load(folder);
15.  
16.     CamlQuery camlQuery = new CamlQuery();
17.     camlQuery.ViewXml = @"<View Scope='Recursive'>
18.                         <Query>
19.                             <Where>
20.                                 <Eq>
21.                                     <FieldRef Name='FileLeafRef'></FieldRef>
22.                                     <Value Type='Text'>" + fileName + @"</Value>
23.                                 </Eq>
24.                             </Where>
25.                         </Query>
26.                 </View>";
27.     camlQuery.FolderServerRelativeUrl = folderServerRelativeUrl;
28.     ListItemCollection listItems = docLib.GetItems(camlQuery);
29.     clientContext.Load(listItems);
30.     clientContext.ExecuteQuery();
31.  
32.     return listItems.Count > 0;
33. }

Note: This version already includes the two-liner to check the existence of the folder path. If you don’t need that, remove it.

Usage of this version:

1. var webUrl = "http://YourSharePoint/site/subsite&quot;;
2. var docLibTitle = "Documents";
3. var folderPath = "folder/subfolder";
4. ClientContext clientContext = new ClientContext(webUrl);
5. var fileName = "document.docx";
6.  
7. bool fileFound = FileExists(clientContext, docLibTitle, fileName, folderPath);

Copy an XsltListViewWebPart from another SharePoint Site via PowerShell – The client-side solution

In my recent post I’ve illustrated, how to display SharePoint lists from other sites via PowerShell. As I told you, that solution does work only if you have direct access to the SharePoint server. Based on my experience that is not always the case. In the current post I introduce you a solution that should work even in such cases. We built this solution on the Managed Client-Object Modell of SharePoint.

Since I knew, that the CreateWebPartFromList method of the Microsoft.SharePoint.WebPartPages.SPWebPartManager is not accessible via the client object model, I first planned to apply the another approach: export the source web part via a LimitedWebPartManager instance (the client-side equivalent of SPLimitedWebPartManager), then use another LimitedWebPartManager instance to import it onto the target page. BUT (there is almost always a but…) it turned out, that although LimitedWebPartManager supports the ImportWebPart method, the ExportWebPart method is not available on the client-side (Note: as Waldek Mastykarz reported, the ExportWebPart method should be available since the March 2016 SharePoint Online CSOM update). So I came up with a fall-back plan and exported the web part by calling the exportwp.aspx page as described here by Anatoly Mironov.

As we export and import the web part from / to another sites, we create to different context objects to access them.

We read the response from the exportwp.aspx page as XML, and set the WebId property according to the ID of the source web site. There is apparently an issue with the ViewGuid property (more about them here), so we have to append it, for example, by cloning an existing XML node, like the one for the WebId property. A bit dirty workaround, but seems to work at me…

Finally, we import the web part to the target page and add it to the web part zone / position we wish.

1. $sourceWebUrl = "http://YourSharePoint/Site1/Site2&quot;
2. $listTitle = "YourList"
3. $viewTitle = "YourView" # name of the view, like "All Items"
4.  
5. # set the path according the location of the assemblies
6. Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
7. Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
8.  
9. $clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($sourceWebUrl)
10.  
11. $sourceWeb = $clientContext.Web
12. $list = $sourceWeb.Lists.GetByTitle($listTitle)
13. $views = $list.Views
14.  
15. $clientContext.Load($sourceWeb)
16. $clientContext.Load($list)
17. $clientContext.Load($views)
18. $clientContext.ExecuteQuery()
19.  
20. $sourceWebId = $sourceWeb.Id
21.  
22. $view = $views | ? { $_.Title -eq $viewTitle }
23.  
24. $targetWebUrl = "http://YourSharePoint/Site1&quot;
25. # This path should be the site relative URL of the page. If you have the page sub webs, include them in the path
26. $targetPageSiteRelUrl = "/Site1/SitePages/SubSiteLisTest.aspx"
27. $targetWebPartZoneId = "Bottom" # change the ID of the web part zone to match your needs
28. $targetWebPartIndex = 0 # the intended position of the web part in the zone
29.  
30.  
31. if (!$view.ServerObjectIsNull)
32. {
33.     Write-Host View found, exporting WebPart…
34.  
35.     $file = $list.RootFolder.Files.GetByUrl($view.ServerRelativeUrl)
36.     $clientContext.Load($file)
37.  
38.     $webPartManager = $file.GetLimitedWebPartManager([Microsoft.SharePoint.Client.WebParts.PersonalizationScope]::Shared)
39.     $webParts = $webPartManager.WebParts
40.     $clientContext.Load($webParts)
41.     $clientContext.ExecuteQuery()
42.  
43.     # I assume there is a single web part on the view page
44.     # if this assumption is false, you should filter the web parts first
45.     $webPart = $webParts[0]
46.     # https://www.red-gate.com/simple-talk/blogs/getting-the-absolute-url-of-a-file-in-csom/
47.     $viewAbsoluteUrl = (New-Object System.Uri($clientContext.Url)).GetLeftPart([System.UriPartial]::Authority) + $view.ServerRelativeUrl
48.  
49.     $exportWPUrl = $sourceWebUrl + "/_vti_bin/exportwp.aspx?pageurl=" + [System.Web.HttpUtility]::UrlEncode($viewAbsoluteUrl) + "&guidstring=" + $webPart.Id
50.  
51.     $request = [System.Net.WebRequest]::Create($exportWPUrl)
52.     $request.UseDefaultCredentials = $true
53.  
54.     $response = $request.GetResponse()
55.     $reader = New-Object System.IO.StreamReader $response.GetResponseStream()
56.  
57.     $wpXml = [Xml]$reader.ReadToEnd()
58.     $properties = $wpXml.webParts.webPart.data.properties
59.     $webIdProp = $properties.property | ? { $_.name -eq "WebId" }
60.     $webIdProp.InnerText = $sourceWebId
61.  
62.     # "For example, setting the ViewGuid or the Toolbar properties doesn't do anything!" see:
63.     # http://blog.bonzai-intranet.com/analysthq/2014/10/adding-an-xsltlistviewwebpart-with-a-custom-view-using-javascript/
64.     # as a workaround for the missing ViewGuid property, clone the WebId property and change its name / type / value
65.     $viewIdProp = $webIdProp.Clone()
66.     $viewIdProp.name = "ViewGuid"
67.     $viewIdProp.type = "string"    
68.     $viewIdProp.InnerText = $view.ID.ToString("B").ToUpper() # "convert to a format like {8C1D2A1A-5BE8-469D-806E-2112965D2C1C}"
69.  
70.     [Void]$properties.AppendChild($viewIdProp)  
71.  
72.     $wpText = $wpXml.OuterXml
73.  
74.     Write-Host Export completed
75.  
76.     Write-Host Importing WebPart…
77.     
78.     $targetClientContext = New-Object Microsoft.SharePoint.Client.ClientContext($targetWebUrl)
79.     $targetFile = $targetClientContext.Web.GetFileByServerRelativeUrl($targetPageSiteRelUrl)
80.  
81.     $targetWebPartManager = $targetFile.GetLimitedWebPartManager([Microsoft.SharePoint.Client.WebParts.PersonalizationScope]::Shared)
82.     # note the difference:
83.     # the server-side version of ImportWebPart returns a WebPart
84.     # the client-side equvalent of ImportWebPart returns a WebPartDefinition
85.     $targetWebPartDef = $targetWebPartManager.ImportWebPart($wpText)
86.     # you could set optionally the WebId and ViewGuid properties at this point as well, but be aware of the issue with the ViewGuid property I mentioned above..
87.     #$targetWebPartDef.WebPart.Properties["WebId"] = $sourceWebId
88.     #$targetWebPartDef.WebPart.Properties["ViewGuid"] = $view.ID
89.     # note the difference:
90.     # the server-side version of AddWebPart returns void
91.     # the client-side equvalent of AddWebPart returns a WebPartDefinition
92.     [Void]$targetWebPartManager.AddWebPart($targetWebPartDef.WebPart, $targetWebPartZoneId, $targetWebPartIndex)
93.     # or if you need the WebPartDefinition later, you can use these lines
94.     #$targetWebPartDef = $targetWebPartManager.AddWebPart($targetWebPartDef.WebPart, $targetWebPartZoneId, $targetWebPartIndex)
95.     #$targetClientContext.Load($targetFile)
96.     $targetClientContext.ExecuteQuery()
97.     
98.     # write code to check in / publish the page here as required
99.  
100.     Write-Host Import completed
101.  
102. }
103. else
104. {
105.     Write-Host View $viewTitle not found
106. }
107.  
108.  
109. # http://wvg-epm01e.sv-services.at/Test-Site2/_vti_bin/exportwp.aspx?pageurl=/Test-Site2/Lists/TestList/test.aspx&guidstring=8c1d2a1a-5be8-469d-806e-2112965d2c1c

That’s it, you should now be able to copy list views (XsltListViewWebPart web parts) from one SharePoint site to another from client-side via PowerShell. Of course, that is limited to a site collection scope, and there are still known issues with copying list views (generally, not limited to the PowerShell solutions), for example, copying views for a document library with a folder structure seems not to work if  you copy it from a parent site to a sub site. More about that eventually later, as soon as I collect a bit more background information about the problem.

Approving all pending documents (and folders) of a specified library using PowerShell on the Client Side

A few years ago I already wrote about how to approve all pending document in a document library via PowerShell. That time I achieved that using the server side object model of SharePoint. Recently we had a situation, where we were not allowed to log on the server, so we had to do the approval from the client side. To achieve that, I’ve adapted the script to the requirements of the client object model.

Here is the result:

1. $url = "http://YourSharePointServer/Web/SubWeb&quot;
2.  
3. # set path according to your current configuration
4. Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
5. Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
6.  
7.  
8. # set credentials, if the current credentials would not be appropriate
9. #$domain = "YourDomain"
10. #$userName = "YourUserName"
11. #$pwd = Read-Host -Prompt ("Enter password for $domain\$userName") -AsSecureString
12. #$credentials = New-Object System.Net.NetworkCredential($userName, $pwd, $domain);
13.  
14. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
15. #$ctx.Credentials  = $credentials
16.  
17. $web = $ctx.Web
18.  
19.  
20. function approveItems($listTitle)  
21. {
22.   Write-Host Processing $listTitle
23.   $list = $web.Lists.GetByTitle($listTitle)
24.   $query = New-Object Microsoft.SharePoint.Client.CamlQuery
25.   $query.ViewXml = "<View Scope = 'RecursiveAll'><ViewFields><FieldRef Name=\'Name\'/><FieldRef Name=\'_ModerationStatus\'/></ViewFields><Query><Where><Eq><FieldRef Name='_ModerationStatus' /><Value Type='ModStat'>2</Value></Eq></Where></Query></View>"
26.   $items = $list.GetItems($query)
27.   $ctx.Load($items)
28.   $ctx.ExecuteQuery()
29.  
30.   $items | % {
31.       Write-Host Approving:$_["FileLeafRef"]
32.     $_["_ModerationStatus"] = 0
33.     $_.Update()
34.     # if you have an error "The request uses too many resources", call ExecuteQuery here
35.     # $ctx.ExecuteQuery()
36.   }
37.  
38.   $ctx.ExecuteQuery()
39.   Write-Host —————————
40. }
41.  
42. approveItems "TitleOfYourList"

The script assumes, that your current credentials allow you to perform the approval. If it would be not the case, you can comment out the section with credentials in the script, and read for the password of the user having permission to the task. I don’t suggest storing the password in the script.

If the library contains a lot of items waiting for approval, you may get an error message “The request uses too many resources” (see details here). In this case you should call the ExecuteQuery method in the loop for each item, instead of sending the request in a single batch.

Copying Hierarchical Lookup Table Entries via the Managed Object Model

After I’ve described, how to copy flat lookup tables via the Project Server managed object model, this time I will go a step forward, and show how you can copy lookup tables with hierarchy, like the RBS (resource breakdown structure) table.

The complexity of the task (comparing to the flat lookup tables) comes to the fact, that child entries are bound to their respective parent entries not via the IDs (like having a property called ParentId) but simply via the FullValue property. See the properties of the LookupEntry class in the documentation. For example (assuming the separator character used in the code mask is the period “.”), the parent entry of a child entry having its FullValue property like “Division.Subdivision.SubSubdivision” is the entry having a FullValue property like “Division.Subdivision”. Furthermore, the parent entry should be already included in the lookup table, as we inserts its child items, but it seems to be fulfilled by the standard Project Server behavior, as it returns entries in the correct order (parent entries first, their child entries next) for a simply request.

As in the case of the flat tables, we should copy the target entries one by one, by adding new LookupEntryCreationInformation instances to the existing Entries property (of type LooupEntryCollection) of the target lookup table.

Just to make our life a bit harder, in contrast to the LookupEntry class the LookupEntryCreationInformation class has a property ParentId, but no FullValue property at all. It has, however a Value property that you should to set to the value of the child entry, without the joined values of the parent entries. You should set the ParentId property to the value of the Id of the parent entry only if there is a parent entry, otherwise you mustn’t set this property (for example, to null). You can append the LookupEntryCreationInformation instance to the target LooupEntryCollection instance via Add method.

If you would like to get the Id of the parent entry, it would be nice to split the last tag from the FullValue of the current LookupEntry instance to first get the full value of the parent entry (like by splitting SubSubdvision from “Division.Subdivision.SubSubdivision” we would get “Division.Subdivision”, the FullValue of the parent entry), and make a query for the LookupEntry having the same value in the collection of already appended entries afterwards, like this:

parentId = ltTargetEntries.First(e => e.FullValue == parentFullValue).Id;

If you try that, you get the very same exception, that you receive if you try to access a property that you have not explicitly or implicitly requested in the client object model:

An unhandled exception of type ‘Microsoft.SharePoint.Client.PropertyOrFieldNotInitializedException’ occurred in Microsoft.SharePoint.Client.Runtime.dll
Additional information: The property or field ‘FullValue’ has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.

You could request the entire entry collection including the FullValue property of the entries after each update, but it would not be very efficient. Instead of this, we create a dictionary object of type Dictionary<Guid, string> to store a local mapping of the Id – FullValue pairs, and use this mapping to look up the parent entries.

This method assumes the target lookup table already exists, and both of the source and target tables have the same depth / code mask and the period character “.” as separator:

1. private void CopyHierarchicalLookupTableValues(string sourcePwa, string sourceTable, string targetPwa, string targetTable)
2. {
3.     var separator = '.';
4.  
5.     LookupEntryCollection ltSourceEntries = null;
6.     using (var pcSource = new ProjectContext(sourcePwa))
7.     {
8.         pcSource.Load(pcSource.LookupTables, lts => lts.Where(lt => lt.Name == sourceTable).Include(lt => lt.Entries.Include(e => e.FullValue, e => e.Id, e => e.SortIndex)));
9.         pcSource.ExecuteQuery();
10.  
11.         if (pcSource.LookupTables.Any())
12.         {
13.             ltSourceEntries = pcSource.LookupTables.First().Entries;
14.         }
15.         else
16.         {
17.             Console.WriteLine("Source table '{0}' not found on PWA '{1}'", sourceTable, sourcePwa);
18.         }
19.     }
20.  
21.     if (ltSourceEntries != null)
22.     {
23.         using (var pcTarget = new ProjectContext(targetPwa))
24.         {
25.             pcTarget.Load(pcTarget.LookupTables, lts => lts.Where(lt => lt.Name == targetTable).Include(lt => lt.Name));
26.             pcTarget.ExecuteQuery();
27.  
28.             // target table exist
29.             if (pcTarget.LookupTables.Any())
30.             {
31.                 var ltTargetEntries = pcTarget.LookupTables.First().Entries;
32.                 var localIdToFullValueMap = new Dictionary<Guid, string>();
33.  
34.                 // we cannot assign the FullValue property the value that includes the separator characters
35.                 // to avoid LookupTableItemContainsSeparator = 11051 error
36.                 // we should  split the value at separator characters and assign the last item to the Value property and if there is a parent item
37.                 // set the ParentId property as well, see later
38.                 // https://msdn.microsoft.com/en-us/library/office/ms508961.aspx
39.                 ltSourceEntries.ToList().ForEach(lte =>
40.                 {
41.                     var value = lte.FullValue;
42.                     Console.WriteLine("FullValue: '{0}'", value);
43.                     Guid? parentId = null;
44.                     var parentFullValue = string.Empty;
45.  
46.                     var lastIndexOfSeparator = value.LastIndexOf(separator);
47.                     if (lastIndexOfSeparator > -1)
48.                     {
49.                         parentFullValue = value.Substring(0, lastIndexOfSeparator);
50.                         value = value.Substring(lastIndexOfSeparator + 1);
51.                         Console.WriteLine("value: '{0}'", value);
52.                         Console.WriteLine("parentFullValue: '{0}'", parentFullValue);
53.  
54.                         // parent should have been already appended to avoid the error:
55.                         // An unhandled exception of type 'Microsoft.SharePoint.Client.PropertyOrFieldNotInitializedException' occurred in Microsoft.SharePoint.Client.Runtime.dll
56.                         // Additional information: The property or field 'FullValue' has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.
57.                         //parentId = ltTargetEntries.First(e => e.FullValue == parentFullValue).Id;
58.                         parentId = localIdToFullValueMap.First(e => e.Value == parentFullValue).Key;
59.                         Console.WriteLine("parentId: '{0}'", parentId);
60.  
61.                     }
62.  
63.                     // instead creating a new ID, you can copy the existing ID
64.                     // it works only if you copy the entries to another PWA instance,
65.                     // and only if there wasn't already an entry with the same ID
66.                     var id = Guid.NewGuid(); // lte.Id;
67.  
68.                     var leci = new LookupEntryCreationInformation
69.                     {
70.                         Id = id,
71.                         Value = new LookupEntryValue { TextValue = value },
72.                         SortIndex = lte.SortIndex
73.                     };
74.  
75.                     Console.WriteLine("leci Id: '{0}', Value: '{1}'", leci.Id, leci.Value.TextValue);
76.                     var fullValue = value;
77.  
78.                     // we should set the ParentId property only if the entry has really a parent
79.                     // setting the ParentId property to null is not OK
80.                     if (parentId.HasValue)
81.                     {
82.                         leci.ParentId = parentId.Value;
83.                         fullValue = parentFullValue + separator + value;
84.                     }
85.  
86.  
87.                     localIdToFullValueMap.Add(leci.Id, fullValue);
88.  
89.                     ltTargetEntries.Add(leci);
90.                     // if there are a lot of entries, it might be advisable to update and execute query after each of the entries
91.                     // to avoid "The request uses too many resources" error message
92.                     // https://pholpar.wordpress.com/2015/07/19/how-to-avoid-the-request-uses-too-many-resources-when-using-the-client-object-model-via-automated-batching-of-commands/
93.                     // pcTarget.LookupTables.Update();
94.                     // pcTarget.ExecuteQuery();
95.                 });
96.  
97.                 pcTarget.LookupTables.Update();
98.                 pcTarget.ExecuteQuery();
99.             }
100.             else
101.             {
102.                 Console.WriteLine("Target table '{0}' not found on PWA '{1}'", targetTable, targetPwa);
103.             }
104.         }
105.     }
106. }

The following call copies the lookup table RBS from one PWA instance to another one:

CopyHierarchicalLookupTableValues("http://YourProjectServer/PWA&quot;, "RBS", "http://AnotherProjectServer/PWA&quot;, "RBS");

The notes I made for the flat lookup tables apply for the hierarchical case as well:

If your lookup table has not a lot of entries, you can probably copy them in a single batch, using a single call to the ExecuteQuery method. Otherwise, if thee batch size exceeds the 2 MB limit, you might have an exception like “The request uses too many resources”. In this case I suggest you to invoke the ExecuteQuery method for each entry, or create an ExecuteQueryBatch method, as described in this post.

Theoretically, you could copy the entries with their ID, but technically it is not always an option. For example, if you would like to copy the entries within the same PWA instance, you can’t have two entries sharing the same IDs. Based on my experience, if you have already an entry with the same ID, and you would like to copy it into another lookup table, although no exception is thrown, the entry won’t be copied.

And one last additional note yet: Of course, you can copy not only hierarchical lookup tables, but flat lookup tables as well with this script.

Copying Flat Lookup Table Entries via the Managed Object Model

Assume you have in Project Server a flat lookup table (I mean a lookup table having a single level, without any hierarchy between the entries), and you would like to copy the entries to another (already existing!) lookup table, that may exist on the same or on another server / PWA instance. You can do the via the managed object model of Project Server, as demonstrated by the code below:

1. private void CopyLookupTableValues(string sourcePwa, string sourceTable, string targetPwa, string targetTable)
2. {
3.     LookupEntryCollection ltSourceEntries = null;
4.     using (var pcSource = new ProjectContext(sourcePwa))
5.     {
6.         pcSource.Load(pcSource.LookupTables, lts => lts.Where(lt => lt.Name == sourceTable).Include(lt => lt.Masks, lt => lt.Entries.Include(e => e.FullValue, e => e.Id, e => e.SortIndex)));
7.         pcSource.ExecuteQuery();
8.  
9.         if (pcSource.LookupTables.Any())
10.         {
11.             ltSourceEntries = pcSource.LookupTables.First().Entries;
12.         }
13.         else
14.         {
15.             Console.WriteLine("Source table '{0}' not found on PWA '{1}'", sourceTable, sourcePwa);
16.         }
17.     }
18.  
19.     if (ltSourceEntries != null)
20.     {
21.         using (var pcTarget = new ProjectContext(targetPwa))
22.         {
23.             pcTarget.Load(pcTarget.LookupTables, lts => lts.Where(lt => lt.Name == targetTable).Include(lt => lt.Name));
24.             pcTarget.ExecuteQuery();
25.  
26.             // target table exist
27.             if (pcTarget.LookupTables.Any())
28.             {
29.                 var ltTargetEntries = pcTarget.LookupTables.First().Entries;
30.  
31.                 ltSourceEntries.ToList().ForEach(lte => {
32.                     ltTargetEntries.Add(new LookupEntryCreationInformation
33.                         {
34.                             // instead creating a new ID, you can copy the existing ID
35.                             // it works only if you copy the entries to another PWA instance,
36.                             // and only if there wasn't already an entry with the same ID
37.                             Id = Guid.NewGuid(), // lte.Id,
38.                             Value = new LookupEntryValue { TextValue = lte.FullValue },
39.                             SortIndex = lte.SortIndex
40.                         });
41.                     // if you have a lot of entries, it might be better to execute the query for each entries
42.                     // to avoid 'The request uses too many resources' error
43.                     // pcTarget.LookupTables.Update();
44.                     // pcTarget.ExecuteQuery();
45.                 });
46.  
47.                 pcTarget.LookupTables.Update();
48.                 pcTarget.ExecuteQuery();
49.             }
50.             else
51.             {
52.                 Console.WriteLine("Target table '{0}' not found on PWA '{1}'", targetTable, targetPwa);
53.             }
54.         }
55.     }
56. }

The following call copies the lookup table Divisions from one PWA instance to another one:

CopyLookupTableValues("http://YourProjectServer/PWA&quot;, "Divisions", "http://AnotherProjectServer/PWA&quot;, "Divisions");

If your lookup table has not a lot of entries, you can probably copy them in a single batch, using a single call to the ExecuteQuery method. Otherwise, if thee batch size exceeds the 2 MB limit, you might have an exception like “The request uses too many resources”. In this case I suggest you to invoke the ExecuteQuery method for each entry, or create an ExecuteQueryBatch method, as described in this post.

Theoretically, you could copy the entries with their ID, but technically it is not always an option. For example, if you would like to copy the entries within the same PWA instance, you can’t have two entries sharing the same IDs. Based on my experience, if you have already an entry with the same ID, and you would like to copy it into another lookup table, although no exception is thrown, the entry won’t be copied.

The sample above works only for flat (non-hierarchical) lookup tables. You can copy hierarchical lookup tables (like RBS – resource breakdown structure) as well, but it requires a bit more coding, as I show you in the next post.

You can find further sample codes to manipulate Project Server enterprise custom fields and lookup table via the client object model in this older post.

How to get the value of a Project Server Enterprise Custom Field via the Project Server Managed Client Object Model

About two years ago I posted a code about how to set the value of a Project Server Enterprise Field via the managed client OM. Again and again I get the question how to get the value, once it is set already.

In the first case I assume, you already know the ID of your project and the internal name of the field you would like to query. In this case, you need only send a single request to the server, as shown in this code:

1. var url = @"http://YourProjectServer/pwa&quot;;
2. var projectContext = new ProjectContext(url);
3.  
4. var projId = new Guid("98138ffd-d0fa-e311-83c6-005056b45654");
5. var cfInternalName = "Custom_b278fdf35d16e4119568005056b45654";
6.  
7. var proj = projectContext.Projects.GetByGuid(projId);
8. projectContext.Load(proj, p => p[cfInternalName], p => p.Name);
9.  
10. projectContext.ExecuteQuery();
11.  
12. Console.WriteLine(proj.Name, proj.FieldValues[cfInternalName]);

If either the ID of your project or the internal name of the field is unknown, you need an extra round-trip before the query shown in the previous code to determine their value. In the code below I assume you know none of these values:

1. var url = @"http://YourProjectServer/pwa&quot;;
2. var projectContext = new ProjectContext(url);
3. var projName = "Your Project Name";
4. var fieldName = "NameOfTheField";
5.  
6. projectContext.Load(projectContext.Projects, ps => ps.Include(p => p.Id, p => p.Name));
7. projectContext.Load(projectContext.CustomFields, cfs => cfs.Include(cf => cf.InternalName, cf => cf.Name));
8. projectContext.ExecuteQuery();
9.  
10. var projId = projectContext.Projects.First(p => p.Name == projName).Id;
11. var cfInternalName = projectContext.CustomFields.First(cf => cf.Name == fieldName).InternalName;
12.  
13. var proj = projectContext.Projects.GetByGuid(projId);
14. projectContext.Load(proj, p => p[cfInternalName], p => p.Name);
15.  
16. projectContext.ExecuteQuery();
17.  
18. Console.WriteLine(proj.Name, proj.FieldValues[cfInternalName]);

I hope it helps to read the custom field values, for example the values set by the code in the former post.

Getting the Item Count of all Lists of all Sub-Sites via a Single Request from Client Code

Recently I had a task to get the item count of all lists of all Project Web Sites (PWS) of a Project Server instance from a client-side application. Note, that the PWSs are located directly under the Project Web Access (PWA) site, so there is no deeper site structure in this task to deal with, so I was pretty sure that it can be achieved in a single request. Although in my case the task was Project Server related, one can use the same method in the case of SharePoint Server as well, it is only important, that you should not have a multiple level site structure, for a deeper site structure this method simply does not work.

I show you both the REST (OData) and the managed client object model approach. Let’s start with the client OM sample:

1. string siteUrl = "http://YourProjectServer/PWA&quot;;
2. using (var clientContext = new ClientContext(siteUrl))
3. {
4.     var rootWeb = clientContext.Web;
5.  
6.     clientContext.Load(rootWeb, rw => rw.Webs.Include(w => w.Title, w => w.ServerRelativeUrl, w => w.Lists.Include(l => l.Title, l => l.ItemCount)));
7.     clientContext.ExecuteQuery();
8.  
9.     foreach(var web in rootWeb.Webs)
10.     {
11.         if (web.Lists.Any())
12.         {
13.             Console.WriteLine("Lists of web '{0}' [{1}]", web.Title, web.ServerRelativeUrl);
14.             foreach (var list in web.Lists)
15.             {
16.                 Console.WriteLine("'{0}' [Item count: {1}]", list.Title, list.ItemCount);
17.             }
18.         }
19.     }
20. }

The corresponding REST query can be submitted as a GET request sent to this URL:

http://YourProjectServer/PWA/_api/web/webs?$expand=Lists&$select=ServerRelativeUrl,Title,Lists/ItemCount,Lists/Title

If you need the item count only from a specific list (for example, the lists with title ‘Risks’) for all subsites, you can easily achieve that in the client OM sample by including a Where clause in the query:

clientContext.Load(rootWeb, rw => rw.Webs.Include(w => w.Title, w => w.ServerRelativeUrl, w => w.Lists.Include(l => l.Title, l => l.ItemCount).Where(l => l.Title == "Risks")));

The corresponding REST query would be:

http://YourProjectServer/PWA/_api/web/webs?$expand=Lists&$filter=Lists/Title eq ‘Risks’&$select=ServerRelativeUrl,Title,Lists/ItemCount,Lists/Title

However, when submitting this request I get a response with status HTTP 400 and the message: The field or property ‘Title’ does not exist.

I’m working on a solution and update this post as soon as I found one. Feel free to help me by sending it as a comment.

How to restrict the available users in a ‘Person or Group’ field to Project Server resources?

Assume you have a task list in your Project Web Access (PWA) site or on one of the Project Web Sites (PWS) in your Project Server and you would like to restrict the users available in the Assigned To field (field type of  ‘Person or Group‘) to users who are specified as Enterprise Resource in Project Server, that is running in the “classical” Project Server permission mode, and not in the new SharePoint Server permission mode. There is no synchronization configured between Active Directory groups and Project Server resources.

You can limit a ‘Person or Group‘ field to a specific SharePoint group, but there is no built-in solution to sync enterprise resources to SharePoint groups. In this post I show you, how to achieve that via PowerShell and the managed client object models of Project Server and SharePoint.

Note: You could get the login names of users assigned to the enterprise resources via REST as well (http://YourProjectServer/PWA/_api/ProjectServer/EnterpriseResources?$expand=User&$select=User/LoginName), but in my sample I still use the client object model of  Project Server.

My goal was to create a PowerShell solution, because it makes it easy to change the code on the server without any kind of compiler. I first created a C# solution, because the language elements of C# (like extension methods, generics and LINQ) help us to write compact, effective and readable code. For example, since the language elements of PowerShell do not support the LINQ expressions, you cannot simply restrict the elements and their properties returned by a client object model request, as I illustrated my former posts here, here and here. Having the working C# source code, I included it in my PowerShell script as literal string and built the .NET application at runtime, just as I illustrated in this post. In the C# code I utilized an extension method to help automated batching of the client object model request. More about this solution can be read here.

The logic of the synchronization is simple: we read the list of all non-generic enterprise resources, and store the login names (it the user exists) as string in a generic list. Then read the members of the SharePoint group we are synchronizing and store their login names as string in another generic list. Finally, we add the missing users to the SharePoint group and remove the extra users from the group.

The final code is included here:

1. $pwaUrl = "http://YourProjectServer/PWA&quot;;
2. $grpName = "AllResourcesGroup";
3.  
4. $referencedAssemblies = (
5.     "Microsoft.SharePoint.Client, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c",
6.     "Microsoft.SharePoint.Client.Runtime, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c",
7.     "Microsoft.ProjectServer.Client, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c",
8.     "System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
9.  
10. $sourceCode = @"
11. using System;
12. using System.Linq;
13. using System.Collections.Generic;
14. using Microsoft.SharePoint.Client;
15. using Microsoft.ProjectServer.Client;
16.  
17. public static class Extensions
18. {
19.     // batching to avoid
20.     // Microsoft.SharePoint.Client.ServerException: The request uses too many resources
21.     // https://msdn.microsoft.com/en-us/library/office/jj163082.aspx
22.     public static void ExecuteQueryBatch<T>(this ClientContext clientContext, IEnumerable<T> itemsToProcess, Action<T> action, int batchSize)
23.     {
24.         var counter = 1;
25.  
26.         foreach (var itemToProcess in itemsToProcess)
27.         {
28.             action(itemToProcess);
29.             counter++;
30.  
31.             if (counter > batchSize)
32.             {
33.                 clientContext.ExecuteQuery();
34.                 counter = 1;
35.             }
36.         }
37.  
38.         if (counter > 1)
39.         {
40.             clientContext.ExecuteQuery();
41.         }
42.     }
43. }
44.  
45. public static class Helper
46. {
47.     public static void SyncGroupMembers(string pwaUrl, string grpName)
48.     {
49.         List<string> resLogins = new List<string>();
50.         List<string> grpLogins = new List<string>();
51.         var batchSize = 20;
52.  
53.         using (var projectContext = new ProjectContext(pwaUrl))
54.         {
55.             var resources = projectContext.EnterpriseResources;
56.             projectContext.Load(resources, rs => rs.Where(r => !r.IsGeneric).Include(r => r.User.LoginName));
57.  
58.             projectContext.ExecuteQuery();
59.  
60.             resLogins.AddRange(resources.ToList().Where(r => r.User.ServerObjectIsNull == false).ToList().Select(r => r.User.LoginName.ToLower()));               
61.         }
62.         using (var clientContext = new ClientContext(pwaUrl))
63.         {
64.             var web = clientContext.Web;
65.  
66.             var grp = web.SiteGroups.GetByName(grpName);
67.             clientContext.Load(grp, g => g.Users.Include(u => u.LoginName));
68.  
69.             clientContext.ExecuteQuery();
70.  
71.             grpLogins.AddRange(grp.Users.ToList().ToList().Select(u => u.LoginName.ToLower()));
72.  
73.             var usersToAdd = resLogins.Where(l => !grpLogins.Contains(l));
74.             clientContext.ExecuteQueryBatch<string>(usersToAdd,
75.                 new Action<string>(loginName =>
76.                 {
77.                     var user = web.EnsureUser(loginName);
78.                     grp.Users.AddUser(user);
79.                 }),
80.                 batchSize);
81.  
82.             var usersToRemove = grpLogins.Where(l => !resLogins.Contains(l));
83.             clientContext.ExecuteQueryBatch<string>(usersToRemove,
84.                 new Action<string>(loginName =>
85.                 {
86.                     grp.Users.RemoveByLoginName(loginName);
87.                 }),
88.                 batchSize);
89.         }
90.     }
91. }
92.  
93. "@
95. Add-Type -ReferencedAssemblies $referencedAssemblies -TypeDefinition $sourceCode -Language CSharp;
96.  
97. [Helper]::SyncGroupMembers($pwaUrl, $grpName)

How to avoid ‘The request uses too many resources’ when using the client object model via automated batching of commands

One of the reasons, I prefer the client object model of SharePoint to the REST interface, is its capability of batching requests.

For example, you can add multiple users to a SharePoint group using the code below, and it is sent as a single request to the server:

1. using (var clientContext = new ClientContext(url))
2. {
3.     var web = clientContext.Web;
4.     var grp = web.SiteGroups.GetByName("YourGroup");
5.  
6.     var usersToAdd = new List<string>() { @"i:0#.w|domain\user1", @"i:0#.w|domain\user2" };
7.  
8.     foreach (var loginName in usersToAdd)
9.     {
10.         var user = web.EnsureUser(loginName);
11.         grp.Users.AddUser(user);
12.     }
13.  
14.     clientContext.ExecuteQuery();
15. }

However, as the number of  users you would like to add increases, you might have issues, as the operational requests in your batch are exceeding the 2 MB limit.

How could we solve the problem relative painless, avoiding the error, and still keeping our code readable?

The good news is that it is easy to achieve using extension method, generic, and the Action class. We can extend the ClientContext with an ExecuteQueryBatch method, and pass the list of parameter values to be processed in an IEnumerable, the action to be performed, and the count of  items should be processed in a single batch. The method splits the parameter values into batches, calling the ExecuteQuery method on the ClientContext for each batch.

If the action, you would perform on the client objects has a single parameter (as in our case above, the login name is a single parameter of type String), the ExecuteQueryBatch method can be defined as:

1. public static class Extensions
2. {
3.     public static void ExecuteQueryBatch<T>(this ClientContext clientContext, IEnumerable<T> itemsToProcess, Action<T> action, int batchSize)
4.     {
5.         var counter = 1;
6.  
7.         foreach (var itemToProcess in itemsToProcess)
8.         {
9.             action(itemToProcess);
10.  
11.             counter++;
12.             if (counter > batchSize)
13.             {
14.                 clientContext.ExecuteQuery();
15.                 counter = 1;
16.             }
17.         }
18.  
19.         if (counter > 1)
20.         {
21.             clientContext.ExecuteQuery();
22.         }
23.     }
24. }

Having the ExecuteQueryBatch method in this form, the original code can be modified:

1. var batchSize = 20;
2.  
3. using (var clientContext = new ClientContext(url))
4. {
5.     var web = clientContext.Web;
6.     var grp = web.SiteGroups.GetByName("YourGroup");
7.  
8.     var usersToAdd = new List<string>() { @"i:0#.w|domain\user1", @"i:0#.w|domain\user2" /* and a lot of other logins */ };
9.  
10.     clientContext.ExecuteQueryBatch<string>(usersToAdd,
11.         new Action<string>(loginName =>
12.         {
13.             var user = web.EnsureUser(loginName);
14.             grp.Users.AddUser(user);
15.         }),
16.         batchSize);
17.  
18.     clientContext.ExecuteQuery();
19. }

The size of batch you can use depends on the complexity of the action. For a complex action should be the batch smaller. The ideal value should you find experimentally.

Actions with multiple parameter require additional overloads of the the ExecuteQueryBatch extension method.

In my next post I’ll illustrate how to utilize this extension method in a practical example.

Breaking Changes in Project Server Update?

Recently I have extended the Visual Studio solution, that includes the code sample for a former blog post, illustrating how to register Project Server event handlers via the managed client object model.

When I wanted to build the solution, the build was broken because of a compile time error in the code I have not change since last November:

‘Microsoft.ProjectServer.Client.EventHandlerCreationInformation’ does not contain a definition for ‘CancelOnError’

I’ve opened the corresponding assembly (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.ProjectServer.Client.dll) in Reflector,  and found that there is really no CancelOnError property defined on the class Microsoft.ProjectServer.Client.EventHandlerCreationInformation.

Based on the official documentation, this property should exist, and I’m sure I was able to compile my code, so it existed at that time.

Our developer environment was recently patched from the SP1 patch level to the February 2015 PU patch level, so it must have been “lost” that time.

I found another VM that is at the RTM level, checked the same class in the same assembly, and found the property defined:

Note: the problem affects not only the managed client object model, but the JavaScript object model and the server-side object model as well. It should affect the out-of-the box feature receiver described in my former post, and all of the packages that rely on this functionality.

We have the same issue on the server side as well. Both of the the classes Microsoft.ProjectServer.EventHandlerCreationInformation and Microsoft.ProjectServer.EventHandler (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\CONFIG\BIN\Microsoft.ProjectServer.dll) had the CancelOnError property in the previous version, but it is simply no more defined without any official warning on the change, causing both server side and client side code referring to this property to fail.