Second Life of a Hungarian SharePoint Geek

June 25, 2017

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.

June 20, 2017

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.

March 15, 2016

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

Filed under: Managed Client OM, Project Server — Tags: , — Peter Holpar @ 22:20

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.

February 27, 2016

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

Filed under: Managed Client OM, OData, Project Server, SP 2013 — Tags: , , , — Peter Holpar @ 14:43

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. Winking smile

July 19, 2015

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

Filed under: Managed Client OM, PowerShell, PS 2013, SP 2013 — Tags: , , , — Peter Holpar @ 21:33

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. "@
  94. Add-Type -ReferencedAssemblies $referencedAssemblies -TypeDefinition $sourceCode -Language CSharp;
  95.  
  96. [Helper]::SyncGroupMembers($pwaUrl, $grpName)

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

Filed under: Managed Client OM, SP 2013 — Tags: , — Peter Holpar @ 21:32

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.

April 13, 2015

Breaking Changes in Project Server Update?

Filed under: Managed Client OM, Project Server — Tags: , — Peter Holpar @ 21:02

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’

image

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.

image

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:

image

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.

April 8, 2015

Automating the Deployment of a Customized Project Web Site Template via PowerShell and the Managed Client Object Model

Filed under: ALM, Managed Client OM, PowerShell, Project Server — Tags: , , , — Peter Holpar @ 21:45

Assume, you have created a customized web site template for your enterprise project type in the development environment as described here, and now you would like to deploy it into the test farm. Of course, you can manually delete the former site template, upload the new one, re-configure it to be the associated web site template for your enterprise project type, and finally re-create your test project (that means, checking in and deleting the existing one, and create it again using the new template), but this procedure is boring, cumbersome and – as any human-based process – rather error-prone.

Why do not automate this step as well?

I’ve created a PowerShell script that performs the steps outlined above. The first steps (deleting the former version of the site template and uploading the new one) can be done by native PowerShell Cmdlets, but for the remaining, Project Server related tasks require the Managed Client Object Model, so we import the necessary assemblies into the process.

First we get a list of all projects and a list of all enterprise project types, then query for the right ones on the “client side”.

Note: Although PowerShell does not support .NET extension methods (like the Where and Include methods of the client object model) natively, we could restrict the items returned by these queries to include really only the item we need (see solution here), and include only the properties we need (as described here). As the item count of the projects and enterprise project types is not significant, and we should use the script on the server itself due to the SharePoint Cmdlets, it has no sense in this case to limit the network traffic via these tricks.

Next, we update the web site template setting (WorkspaceTemplateName  property) of the enterprise project type. We need this step as the original vale was reset to the default value as we deleted the original site template on re-upload.

If the test project is found, we delete it (after we checked it in, if it was checked out), and create it using the updated template.

Since these last steps (project check-in, deletion, and creation) are all queue-based operations, we should use the WaitForQueue method to be sure the former operation is completed before we start the next step.

$pwaUrl = "http://YourProjectServer/PWA/&quot;
$solutionName = "YourSiteTemplate"
$wspFileName = $solutionName + ".wsp"
$timeoutSeconds = 1000
$projName = "TestProj"

# English
$projType = "Enterprise Project"
$pwaLcid = 1033
# German
#$projType = "Enterprise-Projekt"
#$pwaLcid = 1031

# path of the folder containing the .wsp
$localRootPath = "D:\SiteTemplates\"
$wspLocalPath = $localRootPath + $wspFileName

# uninstall / remove the site template if activated / found
$solution = Get-SPUserSolution -Identity $wspFileName -Site $pwaUrl -ErrorAction SilentlyContinue
If ($solution -ne $Null) {
  If ($solution.Status -eq "Activated") {
    Write-Host Uninstalling web site template
    Uninstall-SPUserSolution -Identity $solutionName -Site $pwaUrl -Confirm:$False
  }
  Write-Host Removing web site template
  Remove-SPUserSolution -Identity $wspFileName -Site $pwaUrl -Confirm:$False
}

