Simple C# class to populate datatable from FetchXML

If you want to populate a datatable object with results retrieved from a FetchXML query, there are several differences in how the results are returned compared with a typical SQL query. In this sample I show an easy way to address some of the more annoying ones.

C# (29.8 KB)
 
 
 
 
 
(0)
756 times
Add to favorites
7/5/2013
E-mail Twitter del.icio.us Digg Facebook
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;

namespace Fetch2DataTable
{
    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;
        }
    }


}