How to change values of ListItems recursively in Sharepoint 2013 using CSOM

Question

My Situation

Hi, my client has a Sharepoint Server 2013 on Premise. He wants to write metadata on multiple files. More specifically he wants to choose a folder and then the application should write the same value in a column for every file/folder in that folder.

What I already did

I wrote a CAML-query that returns all files/folder below the chosen folder. I then write the value for each item and update it.

What I want to do

I want to write an application where my client can choose a folder. After that the software should update all files under that folder. I want to write this application using C# and CSOM.

My Problem

This is very slow. With multiple thousands of files per folder it takes hours just for one folder to complete.

My Questions

Is there a way to speed things up?
Can CAML-Query also update values?
What is the recommended way of doing what I want to do?
Is there another way of achieving what my client wants?

Thanks for your help.

Answer 1

You could take advantage of feature called Request Batching, which in turn could dramatically affect the performance by minimizing the number of messages that are passed between the client and the server.

From the documentation:

The CSOM programming model is built around request batching. When you work with the CSOM, you can perform a series of data operations on the ClientContext object. These operations are submitted to the server in a single request when you call the ClientContext.BeginExecuteQuery method.

For comparison lets demonstrate two ways the updating of multiple list items:

Standard way:

foreach (var item in items)
{
   item["LastReviewed"] = DateTime.Now;
   item.Update();
   ctx.ExecuteQuery();
}

Batched update:

foreach (var item in items)
{
   item["LastReviewed"] = DateTime.Now;
   item.Update();
}
ctx.ExecuteQuery();

Note: in the second approach only a single batched request is submitted to the server

Complete example

var listTitle = "Documents";
var folderUrl = "Archive/2013";

using (var ctx = GetContext(url, userName, password))
{
     //1. Get list items operation
     var list = ctx.Web.Lists.GetByTitle(listTitle);
     var items = list.GetListItems(folderUrl);
     ctx.Load(items);
     ctx.ExecuteQuery();


     //2. Update list items operation
     var watch = Stopwatch.StartNew();
     foreach (var item in items)
     {
           if(Convert.ToInt32(item["FSObjType"]) == 1) //skip folders
               continue;

           item["LastReviewed"] = DateTime.Now;
           item.Update();
           //ctx.ExecuteQuery();
           Console.WriteLine("{0} has been updated", item["FileRef"]);
     }
     ctx.ExecuteQuery();  //execute batched request
     watch.Stop();
     Console.WriteLine("Update operation completed: {0}", watch.ElapsedMilliseconds); 

     Console.ReadLine();
}

where GetListItems is the extension method for getting list items located under a specific folder:

using System.Linq;
using Microsoft.SharePoint.Client;

namespace SharePoint.Client.Extensions
{
    public static class ListCollectionExtensions
    {

        /// <summary>
        /// Get list items located under specific folder 
        /// </summary>
        /// <param name="list"></param>
        /// <param name="relativeFolderUrl"></param>
        /// <returns></returns>
        public static ListItemCollection GetListItems(this List list, string relativeFolderUrl)
        {
            var ctx = list.Context;
            if (!list.IsPropertyAvailable("RootFolder"))
            {
                ctx.Load(list.RootFolder, f => f.ServerRelativeUrl);
                ctx.ExecuteQuery();
            }
            var folderUrl = list.RootFolder.ServerRelativeUrl + "/" + relativeFolderUrl;
            var qry = CamlQuery.CreateAllItemsQuery();
            qry.FolderServerRelativeUrl = folderUrl;
            var items = list.GetItems(qry);
            return items;
        }
    }
}