# upload and activate the new version
Write-Host Uploading new web site template
Add-SPUserSolution -LiteralPath $wspLocalPath -Site $pwaUrl
Write-Host Installing new web site template
$dummy = Install-SPUserSolution -Identity $solutionName -Site $pwaUrl
 
# set the path according the location of the assemblies
Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.ProjectServer.Client.dll"
Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

$projectContext = New-Object Microsoft.ProjectServer.Client.ProjectContext($pwaUrl)

# get lists of enterprise project types and projects
$projectTypes = $projectContext.LoadQuery($projectContext.EnterpriseProjectTypes)
$projects = $projectContext.Projects
$projectList = $projectContext.LoadQuery($projectContext.Projects)

$projectContext.ExecuteQuery()

$entProjType = $projectTypes | ? { $_.Name -eq $projType }
$project = $projectList | ? { $_.Name -eq $projName }

Write-Host Updating web site template for the enterprise project type
$web = Get-SPWeb $pwaUrl
$template = $web.GetAvailableWebTemplates($pwaLcid) | ? { $_.Title -eq $solutionName }

$entProjType.WorkspaceTemplateName = $template.Name
$projectContext.EnterpriseProjectTypes.Update()
$projectContext.ExecuteQuery()

If ($project -ne $Null) {
  If ($project.IsCheckedOut) {
    Write-Host Project $projName is checked out, checking it in before deletion
    $checkInJob = $project.Draft.CheckIn($True)
    $checkInJobState = $projectContext.WaitForQueue($checkInJob, $timeoutSeconds)
    Write-Host Check-in project job status: $checkInJobState
  }
  Write-Host Deleting existing project $projName
  # we can delete the project either this way
  #$removeProjResult = $projects.Remove($project)
  #$removeJob = $projects.Update()
  # or
  $removeJob = $project.DeleteObject()
  $removeJobState = $projectContext.WaitForQueue($removeJob, $timeoutSeconds)
  Write-Host Remove project job status: $removeJobState
}

I found the set of Project Server PowerShell Cmdlets is limited, and rather operation-based. You can use it, as long as your single task is to administer Project Server instances and databases. However, when it comes to the interaction with Project Server entities, you have to involve the Managed Client Object Model. Hopefully this example provides not only a reusable tool, but also helps you understand, how to extend your own PowerShell library with the methods borrowed from the client side .NET libraries.

Automating Project Server development tasks via PowerShell and the Client Object Model – Customizing Project Web Site templates

Filed under: ALM, Managed Client OM, PowerShell, Project Server — Tags: , , , — Peter Holpar @ 21:35

I start with a note this time: Even though you were not interested in Project Server itself at all, I suggest you to read the post further, while most of the issues discussed below are not Project Server specific, they apply to SharePoint as well.

Recently I work mostly on a Project Server customization project. As I’ve learned on my former development projects, I try to automate so much repetitive tasks as possible (like automating the PWA provisioning), thus remains more time for the really interesting stuff. I plan to post my results on this blog to share the scripts and document the experiences for myself as well.

One of the very first tasks (and probably a never-ending one) was to create a customized Project Web Site (PWS) site template. New Enterprise Projects created in the Project Web Access (PWA) should have their PWS created based on the custom site template.

The process of customizing a PWS site template is described in this post, however, there are a few issues if we apply this approach alone, just to name a few of them:

– PWS master pages cannot be edited using SharePoint Designer by default. There is a workaround for this issue.

– If I create a custom master page for the PWA and would like a PWS to refer the same master page, I can set it for example using PowerShell. However, if I create a site template from this PWS, this configuration seems to be lost in the template, and template refers to the default seattle.master. I have similar experience with the site image / logo, I can set one, but this setting seems to be not saved in the template.

– The standard navigation structure of a project site (and all site template created based on it) contains project-specific navigation nodes, like Project Details that contains the Guid of the current project as a query string parameter. If you create a site template from this site, any project sites that will be created based on this template will contain this node twice: one of the is created based on the site template (wrong Guid, referring to the project the original site belongs to, thus wrong URL), and another one is created dynamically as the project web site gets provisioned.

