Migrate data using SharePoint Client Code - LookupId
Two weeks ago I received an email asking how my SharePoint 2007 ratings work in order to include totals. Indeed is simple, but perhaps not clear, that there is a cross lookup between both lists:
- Both lists have a lookup column. The rate information list has a lookup column to the content list (id_session)
- The content information list has a lookup column to the rating list. So it shows a count() option.
This can be made manually or through a SharePoint feature, in that scenario it will need to get the list id to get the connection.
But wait, didn't the title of this post talk about other thing? True, be patient. The server with that customization was migrated to SharePoint 2010 (RC at this time), and after some issues, we prepared the migration to the OOB ratings features coming inside SharePoint 2010.
I don't have physical access to the server, so the first idea was to use the REST API, but it was not available/configured :(. So I selected SharePoint Client Object Model. In this scenario there has been many approaches, but the most relevant was Hubkey client API (hey remember RPC is deprecated!). Opening this way a lot of multiple scenarios and tools working "oustside" the farm.
The overall thing was pretty easy and fast, but the most trickiest (some minutes :)) thing was the value of that lookup column. As that column was the total number of items on the other list, I need to include the LookupId property not the value.
And here it is the sample code:
// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)
using System;
using Microsoft.SharePoint.Client;
using System.Xml.Linq;
using System.Linq;
using System.Collections.Generic;
using System.Configuration;
namespace FeedbackMigration
{
class Program
{
static string feedbackSourceSite = ConfigurationManager.AppSettings["urlSource"];
static string feedbackTargetSite = ConfigurationManager.AppSettings["urlTarget"];
static string feedbackList = "feedback";
static string usage = "Please enter migration mode: export = 1, import = 2\nUsage: FeedbackMigration <num>";
static void Main(string[] args)
{
if (args.Length == 0)
{
Console.WriteLine(usage);
}
else
{
switch (int.Parse(args[0]))
{
case 1:
ReadFeedback();
break;
case 2:
WriteFeedback();
break;
default:
Console.WriteLine(usage);
break;
}
}
}
static void ReadFeedback() {
try
{
ClientContext clientContext = new ClientContext(feedbackSourceSite);
List list = clientContext.Web.Lists.GetByTitle(feedbackList);
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = @"<View>
<RowLimit>200</RowLimit>
</View>";
ListItemCollection listItems = list.GetItems(camlQuery);
IEnumerable<ListItem> feedbacks = clientContext.LoadQuery(
listItems.Include(
feedback => feedback.Id,
feedback => feedback["Title"],
feedback => feedback["Author"],
feedback => feedback["Feedback"],
feedback => feedback["Manager"],
feedback => feedback["points"],
feedback => feedback["votes"],
feedback => feedback["Quarter"],
feedback => feedback["Speciality"],
feedback => feedback["Manager_x0020_Review"],
feedback => feedback["Region"]));
clientContext.ExecuteQuery();
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Started read process feedback list");
XElement xml = new XElement("feedbacks",
from f in feedbacks
orderby f.Id
select new XElement("feedback",
new XAttribute("id", f.Id),
new XElement("title", f["Title"]),
new XElement("author", ((FieldUserValue)f["Author"]).LookupValue),
new XElement("description", f["Feedback"]),
new XElement("manager", ((FieldUserValue)f["Manager"]).LookupValue),
new XElement("points", f["points"]),
new XElement("votes", ((FieldLookupValue)f["votes"]).LookupId),
new XElement("quarter", f["Quarter"]),
new XElement("cluster", f["Speciality"]),
new XElement("review", f["Manager_x0020_Review"]),
new XElement("region", f["Region"]))
);
xml.Save(String.Format("feedback-{0}.xml", DateTime.Now.ToString("yyMMddHHmmss")));
Console.WriteLine("Finish");
Console.ResetColor();
Console.ReadKey();
}
catch (System.Net.WebException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Not able to connect to the server: \n{0}", ex.ToString());
Console.ResetColor();
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Error: \n{0}", ex.ToString());
Console.ResetColor();
}
}
static void WriteFeedback() {
try
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Started write process from feedback xml");
XDocument loaded = XDocument.Load(@"feedback.xml");
ClientContext clientContext = new ClientContext(feedbackTargetSite);
List list = clientContext.Web.Lists.GetByTitle(feedbackList);
foreach (XElement f in loaded.Root.Nodes()){
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
ListItem listItem = list.AddItem(itemCreateInfo);
listItem["Title"] = f.Element("title").Value;
listItem["Author"] = FieldUserValue.FromUser(f.Element("author").Value);
listItem["Feedback_x0020_description"] = f.Element("description").Value;
listItem["Manager"] = FieldUserValue.FromUser(f.Element("manager").Value);
listItem["Quarter"] = f.Element("quarter").Value;
listItem["Cluster"] = f.Element("cluster").Value;
listItem["Manager_x0020_Review"] = f.Element("review").Value;
listItem["Region"] = f.Element("region").Value;
listItem.Update();
clientContext.ExecuteQuery();
Console.WriteLine("Item added {0}", f.Element("title").Value);
}
Console.WriteLine("Finish");
Console.ResetColor();
Console.ReadKey();
}
catch (System.IO.FileNotFoundException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Not able to find the file: \n{0}", ex.ToString());
Console.ResetColor();
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Error: \n{0}", ex.ToString());
Console.ResetColor();
}
}
}
}
Bye!