Archive SharePoint List Items To SQL Database using CSOM - Part Two

Prashant Verma
8y
21k
0
2

Article

GB.ListToSQLArchiver.zip

Before reading this article, please go through the following article series:

Archive SharePoint List Items to SQL Database using CSOM - Part One

In the Part One of this article we generated table script. Now we can execute this script to create the SQL Table based on list schema.

2 of 3 key tasks are completed.

Create a List view which will have the items to be archived.
Create the SQL Table based on the List Schema.

Now the most important and complex part is to perform the Archival Operation. Before moving ahead make sure your Table is created in the database.

We are going to perform the following actions:

Generate the Insert Query.
Dynamically populate the Insert Query with the List Item column values.
Loop on the View Data and archive all the items into the Table and Delete It from SharePoint List.

Below code snippet generates the Insert Query:

public StringBuilder GenerateInsertQuery(List list, string viewName)
{
StringBuilder sbInsertParm = newStringBuilder();
var listFieldCollection = list.Fields;
list.Context.Load(listFieldCollection);
list.Context.ExecuteQuery();
int count = 1;
foreach(var field in listFieldCollection)
{
try
{
if (FilterColumn(field))
{
var fieldtitle = field.Title.Replace(' ', '_');
sbInsertParm.AppendFormat("@{0}", "p" + count++);
sbInsertParm.Append(",");
sbInsertTable.AppendFormat("[{0}]", fieldtitle);
sbInsertTable.Append(",");
}
} catch (Exception)
{
throw;
}
}
sbInsertParm.Remove(sbInsertParm.Length - 1, 1);
sbInsertTable.Append(",");
}
sbInsertTable.Remove(sbInsertTable.Length - 1, 1);
return sbInsertTable;
}

In this code, I am loading all the List Field Collection and Looping into it, same as creating table script so that the columns generated during CreateTableScript method and column in insert query is the same.

Note: FilterColumn method is same as CreateTableScript Method.

Now we generated the insert query, we are going to perform the Archival operation in the below code snippet:

public void ArchiveAndDelete(string insertcmd, List list, string ViewName)
{
_context = list.Context asClientContext;
_context.RequestTimeout = System.Threading.Timeout.Infinite;
var view = list.Views.GetByTitle(ViewName);
_context.Load(view);
_context.ExecuteQuery();
//If the List has Items more than its threshold value, we have to use ListItemCollectionPosition to get items.
ListItemCollectionPosition itemCreateInfo = null;
//Get Batch items based on rowlimit
string viewquery = "<View><Query>" + view.ViewQuery + "</Query><RowLimit>" + Constants.ROWLIMIT + "</RowLimit></View>";
CamlQuery query = newCamlQuery()
{
ViewXml = viewquery
};
//Get the View Fields from List
var lc = GetListColumns(list);
do {
query.ListItemCollectionPosition = itemCreateInfo;
var items = list.GetItems(query);
_context.Load(items);
_context.ExecuteQuery();
ArchiveItems(items, insertcmd, lc);
itemCreateInfo = items.ListItemCollectionPosition;
} while (itemCreateInfo != null);
}

In the above code snippet, ArchiveAndDeletemethod take 3 parameters. InsertCmd which we generated, List to be Archived, and the View from where we pull the Filtered data.

ListItemCollectionPosition stores the last index of the items fetched from list. It is useful when you want to apply paging or when you want to fetch items from the list which has crossed its threshold limit.

Value of itemCreateInfo is null when there are no more items left in the specified query.

ArchiveandDelete uses 2 private methods GetListColumns and ArchiveItems method.

private List < string > GetListColumns(List list)
{
try
{
List < string > viewcolumns = newList < string > ();
var listfields = list.Fields;
_context.Load(listfields);
_context.ExecuteQuery();
foreach(var field in listfields)
{
if (FilterColumn(field))
viewcolumns.Add(field.InternalName);
}
viewcolumns.AddRange(Constants.DEFAULTFIELDS.Split(','));
return viewcolumns;
} catch (Exception)
{
throw;
}
}

GetListColumns returns the collection of internal field names in a list which is then used to get the items properties against those fields. For example:

item[FieldInternalname]
private void ArchiveItems(ListItemCollection items, string insertcmd, List < string > vc)
{
foreach(var item in items.ToList())
{
int itemid = item.Id;
string itemname = string.Empty;
itemname = itemname.Replace("'", "");
try {
//Build Insert command
var insertVal = GetInsertValue(item: item, viewColumns: vc, itemId: itemid, listName: ListName);
var insertCommand = newStringBuilder(insertcmd.Substring(0, insertcmd.IndexOf("VALUES")));
insertCommand.Append("VALUES (" + insertVal + ")");
// send insert command to database.
int success = 0;
success = InsertData(insertCommand.ToString());
if (success == 1)
{
// item was inserted successfully. log it to tracking db.
Console.WriteLine("Item ID: {0} Name: {1} Archived to SQL Table Successfully.", itemid, itemname);
try
{
item.DeleteObject();
items.Context.ExecuteQuery();
// item was deleted from List. log it to tracking DB.
Console.WriteLine("Item ID: {0} Name: {1} Deleted from List", itemid, itemname);
} catch (Exception ex)
{
//Log
}
}
} catch (Exception ex)
{
//Log
}
}
}

ArchiveItems method loops through the listItemColelction and for each item it builds the insert command by loading the values from the respective fields. And once the insert command is completed we are just adding it to the SQL table using ADO.NET.