Introduction

FetchXML is the format for executing queries in Microsoft Dynamic CRM Online. This is a proprietary format of Microsoft for retrieving data from Microsoft dynamics CRM.

Background

SQL like queries can be fired on CRM Online using FetchXML. You can create FetchXML using Advanced find option in CRM Online. Under Advanced Find, there is an option to download FetchXML query. After getting first draft of FetchxML via Advanced Find option in CRM Online, you can do the necessary tweaks so as to meet any special needs for creating Reports or any custom code. For testing FetchXML query, this tool can be used.

Using the Code

Inside the project TestFetchXML, there is a method "btnFetchXML_Click" in the FetchXML class for executing the query on CRM Online. Results of the the query are returned in the form of entity collection. This entity collection is parsed and all the rows returned are displayed in the output text box. In case fetchXML or credentials are incorrect, Exception will be printed in the output textbox.

For getting the organizationURI , open CRM Online site for your organization. Click on Settings -> Customizations -> Developer Resources. From Service EndPoints, Choose the URL specified under Organization Service.

Credentials used are Office 365 credentials. Sample URI and credentials are loaded on load of the application.

  Uri organizationUri = new Uri(txtURI.Text);
  Uri homeRealmUri = null;
  ClientCredentials credentials = new ClientCredentials();
  credentials.UserName.UserName = txtUserName.Text;
  credentials.UserName.Password = txtPassword.Text;

  String fetchXmlresp = string.Empty;
  Guid _accountid = Guid.Empty;
  OrganizationServiceProxy orgProxy = 
  new OrganizationServiceProxy(organizationUri, homeRealmUri, credentials, null);
  IOrganizationService _service = (IOrganizationService)orgProxy;
  
  try
  {
  txtOutput.Text = "";
  Microsoft.Xrm.Sdk.AliasedValue result;
  Microsoft.Xrm.Sdk.EntityReference entityref;
               
  Microsoft.Xrm.Sdk.EntityCollection resultEntity = 
  	_service.RetrieveMultiple(new FetchExpression(txtinput.Text));
  foreach (var c in resultEntity.Entities)
  {
      txtOutput.Text = txtOutput.Text  + "\r\n";
      for (int i = 0 ; i< c.Attributes.Count; i++)
      {
          string colName = c.Attributes.Keys.ElementAt(i);
          string resulttemp = c.Attributes.Values.ElementAt(i).ToString();
          if (resulttemp.IndexOf("Microsoft.Xrm.Sdk.EntityReference") > -1)
          {
             entityref = (Microsoft.Xrm.Sdk.EntityReference)c.Attributes.Values.ElementAt(i);
             resulttemp = entityref.Name;
          }
          txtOutput.Text = txtOutput.Text + " " + resulttemp ;
       }
    }
  }
  catch (Exception ex)
  {
       txtOutput.Text = ex.Message;
  }

FetchXML Executor for CRM Online