Retrieving the Message from Service Bus Queue

This is a follow up to the previous post where I sent a strongly typed XML message from a CRM plugin to a Service Bus Queue. This is what the Xml looks like that I sent to the queue.
Note that the root node is the name of the entity. This is a snippet to show the attributes and then the formatted values.  You can easily manipulate the Xml by changing the way it populates the datatable.

 <contact>
  <attributes>
    <address1_addressid>c6fd930f-512a-42fb-a645-af4a672c740f</address1_addressid>
    <address2_addressid>6a280e36-c009-463b-84f1-8666c2dfc5ba</address2_addressid>
    <address2_addresstypecode>1</address2_addresstypecode>
    <address2_freighttermscode>1</address2_freighttermscode>
    <address2_shippingmethodcode>1</address2_shippingmethodcode>
    <address3_addressid>5475a20e-3c79-479a-a1d5-05b0f144a8fc</address3_addressid>
    <contactid>1d50bcf0-2519-e611-80da-5065f38b46e1</contactid>
    <createdby>Charles Emes</createdby>
    <createdon>13/05/2016 16:15:51</createdon>
;... more attributes
;... now the formatted values
    <address2_addresstypecodename>Default Value</address2_addresstypecodename>
    <address2_freighttermscodename>Default Value</address2_freighttermscodename>
    <address2_shippingmethodcodename>Default Value</address2_shippingmethodcodename>
    <createdonname>13/05/2016 17:15</createdonname>
  </attributes></contact>

What I'm missing is a namespace and that is easy enough to add once I load it into an XmlDocument. I'm a BizTalk developer so right now I'm happy with a message that I can transform into anything I want. The attributes are in alphabetical order so I can cope with missing attributes easily enough in the XSD by making Nillable=true and MinOccurs=0. But I can also deserialize this into a contact object by creating a class form the XSD using the XSD.EXE.  Note this is absolutely not a CRM Contact object but my own object. Check out my usings because there is no Microsoft.Xrm.SDK anywhere to be seen. 

This receiver is just a console app and I ripped the code from another blogger. I'm just using it show what you can do with the message now you've got it.

     
static void Main(string[] args)
{
string connectionString = CloudConfigurationManager.GetSetting("Microsoft.ServiceBus.ConnectionString");
QueueClient Client = QueueClient.CreateFromConnectionString(connectionString, "anoqueue");
//Console.WriteLine("\nReceiving message from Queue...");
BrokeredMessage message = null;
NamespaceManager namespaceManager = NamespaceManager.Create();
while (true)
{
try
{
//receive messages from Queue
message = Client.Receive(TimeSpan.FromHours(10));
if (message != null)
{
Console.WriteLine(string.Format("Message received: Id = {0}", message.MessageId));
string s = new StreamReader(message.GetBody<Stream>(), Encoding.ASCII).ReadToEnd();
// load into an XML Document to s
System.Xml.XmlDocument xmldoc = new System.Xml.XmlDocument();
xmldoc.LoadXml(s);
// need to add a namespace because it doesn't have one
xmldoc.DocumentElement.SetAttribute("xmlns", "http://xrm.generic.schemas");
string mydocpath = @"C:\Projects\Test\ReceiveFromSB\";
xmldoc.Save(mydocpath + "saved.xml");
// Deserialize to an object too
XmlRootAttribute xRoot = new XmlRootAttribute();
xRoot.ElementName = message.Properties["EntityName"].ToString();
XmlSerializer serializer = new XmlSerializer(typeof(contact),xRoot);
StringReader rdr = new StringReader(s);
contact myContact = (contact)serializer.Deserialize(rdr);
// Now delete the message
message.Complete();

}
else
{
//no more messages in the queue
break;
}
}
catch (MessagingException me)
{
if (!me.IsTransient)
{
Console.WriteLine(me.Message);
throw;
}
else
{
// HandleTransientErrors(e);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw;
}
}
Client.Close();
}}
 

Serialize a CRM Entity to XML in Sandbox mode Plugin

I have been struggling with this problem for a while. Previous posts have outlined the problem: how to get changes in CRM sent to Back Office systems as XML messages. When using CRM Online a big frustration is that you cannot serialize objects to XML because the WriteObject method of the serializer throws a security exception.

While you can post the Context of a plugin to the Azure Service Bus, you have to use the CRM SDK to interpret this and turn it into something useful. My previous post outlines how to do this with an Azure Worker Process.