The workflow of my web site template creation and customization process includes four main parts, and two of them – step 2 and step 4 – are automated by our script.

The first part of the process (including step 1 and step 2) is optional. If you have changed nothing in your web site prototype, you can immediately start with the manual manipulation of the extracted web site template content (step 3), otherwise, we have to get a fresh version of the template into our local system for the further customizations.

Step 1: Creation and customization a SharePoint web site, that serves as a prototype for the web site template.

A SharePoint web site is customized based on the requirements using the SharePoint web UI, SharePoint Designer (for PWA see this post), or via other tools, like PowerShell scripts (for example, JSLink settings). This is a “manual” task.

Step 2: Creation of the web site template based on the prototype, downloading and extracting the site template.

A site template is created (including content) based on the customized web site. If a former site template having the same name already exists, if will be deleted first.

The site template is downloaded to the local file system (former file having the same name is deleted first).

The content of the .wsp file (CAB format) is extracted into a local folder (folder having the same name is deleted first, if it exists).

Step 3: Customization of the extracted web site template artifacts.

The script is paused. In this step you have the chance to manual customization of the solution files, like ONet.xml.

Step 4: Compressing the customized files into a new site template, and uploading it to SharePoint.

After a key press the script runs further.

Files having the same name as our site template and extension of .cab or .wsp will be deleted. The content of the folder is compressed as .cab and the renamed to .wsp.

In the final step the original web site template is removed and the new version is installed.

Next, a few words about the CAB extraction and compression tools I chose for the automation. Minimal requirements were that the tool must have a command line interface and it should recognize the folder structure to be compressed automatically, without any helper files (like the DDF directive file in case of makecab).

After reading a few comparisons (like this and this one) about the alternative options, I first found IZArc and its command line add-on (including IZARCC for compression and IZARCE for extraction, see their user’s manual for details) to be the best choice. However after a short test I experienced issues with the depth of the folder path and file name length in case of IZARCE, so I fell back to extrac32 for the extraction.

Finally, the script itself:

$pwaUrl = "http://YourProjectServer/PWA/&quot;
$pwsSiteTemplateSourceUrl = $pwaUrl + "YourPrototypeWeb"
$solutionName = "YourSiteTemplate"
$wspFileName = $solutionName + ".wsp"
$cabFileName = $solutionName + ".cab"
$siteTemplateTitle = $solutionName
$siteTemplateName = $solutionName
$siteTemplateDescription = "PWS Website Template"

$localRootPath = "D:\SiteTemplates\"
$wspExtractFolderName = $solutionName
$wspExtractFolder = $localRootPath + $wspExtractFolderName
$wspFilePath = $localRootPath + $wspFileName
$wspLocalPath = $localRootPath + $wspFileName
$wspUrl = $pwaUrl + "_catalogs/solutions/" + $wspFileName

$cabFilePath = $localRootPath + $cabFileName

