For a project I was working on, there was a requirement to retrieve data from Microsoft Dynamics CRM 2011 to provide to Microsoft Dynamics AX 2009. This data retrieval has to be fast and accurate.
I looked at a number of possibilities, but for various reasons they weren’t suitable options. Initially I thought I’d leverage Windows Communications Foundation (WCF) and add the Organization.svc from CRM 2011. The problem: I discovered that AX 2009 cannot consume complex WCF services.
Next I tried using the Microsoft.Xrm.Sdk.dll from CRM 2011, but that requires the use of the .NET Framework 4.0, and AX 2009 is not compatible with CLR 4.0.
Then, I tried to use CLRInterop, however generic types are not supported in AX’s marshalling layer (even in AX 2012, or so I’ve been lead to believe). This was required to access objects of type EntityReference or OptionSetValue in CRM 2011.
I then looked at synchronising tables using a third-party application. However, because I sometimes only needed one field integrated, synchronising a table seemed overkill, while it also created duplicate data. There was also a concern that the third-party tool might not always provide instantaneous updates, when they were a critical requirement.
Finally, after all of those attempts I was informed you could access information from CRM 2011 using OData and suggested that I look at using OData as an approach. It worked – and here’s how you can use OData to integrate AX 2009 with CRM 2011.
What is OData?
The Open Data Protocol (OData) is a Web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications today. OData does this by applying and building upon Web technologies such as HTTP, Atom Publishing Protocol (AtomPub) and JSON to provide access to information from a variety of applications, services, and stores.
The ‘CRM 2011 OData Query Designer’ is a tool available for CRM 2011 which helps you design and test your OData queries.
When implemented, an OData query resembles the following: http://crm2011:3456/Test1/xrmservices/2011/OrganizationData.svc/AccountSet?$filter=Address1_City eq 'Auckland'
When that query is executed, Microsoft CRM returns an XML formatted file which looks like the following:
<d:Dsl_Selected m:type="Edm.Boolean">true</d:Dsl_Selected>
<d:Dsl_name>Inspection</d:Dsl_name>
<d:TransactionCurrencyId m:type="Microsoft.Crm.Sdk.Data.Services.EntityReference">
<d:Id m:type="Edm.Guid">6c8b2b7c-62c1-e011-b6cb-00155d087d2e</d:Id>
<d:LogicalName>transactioncurrency</d:LogicalName>
<d:Name>New Zealand Dollar</d:Name>
</d:TransactionCurrencyId>
<d:dsl_actualvalue_Base m:type="Microsoft.Crm.Sdk.Data.Services.Money">
<d:Value m:type="Edm.Decimal">125.0000</d:Value>
</d:dsl_actualvalue_Base>
Retrieving the OData
The first step in creating the integration is to retrieve the OData. I created a method that accepts a URL in the form of a OData query, executes this query and selects the nodes that are relevant to the CRM entity that I am querying. I say “relevant” because the OData XML that is returned contains a significant amount of header information that may not be required.
These nodes are returned as an XmlNodeList, as shown below:
public XmlNodeList retrieveOData(URL _url)
{
str strError; // Error handling
CLRObject exception; // Error handling
CLRObject clrExcMessage; // Error handling
CLRObject innerExc; // Error handling
System.Net.HttpWebRequest httpRequest = null;
System.Net.HttpWebResponse httpResponse = null;
System.Net.NetworkCredential credential;
System.IO.Stream stream;
System.IO.StreamReader reader;
CLRObject clrObj = null;
XmlDocument xmlDoc;
XmlNodeList nodes;
str msg;
SdcaParameters_INT parameters = SdcaParameters_INT::find();
SMTPPassword password;
;
try
{
new InteropPermission(InteropKind::ClrInterop).assert();
clrObj = System.Net.WebRequest::Create(_url);
httpRequest = clrObj;
password = SdcaParameters_INT::password();
credential = new System.Net.NetworkCredential(parameters.CRMUserName,password,parameters.CRMDomain);
httpRequest.set_Credentials(credential);
httpResponse = httpRequest.GetResponse();
stream = httpResponse.GetResponseStream();
reader = new System.IO.StreamReader(stream);
msg = reader.ReadToEnd();
xmlDoc = new XmlDocument();
xmlDoc.loadXml(msg);
xmlNamespaceMgr = new XmlNamespaceManager(xmlDoc.nameTable());
xmlNamespaceMgr.AddNamespace(#Atom, #AtomURI);
xmlNamespaceMgr.AddNamespace(#Metadata, #MetadataURI);
xmlNamespaceMgr.AddNamespace(#DataServices, #DataServicesURI);
nodes = xmlDoc.documentElement().selectNodes(#AtomEntry, xmlNamespaceMgr);
CodeAccessPermission::revertAssert();
}
catch(Exception::CLRError)
{
RFID::exceptionHandler();
}
catch(Exception::Internal)
{
// BP Deviation Documented
exception = CLRInterop::getLastException();
if(exception)
{
clrExcMessage = exception.get_Message();
// BP Deviation Documented
strError = CLRInterop::getAnyTypeForObject( clrExcMessage );
innerExc = exception.get_InnerException();
while(innerExc != null)
{
clrExcMessage = innerExc.get_Message();
// BP Deviation Documented
strError = strError + "\n" + CLRInterop::getAnyTypeForObject( clrExcMessage );
innerExc = innerExc.get_InnerException();
}
throw error(strError);
}
}
return nodes;
}
Traversing the node list
Once the OData is retrieved and the XMLNodeList returned, it needs to be traversed and the necessary information needs to be extracted.
You can access a node within OData using XPath expressions, however I wanted a more a generic process. Following a design pattern similar to the Application Integration Framework (AIF) I thought I’d use a class to act as a buffer to store the retrieved nodes from the XML document.
The second method, traverseOData, takes the retrieved XmlNodeList and an object. This object is the class that is to act as a buffer. As the program traverses the XMLNodeList it checks to see if there is a method in the class that has the same name as the node.
Using the OData example above, when the program traversed the XMLNodeList and got to the node <d:Dsl_name>Inspection</d:Dsl_name> it would check to see if the class contained a method called “dsl_name.” If it did, it would execute this method and pass through two parameters: the value and the identity. In this example, there is no identity, only the value.
Name dsl_name(Name _name, str _id = ‘’)
{
name = _name;
return name;
}
In a situation where the retrieved OData contains an EntityReference, the identifier, or GUID, would be passed through to the method and this could then be used to retrieve other specific data if required.
public void traverseOData(XmlNodeList _nodes, Object _object)
{
XmlNodeList properties;
XmlNamedNodeMap xmlAttributes;
Object dictClassObject;
int i, j, l;
str methodName;
str value, id;
SysDictClass dictClass = new SysDictClass(classidget(_object));
;
for (i=0; i<_nodes.length(); i++)
{
properties = _nodes.item(i).selectSingleNode(#AtomContentProperties, this.getXmlNamespaceManager()).childNodes();
for (j=0; j<properties.length(); j++)
{
value = "";
id = "";
methodName = properties.item(j).baseName();
xmlAttributes = properties.item(j).attributes();
for (l=0; l<xmlAttributes.length(); l++)
{
switch (xmlAttributes.item(l).text())
{
case #Money :
value = properties.item(j).getNamedElement(#Value).innerXml();
break;
case #EntityReference :
id = properties.item(j).getNamedElement(#Id).innerXml();
value = properties.item(j).getNamedElement(#Name).innerXml();
break;
case #OptionSetValue :
value = properties.item(j).getNamedElement(#Value).innerXml();
break;
default :
break;
}
}
if (!value)
{
value = properties.item(j).innerXml();
}
if (dictClass.hasObjectMethod(methodName))
{
dictClass.callObject(methodName, _object, value, id);
}
}
}
}
Using the integration
As outlined above, I created a class for each “entity” in CRM from which I wanted to retrieve data. Within each of these I created a method that was named exactly the same as the node from which I wanted to retrieve data.
Some examples are:
void dsl_buyerid(str _buyerName, str _id = '')
{
;
custName = _buyerName;
this.setCustAccount(str2guid(_id));
}
void dsl_lastbid(str _salePrice = salePrice, str _id = '')
{
;
salePrice = _salePrice;
}
SdcaSalesPriceLastBid_INT salesPrice()
{
return str2num(salePrice);
}
I also created a run method that would get the appropriate XML node list and call the method to traverse the node list passing through the node list and itself. This is shown below.
void run()
{
XmlNodeList nodes;
URL url;
AXCRMODataHelper_INT axCRMODataHelper = new AXCRMODataHelper_INT();
SysDictClass dictClass;
;
url = AXCRMODataHelper_INT::getURLDsl_LotBySalesIdLotNumber(salesId, lotNumber);
nodes = axCRMODataHelper.retrieveOData(url);
if (!nodes.length())
{
throw error("Invalid selection");
}
dictClass = new SysDictClass(classNum(AxSdcaLot_INT));
axCRMODataHelper.traverseOData(nodes, this);
}
This class could then be initialised and called from anywhere within Microsoft Dynamics AX 2009:
axSdcaLot = AxSdcaLot_INT::construct(sdcaCurrentSaleParameters.SalesId, lotNumber);
axSdcaLot.run();
And the data accessed.
axSdcaLot.salesPrice();
It’s important to note that the approach outlined above works perfectly well when only one record is returned, however sometimes your query will return multiple records. To manage this, I needed to create a new method which followed the same logic except the returned object gets stored in a map.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.