Simple C# class to populate datatable from FetchXML

I've typically preferred to access Dynamics CRM data using SQL queries, so I have never worked much with FetchXML. I've recently started working on a bit of a hobby project where it makes more sense to populate a datatable from using FetchXML than to use a SQL query, but unfortunately I have encountered three differences between FetchXML and direct SQL that I don't particularly like:

  1. FetchXML results do not include attributes that are not populated in CRM, even if they are explicitly requested in the query.
  2. You can't retrieve the label for a picklist using the attribute name + "name" convention.
  3. Your code has to add additional logic to deal with AliasedValues, EntityReferences, etc.

Here is a class I wrote to execute a FetchXML query and populate a datatable with the results.

class QueryUtility  
{  
public DataTable ExecuteFetchXml(string fetchXmlFragment, IOrganizationService service)  
{  
//execute fetchxml  
FetchExpression fetch = new FetchExpression(fetchXmlFragment);  
EntityCollection fetchresults = service.RetrieveMultiple(fetch);  

DataTable resultsTable = new DataTable("results");  
if (fetchresults.Entities.Count > 0)  
{  
for(int i=0;i<fetchresults.Entities.Count;i++)  
{  
var entity = fetchresults.Entities[i];  
DataRow row = resultsTable.NewRow();  
foreach (var attribute in entity.Attributes)  
{  
if (!resultsTable.Columns.Contains(attribute.Key))  
{  
resultsTable.Columns.Add(attribute.Key);  
}  
row[attribute.Key] = getAttributeValue(attribute.Value).ToString();  
}  
foreach (var fv in entity.FormattedValues)  
{  
if (!resultsTable.Columns.Contains(fv.Key + "name"))  
{  
resultsTable.Columns.Add(fv.Key + "name");  
}  
row[fv.Key + "name"] = fv.Value;  
}  

resultsTable.Rows.Add(row);  
}  
}  
return resultsTable;  
}  

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;  
}  
}
comments powered by Disqus