Tutorial 6: Extending Integration

Dynamicweb features an extensive framework for importing and exporting data to and from a Dynamicweb solution – either on an ad-hoc basis, on a schedule, or in real-time.

Dynamicweb contains the following integration tools and features:

  • The Data Integration module lets you create integration activities which accomplish a particular task, such as exporting or importing user data, exporting orders, etc.
  • A Batch Integration uses scheduled tasks to import and export data at intervals (hourly, daily, weekly, etc.)
  • A Live integration retrieves data from a remote system in real-time, and uses it to show e.g. live prices or stock states, or to create orders in a remote system as they are placed

Batch and live integrations – collectively referred to as the Integration Framework – communicate with external systems through a web service called DynamicwebConnectorService, which is installed on the perimeter network of the remote environment (Figure 1.1).

Figure 1.1 An overview of an integration

You can read much more about Integration in Dynamicweb in the general Integration documentation.

Assuming that you are somewhat familiar with the basics of integration in Dynamicweb, the integration framework may be extended in the following places:

  • You can create custom data integration providers if the standard integration providers are too limited in their scope for your needs
  • You can use table scripting to process data from the source provider before it’s passed to the destination provider
  • You can create custom batch integration scheduled task add-ins to handle data being sent to and received from the DynamicwebConnectorService
  • You can subscribe to the JobFinished notification, which fires just after a data integration activity has been executed
  • You can create custom DynamicwebConnectorService add-ins to handle requests and responses from Dynamicweb and an external system, e.g. an ERP system.

 If you are working on the remote side of an integration, you can also extend the plugin/code unit installed on the remote system, but that is outside the scope of this tutorial.

An integration provider is a piece of software for moving data between Dynamicweb and an external data source, like an XML file, a CSV file or an SQL database.

Integration providers are used in integration activities, which are jobs that accomplish something when they are executed, e.g. 'import products from a file' or 'export users to a file'.

An integration activity consists of:

  • A source provider matching the data source - either the Dynamicweb solution (export) or a file (import)
  • A destination provider matching the data destination - either the Dynamicweb solution (import) or a file (export)

You can read about the standard integration providers here – and if they are too limited in their scope of their functionality, you can also create custom data integration providers.

Data entering your Dynamicweb solution can be handled and processed in various ways – if the source is XML data, for instance, you can use an XSLT template to transform the data, or you can incorporate logic into the plugin extracting data from the remote system.

However, for more advanced processing needs you may need to look up things on the Dynamicweb solution being imported to – e.g. a list of auto-IDs – and use that information to enrich the data entering the system in some way.

In that case you can use table scripting to process or overwrite source row values when they are being passed from the source provider to the destination provider.

To get started:   

  1. Create a new project inside Visual Studio and select Class Library as the project template
  2. Add a NuGet reference to the Dynamicweb.DataIntegration package.
  3. Add a new item to the project by the right clicking on the project and selecting Add > New Item
  4. Select Dynamicweb 9 > Data Integration > ExampleTableScript item (Figure 3.1)
Figure 3.1 The ExampleTableScript item

The ExampleTableScript item contains the following code:

