Custom Data Integration providers

If none of our built-in Data Integration providers suit you, you can create a custom Data Integration provider tailored to your specific needs.

That involves:

  • Downloading and installing the Visual Studio templates for Dynamicweb 8 or 9, as appropriate (you must be logged in to your account)
  • Creating a project, adding the appropriate references, and creating a new item from a template
  • Adding custom logic to the template
  • Implementing your source or destination provider
  • Adding custom fields/controls to the provider UI
  • Building the project and moving the .dll to the solution bin-folder

See more below.

To get started:

  • Create a new project inside Visual Studio and select “Class library” as the project template (Figure 2.1).
Figure 2.1 Creating the project
  • Add references to Dynamicweb.dll and the other assemblies that contain the APIs that we will need:
    • Dynamicweb.dll
    • Dynamicweb.Data.dll
    • Dynamicweb.Data.Providers.dll
  • Add a new item to the project by the right clicking on the project and choosing "Add" -> "New Item". Choose “Dynamicweb 8 -> Data Integration -> ExampleSourceProvider” or "ExampleDestinationProvider" (Figure 2.2)
Figure 2.2 Selecting the appropriate template
  • Save and build your project – and copy the DLL into the Dynamicweb admin/bin folder.

Check that your new custom provider shows up in the Data Integration module when creating a new data integration activity (Figure 2.3). 

Figure 2.3 Verify that the provider is available when creating a Data Integration activity

To implement your own logic do the following:

  • For a source provider:
    • Change “ExampleSourceProvider.GetOriginalSourceSchema()” to reflect the tables and columns you wish to provide.
    • Change “ExampleSourceReader.GetNext()” to return the next row the given mapping.
    • Change “ExampleSourceReader.IsDone()” to return false when all rows have been imported
  • For a destination provider:
    • Change “ExampleDestinationProvider.GetOriginalDestinationSchema()” to reflect the tables and columns you wish to provide.
    • ExampleDestinationProvider.RunJob() – to make changes in the job running flow
    • ExampleDestinationProvider.Write() – to write the row which was read from the source

For a source provider you need to implement the "ISource" interface and inherit from "ConfigurableAddIn" class.

In the GetOriginalSourceSchema() method you need to return the tables and their columns which will be read from the source(csv/xml file, database, etc.). Here is the example which returns the schema with two tables:

public Schema GetOriginalSourceSchema() { Schema result = new Schema(); var table = result.AddTable("Groups", ""); table.AddColumn(new Column("GroupID", typeof(string), table, true, false)); table.AddColumn((new Column("GroupName", typeof(string), table))); table = result.AddTable("Products", ""); table.AddColumn(new Column("ProductID", typeof(string), table, true, false)); table.AddColumn((new Column("ProductName", typeof(string), table))); return result; }

In the GetReader() method you need to create an instance of your reader class: 

public ISourceReader GetReader(Mapping mapping) { return new ExampleSourceReader(mapping); }

The reader class should implement the "ISourceReader" interface. In the GetNext() method you should look at the mapping, which columns are needed, and only include those. In the IsDone() method check if there are any more items to read - if not, return true. Below is an example of sql source reader. It makes the sql data reader from current mapping table and its columns:

private SqlDataReader _reader; private Mapping _mapping; public ExampleSourceReader(Mapping mapping) { _mapping = mapping; _command = new SqlCommand { Connection = connection }; string columns = GetColumns(); string fromTables = GetFromTables(); string sql = "select " + columns + " from " + fromTables; _command.CommandText = sql; _reader = _command.ExecuteReader(); } private string GetFromTables() { return "[" + _mapping.SourceTable.SqlSchema + "].[" + _mapping.SourceTable.Name + "]"; } private string GetColumns() { string columns = _mapping.GetColumnMappings().Aggregate("", (current, fm) => (fm.SourceColumn != null) ? current + "[" + fm.SourceColumn.Name + "], " : current); columns = columns.Substring(0, columns.Length - 2); return columns; } public bool IsDone() { if (_reader.Read()) return false; _reader.Close(); return true; } public Dictionary<string, object> GetNext() { Dictionary<string, object> result = new Dictionary<string, object>(); foreach (ColumnMapping columnMapping in _mapping.GetColumnMappings()) { if (columnMapping.SourceColumn != null) result.Add(columnMapping.SourceColumn.Name, _reader[columnMapping.SourceColumn.Name]); } return result; }

For destination provider you need to implement "IDestination" interface and inherit from "ConfigurableAddIn" class.

In the GetOriginalDestinationSchema() method you need to return the tables and their columns which will be read from the destination source(csv/xml file, database, etc). For example see method GetOriginalSourceSchema() in the "Implementation source provider" section.

