With the earlier versions of Dynamics CRM you could construct FetchXML queries and the results would be returned as XML. I liked that feature but is has been deprecated in later releases. I had a need for this again recently because I wanted to get data from CRM in a format where it was easy to mail merge. I'm a big fan of Aspose Words for .Net as I have used it several times in the past with great success.
Aspose has the advantage of being able to design normal mail merge templates in Word so it is easy for a user to amend the template. Aspose uses mail merge fields just as the typical mail merge template does but you just add them manually to your template rather than from a data source.
Aspose uses data tables as the data source and I wanted a way to convert the results of a FetchXML query into a data table so it was ready to mail merge. In fact because Aspose can merge multiple data tables I need to create a dataset which I will convert to an XML string using GetXml().
A key factor was I wanted the code to be entirely ignorant of the columns in the result set. I want to be able to select any number of columns from a CRM entity, use lookup values and pick lists and columns from related tables. Unfortunately when querying pick lists you only get the value and not the text. You will need another method to get the text equivalent.
I am not detailing how I do the Aspose merge in this post. It should be easy enough if you follow their samples. Just remember for this approach to work, the field names in the data table need to be unique.
To get started I used the CRMSVCUTL.EXE utility to generate the classes of the custom entities.
crmsvcutil /url:http://localhost:5555/Org1/XRMServices/2011/Organization.svc
/out:Xrm.cs /namespace:Xrm /serviceContextName:XrmServiceContext
I added the Xrm.cs to my project and references to Microsoft.Xrm.Sdk.dll and Microsoft.Xrm.Client.dll which can be found in the SDK\bin directory. I also added System.RunTime.Serialization and System.Data. The connection string for CRM I added to the app.config. I have tried this code on Dynamics CRM 2011 and CRM 2013 and it works fine.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Client;
using Microsoft.Xrm.Client.Services;
using Xrm;
static IOrganizationService _service;
static OrganizationService _orgService;
static string FetchExpression()
{
String connectionString = ConfigurationManager.AppSettings["xrmConnectionString"];
// Establish a connection to the organization web service using CrmConnection.
Microsoft.Xrm.Client.CrmConnection connection = CrmConnection.Parse(connectionString);
// Obtain an organization service proxy.
// The using statement assures that the service proxy will be properly disposed.
using (_orgService = new OrganizationService(connection))
{
string fetchquery = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
<entity name='contact'>
<attribute name='fullname' />
<attribute name='new_referencenumber' />
<attribute name='new_locationid' />
<attribute name='contactid' />
<attribute name='new_contactmechanism' />
<order attribute='fullname' descending='false' />
<filter type='and'>
<condition attribute='statecode' operator='eq' value='0' />
<condition attribute='fullname' operator='not-null' />
</filter>
<link-entity name='systemuser' from='systemuserid' to='owninguser' visible='false' link-type='outer' alias=user'>
<attribute name='domainname' />
</link-entity>
</entity>
</fetch>";
_service = (IOrganizationService)_orgService;
RetrieveMultipleRequest req = new RetrieveMultipleRequest();
FetchExpression fetch = new FetchExpression(fetchquery);
req.Query = fetch;
RetrieveMultipleResponse resp = (RetrieveMultipleResponse)_service.Execute(req);
// Create a dataset and datatable
DataSet results = new DataSet("Results");
DataTable Table1 = new DataTable("SingleRowTable");
DataColumn column;
DataRow workRow;
foreach (Entity entity in resp.EntityCollection.Entities)
{
//create the columns in the data table
foreach (KeyValuePair<String, Object> attribute in entity.Attributes)
{
column = new DataColumn();
switch (attribute.Value.GetType().Name)
{
case "AliasedValue":
column.DataType = entity.GetAttributeValue<AliasedValue>(attribute.Key).Value.GetType();
break;
case "EntityReference":
column.DataType = entity.GetEntityReferenceValue<EntityReference>(attribute.Key).Name.GetType();
break;
case "OptionSetValue":
column.DataType = entity.GetAttributeValue<OptionSetValue>(attribute.Key).Value.GetType();
break;
default :
column.DataType = attribute.Value.GetType();
break;
}
column.ColumnName = attribute.Key;
Table1.Columns.Add(column);
}
// add the values to the row
workRow = Table1.NewRow();
foreach (KeyValuePair<String, Object> attribute in entity.Attributes)
{
switch (attribute.Value.GetType().Name)
{
case "AliasedValue":
workRow[attribute.Key] = entity.GetAttributeValue<AliasedValue>(attribute.Key).Value;
break;
case "EntityReference":
workRow[attribute.Key] = entity.GetEntityReferenceValue<EntityReference>(attribute.Key).Name;
break;
case "OptionSetValue":
workRow[attribute.Key] = entity.GetAttributeValue<OptionSetValue>(attribute.Key).Value;
break;
default:
workRow[attribute.Key] = attribute.Value;
break;
}
}
Table1.Rows.Add(workRow);
// only one row expected so exit
break;
}
results.Tables.Add(Table1);
return results.GetXml();
}
}
The results are in a very simple XML structure and already to use for the mail merge.
<Results>
<SingleRowTable>
<fullname>Adam Ant</fullname>
<new_referencenumber>PSN-00000286</new_referencenumber>
<new_locationid>Brighton</new_locationid>
<contactid>2d31f962-5f44-e211-ae45-00155d010a10</contactid>
<new_contactmechanism>100000009</new_contactmechanism>
<user.domainname>MSCRM2011\Administrator</user.domainname>
</SingleRowTable>
</Results>
No comments:
Post a Comment