(You could construct the XML message line by line using string builder but seriously who wants to do that?)

I found a simple solution which may solve the problem. It was this post that provided the inspiration. It describes how to turn FetchXML results into a data table.  Now FetchXML returns an entity collection and the code shows how to load that into a data table and it also handles the special CRM data types like Entity Reference. 

So there you are in a Sandbox plugin.  You have the Post-Image entity and you want to convert that into a nicely structured XML file that you can post to the Azure Service Bus.  You can use part of the code above (ignoring the FetchXML query) to convert the Post-Image to a data table.  I put it into a function I called ConvertEntityToDataTable.

Now getting XML is easy.

DataSet ds = new DataSet("Invoice");
DataTable dt = new DataTable("Attributes");
ConvertEntityToDataTable(dt, entity);
ds.Tables.Add(dt);

string xml = ds.GetXml();

The result looks like this (I've just given an extract)
<Invoice>
 <Attributes>
 <billto_city>London</billto_city>
 <billto_line1>12 Hgh Street</billto_line1>
 <billto_line2>Clapham Common</billto_line2>
 <billto_line3>Clapham</billto_line3>
 </Attributes>
</Invoice>

Remember that PostImages provide their attributes in alphabetical order which is great for mapping structured XML messages. The Target entity does not so you would need to address that.
The XML is missing some things I would need to add:
  (a) the xml declaration at the top specifying the encoding
  (b) a namespace to identify the message to an ESB

But I am delighted with the result because now I can construct proper XML messages in a Sandbox plugin. 

The two functions required are shown below
///////
         private void ConvertEntityToDataTable(DataTable dataTable, Entity entity)
        {
            DataRow row = dataTable.NewRow();
            foreach (var attribute in entity.Attributes)
            {
                if (!dataTable.Columns.Contains(attribute.Key))
                {
                    dataTable.Columns.Add(attribute.Key);
                }
                row[attribute.Key] = getAttributeValue(attribute.Value).ToString();
            }
            foreach (var fv in entity.FormattedValues)
            {
                if (!dataTable.Columns.Contains(fv.Key + "name"))
                {
                    dataTable.Columns.Add(fv.Key + "name");
                }
                row[fv.Key + "name"] = fv.Value;
            }
            dataTable.Rows.Add(row);
        }
///////
        private object getAttributeValue(object entityValue)
        {
            object output = "";
            switch (entityValue.ToString())
            {
                case "Microsoft.Xrm.Sdk.EntityReference":
                    output = ((EntityReference)entityValue).Name;
                    break;
                case "Microsoft.Xrm.Sdk.OptionSetValue":
                    output = ((OptionSetValue)entityValue).Value.ToString();
                    break;
                case "Microsoft.Xrm.Sdk.Money":
                    output = ((Money)entityValue).Value.ToString();
                    break;
                case "Microsoft.Xrm.Sdk.AliasedValue":
                    output = getAttributeValue(((Microsoft.Xrm.Sdk.AliasedValue)entityValue).Value);
                    break;
                default:
                    output = entityValue.ToString();
                    break;
            }
            return output;
        }
    }

Transforming XML using BizTalk Mapper generated XSLT

If I have to write XSLT I try and make use of the BizTalk Mapper because it can generate XSLT in a fraction of the time.  It also supplies a test harness so you can use an input file to test out your map.
You can add functoids to your map and I make use of the Scripting functoid so that I can write inline C# script.  Here is an example of a function that takes a string source element and will truncate it if it is longer than the string length the target element can handle.  It writes out an Information message to the event log if the maximum length is exceeded.
The function takes two extra parameters apart from the source element:  the name of the source element and the maximum length.  Add them by clicking on the ellipses next to Configure Functoid Inputs.

Here is the inline C# script.
public string Transform(string param1, string fieldname, int32 maxlength)
{
       if (param1.Length > maxlength)
      {
             System.Diagnostics.EventLog.WriteEntry("Transformation", fieldname + " in excess of " +   maxlength.ToString() + " chars, truncating", System.Diagnostics.EventLogEntryType.Warning);
             return param1.Substring(0, 20);
      }
      else
     {
             return param1;
      }
}

What BizTalk does is to write this out as a function at the botton of the XSLT.  To produce the XSLT, use Validate Map and then navigate to where the output window has written the file. 

