Simple C# class to populate datatable from FetchXML
Views (2720)
lucasalexander
886
Like
Share
ReportI'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:
- FetchXML results do not include attributes that are not populated in CRM, even if they are explicitly requested in the query.
- You can't retrieve the label for a picklist using the attribute name + "name" convention.
- 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;
}
}
This was originally posted here.
*This post is locked for comments