using System.Linq; using System.Collections.Generic; using Dynamicweb.DataIntegration.Integration; namespace T3 { public class ExampleTableScript1 : TableScript { public override string ScriptingName { get { return "Sample input processing script"; } } public override void ProcessInputRow(Mapping mapping, Dictionary<string, object> row) { //TODO: add you code for processing the input row //note: the updated value should have the same type as the input value //for example: set ProductPrice column values to some value if (mapping.SourceTable != null) { var column = mapping.SourceTable.Columns.FirstOrDefault(c => c != null && c.Name.ToLower() == "ProductPrice".ToLower()); if (column != null && row.ContainsKey(column.Name)) { row[column.Name] = "10.5"; } } } } }
  • In ScriptingName you provide the name which will be shown in the drop-down when selecting your table script.
  • In ProcessInputRow you can process the values from source and output your custom values.

Save and build the project, then copy the DLL into the Dynamicweb admin/bin folder.

Check that the new custom table script shows up in the Data Integration module, when editing table mappings in the data integration activity (Figure 3.3).  

Figure 3.3 Using a table script

Custom scheduled task add-ins can be used to customize the requests and responses being sent to and received from the DynamicwebConnectorService.

To get started:   

  1. Create a new project inside Visual Studio and select Class Library as the project template
  2. Add a new item to the project by the right clicking on the project and choosing Add > New Item.
  3. Select the Dynamicweb 9 -> Data Integration -> ExampleBatchIntegrationScheduledTaskAddin item (Figure 4.1))
Figure 4.1 Select the example add-in item

This example of a scheduled task add-in is based on the default provider called Import data with custom request add-in – open your batch integration framework interface and check it out.

In the first part of the code you must add the relevant references, create an add-in name, and add the AddInParameters you want to use in the provider.

First, add NuGet references to the following packages:

Dynamicweb Dynamicweb.Core Dynamicweb.DataIntegration Dynamicweb.DataIntegration.Providers.XmlProvider Dynamicweb.Environment Dynamicweb.Extensibility Dynamicweb.Logging Dynamicweb.Scheduling

In the AddInName attribute you can define the custom scheduled task add-in name, which will be shown in the add-in selector dropdown when creating a scheduled task: 

//Remember to decorate the class with Addin information. [AddInName(".DataIntegration.Examples"), AddInLabel("DataIntegration.Examples"), AddInDescription("Example scheduled task add-in"), AddInIgnore(false)] public class ExampleBatchIntegrationScheduledTaskAddin : BatchIntegrationScheduledTaskAddin, IDropDownOptions

You can then define input controls for the add-in parameters – in this case they are used to render the parameters from the Import data with custom request add-in, i.e. Web service URL, Security key, Request XML, and Import Activity.

Read about the control options available to you here.

[AddInParameter("Web service URL"), AddInParameterEditor(typeof(TextParameterEditor), "inputClass=NewUIinput;")] public string WebServiceURI { get; set; } [AddInParameter("Security key"), AddInParameterEditor(typeof(TextParameterEditor), "inputClass=NewUIinput;")] public string SecurityKey { get; set; } [AddInParameter("Request XML"), AddInParameterEditor(typeof(TextParameterEditor), "inputClass=NewUIinput;")] public string RequestXML { get; set; } [AddInParameter("Import activity"), AddInParameterEditor(typeof(DropDownParameterEditor), "NewGUI=true;")] public string ImportActivity { get; set; }

The Run() method contains the main add-in functionality.

First, set up the logger:

SetupLogging();

Then check if the Import activity exists and create its instance:

string job = ImportActivity; string LogFile = job.Replace(";", "--") + DateTime.Now.ToString("yyyyMMdd-HHmmssFFFFFFF") + ".log"; logFileFullPath = Context.Current.Server.MapPath("/Files/System/Log/DataIntegration/" + LogFile); //load the job from file Job jobToRun = new Job(Context.Current.Server.MapPath("/Files/" + Dynamicweb.Content.Files.FilesAndFolders.GetFilesFolderName() + "/Integration/jobs/") + job + ".xml", logFileFullPath);

Then we check if we have a source provider: 

if (jobToRun.Source != null){ }

Get the xml data from the Dynamicweb Connector:

string xml = ErpServiceCaller.GetDataFromRequestString(WebServiceURI, SecurityKey, RequestXML);

Then the xml string is saved to the file if the job source provider is the Xml provider:

((XmlProvider)jobToRun.Source).WriteToSourceFile(xml);

After that the job is executed, and the xml from the remote system is imported to the relevant Destination provider configured in the “Import activity” data integration job.

ret = jobToRun.Run();

To use the custom add-in, save and build your project – then copy the DLL into the Dynamicweb admin/bin folder.

Check that your new custom scheduled task add-in shows up in Settings > Integration > Integration Framework Batch when creating a new batch scheduled task (Figure 8.1).

Figure 8.1 Using the custom batch integration scheduled task add-in

The Job Finished notification subscriber executes just after a data integration job finishes its execution. You can check if the data integration job was executed OK or failed,  you can get the log file content, and enumerate the destination table names that were used to import data to.

To subscribe to the Job Finished notification:   

  1. Create a new project inside Visual Studio and select Class Library as the project template
  2. Add a new item to the project by the right clicking on the project and choosing Add > New Item
  3. Select the  Dynamicweb 9 > Data Integration > JobFinishedObserver item (Figure 9.1)
Figure 9.1 The JobFinishedObserver example item

To create a custom JobFinished subscriber, add NuGet references to the following packages:

Dynamicweb.DataIntegration Dynamicweb.Extensibility

Then modify the code in the OnNotify method – the sample code below shows what properties you can access there:

public override void OnNotify(string notification, NotificationArgs args) { var integrationArgs = args as Integration.JobFinishedIntegrationArgs; //TODO: Add code here if (integrationArgs.JobFailed) { string log = System.IO.File.ReadAllText(integrationArgs.LogFile); foreach(string table in integrationArgs.DestinationTables) { } } }

Save and build your project – then copy the DLL into the Dynamicweb admin/bin folder. After a data integration job is run, your notification subscriber code will be executed.

The DynamicwebConnectorService is a web service which functions as the bridge between a Dynamicweb solution and a remote system – typically an ERP system. The DynamicwebConnectorService is installed on the DMZ/Perimeter network of the remote environment.

Basically, the DynamicwebConnectorService receives queries from Dynamicweb and relays them to the remote system – then receives a response from the remote system and relay it to Dynamicweb.

By default, the DynamicwebConnectorService contains add-ins for Dynamics NAV, Dynamics AX & Perfion – but if you’re creating an integration to a system which is not included by default you can create a custom DynamicwebConnectorService add-in.

To get started:

  1. Create a new project inside Visual Studio and select “Class library” as the project template
  2. Add a new item to the project by the right clicking on the project and choosing Add > New Item
  3. Choose “Dynamicweb 9 -> Data Integration -> DynamicwebConnectorAddin” (Figure 11.1)
Figure 11.1 The DynamicwebConnectorServiceAddIn item

Add references to:

  • System.Configuration
  • DynamicwebServiceClass.dll – which can be found in the DynamicwebConnectorService folder which can be downloaded here.

In the ProcessRequest method, write your code for handling the XML requests from Dynamicweb to the remote system. If you plan on also using a custom batch integration scheduled task add-in – as shown earlier in this article – you can write/use your own XML requests and handle them yourself inside the ProcessRequest method.

Here are some examples of the requests that can come from a Dynamicweb solution via batch integration add-ins– these are the kinds of request and responses your custom add-in should be able to handle.

Getting Ecommerce data from the Remote system:

<GetEcomData> <tables> <Products type="all"/>" <Currencies type="all"/> <Languages type="all"/> <Manufacturers type="all"/> <Units type="all"/> </tables> </GetEcomData>

Gettings users from the Remote system:

<GetEcomData> <tables> <Users type="all"/> </tables> </GetEcomData>

Exporting the Dynamicweb users into the Remote system:

<PutEcomUsers> <table tableName="AccessUser"> <item table="AccessUser"> <column columnName="AccessUserId"><![CDATA[1000]]></column> <column columnName="AccessUserUserName"><![CDATA[bond]]></column> <column columnName="AccessUserPassword"><![CDATA[007]]></column> <column columnName="AccessUserName"><![CDATA[James Bond]]></column> <column columnName="AccessUserDepartment"><![CDATA[Secret]]></column> <column columnName="AccessUserEmail"><![CDATA[007@mi6.co.uk]]></column> <column columnName="AccessUserPhone"><![CDATA[12341234]]></column> <column columnName="AccessUserFax"><![CDATA[]]></column> <column columnName="AccessUserGroups"><![CDATA[Agents]]></column> <column columnName="AccessUserAddress"><![CDATA[Steet name]]></column> <column columnName="AccessUserAddress2"><![CDATA[Address2]]></column> <column columnName="AccessUserZip"><![CDATA[12345]]></column> <column columnName="AccessUserCity"><![CDATA[City name]]></column> <column columnName="AccessUserCountry"><![CDATA[England]]></column> <column columnName="AccessUserJobTitle"><![CDATA[Agent]]></column> <column columnName="AccessUserCompany"><![CDATA[MI6]]></column> <column columnName="AccessUserPhonePriv"><![CDATA[23452345]]></column> <column columnName="AccessUserMobile"><![CDATA[45674576]]></column> <column columnName="AccessUserCustomerNumber"><![CDATA[67896789]]></column> <column columnName="AccessUserCurrencyCharacter"><![CDATA[EUR]]></column> <column columnName="AccessUserLastName"><![CDATA[Bond]]></column> <column columnName="AccessUserMiddleName"><![CDATA[]]></column> <column columnName="AccessUserActive"><![CDATA[True]]></column> <column columnName="AccessUserBusiness"><![CDATA[56785678]]></column> <column columnName="AccessUserInitials" isNull="true" /> <column columnName="AccessUserWeb" isNull="true" /> <column columnName="AccessUserExternalId"><![CDATA[007@mi6.co.uk]]></column> <column columnName="AccessUserState"><![CDATA[Region name]]></column> <column columnName="AccessUserVatRegNumber"><![CDATA[]]></column> <column columnName="AccessUserTitle"><![CDATA[Mr.]]></column> <column columnName="AccessUserFirstName"><![CDATA[James]]></column> <column columnName="AccessUserHouseNumber"><![CDATA[1]]></column> </item> <item table="AccessUser"> <column columnName="AccessUserId"><![CDATA[1001]]></column> <column columnName="AccessUserUserName"><![CDATA[test]]></column> <column columnName="AccessUserPassword"><![CDATA[008]]></column> <column columnName="AccessUserName"><![CDATA[James test]]></column> <column columnName="AccessUserDepartment"><![CDATA[test]]></column> <column columnName="AccessUserEmail"><![CDATA[test@mi6.co.uk]]></column> <column columnName="AccessUserPhone"><![CDATA[12341234]]></column> <column columnName="AccessUserFax"><![CDATA[]]></column> <column columnName="AccessUserGroups"><![CDATA[Agents]]></column> <column columnName="AccessUserAddress"><![CDATA[Steet name]]></column> <column columnName="AccessUserAddress2"><![CDATA[Address2]]></column> <column columnName="AccessUserZip"><![CDATA[12345]]></column> <column columnName="AccessUserCity"><![CDATA[City name]]></column> <column columnName="AccessUserCountry"><![CDATA[England]]></column> <column columnName="AccessUserJobTitle"><![CDATA[Agent]]></column> <column columnName="AccessUserCompany"><![CDATA[MI6]]></column> <column columnName="AccessUserPhonePriv"><![CDATA[23452345]]></column> <column columnName="AccessUserMobile"><![CDATA[45674576]]></column> <column columnName="AccessUserCustomerNumber"><![CDATA[67896789]]></column> <column columnName="AccessUserCurrencyCharacter"><![CDATA[EUR]]></column> <column columnName="AccessUserLastName"><![CDATA[Bond]]></column> <column columnName="AccessUserMiddleName"><![CDATA[]]></column> <column columnName="AccessUserActive"><![CDATA[True]]></column> <column columnName="AccessUserBusiness"><![CDATA[56785678]]></column> </item> </table> </PutEcomUsers>

Exporting Dynamicweb orders to the remote system:

<PutEcomOrders> <table tableName="EcomOrders"> <item table="EcomOrders"> <column columnName="OrderID"><![CDATA[ORDER164]]></column> <column columnName="OrderShopID" isNull="true" /> <column columnName="OrderDate"><![CDATA[13-02-2015 11:28:15:450]]></column> <column columnName="OrderModified"><![CDATA[13-02-2015 11:28:33:260]]></column> <column columnName="OrderComplete"><![CDATA[True]]></column> <column columnName="OrderDeleted"><![CDATA[False]]></column> <column columnName="OrderStateID"><![CDATA[OS1]]></column> <column columnName="OrderVAT"><![CDATA[0]]></column> <column columnName="OrderIP"><![CDATA[127.0.0.1]]></column> <column columnName="OrderReferrer" isNull="true" /> <column columnName="OrderTransactionValue" isNull="true" /> <column columnName="OrderTransactionType" isNull="true" /> <column columnName="OrderTransactionStatus" isNull="true" /> <column columnName="OrderTransactionAmount"><![CDATA[0]]></column> <column columnName="OrderTransactionPayGatewayCode" isNull="true" /> <column columnName="OrderTrackTraceNumber" isNull="true" /> <column columnName="OrderShippingMethod"><![CDATA[]]></column> <column columnName="OrderShippingMethodFee"><![CDATA[0]]></column> <column columnName="OrderPaymentMethod"><![CDATA[]]></column> <column columnName="OrderPaymentMethodFee"><![CDATA[0]]></column> <column columnName="OrderSalesDiscount"><![CDATA[0]]></column> <column columnName="OrderCurrencyName"><![CDATA[DKK]]></column> <column columnName="OrderCurrencyRate"><![CDATA[100]]></column> <column columnName="OrderCurrencyCode"><![CDATA[DKK]]></column> <column columnName="OrderCart"><![CDATA[False]]></column> <column columnName="OrderFieldsXML" isNull="true" /> <column columnName="OrderReSendEmail" isNull="true" /> <column columnName="OrderCustomerNumber" isNull="true" /> <column columnName="OrderCustomerCompany"><![CDATA[]]></column> <column columnName="OrderCustomerName"><![CDATA[]]></column> <column columnName="OrderCustomerAddress"><![CDATA[]]></column> <column columnName="OrderCustomerAddress2"><![CDATA[]]></column> <column columnName="OrderCustomerZip"><![CDATA[]]></column> <column columnName="OrderCustomerCity"><![CDATA[]]></column> <column columnName="OrderCustomerCountry"><![CDATA[Ukraine]]></column> <column columnName="OrderCustomerRegion" isNull="true" /> <column columnName="OrderCustomerPhone"><![CDATA[]]></column> <column columnName="OrderCustomerFax"><![CDATA[]]></column> <column columnName="OrderCustomerEmail"><![CDATA[]]></column> <column columnName="OrderCustomerCell"><![CDATA[]]></column> <column columnName="OrderCustomerRefID" isNull="true" /> <column columnName="OrderCustomerEAN" isNull="true" /> <column columnName="OrderCustomerVatRegNumber" isNull="true" /> <column columnName="OrderDeliveryCompany"><![CDATA[]]></column> <column columnName="OrderDeliveryName"><![CDATA[]]></column> <column columnName="OrderDeliveryAddress"><![CDATA[]]></column> <column columnName="OrderDeliveryAddress2"><![CDATA[]]></column> <column columnName="OrderDeliveryZip"><![CDATA[]]></column> <column columnName="OrderDeliveryCity"><![CDATA[]]></column> <column columnName="OrderDeliveryCountry"><![CDATA[Ukraine]]></column> <column columnName="OrderDeliveryRegion" isNull="true" /> <column columnName="OrderDeliveryPhone"><![CDATA[]]></column> <column columnName="OrderDeliveryFax" isNull="true" /> <column columnName="OrderDeliveryEmail"><![CDATA[]]></column> <column columnName="OrderDeliveryCell"><![CDATA[]]></column> <column columnName="OrderTotalPrice"><![CDATA[0]]></column> <column columnName="OrderComment" isNull="true" /> <column columnName="OrderCustomerComment"><![CDATA[]]></column> <column columnName="OrderWeight"><![CDATA[0]]></column> <column columnName="OrderVolume"><![CDATA[0]]></column> <column columnName="OrderPriceWithVAT"><![CDATA[4360.22]]></column> <column columnName="OrderPriceWithoutVAT"><![CDATA[4360]]></column> <column columnName="OrderPriceVAT"><![CDATA[0]]></column> <column columnName="OrderPriceVATPercent"><![CDATA[0]]></column> <column columnName="OrderShippingFeeWithVAT"><![CDATA[0]]></column> <column columnName="OrderShippingFeeWithoutVAT"><![CDATA[0]]></column> <column columnName="OrderShippingFeeVAT"><![CDATA[0]]></column> <column columnName="OrderShippingFeeVATPercent"><![CDATA[0]]></column> <column columnName="OrderPaymentFeeWithVAT"><![CDATA[0]]></column> <column columnName="OrderPaymentFeeWithoutVAT"><![CDATA[0]]></column> <column columnName="OrderPaymentFeeVAT"><![CDATA[0]]></column> <column columnName="OrderPaymentFeeVATPercent"><![CDATA[0]]></column> <column columnName="OrderPriceBeforeFeesWithVAT"><![CDATA[3744]]></column> <column columnName="OrderPriceBeforeFeesWithoutVAT"><![CDATA[3744]]></column> <column columnName="OrderPriceBeforeFeesVAT"><![CDATA[0.27]]></column> <column columnName="OrderPriceBeforeFeesVATPercent"><![CDATA[0.00721153846153846]]></column> <column columnName="OrderCustomerAccessUserID"><![CDATA[0]]></column> <column columnName="OrderCustomerAccessUserUserName" isNull="true" /> <column columnName="OrderShippingMethodID"><![CDATA[]]></column> <column columnName="OrderPaymentMethodID"><![CDATA[]]></column> <column columnName="OrderGatewayResult"><![CDATA[<result><EcomOrderCustomerCompany></EcomOrderCustomerCompany><EcomOrderCustomerName></EcomOrderCustomerName><EcomOrderCustomerAddress></EcomOrderCustomerAddress><EcomOrderCustomerAddress2></EcomOrderCustomerAddress2><EcomOrderCustomerZip></EcomOrderCustomerZip><EcomOrderCustomerCity></EcomOrderCustomerCity><EcomOrderCustomerCountry>UA</EcomOrderCustomerCountry><EcomOrderCustomerEmail></EcomOrderCustomerEmail><EcomOrderCustomerPhone></EcomOrderCustomerPhone><EcomOrderCustomerFax></EcomOrderCustomerFax><EcomOrderCustomerCell></EcomOrderCustomerCell><EcomOrderCustomerComment></EcomOrderCustomerComment><EcomOrderDeliveryCompany></EcomOrderDeliveryCompany><EcomOrderDeliveryName></EcomOrderDeliveryName><EcomOrderDeliveryAddress></EcomOrderDeliveryAddress><EcomOrderDeliveryAddress2></EcomOrderDeliveryAddress2><EcomOrderDeliveryZip></EcomOrderDeliveryZip><EcomOrderDeliveryCity></EcomOrderDeliveryCity><EcomOrderDeliveryCountry>UA</EcomOrderDeliveryCountry><EcomOrderDeliveryEmail></EcomOrderDeliveryEmail><EcomOrderDeliveryPhone></EcomOrderDeliveryPhone><EcomOrderDeliveryCell></EcomOrderDeliveryCell><EcomOrderCustomerAccepted>1</EcomOrderCustomerAccepted><CartV2.GotoStep2>Afgiv ordre</CartV2.GotoStep2></result>]]></column> <column columnName="OrderStepNum"><![CDATA[0]]></column> <column columnName="OrderTransactionNumber" isNull="true" /> <column columnName="OrderCustomerCountryCode"><![CDATA[UA]]></column> <column columnName="OrderDeliveryCountryCode"><![CDATA[UA]]></column> <column columnName="OrderStepHistory" isNull="true" /> <column columnName="OrderLanguageID"><![CDATA[DAN]]></column> <column columnName="OrderTransactionMailSend"><![CDATA[False]]></column> <column columnName="OrderShippingMethodDescription"><![CDATA[]]></column> <column columnName="OrderPaymentMethodDescription"><![CDATA[]]></column> <column columnName="OrderCustomerNewsletterSubcribe"><![CDATA[False]]></column> <column columnName="OrderGatewayPaymentStatus" isNull="true" /> <column columnName="OrderGatewayTransactionProblems"> <![CDATA[<?xml version="1.0" encoding="utf-16"?> <ArrayOfString xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />]]> </column> <column columnName="OrderGatewayUniqueID"><![CDATA[]]></column> <column columnName="OrderCaptureInfo"><![CDATA[<?xml version="1.0" encoding="utf-8" standalone="yes"?><OrderCaptureInfo><Timestamp>-8587777871900251869</Timestamp><State>NotCaptured</State><Message></Message></OrderCaptureInfo>]]></column> <column columnName="OrderCartV2StepIndex"><![CDATA[2]]></column> <column columnName="OrderSecret"><![CDATA[b8f8e7fe79da4fb29bae9e0a58bda788]]></column> <column columnName="OrderShippingCountrySelection"><![CDATA[DeliveryCountryThenCustomerCountry]]></column> <column columnName="OrderPaymentCountrySelection"><![CDATA[CustomerCountry]]></column> <column columnName="OrderCustomerAccepted"><![CDATA[True]]></column> <column columnName="OrderDebuggingInfo"><![CDATA[]]></column> <column columnName="OrderHasSetUserDetails"><![CDATA[False]]></column> <column columnName="OrderTrackAndTraceID"><![CDATA[0]]></column> <column columnName="OrderTrackAndTraceParameters" isNull="true" /> <column columnName="OrderIntegrationOrderID"><![CDATA[SO-101295]]></column> <column columnName="OrderRequisition"><![CDATA[]]></column> <column columnName="OrderAXOrderID" isNull="true" /> <column columnName="OrderVisitorSessionID"><![CDATA[emx5mjv1wpz1md2cp3ra5l1x]]></column> <column columnName="OrderIsExported"><![CDATA[True]]></column> <column columnName="OrderVoucherCode"><![CDATA[]]></column> <column columnName="OrderCustomerSurname" isNull="true" /> <column columnName="OrderCustomerInitials" isNull="true" /> <column columnName="OrderCustomerPrefix" isNull="true" /> <column columnName="OrderDeliverySurname" isNull="true" /> <column columnName="OrderDeliveryInitials" isNull="true" /> <column columnName="OrderDeliveryPrefix" isNull="true" /> <column columnName="OrderShippingDocumentData" isNull="true" /> <column columnName="OrderShippingProviderData" isNull="true" /> <column columnName="OrderCompletedDate"><![CDATA[13-02-2015 11:28:33:017]]></column> <column columnName="OrderImpersonatingUserID" isNull="true" /> <column columnName="OrderSecondaryUserID"><![CDATA[0]]></column> <column columnName="OrderReceiptShowCount"><![CDATA[0]]></column> <column columnName="OrderCaptureAmount"><![CDATA[0]]></column> <column columnName="OrderTransactionCardType" isNull="true" /> <column columnName="OrderExternalPaymentFee"><![CDATA[0]]></column> <column columnName="OrderPriceCalculationDate"><![CDATA[13-02-2015 11:28:16:207]]></column> <column columnName="OrderCustomerTitle" isNull="true" /> <column columnName="OrderCustomerFirstName" isNull="true" /> <column columnName="OrderCustomerMiddleName" isNull="true" /> <column columnName="OrderCustomerHouseNumber" isNull="true" /> <column columnName="OrderDeliveryTitle" isNull="true" /> <column columnName="OrderDeliveryFirstName" isNull="true" /> <column columnName="OrderDeliveryMiddleName" isNull="true" /> <column columnName="OrderDeliveryHouseNumber" isNull="true" /> <column columnName="OrderTotalPoints"><![CDATA[0]]></column> <column columnName="OrderTotalRewardPoints"><![CDATA[0]]></column> <column columnName="OrderTotalDiscountWithVAT"><![CDATA[-416]]></column> <column columnName="OrderTotalDiscountWithoutVAT"><![CDATA[-416]]></column> <column columnName="OrderTotalDiscountVAT"><![CDATA[-0.03]]></column> <column columnName="OrderTotalDiscountVATPercent"><![CDATA[0.00721153846153846]]></column> <column columnName="OrderIsQuote"><![CDATA[False]]></column> <column columnName="OrderTransactionCardNumber" isNull="true" /> <column columnName="OrderCheckoutPageID"><![CDATA[350]]></column> <column columnName="DeliveryDate"><![CDATA[01-01-1753 00:00:00:000]]></column> <column columnName="OrderVisitorSessionDate"><![CDATA[13-02-2015 11:28:32:213]]></column> <column columnName="OrderPriceCalculatedByProvider"><![CDATA[False]]></column> <column columnName="OrderTaxTransactionNumber" isNull="true" /> <column columnName="OrderVoucherUseType"><![CDATA[0]]></column> <column columnName="OrderContextID" isNull="true" /> <column columnName="OrderCustomerAccessUserExternalId" isNull="true" /> </item> </table> <table tableName="EcomOrderLines"> <item table="EcomOrderLines"> <column columnName="OrderLineID"><![CDATA[OL525]]></column> <column columnName="OrderLineOrderID"><![CDATA[ORDER164]]></column> <column columnName="OrderLineParentLineID"><![CDATA[]]></column> <column columnName="OrderLineBOM"><![CDATA[False]]></column> <column columnName="OrderLineDate"><![CDATA[13-02-2015 11:28:15:767]]></column> <column columnName="OrderLineModified"><![CDATA[13-02-2015 11:28:16:137]]></column> <column columnName="OrderLineProductNumber"><![CDATA[g1p1]]></column> <column columnName="OrderLineProductID"><![CDATA[PROD94]]></column> <column columnName="OrderLineProductVariantID"><![CDATA[]]></column> <column columnName="OrderLineProductName"><![CDATA[g1p1]]></column> <column columnName="OrderLineProductVariantText"><![CDATA[]]></column> <column columnName="OrderLineUnitPrice" isNull="true" /> <column columnName="OrderLineVatPercent" isNull="true" /> <column columnName="OrderLineVatPrice" isNull="true" /> <column columnName="OrderLineQuantity"><![CDATA[1]]></column> <column columnName="OrderLineType"><![CDATA[2]]></column> <column columnName="OrderLineReference"><![CDATA[http://head.local.dynamicweb.dk/da-DK/products.aspx]]></column> <column columnName="OrderLineBOMItemID"><![CDATA[]]></column> <column columnName="OrderLineUnitID"><![CDATA[]]></column> <column columnName="OrderLineWeight"><![CDATA[0]]></column> <column columnName="OrderLineVolume"><![CDATA[0]]></column> <column columnName="OrderLinePriceWithVAT"><![CDATA[4160]]></column> <column columnName="OrderLinePriceWithoutVAT"><![CDATA[4160]]></column> <column columnName="OrderLinePriceVAT"><![CDATA[0.3]]></column> <column columnName="OrderLinePriceVATPercent"><![CDATA[0.00721153846153846]]></column> <column columnName="OrderLineUnitPriceWithVAT"><![CDATA[4160]]></column> <column columnName="OrderLineUnitPriceWithoutVAT"><![CDATA[4160]]></column> <column columnName="OrderLineUnitPriceVAT"><![CDATA[0]]></column> <column columnName="OrderLineUnitPriceVATPercent"><![CDATA[0]]></column> <column columnName="OrderLinePageId"><![CDATA[3]]></column> <column columnName="OrderLineDiscountID"><![CDATA[]]></column> <column columnName="OrderLineFieldValues"><![CDATA[<OrderLineFieldValueCollection />]]></column> <column columnName="OrderLineAttachment"><![CDATA[]]></column> <column columnName="OrderLineWishListID"><![CDATA[0]]></column> <column columnName="OrderLinePriceCalculationReference"><![CDATA[]]></column> <column columnName="OrderLineUnitPriceCalculationReference"><![CDATA[]]></column> <column columnName="OrderLineUnitPoints" isNull="true" /> <column columnName="OrderLineUnitRewardPoints"><![CDATA[0]]></column> <column columnName="OrderLinePoints" isNull="true" /> <column columnName="OrderLineRewardPoints"><![CDATA[0]]></column> <column columnName="OrderLineRewardId"><![CDATA[0]]></column> <column columnName="OrderLineReverseChargeForVat"><![CDATA[False]]></column> <column columnName="OrderLineTotalDiscountWithVAT"><![CDATA[0]]></column> <column columnName="OrderLineTotalDiscountWithoutVAT"><![CDATA[0]]></column> <column columnName="OrderLineTotalDiscountVAT"><![CDATA[0]]></column> <column columnName="OrderLineTotalDiscountVATPercent"><![CDATA[0]]></column> </item> </table> </PutEcomOrders>

Once the DynamicwebConnectorService receives one of the requests – standard or custom – it must be handled, relayed to the remote system, and the response must be wrapped in the XML format accepted by Dynamicweb data integration – see examples of default requests and responses for various features here.

Keep in mind that you can always export a part of the database to XML using the Dynamicweb provider as source and the XML provider as destination to view a concrete example of the XML format Dynamicweb understands.

In the example add-in, the ProcessRequest method breaks down the request XML..

<GetEcomData> <tables> <Users type="all"/> </tables> </GetEcomData>

…and translates it into an SQL statement, which is then used to query the SQL database specified in a connectionString parameter from the settings in the DynamicwebConnectorService config file:

public override string ProcessRequest(string request) { string result = ""; NameValueCollection connectorSettings = (NameValueCollection)ConfigurationManager.GetSection("DynamicwebConnectorAddin"); string connectionString = connectorSettings["ConnectionString"]; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); XmlDocument doc = new XmlDocument(); doc.LoadXml(request); if (doc.DocumentElement.Name == "GetEcomData") { result = "<tables>"; foreach (XmlNode node in doc.SelectNodes("//tables/*")) { switch (node.Name) { case "Users": string sqlCommand = "select * from Users"; SqlCommand command = new SqlCommand(sqlCommand, connection); var reader = command.ExecuteReader(); result = result + WrapInXml("AccessUser", reader); break; default: throw new Exception("request for table '" + node.Name + "' was not recognized by the Connector"); } } result = result + "</tables>"; } else { throw new Exception("request of type '" + doc.DocumentElement.Name + "' was not recognized by the Connector"); } } return result; }

As you can see, this implementation connector add-in retrieves all users from the [Users] table of an SQL server database, then runs the response through the WrapInXml method to wrap the data in the format understood by Dynamicweb: 

public static string WrapInXml(string tableName, SqlDataReader reader) { StringWriter sw = new StringWriter(); XmlWriter xml = new XmlTextWriter(sw); xml.WriteStartElement("table"); xml.WriteAttributeString("tableName", tableName); while (reader.Read()) { xml.WriteStartElement("item"); xml.WriteAttributeString("table", tableName); for (int i = 0; i < reader.FieldCount; i++) { if (reader[i] != DBNull.Value) { xml.WriteStartElement("column"); xml.WriteAttributeString("columnName", reader.GetName(i)); xml.WriteValue(reader[i]); xml.WriteEndElement(); } else { xml.WriteStartElement("column"); xml.WriteAttributeString("columnName", reader.GetName(i)); xml.WriteAttributeString("isNull", "true"); xml.WriteEndElement(); } } xml.WriteEndElement(); } xml.WriteEndElement(); return sw.ToString(); }

To use the custom DynamicwebConnectorService add-in, save and build your project – then copy the .dll to the DynamicwebConnectorService folder.

Open the DynamicwebConnectorService config file and make the necessary changes:

<?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> <section name="DynamicwebConnectorAddin" type="System.Configuration.NameValueSectionHandler"/> </configSections> <DynamicwebConnectorAddin> <add key="ConnectionString" value="YOUR CONNECTION STRING" /> </DynamicwebConnectorAddin> <appSettings> <add key="ServiceName" value="DynamicwebService" /> <add key="testMode" value="False" /> <add key="TestOutputFile" value="c:\exportContent.xml" /> <add key="Secret" value="test" /> <add key="WebserviceURI" value="http://localhost:8090/DynamicwebService"/> <add key="ErpConnectorType" value="DataIntegration.Examples.DynamicwebConnectorAddin"/> </appSettings> </configuration>

You must:

  • In the configSection specify the name of your custom add-in
  • Create a settings section – here the <DynamicwebConnecorAdin> – with the appropriate nodes, here the connectionString parameter used to connect to an SQL database
  • Use the ErpConnectorType parameter in the appSettings to specify that the connector service should use your custom add-in

Next you should start the Dynamicweb service and check your connector is working using the DynamicwebConnectorService test app (Figure 16.2).

Figure 16.2 The DynamicwebConnectorService test app

In this tutorial, you’ve learned the basics of extending the default integration features in Dynamicweb, including:

  • Creating a custom data integration provider
  • Using table scripting to process data entering Dynamicweb with reference to the Dynamicweb database
  • Creating custom batch integration scheduled task add-ins
  • Subscribing to the JobFinished notification
  • Creating a custom DynamicwebConnectorService add-in for integrating with remote systems not covered by default

This concludes the T3 tutorials – good luck on the certification test!