This is an example how you can use Dynamic Columns to pull in additional data to decorate your resultset with related Data. You could easily use this same process to decorate your data with additional data from other data sources. If you think about this makes Data Sync a very powerful Data Migration/Transformation tool. In this example I created a SharePoint List with Product data from the Northwind Database I then created a Lookup List with Category names and linked the 2 lists together via a SharePoint Lookup Column. The Goal is to pull out the "Description" column from the Category List for each Product in the Product List.
Here is the code for the Dynamic Columns in here we load the "Categories" list into a Data Table from SharePoint and create an index to lookup the Description value for each row.
#region Usings
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using Simego.DataSync.Providers.MSSharePoint;
#endregion
class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal //Do Not Change This Line
{
//Category Data
DataTable categories;
//Category Index
Dictionary<int, DataRow> lookup = new Dictionary<int, DataRow>();
/// <summary>
/// Get the Category ID from the Category Lookup Column
/// </summary>
public int O_CategoryID
{
get {
int ix = this.Category.IndexOf(";#");
if ( ix > 0 )
return Convert.ToInt32(this.Category.Substring(0, ix));
return 0;
}
}
/// <summary>
/// Description
/// </summary>
public string O_CategoryDescription {
get
{
object val = lookup[O_CategoryID]["Description"];
if ( val == DBNull.Value ) return null;
return (string)val;
}
}
public override void Setup()
{
//Get a DataTable object of the related List via a new SharePoint Reader
SharePointDataSourceReader reader = new SharePointDataSourceReader();
reader.ListUrl = ((SharePointDataSourceReader)this.DataSourceA.Reader).ListUrl;
reader.ListName = "Categories";
//Get the Default Schema for this List
reader.SchemaMap = reader.GetDefaultDataSchema();
//Load the Data Table
categories = reader.GetDataTable();
//Create a Lookup Index to help speed-up lookups
foreach(DataRow r in categories.Rows)
lookup.Add((int)r["ID"], r);
}
}
This is the result, here you can see we have pulled in the Description value from the related Categories List.