Introduction
In many enterprise applications, accessing data from SharePoint lists is a common requirement. However, when dealing with large lists that exceed SharePoint's default threshold limit, developers often encounter challenges in efficiently retrieving all the data. In this blog post, we'll explore how to overcome this limitation using C# and the .NET framework.
- Problem Statement: SharePoint imposes a default threshold limit of 5000 items for any given list. When attempting to retrieve data from a list that exceeds this limit, developers face the dilemma of how to efficiently fetch all the items without hitting this threshold.
- Solution Overview: We'll leverage the SharePoint CSOM (Client Side Object Model) in combination with C# to retrieve data from a SharePoint list that contains more than 4800 items. By using iterative queries and specifying a suitable CAML (Collaborative Application Markup Language) query, we can efficiently fetch data in manageable chunks without hitting the threshold limit.
Code Walkthrough
Step 1. Installing PnP.Framework: Before diving into the code, ensure you have the PnP.Framework installed. You can download it from the NuGet Package Manager for your solution. Simply navigate to Tools -> NuGet Package Manager -> Manage NuGet Packages for Solution, and search for "PnP.Framework" (version 1.14.0).
Step 2. Setting up Authentication and Connection, We start by setting the SharePoint site URL, client ID, client secret, and database connection string for authentication and data access from appsettings.json.
Step 3. Generate Sharepoint site context.
Using the Authentication Manager to get the context of the SharePoint site, we obtain an authenticated context for interacting with SharePoint.
using PnP.Framework;
public async Task ProcessCandidateInformationList(string? SiteUrl, string? ClientID, string? ClientSecret, string? ConnectionString)
{
try
{
//refere this blog to generate Client ID & Client Secret
//https://www.c-sharpcorner.com/blogs/sharepoint-integration-registering-site-for-client-id-secret
// Setting SharePoint site URL, client ID, client secret, and database connection string
SiteUrl = "https://(Domain Name).com/sites/(List Name)";
ClientID = "********-****-****-****-************";
ClientSecret= "*******************************************=";
ConnectionString = "Data Source=(Server Name);Initial Catalog=(Database Name);user id=(Username for connection on db);password=(Password);TrustServerCertificate=True;";
// Authenticating with SharePoint
using (ClientContext context = new AuthenticationManager().GetACSAppOnlyContext(SiteUrl, ClientID, ClientSecret))
{
ListItemCollection listItems;
// Retrieving target SharePoint list
List targetList = context.Web.Lists.GetByTitle("(List Name)");
CamlQuery query = new CamlQuery
{
ViewXml = $"<View Scope='RecursiveAll'><RowLimit>4800</RowLimit></View>"
};
// Iteratively fetching list items
do
{
listItems = targetList.GetItems(query);
context.Load(listItems);
context.ExecuteQuery();
// Processing retrieved items
foreach (var item in listItems)
{
// Outputting item details (for demonstration purposes)
Console.WriteLine(item);
}
query.ListItemCollectionPosition = listItems.ListItemCollectionPosition;
} while (listItems.ListItemCollectionPosition != null);
}
}
catch (Exception ex)
{
// Handling exceptions
Console.WriteLine("An error occurred in ProcessCandidateInformationList: " + ex);
}
}
Step 4. Retrieve the target SharePoint list using its title.
Step 5. Fetch data in manageable chunks by employing a CAML query with a row limit of 4800 items.
Step 6. Utilize iterative querying to fetch items batch by batch until all items are retrieved.
Step 7. Process each batch of items according to the application's requirements.
Conclusion
Efficiently fetching large SharePoint lists in C# using .NET is critical for enterprise applications. By leveraging SharePoint CSOM and C#, developers can overcome the default threshold limit and fetch data in manageable chunks. Implementing iterative querying and CAML queries ensures efficient data retrieval. Additionally, registering the application with SharePoint to obtain a client ID and client secret enhances security and access control. Overall, these steps empower developers to seamlessly integrate SharePoint data into their .NET applications, enhancing productivity and functionality.
Creating Client ID and Client Secret for SharePoint Site