none
Best approach to upload a list of object to a SharePoint list? RRS feed

  • Question

  • I defined a SharePoint list I would like to update through my C# code, and I am wondering what the best approach would be.

    I am working actually with SharePoint Online and Microsoft.SharePoint.Client v16.x within VS2015.

    Thus, I follow the MSDN recommandations with SharePoint lists: https://msdn.microsoft.com/en-us/library/office/fp179912.aspx

    I a bit confused with the 'SQL like' approach of the CSOM Framework while I would have expect a more 'object oriented' approach.

    Anyway, my current generic approach is to read a generic List<T>, read all public properties at one side; read the items on the SP List and their fields names on the other side. The SP field names should match the public properties of my object.

    Here is my code, using generic and reflection:

     private int UploadItemsToList<T>(ClientOM.ClientContext clientContext, string listTitle, Dictionary<int, T> dictSource)
            {
                ClientOM.List list = clientContext.Web.Lists.GetByTitle(listTitle);
    
                ClientOM.CamlQuery query = ClientOM.CamlQuery.CreateAllItemsQuery();
                ClientOM.ListItemCollection items = list.GetItems(query);
    
                // Retrieve all items in the ListItemCollection from List.GetItems(Query). 
                clientContext.Load(items);
                clientContext.ExecuteQuery();
    
                clientContext.Load(list.Fields);
                clientContext.ExecuteQuery();
    
                // get fields per name            
                Dictionary<string, PropertyInfo> propertiesPerNames = Reflection.Utils.GetAllPropertiesOfClass<T>().ToDictionary(p => p.Name);
    
                HashSet<int> idUpdated = new HashSet<int>();
    
                int itemsModified = 0;
    
                var itemsToDelete = new List<ClientOM.ListItem>();
    
                // Update existing items
                foreach (ClientOM.ListItem listItem in items)
                {
                    // read the employeeId of existing item
                    var employeeIdObj = listItem["EmployeeId"];
    
                    int employeeId;
                    if (employeeIdObj != null && int.TryParse(employeeIdObj.ToString(), out employeeId) && dictSource.ContainsKey(employeeId))
                    {
                        var itemSource = dictSource[employeeId];
                        Dictionary<string, object> currentSpValues = listItem.FieldValues;
                        if (UpdateSPListItem(propertiesPerNames, itemSource, listItem, currentSpValues))
                        {
                            clientContext.ExecuteQuery();
                            itemsModified++;
                        }
                        idUpdated.Add(employeeId);
                    }
                    else
                    {
                        // delete row with employeeId not found in the source
                        itemsToDelete.Add(listItem);
                    }
                }
    
                // Delete items
                if (itemsToDelete.Count > 0)
                {
                    itemsModified += itemsToDelete.Count;
                    itemsToDelete.DeleteItemsInList(item => item.DeleteObject());
                    clientContext.ExecuteQuery();
                }
    
                // Create items
                var itemCreateInfo = new ClientOM.ListItemCreationInformation();
                var allNewPersonalIds = dictSource.Keys.Where(id => !idUpdated.Contains(id)).ToList();
                foreach (int employeeId in allNewPersonalIds)
                {
                    // id defined in the source but not found in the current SP list: create item!                
                    var newItem = list.AddItem(itemCreateInfo);
                    if (UpdateSPListItem(propertiesPerNames, dictSource[employeeId], newItem, null))
                    {
                        clientContext.ExecuteQuery();
                        itemsModified++;
                    }
                }
    
                return itemsModified;
            }
    
            private bool UpdateSPListItem<T>(Dictionary<string, PropertyInfo> propertiesPerNames, T itemSource, ClientOM.ListItem listItem, Dictionary<string, object> currentSpValues)
            {
                bool updated = false;
                foreach (string propertyName in propertiesPerNames.Keys)
                {
                    // read source value
                    var sourceValue = propertiesPerNames[propertyName].GetValue(itemSource);
    
                    bool updateField = currentSpValues == null;
    
                    if (currentSpValues != null && currentSpValues.ContainsKey(propertyName))
                    {
                        var currentSpValue = currentSpValues[propertyName];
                        updateField = currentSpValue == null || (sourceValue != null && currentSpValue != null && currentSpValue.ToString() != sourceValue.ToString());
                    }
    
                    if (updateField)
                    {
                        listItem[propertyName] = sourceValue;
                        listItem.Update();
                        if (!updated)
                            updated = true;
                    }
                }
                return updated;
            }

    It works, but it is very slow: over 200 seconds for 150 items, so over 1s per item.
    It seems to me that over 90% of the time is in the clientContext.ExecuteQuery() calls!
    Is there any better approach, or is SharePoint online always that slow?
    I also serialize that list into CSV and upload the file into SharePoint, and it takes 20 seconds, also slow!

    Thanks for your advices!


    • Edited by EricBDev Wednesday, April 5, 2017 10:30 AM
    Wednesday, April 5, 2017 10:27 AM

All replies

  • Hi,

    I think you should update your logic to support Batch Request instead of create/update one by one.

    https://msdn.microsoft.com/en-us/library/ff798420.aspx

    Here is another thread for your reference.

    http://www.vrdmn.com/2013/07/batch-operations-using-javascript.html

    Check the ‘Rules and best practices for using the SharePoint .NET client object model’ section, it may help you optimize your logic performance.

    https://msdn.microsoft.com/en-us/library/office/fp179912(v=office.15).aspx

    Here is one thread mentioned REST api is outperform CSOM, but the performance could be related to network status/list data amount etc, you could test them and  select proper one based on your case.

    http://blog.mannsoftware.com/?p=1521

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, April 6, 2017 2:53 AM
  • Thanks for your answer.

    How deal "Request Batchting" with the max amount of data on the line?

    Because I initially put way less 'clientContext.ExecuteQuery();' in my code but had then many times the error in the next one that my data are too much and cannot get processed, with a limit of 2 MB I think.

    Quite incredible that the SQL approach of CSOM it that verbose, my whole list as text is about 50 kB of data, and CSOM tells me I am many times transfering over 2 MB for just a part of it ??

    I can understand the whole query text takes a lot (a for Select from bla, update / insert bla), but that much? It reminds me the advantage of json / rest over xml / web services also often critisized to be too verbose. So maybe I should try the REST API instead of CSOM, just a bit reluctant to stat again the same stuff, so first would like to give CSOM a better chance.
    • Edited by EricBDev Thursday, April 6, 2017 8:25 AM
    Thursday, April 6, 2017 8:21 AM
  • Hi,

    You’re right, SharePoint has a 2 MB limit for an operations request based on official document below.

    https://msdn.microsoft.com/en-us/library/office/jj163082.aspx

    So you may need limit the data rows for each batch request(may 100/200 rows each) if you use CSOM approach.

    Anyway, if you prefer CSOM approach, you could share your solution here. So it may help others who has similar requirements.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, April 10, 2017 9:32 AM