function Using-Culture (
   [System.Globalization.CultureInfo]   $culture = (throw "USAGE: Using-Culture -Culture culture -Script {…}"),
   [ScriptBlock]
   $script = (throw "USAGE: Using-Culture -Culture culture -Script {…}"))
   {
     $OldCulture = [Threading.Thread]::CurrentThread.CurrentCulture
     $OldUICulture = [Threading.Thread]::CurrentThread.CurrentUICulture
         try {
                 [Threading.Thread]::CurrentThread.CurrentCulture = $culture
                 [Threading.Thread]::CurrentThread.CurrentUICulture = $culture
                 Invoke-Command $script
         }
         finally {
                 [Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
                 [Threading.Thread]::CurrentThread.CurrentUICulture = $OldUICulture
         }
   }

function Remove-SiteTemplate-IfExists($solutionName, $wspFileName, $pwaUrl) 
{
  $us = Get-SPUserSolution -Identity $solutionName -Site $pwaUrl -ErrorAction SilentlyContinue
  if ($us -ne $Null)
  {
    Write-Host Former version of site template found on the server. It will be removed…
    Uninstall-SPUserSolution -Identity $solutionName -Site $pwaUrl -Confirm:$False
    Remove-SPUserSolution -Identity $wspFileName -Site $pwaUrl -Confirm:$False
  }
}

function Remove-File-IfExists($path)
{
  If (Test-Path $path)
  {
    If (Test-Path $path -PathType Container)
    {
      Write-Host Deleting folder: $path
      Remove-Item $path -Force -Recurse
    }
    Else
    {
      Write-Host Deleting file: $path
      Remove-Item $path -Force
    }
  }
}

Do { $downloadNewTemplate = Read-Host "Would you like to get a new local version of the site template to edit? (y/n)" }
Until ("y","n" -contains $downloadNewTemplate )

If ($downloadNewTemplate -eq "y")
{

    Remove-SiteTemplate-IfExists $solutionName $wspFileName $pwaUrl

    Using-Culture de-DE { 
     Write-Host Saving site as site template including content
     $web = Get-SPWeb $pwsSiteTemplateSourceUrl
     $web.SaveAsTemplate($siteTemplateName, $siteTemplateTitle, $siteTemplateDescription, 1)
   }

  Remove-File-IfExists $cabFilePath

  Write-Host Downloading site template
  $webClient = New-Object System.Net.WebClient
  $webClient.UseDefaultCredentials  = $True 
  $webClient.DownloadFile($wspUrl, $cabFilePath)

  # clean up former version before downloading the new one
  # be sure you do not lock the deletion, for example, by having one of the subfolders opened in File Explorer,
  # or via any file opened in an application
  Remove-File-IfExists $wspExtractFolder

  Write-Host Extracting site template into folder $wspExtractFolder
  #
http://updates.boot-land.net/052/Tools/IZArc%20MANUAL.TXT
  # limited file lenght / folder structure depth! 😦
  #& "C:\Program Files (x86)\IZArc\IZARCE.exe" -d $cabFilePath $wspExtractFolder

  #http://researchbin.blogspot.co.at/2012/05/making-and-extracting-cab-files-in.html
  #expand $cabFilePath $wspExtractFolder -F:*.*
  extrac32 /Y /E $cabFilePath /L $wspExtractFolder
}

Write-Host "Alter the extracted content of the site template, then press any key to upload the template…"
# wait any key press without any output to the console
#
http://technet.microsoft.com/en-us/library/ff730938.aspx
$dummy = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

# clean up former version before creating the new one
# TODO rename it using a date time pattern instead of deletion!
Remove-File-IfExists $cabFilePath
Remove-File-IfExists $wspFilePath

# makecab: we cannot include multiple files directly. To do that, we have to create a directive file called a Diamond Directive File(DDF) and include instructions in it
#
http://comptb.cects.com/automate-compression-tasks-cli/
& "C:\Program Files (x86)\IZArc\IZARCC.exe" -a -r -p $cabFilePath $wspExtractFolder

Rename-Item $cabFilePath $wspFileName

# remove former solution before uploading and activating the new one
Remove-SiteTemplate-IfExists $solutionName $wspFileName $pwaUrl

Write-Host Installing the new version of the site template
Add-SPUserSolution -LiteralPath $wspFilePath -Site $pwaUrl
$dummy = Install-SPUserSolution -Identity $solutionName -Site $pwaUrl

Note: If you are working with the English version of the PWA and have an English operating system on the server, you don’t need the Using-Culture function. To learn more about it see this post.

March 23, 2015

How to Read the Values of Fields bound to Lookup Tables via the Client Object Model

Filed under: Managed Client OM, Project Server — Tags: , — Peter Holpar @ 05:16

Assume you have an Enterprise Custom Field (let’s call this ECFResField’) defined for project resources, that is bound to a Lookup Table.

How can we read the textural values of the field as it is assigned to you resources? After all, what makes reading such values makes any different than getting field values without lookup tables?

If we have a look at a resource with a lookup table based custom field via an OData / REST query (for example, by http://YourProjectServer/pwa/_api/ProjectServer/EnterpriseResources), you can see, that the value is stored as a reference, like ‘’Entry_4d65d905cac9e411940700505634b541‘.

image

If we access the value via the managed client OM, we get it as a string array, even if only a single value can be selected from the lookup table. The reference value in the array corresponds to the value in the InternalName property of the lookup table entry. If we know the ID of the resource (we want to read the value from), the enterprise custom field (that means we know its internal name as well) and the related lookup table, we can get the result in a single request as shown below:

  1. using (var projectContext = new ProjectContext(pwaUrl))
  2. {
  3.     var lookupTableId = "4c65d905-cac9-e411-9407-00505634b541";
  4.     var resourceId = "f9497d1d-9145-e411-9407-00505634b541";
  5.  
  6.     var res = projectContext.EnterpriseResources.GetById(resourceId);
  7.     var lt = projectContext.LookupTables.GetById(lookupTableId);
  8.     var cfInternalName = "Custom_80bd269ecbc9e411940700505634b541";
  9.     projectContext.Load(res, r => r[cfInternalName]);
  10.     projectContext.Load(lt.Entries);
  11.     projectContext.ExecuteQuery();
  12.  
  13.     var valueEntries = res[cfInternalName] as string[];
  14.     if (valueEntries != null)
  15.     {
  16.         foreach (var valueEntry in valueEntries)
  17.         {
  18.             var lookupText = lt.Entries.FirstOrDefault(e => e.InternalName == valueEntry) as LookupText;
  19.             var ltValue = (lookupText != null) ? lookupText.Value : null;
  20.             Console.WriteLine("Value: '{0}' (Entry was '{1}')", ltValue, valueEntry);
  21.         }
  22.     }
  23. }

However, if these values are unknown, and we know only the name of the resource and the field, we need to submit an extra request to get the IDs for the second step:

  1. using (var projectContext = new ProjectContext(pwaUrl))
  2. {
  3.     var resourceName = "Brian Cox";
  4.     var fieldName = "ResField";
  5.  
  6.     projectContext.Load(projectContext.EnterpriseResources, ers => ers.Include(r => r.Id, r => r.Name));
  7.     projectContext.Load(projectContext.CustomFields, cfs => cfs.Include(f => f.Name, f => f.InternalName, f => f.LookupTable.Id, f => f.LookupEntries));
  8.  
  9.     projectContext.ExecuteQuery();
  10.  
  11.     var resourceId = projectContext.EnterpriseResources.First(er => er.Name == resourceName).Id.ToString();
  12.     var cf = projectContext.CustomFields.First(f => f.Name == fieldName);
  13.     var cfInternalName = cf.InternalName;
  14.     var lookupTableId = cf.LookupTable.Id.ToString();
  15.  
  16.     var res = projectContext.EnterpriseResources.GetById(resourceId);
  17.     var lt = projectContext.LookupTables.GetById(lookupTableId);
  18.  
  19.     projectContext.Load(res, r => r[cfInternalName]);
  20.     projectContext.Load(lt.Entries);
  21.     projectContext.ExecuteQuery();
  22.  
  23.     var valueEntries = res[cfInternalName] as string[];
  24.     if (valueEntries != null)
  25.     {
  26.         foreach (var valueEntry in valueEntries)
  27.         {
  28.             var lookupText = lt.Entries.FirstOrDefault(e => e.InternalName == valueEntry) as LookupText;
  29.             var ltValue = (lookupText != null) ? lookupText.Value : null;
  30.             Console.WriteLine("Value: '{0}' (Entry was '{1}')", ltValue, valueEntry);
  31.         }
  32.     }
  33. }

Note: although this post was about a custom field defined for the resource entity, you can apply the same technique for project and task fields as well.

Older Posts »

Create a free website or blog at WordPress.com.