Sharepoint Online Csom: Getting Root folders from document library in a paginated response

0

I have created a C# console application that loads in a document library, at the root of this document library we have our client folders. I pass a client folder through two functions that simply check the contents of these folders and deletes them if they are empty. That part was simple enough. The issue is that our library exceeds the 5000 view limit for document libraries. Breaking it down isn't an option as I have begged for that over the years. I am able to pull down all items in rows of 100 from the library into a listItemCollection using this guide here: https://morgantechspace.com/2017/08/get-all-files-from-sharepoint-document-library-csom.html

The issue with this is that I need to start from the root folder level and this puts all folders and files in the library into a single list. My program can easily dive through Client folders if I hand it the top level folder. but handling all files from a single list would be massively painful I believe.

I am currently using the following block to get all Folders in the root of the document library, what we call Client folders.

 static void Main(string[] args)
        {
            string userName = "MyUserName";
            Console.WriteLine("Enter Your Password Please  ------- ");
            SecureString password = GetPasswordOfYourSite();
            string webUri = "https://conferencetechinc.sharepoint.com/sites/yoda";
            List<ListItem> items = new List<ListItem>();


            using (ClientContext clientContext = new ClientContext(webUri))
            {

                int rowLimit = 100;
                var camlQuery = new CamlQuery();
                camlQuery.ViewXml = @"<View Scope='Recursive'>
                    <Query>
                    </Query>
                    <RowLimit Paged='TRUE'>" + rowLimit + "</RowLimit></View>";

                clientContext.Credentials = new SharePointOnlineCredentials(userName, password);

                // This value is NOT List internal name
                List targetList = clientContext.Web.Lists.GetByTitle("TestingLibrary");

                // This method only gets the folders which are on top level of the list/library
                FolderCollection oFolderCollection = targetList.RootFolder.Folders;

                // Load folder collection

                clientContext.ExecuteQuery();

                //Write Folder name and folder URl on console
                foreach (Folder client in oFolderCollection)
                {
                    if (client.Name != "Forms")
                    {
                        Console.WriteLine("Folder Name: " + client.Name);
                          // YAY! Here we have the client level.  We must now break them down from here.
                        // Pass them to a method that injests the next level of folders - Opportunity, once we are at project or order level,
                        // we then pass the folder to a generic recursive fileChecker which returns a bool for deletion in the ClientFileDiver
                        clientFileDiver(client, clientContext);
                    }

                }
            }

I know I need to apply the Caml Query to get my desired results of returning the Folders in the Root of the Library in paginated responses, but I am unsure of how to apply it or if it's possible to apply it to targetList.RootFolder. I just started learning Sharepoint CSOM this week and am enjoying it so any help is highly appreciated. Hope I put enough information in here.

****Continued****

 using (ClientContext clientContext = new ClientContext(webUri))
            {

                int rowLimit = 1;
                var camlQuery = new CamlQuery();
                camlQuery.ViewXml = @"<RowLimit Paged='TRUE'>" + rowLimit + "</RowLimit></View>";

                clientContext.Credentials = new SharePointOnlineCredentials(userName, password);
                ListItemCollectionPosition position = null;

                // This value is NOT List internal name
                List targetList = clientContext.Web.Lists.GetByTitle("Clients");
                   // FolderCollection oFolderCollection = targetList.RootFolder.Folders;
                   // clientContext.Load(oFolderCollection);
                    //clientContext.ExecuteQuery();
                do
                {
                    ListItemCollection listItems = null;
                    camlQuery.ListItemCollectionPosition = position;
                    listItems = targetList.GetItems(camlQuery);
                    clientContext.Load(listItems);
                    clientContext.ExecuteQuery();
                    position = listItems.ListItemCollectionPosition;
                    items.AddRange(listItems.ToList());

                    foreach (ListItem item in listItems)
                    {
                        clientContext.Load(item);
                        clientContext.Load(item.Folder);
                        clientContext.ExecuteQuery();
                        if(item.Folder.GetType() == typeof(Folder))
                        {
                            Console.WriteLine(item.Folder.GetType());
                            Console.WriteLine(item.Folder.Name);
                            Console.ReadKey();
                        }

                    }

                }
                while (position != null);

This seems to be closer to what I need I believe but I am still hitting the following:

    Microsoft.SharePoint.Client.ServerException
  HResult=0x80131500
  Message=The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.
  Source=Microsoft.SharePoint.Client.Runtime
  StackTrace:
   at Microsoft.SharePoint.Client.ClientRequest.ProcessResponseStream(Stream responseStream)
   at Microsoft.SharePoint.Client.ClientRequest.ProcessResponse()
   at Microsoft.SharePoint.Client.ClientRequest.ExecuteQueryToServer(ChunkStringBuilder sb)
   at Microsoft.SharePoint.Client.ClientRequest.ExecuteQuery()
   at Microsoft.SharePoint.Client.ClientRuntimeContext.ExecuteQuery()
   at Microsoft.SharePoint.Client.ClientContext.ExecuteQuery()
   at ConsoleApp1.Program.Main(String[] args) in C:\Users\andre\source\repos\ConsoleApp1\ConsoleApp1\Program.cs:line 48

  This exception was originally thrown at this call stack:
    [External Code]
    ConsoleApp1.Program.Main(string[]) in Program.cs

Although I am only requesting 1 row in the request. Unless I am requesting that wrong am I to assume that it just looks at the overall size of the Document Library and it just says "Nope too big" even if we only request the information in parts? I also may be applying the Caml query wrong but from what I've learned today that should be the way to only apply a row limit to the data. maybe there's something else I am missing though?

sharepoint
csom
caml
asked on Stack Overflow May 7, 2020 by Andrew Gerstner • edited May 7, 2020 by Andrew Gerstner

1 Answer

0

The issue is mainly because of the Caml, change it to below:

<View Scope='RecursiveAll'><RowLimit>5000</RowLimit></View>

Here is the complete sample code:

            using (ClientContext ctx = new ClientContext("https://zheguo.sharepoint.com/"))
            {

                ctx.Credentials = new SharePointOnlineCredentials(account, secret);
                List list = ctx.Web.Lists.GetByTitle("docs1");

                ListItemCollectionPosition position = null;
                var page = 1;

                do
                {
                    CamlQuery camlQuery = new CamlQuery();
                    camlQuery.ViewXml = @"<View Scope='RecursiveAll'><RowLimit>5000</RowLimit></View>";
                    camlQuery.ListItemCollectionPosition = position;
                    camlQuery.FolderServerRelativeUrl = "/docs1/";
                    var collListItem = list.GetItems(camlQuery);
                    ctx.Load(collListItem, listitems => listitems.Include(
                        item => item.Id, item => item["FileRef"])
                        , listitems => listitems.ListItemCollectionPosition);
                    ctx.ExecuteQuery();

                    position = collListItem.ListItemCollectionPosition;
                    foreach (var listItem in collListItem)
                    {
                        Console.WriteLine(listItem["FileRef"]);
                    }

                    page++;
                }
                while (position != null);
    }

Reference:

SharePoint Online: How to Get All List Items from Large Lists ( >5000 Items)

answered on Stack Overflow May 8, 2020 by Jerry_MSFT

User contributions licensed under CC BY-SA 3.0