Archive SharePoint List Items to SQL Database Using CSOM: Part One

Introduction:

SharePoint List contains important information and when SharePoint List View items count goes above its threshold value, archiving the old items becomes important. In this article we will learn how to archive the SharePoint List Items to SQL Table using CSOM.

Scenario:

When a SharePoint List View goes beyond its threshold limit, you want to keep the old records as well as make sure you do not cross the SharePoint List threshold. To overcome list view threshold we can create a list view which narrow the results under threshold value. But what if the List Item count is more than 50 thousand, your SharePoint content database grows bigger with the every new item added.



In this article we are going to archive the list items in SQL table outside of SharePoint database.

Prerequisite:

• SQL Server
• SharePoint with more than its threshold limit

Solution:

The following are the key things to be done:

1. Create a List view which will have the items to be archived.
2. Create the SQL Table based on the List Schema.
3. Perform the Archive Job using CSOM.

The purpose of creating List view is to avoid the CamlQuery for filtering the List Items. We can just get the View Query and pull the data from List.



Now this one is tricky, creating the SQL table based on list Schema. By default SharePoint creates default read-only hidden fields other than the custom fields we add. For example: Created By and Modified By are useful. But there are many other columns which are not usable for this application. You can filter what columns you want to have in yourTABLE.

Below code snippet reads the List Schema and generate the Create Table Script: