Importing Content using the Core Service in SDL Tridion 2011

Importing content is often one of the first things a client asks for when starting a new project. Having data from a 3rd party system migrated or integrated into Tridion items to then be published and accessible on their website is usually a must have requirement. The most common scenario we come across is clients that have data in CSV or Microsoft Excel files that that they would like importing into Tridion.

Importing this content has become a lot easier using the new WCF Core Service provided in SDL Tridion 2011. The usual process is:

Import Process

Nuno Linhares has a great article on importing content here but I thought I would expand on this further to show how Building Blocks have been using the Core Service in conjunction with XML Serializing/Deserializing to make working with Tridion content in Custom Pages and applications a lot easier by using Strong Typing.

The example I will use here is a customer that has asked for their Product Prices that they currently available in an Excel file to be imported into Tridion and matched to the product Components that they have already been created in Tridion. The solution will utilise C# and .Net Framework 4.0.

The great thing about the new Core Service is that all code can be developed and debugged locally unlike previous Tridion versions you will have to deploy and debug the code directly on the Content Manager Server:

    1. Create your web application and add a web page with a FileUpload Control and a Submit Button
    2. Add a service reference to your Core Service e.g. http://tridion/webservices/CoreService.svc and give it a namespace of Tridion
    3. Next we can start building up our Class files for the Tridion Objects

a) Create a ComponentLink Class which we will need to link our Product Price to the Product

[code lang="c-sharp"]using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Xml.Serialization;

/// <summary>
/// Summary description for ComponentLink
/// </summary>
[Serializable]
public class ComponentLink
{
[XmlNamespaceDeclarations]
public XmlSerializerNamespaces Namespaces;

public ComponentLink()
{
Namespaces = new XmlSerializerNamespaces();
Namespaces.Add("xlink", "http://www.w3.org/1999/xlink");
}

[XmlAttribute("href", Namespace = "http://www.w3.org/1999/xlink")]
public string TcmId { get; set; }

[XmlText]
public string Text { get; set; }

[XmlAttribute("title", Namespace = "http://www.w3.org/1999/xlink")]
public string Title { get; set; }

}[/code]
b) Create the ProductPrice Class that matches the same XML structure as the Schema created in Tridion

[code lang="c-sharp"]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Xml.Serialization;
using System.Xml;
using System.IO;
using System.Text;

[Serializable]
[XmlRoot(ElementName = "Content",
Namespace = "uuid:341ebd00-fcf9-46d3-b4db-c8402acf798b")]
public class ProductPrice
{
private ComponentLink _product;

public ProductPrice()
{
Product = new ComponentLink();
}

[XmlElement("product")]
public ComponentLink Product
{
get { return _product ?? (_product = new ComponentLink()); }
set { _product = value; }
}

[XmlIgnore]
public string TcmId { get; set; }

[XmlElement("pricing_id")]
public string PricingId { get; set; }

[XmlElement("setup_fee")]
public decimal SetupFee { get; set; }

[XmlElement("three_month_rate")]
public decimal ThreeMonthRate { get; set; }

[XmlElement("twelve_month_rate")]
public decimal TwelveMonthRate { get; set; }

public string Serialize()
{
XmlWriterSettings settings = new XmlWriterSettings { OmitXmlDeclaration = true,
Indent = true, Encoding = Encoding.ASCII };

using (MemoryStream stream = new MemoryStream())
using (XmlWriter writer = XmlWriter.Create(stream, settings))
{
XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
ns.Add("", "uuid:341ebd00-fcf9-46d3-b4db-c8402acf798b");

XmlSerializer serializer = new XmlSerializer(typeof(ProductPrice));
serializer.Serialize(writer, this, ns);

string xml = Encoding.ASCII.GetString(stream.ToArray());
return xml;
}
}

public static ProductPrice Deserialize(string xml)
{
ProductPrice result;

using (StringReader stringReader = new StringReader(xml))
using (XmlReader xmlReader = XmlReader.Create(stringReader))
{
XmlSerializer serializer = new XmlSerializer(typeof(ProductPrice));
result = (ProductPrice)serializer.Deserialize(xmlReader);
}

return result;
}
}
[/code]
Note: You must ensure that the order of the attributes in your classes are the exact same order as your schema fields. You also must ensure that your uuid namespace in your Serialize method is exactly the same as the one in Tridion

    1. Create a method on your web page to parse the Excel file into a list of ProductPrice

[code lang="c-sharp"]
/// <summary>
/// Excel to List of VOProductPrice
/// </summary>
public List<ProductPrice> ReadXlsFile(string fileLocation)
{
Application app = null;
Workbook book = null;
Worksheet sheet = null;

var result = new List<ProductPrice>();

try
{
app = new ApplicationClass();
app.DisplayAlerts = false;
book = app.Workbooks.Open(Server.MapPath(fileLocation), Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value);

sheet = (Worksheet)book.Sheets[1];

var rowCount = sheet.UsedRange.Rows.Count;
for (var i = 2; i <= rowCount; i++)
{
var price = new ProductPrice();

var priceId = (Range)sheet.Cells[i, 1];
if (priceId == null ||
string.IsNullOrEmpty(priceId.Text.ToString())) continue;
price.PricingId = priceId.Text.ToString();
Marshal.FinalReleaseComObject(priceId);

var productId = (Range)sheet.Cells[i, 3];
if (productId == null ||
string.IsNullOrEmpty(productId.Text.ToString())) continue;
//store the product ID from the 3rd party system temporarily
//in the TcmId field. this will be converted later
price.Product.TcmId = productId.Text.ToString();
Marshal.FinalReleaseComObject(productId);

var threeRate = (Range)sheet.Cells[i, 4];
if (threeRate == null ||
string.IsNullOrEmpty(threeRate.Text.ToString())) continue;
price.ThreeMonthRate = Convert.ToDecimal(threeRate.Text.ToString());
Marshal.FinalReleaseComObject(threeRate);

var twelveRate = (Range)sheet.Cells[i, 7];
if (twelveRate == null ||
string.IsNullOrEmpty(twelveRate.Text.ToString())) continue;
price.TwelveMonthRate = Convert.ToDecimal(twelveRate.Text.ToString());
Marshal.FinalReleaseComObject(twelveRate);

result.Add(price);
}
}
catch (Exception ex)
{
Log.Error("PricingManagement.ReadXlsFile: Error Reading XLS File", ex);
return null;
}
finally
{
//all COM objects must be explicitly destroyed otherwise
//EXCEL.EXE stays open
GC.Collect();
GC.WaitForPendingFinalizers();
if (sheet != null)
{
Marshal.FinalReleaseComObject(sheet);
}
if (book != null)
{
book.Close(Missing.Value, Missing.Value, Missing.Value);
Marshal.FinalReleaseComObject(book);
}
if (app != null)
{
app.Quit();
Marshal.FinalReleaseComObject(app);
}
}
return result;
}
[/code]
Note: Microsoft Excel integration can also be achieved using OleDB if required. It is actually faster than using the Interop assemblies.

    1. Create a Utility Method to extract the TcmId for each Product that has already been created in Tridion to ensure that the Component Link resolves correctly. This is done through simple XML node navigation as there are only a few products to loop through

[code lang="csharp"]
public static string GetProductTcmId(CoreService2010Client client, string productId)
{
string productTcmId = "";
try
{
//get the XML list of component from the folder
var productsXML =
client.GetListXml(ConfigurationManager.AppSettings["ProductFolderTcmId"],
new OrganizationalItemItemsFilterData {
ItemTypes = new[] { ItemType.Component } });

//loop through each item and find out if it is the product we want
foreach (var product in productsXML.Elements())
{
var productData =
client.Read(product.Attribute("ID").Value, null) as ComponentData;
var schemaFields =
client.ReadSchemaFields(productData.Schema.IdRef, false, null);
var content = XDocument.Parse(productData.Content);
XNamespace ns = schemaFields.NamespaceUri;

//check if the product id's match
if (productId == content.Root.Element(ns + "product_id").Value)
{
//return the TcmId
productTcmId = product.Attribute("ID").Value;
//exit the foreach
continue;
}
}
}
catch (Exception ex)
{
Log.Error("Error in GetProductTcmId()", ex);
}
return productTcmId;
}
[/code]

    1. Create a method to loop through the list of ProductPrice and Create the component in Tridion using the Serialize method

[code lang="c-sharp"]
public void CreateComponents(CoreService2010Client client, List<ProductPrice> productPrices)
{
try
{
int count = 1;
foreach (var productPrice in productPrices)
{
// Component name
Log.Debug("Start Processing " + productPrice.PricingId);
ComponentData component;

// Create Component in the correct folder
component = client.GetDefaultData(ItemType.Component,
ConfigurationManager.AppSettings["PriceFolderTcmId"]) as ComponentData;

//get the correct TcmId for the Product
productPrice.Product.TcmId = ProductUtilites.GetProductTcmId(client,
productPrice.Product.TcmId);

//set the component information
component.Title = productPrice.PricingId;
component.Id = "tcm:0-0-0";
//serialize the object to XML for Tridion
component.Content = productPrice.Serialize();
component.Schema.IdRef =
ConfigurationManager.AppSettings["PriceSchemaTcmId"];

try
{
//create the component
client.Create(component, null);
}
catch (Exception ex)
{
Log.Error("PricingManagement.CreateComponents: Unable to save new component", ex);
}

Log.Debug("Finished Processing " + productPrice.PricingId
+ ". Count = " + count);
count++;
}

goodMessage.Text = "Processed " + productPrices.Count + " product prices";
goodMessage.Visible = true;
}
catch (Exception ex)
{
Log.Error("PricingManagement.CreateComponents: Could not create components", ex);
throw;
}
}
[/code]

And that's it!

The code is now complete. By creating your classes up front with the correct XML attributes that map to your Content Schema you no longer have to perform XPath's or reference fields using their string value, you can do it all strongly typed.

We have extended this Custom Page further to include the ability to Update & Create the components in the same run by using the Deserialize method for the update and LINQ to search the list of prices as well as adding caching of the prices that are already in Tridion to improve speed.

Any questions?

If you need more information or have any questions just get in touch and we'd be happy to answer them for you.