Efficient SharePoint file management with .NET CSOM: Folder creation, file retrieval, and archiving

In Dynamics CRM development, Power Automate often serves as our go-to tool for streamlining processes and integrating systems. But what happens when we encounter edge cases like handling massive datasets or implementing intricate business logic that Power Automate simply can’t accommodate? This is where leveraging custom code becomes not just helpful but essential.

We recently encountered a challenge while working on SharePoint file management. Using the CSOM library in .NET, we automated tasks like creating folders, retrieving files, and archiving data seamlessly.

In this blog, we will explain how to handle these scenarios effectively, use SharePoint file management, and share real-world examples to make your development journey smoother.

ClientContex in SharePoint

In SharePoint development, the ClientContext object establishes a connection between your application and the SharePoint site. It serves as the gateway for performing operations like loading data, executing queries, and accessing site resources (e.g., folders, files, and lists).

In our scenario, we created the ClientContext in another class and passed it to the methods.

Here is the code for this:

public static ClientContext ClientContext
{
get { return ConnectToSharepoint(siteUrl, username, password); }
}

public static Web Web
{
get { return ConnectToSharepoint(siteUrl, username, password).Web; }
}

public static ClientContext ConnectToSharepoint(string siteUrl, string username, string password)
{
try
{
ObjectCache cache = MemoryCache.Default;
if (cache.Contains(“sharepointService”))
{
// _log.Info(“Get CRM Service from Cache”);
_service = (ClientContext)cache.Get(“sharepointService”);
return _service;
}

    try
    {
        ClientContext clientContext = new ClientContext(siteUrl);
        clientContext.Credentials = new SharePointOnlineCredentials(username, password.ToSecureString());

        // Add to Cache
        CacheItemPolicy cacheItemPolicy = new CacheItemPolicy();
        cacheItemPolicy.AbsoluteExpiration = DateTime.Now.AddHours(1.0);

        cache.Add("sharepointService", clientContext, cacheItemPolicy);
        return clientContext;
    }
    catch (Exception ex) 
    {
        throw new Exception($"Unable to Connect to SharePoint. Please verify your configuration for SharePoint connection");
    }

    return null;
}
catch (Exception ex)
{
    // Console.WriteLine("Error while connecting to CRM " + ex.Message);
    // Console.ReadKey();
    throw ex;
}

}

Creating folders in SharePoint

When it comes to file management in SharePoint and managing folders programmatically, the CSOM (Client-Side Object Model) in .NET proves to be a powerful tool.

Developers can handle hierarchical structures dynamically by designing a reusable method for folder creation. Here’s an approach we used, designed to create folders within SharePoint, ensuring each layer of the hierarchy is properly checked and developed.

Method overview

To streamline folder creation, we designed a method called CreateSharepointFolders. The goal was to:

• Take user inputs, such as the folder path, date, and facility details
• Dynamically create a folder structure based on the year, month, and additional subfolders

public static void CreateSharepointFolders(string FolderPath, DateTime FolderCreationDate, string FacilityCode, ClientContext clientContext, ITracingService tracingService)
{
try
{
tracingService.Trace(“Starting CreateSharepointFolders method”);
tracingService.Trace($”Parameters: FolderPath={FolderPath}, FolderCreationDate={FolderCreationDate}, FacilityCode={FacilityCode}”);

    string year = FolderCreationDate.Year.ToString();
    string month = FolderCreationDate.ToString("MMM");
    List<string> subFolderList = FacilityCode.Split(',').ToList();
    subFolderList.Add("Archive");
    string[] subFolderNames = subFolderList.ToArray();

    tracingService.Trace($"Year: {year}, Month: {month}");
    tracingService.Trace($"SubFolders to create: {string.Join(", ", subFolderNames)}");

    Web web = clientContext.Web;

    // Get root folder
    Folder rootFolder = web.GetFolderByServerRelativeUrl(FolderPath);
    clientContext.Load(rootFolder);
    clientContext.ExecuteQuery();
    tracingService.Trace("Root folder loaded successfully");

    if (rootFolder != null)
    {
        // Check if Year folder exists, if not, create it
        Folder yearFolder;
        try
        {
            yearFolder = web.GetFolderByServerRelativeUrl($"{FolderPath}/{year}");
            clientContext.Load(yearFolder);
            clientContext.ExecuteQuery();
            tracingService.Trace($"Year folder '{year}' already exists");
        }
        catch (ServerException)
        {
            // Folder does not exist, so create it
            tracingService.Trace($"Year folder '{year}' does not exist, creating it");
            yearFolder = rootFolder.Folders.Add(year);
            clientContext.Load(yearFolder);
            clientContext.ExecuteQuery();
            tracingService.Trace($"Year folder '{year}' created successfully");
        }

        // Create Month folder inside Year folder
        Folder monthFolder;
        try
        {
            monthFolder = web.GetFolderByServerRelativeUrl($"{FolderPath}/{year}/{month}");
            clientContext.Load(monthFolder);
            clientContext.ExecuteQuery();
            tracingService.Trace($"Month folder '{month}' already exists");
        }
        catch (ServerException)
        {
            // Folder does not exist, so create it
            tracingService.Trace($"Month folder '{month}' does not exist, creating it");
            monthFolder = yearFolder.Folders.Add(month);
            clientContext.Load(monthFolder);
            clientContext.ExecuteQuery();
            tracingService.Trace($"Month folder '{month}' created successfully");
        }

        // Create subfolders in the Month folder
        tracingService.Trace("Starting to create subfolders");
        foreach (string subFolderName in subFolderNames)
        {
            tracingService.Trace($"Creating subfolder: {subFolderName.Trim()}");
            Folder subFolder = monthFolder.Folders.Add(subFolderName.Trim());
            clientContext.Load(subFolder);
        }
        clientContext.ExecuteQuery(); // Execute all folder creations at once
        tracingService.Trace("All subfolders created successfully");
    }

    tracingService.Trace("CreateSharepointFolders method completed successfully");
}
catch (Exception ex)
{
    tracingService.Trace($"Error in CreateSharepointFolders: {ex.Message}");
    tracingService.Trace($"Stack trace: {ex.StackTrace}");
    throw;
}

}

Retrieving Excel files from SharePoint

After successfully creating folders, the next step in SharePoint file management best practices is retrieving and processing stored files. We needed to retrieve Excel files from a structured folder path and read their contents into a DataTable for further processing.

Method overview

To retrieve and read Excel files, we implemented the RetrieveSharepointFiles method, which:

• Navigates through a given folder path in SharePoint
• Filters out Excel files (.xlsx, .xls)
• Reads the first sheet of each Excel file into a DataTable
• Returns a list of DataTable objects for further analysis

Here’s the method that fetches Excel files from a specific folder structure:

public List RetrieveSharepointFiles(string folderpath, DateTime Date, string facilityCode, ClientContext clientContext)
{
string folderPath = $”{folderpath}/{Date.Year.ToString()}/{Date.ToString(“MMM”)}/{facilityCode}”;
List listDataTable = new List();
Web web = clientContext.Web;

try
{
    clientContext.Load(web, w => w.ServerRelativeUrl);
    clientContext.ExecuteQuery();
    var SiteName = web.ServerRelativeUrl;

    // Get the folder containing the files
    Folder folder = web.GetFolderByServerRelativeUrl($"{SiteName}{folderPath}");
    clientContext.Load(folder);
    clientContext.ExecuteQuery();

    // Get all files inside the folder
    FileCollection files = folder.Files;
    clientContext.Load(files);
    clientContext.ExecuteQuery();

    List<string> excelFiles = new List<string>();
    foreach (File file in files)
    {
        if (file.Name.EndsWith(".xlsx") || file.Name.EndsWith(".xls"))
        {
            excelFiles.Add(file.Name);
        }
    }

    // If Excel files exist, read them
    if (excelFiles.Count > 0)
    {
        CRMHelper cRMHelper = new CRMHelper();

        foreach (string excelFile in excelFiles)
        {
            listDataTable.Add(cRMHelper.ReadExcelFile(clientContext, folderPath, excelFile, SiteName));
        }

        return listDataTable;
    }
    else
    {
        // "No Excel files found for the given year and month."
    }
}
catch (ServerException ex)
{
    // "Error accessing SharePoint folder: {ex.Message}"
}

return null;

}

