Post
Ask Question

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.

listview

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.

view

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:

  1. public StringBuilder ReadListSchema(List list)  
  2. {  
  3.     StringBuilder script = new StringBuilder();  
  4.     var listFieldCollection = list.Fields;  
  5.     _context.Load(listFieldCollection);  
  6.     _context.ExecuteQuery();  
  7.     script.AppendFormat("CREATE TABLE [{0}] (", list.Title);  
  8.   
  9.     foreach(var field in listFieldCollection)  
  10.     {  
  11.         if (FilterColumn(field))  
  12.             CreateScript(field.Title.Replace(' ', '_'), field.FieldTypeKind, script);  
  13.   
  14.   
  15.     }  
  16.     script.Append("[Created_By] Varchar(MAX), [Modified_By] Varchar(MAX), [Modified] Varchar(MAX), [Created] Varchar(MAX)");  
  17.     script.Append(");");  
  18.     return script;  
  19. }  
  20.   
  21. private void CreateScript(string colName, FieldType colType, StringBuilder script)  
  22. {  
  23.     switch (colType)  
  24.     {  
  25.         case FieldType.Boolean:  
  26.             script.AppendFormat("[{0}] {1},", colName, "BIT");  
  27.         break;  
  28.         case FieldType.DateTime:  
  29.             script.AppendFormat("[{0}] {1},", colName, "DATETIME");  
  30.         break;  
  31.         caseFieldType.Integer:  
  32.             script.AppendFormat("[{0}] {1},", colName, "Int");  
  33.         break;  
  34.         case FieldType.Lookup:  
  35.             script.AppendFormat("[{0}] {1},", colName, "Varchar(MAX)");  
  36.         break;  
  37.         case FieldType.Attachments:  
  38.             script.AppendFormat("[{0}] {1},", colName, "Varchar(MAX)");  
  39.         break;  
  40.         caseFieldType.Number:  
  41.             script.AppendFormat("[{0}] {1},", colName, "Varchar(MAX)");  
  42.         break;  
  43.         default:  
  44.         script.AppendFormat("[{0}] {1},", colName, "Varchar(MAX)");  
  45.         break;  
  46.     }  
  47. }  
  48. private bool FilterColumn(Field field)  
  49. {  
  50.     if (!field.SchemaXml.Contains("BdcField"))  
  51.     {  
  52.         if (field.FieldTypeKind == FieldType.Computed || field.Hidden || field.Sealed)  
  53.         {  
  54.             return false;  
  55.         }  
  56.         elseif(field.ReadOnlyField && field.FieldTypeKind != FieldType.Lookup)  
  57.         {  
  58.             return false;  
  59.         }  
  60.         elseif(!field.CanBeDeleted && field.InternalName != "Attachments")  
  61.         {  
  62.             return false;  
  63.         }  
  64.         return true;  
  65.     } else  
  66.     {  
  67.         returntrue;  
  68.     }  
  69. }  
ReadListSchema Method takes the List to be archived as a parameter and it returns the script to create the SQL Table. Here, I am loading all the Fields in the List then looping each field to create the script.

There is a FilterColumn method which filters the unwanted SharePoint generated columns. And then the CreateScript method just appends the string Builder object.

Note: I will continue with the rest of the application in the next part of this article. Stay tuned.
 
Read more articles on SharePoint:

 


Similar Articles