In the RunJob() method we need to read data from source and write it to the destination. This should be done for all active mappings that are in the job:

public bool RunJob(Job job, string logFile) { List<CsvDestinationWriter> destinationWriters = new List<CsvDestinationWriter>(); try { foreach (var mapping in job.Mappings) { if (mapping.Active && mapping.GetColumnMappings().Count > 0) { destinationWriters.Add(new CsvDestinationWriter(filePath, mapping)); } } foreach (var writer in destinationWriters) { using (ISourceReader sourceReader = writer.Mapping.Source.GetReader(writer.Mapping)) { while (!sourceReader.IsDone()) writer.Write(sourceReader.GetNext()); } } } catch (Exception ex) { //log the exception return false; } finally { foreach (var writer in destinationWriters) { writer.Close(); } } return true; }

Also you need to create the writer class or method to write the row with data read from the source. If you choose writing a class you can implement the "IDestinationWriter" interface. Here is a sample csv writer class which writes data to csv file:

public class CsvDestinationWriter : IDestinationWriter { private readonly Mapping _mapping; private readonly string _path; private TextWriter _writer; private TextWriter Writer { get { if (_writer == null) { _writer = new StreamWriter(_path + "/" + Mapping.DestinationTable.Name + ".csv"); } return _writer; } set { _writer = value; } } public CsvDestinationWriter(string path, Mapping mapping) { _mapping = mapping; _path = path; if (!Directory.Exists(path)) Directory.CreateDirectory(path); } public Mapping Mapping { get { return _mapping; } } public void Write(Dictionary<string, object> row) { string stringToWrite = Mapping.GetColumnMappings() .Where(columnMapping => columnMapping.Active) .Aggregate("", (current, columnMapping) => current + row[columnMapping.SourceColumn.Name].ToString()); Writer.WriteLine(stringToWrite); } public virtual void Close() { Writer.Close(); } }

If you want to create fields and controls to your provider, you can add properties to the provider class, which will then show up in the UI as in Figure 6.1.

Figure 6.1 Adding custom UI controls

Your field/control options are:

Folder selector:

[AddInParameter("Source folder"), AddInParameterEditor(typeof(FolderSelectEditor), "htmlClass=NewUIinput;"), AddInParameterGroup("Source")] public string SourcePath{get;set;}

File selector:

[AddInParameter("Source file"), AddInParameterEditor(typeof(FileManagerEditor), "extensions=xml;usefilesfolder=true;"), AddInParameterGroup("Source")] public string SourceFileName { get; set; }

Drop-down selector:

[AddInParameter("First row in source files contains column names"), AddInParameterEditor(typeof(YesNoParameterEditor), ""), AddInParameterGroup("Source")] public bool SourceFirstRowContainsColumnNames { get; set; }

Text box:

[AddInParameter("Input Field delimiter"), AddInParameterEditor(typeof(TextParameterEditor), "maxLength=1;inputClass=NewUIinput;"), AddInParameterGroup("Source")] public Char SourceFieldDelimiter { get; set; }

The AddinParameterGroup should be "Source" if the parameter should show up for the provider when used as a source, and "Destination" if it is used as a destination.

To save the setting in the XML file add the following code to the SaveAsXml() method:

textWriter.WriteElementString("SourceFirstRowContainsColumnNames", SourceFirstRowContainsColumnNames.ToString(CultureInfo.CurrentCulture));

To apply saved settings you need to read them in the provider constructor:

//Constructor that takes an XmlNode is mandatory, //for loading saved activities public MySourceProvider(XmlNode xmlNode) { //Here, the xml written in the SaveAsXML function must be parsed foreach (XmlNode node in xmlNode.ChildNodes) { switch (node.Name) { case "Schema": _schema = new Schema(node);//Get schema from Xml settings break; case "SourceFirstRowContainsColumnNames": SourceFirstRowContainsColumnNames = node.FirstChild.Value == "True"; break; default: throw new Exception("Unexpected XML node"); } } }

To correctly display the GUI you must add the code to the Serialize() method:

public override string Serialize() { XDocument document = new XDocument(new XDeclaration("1.0", "utf-8", string.Empty)); XElement root = new XElement("Parameters"); document.Add(root); root.Add(new XElement("Parameter", new XAttribute("addin",GetType().FullName), new XAttribute("name","NameOfParameterToBeSerialized"), new XAttribute("value", "ValueOfParameterToBeSerialized"))); return document.ToString();

And UpdateSourceSettings() for source provider or UpdateDestinationSettings() for destination provider:

public override void UpdateSourceSettings(ISource source) { CsvProvider newProvider = (CsvProvider)source; SourceFirstRowContainsColumnNames = newProvider.SourceFirstRowContainsColumnNames; }