Once the Excel files are retrieved, the next step is reading their contents into a DataTable. This allows for efficient data querying and manipulation.

Here’s the method that reads Excel files:

public DataTable ReadExcelFile(ClientContext clientContext, string folderPath, string _fileName, string SiteName)
{
string documentLibrary = folderPath;
string fileName = _fileName;
List columnHeader = new List();
Web web = clientContext.Web;

// Get the file from SharePoint
File file = web.GetFileByServerRelativeUrl($"{SiteName}{documentLibrary}/{fileName}");
clientContext.Load(file);
clientContext.ExecuteQuery();

ClientResult<Stream> streamResult = file.OpenBinaryStream();
clientContext.ExecuteQuery();

// Read the Excel file directly from the stream
using (Stream fileStream = streamResult.Value)
{
    if (fileStream != null)
    {
        using (var reader = ExcelReaderFactory.CreateReader(fileStream))
        {
            var excelConfig = new ExcelDataSetConfiguration
            {
                ConfigureDataTable = _ => new ExcelDataTableConfiguration
                {
                    UseHeaderRow = true
                }
            };

            DataSet result = reader.AsDataSet(excelConfig);
            DataTable sheet = result.Tables[0];
            return sheet;

            // foreach (DataColumn col in sheet.Columns)
            // {
            //     columnHeader.Add(col.ColumnName);
            // }
        }
    }
    else
    {
        // "Failed to open the file stream."
    }
}

return null;

}

Why use DataTable for Excel processing?

The DataTable class provides a structured way to work with tabular data in memory. It offers:

• Efficient Data Querying
• Sorting & Filtering
• Aggregation

Archiving Excel files in SharePoint

Efficient file management includes retrieving files and systematically archiving them. Archiving helps maintain an organized folder structure, ensuring that older files are moved to a designated location without cluttering active directories.

Here’s the method that archives files:

public void ArchiveExcelFile(ClientContext clientContext, string sourcePath, string destinationPath, DateTime date, string facilityCode)
{
string folderPath = $”{sourcePath}/{date.Year.ToString()}/{date.ToString(“MMM”)}/{facilityCode}”;
Web web = clientContext.Web;

clientContext.Load(web, w => w.ServerRelativeUrl);
clientContext.ExecuteQuery();
var siteName = web.ServerRelativeUrl;

// Get the folder containing the files
Folder folder = web.GetFolderByServerRelativeUrl($"{siteName}{folderPath}");
clientContext.Load(folder);
clientContext.ExecuteQuery();

// Get all files inside the folder
FileCollection files = folder.Files;
clientContext.Load(files);
clientContext.ExecuteQuery();

string destinationFolderUrl = $"{siteName}{destinationPath}/{date.Year.ToString()}/{date.ToString("MMM")}/Archive";

foreach (var file in files)
{
    string sourceFileUrl = file.ServerRelativeUrl; // Get the relative URL of the file
    string destinationFileUrl = $"{destinationFolderUrl}/{file.Name}";

    // Move the file
    File sourceFile = clientContext.Web.GetFileByServerRelativeUrl($"{siteName}{sourceFileUrl}");
    sourceFile.MoveTo(destinationFileUrl, MoveOperations.Overwrite);
    clientContext.ExecuteQuery();

    // tracingService.Trace($"File '{file.Name}' has been moved successfully.");
}

}

Conclusion

By implementing automated archiving, we simplify SharePoint file management, ensuring documents are stored in a structured, easily retrievable format. This efficient approach reduces manual effort and improves long-term data accessibility. Need help optimizing SharePoint for better file management? Contact us at marketing@confiz.com.