Now I struggled a bit to get this XSLT to work until I finally found the answer was to use an XPathDocument instead of an XmlDocument. That's it. A transformation using the output from the BizTalk Mapper.  Deep, deep joy. 

Add the following using statements
using System.Xml;
using System.Xml.Xpath;
using System.Xml.Xslt;

// Transforms an XML document
// using an XSLT generated by BizTalk
// note use of XPathDocument
private XmlDocument Transform()
{

     XslCompiledTransform xslt = new XslCompiledTransform();
     // load the xslt
     xslt.Load(@"C:\Projects\Import\MySchema.xsl", new XsltSettings(false, true), new XmlUrlResolver());
     string filePathName = @"C:\Projects\Import\MyXML.xml";
     //Load the XML data file.
     XPathDocument doc1 = new XPathDocument(filePathName);
     // create a memory stream
     MemoryStream ms = new MemoryStream();
    //Create an XmlTextWriter to write to the memory stream
    XmlTextWriter writer = new XmlTextWriter(ms, Encoding.Unicode);
    writer.Formatting = Formatting.Indented;

    //Transform the file.
    xslt.Transform(doc1, null, writer, null);
    ms.Seek(0, SeekOrigin.Begin);  // ** UPDATE changed from ms.Position=0 ****//
     if (ms.Length == 0)
    {
           Exception ex = new Exception ("Transform error , output is null");
           throw ex;
    }
    // load the memory stream into a XML document
    XmlDocument output = new XmlDocument();
    output.Load(ms);
    writer.Close();
    return output;
}

Converting XML to PDF

I was looking for an application that would convert an XML file into a PDF document. In fact I was trying to create an invoice as a PDF file were the invoice had originated from Great Plains (an application from the Microsoft Dynamics suite that claims to be an accounts package).

A colleague recommended Aspose.Words for .NET to me as he was using it for another project. It uses a Word document as a template and sets it up as a mailmerge. Now this made me a bit wary. I've had first hand expereince of programming a Word mailmerge and it wasn't a pleasant outcome. So the thought of doing a mailmerge in Word wasn't appealing. But it turns out that Aspose.Words for .Net has a single line API call that does the mailmerge for you. Brilliant!

doc.MailMerge.ExecuteWithRegions(GetTestOrderTotals());

But the second problem is a failing in Word 2007 - it doesn't support mailmerge with XML documents as a datasource. So I started looking at the examples that ship with Aspose.Words and stumbled on a Sales Invoice demo. In this case the Word document was using the NorthWinds Access database as the mailmerge source. In fact it was using 3 different views for the order header, the order line items and the order totals.

So how was I going to make that work with my XML source? A few minutes Googling gave me the answer. Word is simply using DataTables as the datasource so all I had to do was create a new DataTable with the same name as the one the Word document expected and create the appropriate columns with the correct data types. Then create a new row in the datatable and pass in the values extracted from the XML document. Bingo.

string Total = GetFieldFromXml(xmldoc, "eConnect/SOPTransactionType/taSopHdrIvcInsert/DOCAMNT");
// other strings declared and assigned here
ds.Tables.Add("OrderTotals");
ds.Tables[0].Columns.Add("OrderID");
ds.Tables[0].Columns.Add("Subtotal");
ds.Tables[0].Columns.Add("Freight");
ds.Tables[0].Columns.Add("Total");
ds.Tables[0].Rows.Add(OrderID, Subtotal, Freight, Total);

The order line items was easy to solve - I just created a DataRow for each node within my list of line items in the XML message.

What about creating the PDF document? Easy, just one line of code in Aspose.Words to specify that a PDF version is required and to open in the Web browser. I just did the usual trick of specifying the Response.ContentHeader to be application/pdf.

HttpResponse resp = Context.Response;
System.Text.Encoding enc = System.Text.Encoding.GetEncoding("ISO-8859-1");
resp.ContentEncoding = enc;
resp.ContentType = "application/pdf";
// now display PDF in browser
doc.Save("Aspose.Words.Demos.pdf", SaveFormat.Pdf, SaveType.OpenInBrowser, resp);

I was amazed how simple it was to create a PDF invoice. The great benefit of Aspose.Words is the use of the Word document as a template. It's a snip to modify the layout to get what you want. If you want to add extra fields you'll need to modify the views in Access to produce extra columns. No problem for me because I'm an old hand at Access but if you are unfamiliar with it then you might want to reconsider this option. But this is a great solution for my needs. Watch out though there is license cost to pay!