Feb 18, 2016

How to Get the Entity Column List from Microsoft Dynamics CRM

If you are working to integrate your C# application with Microsoft Dynamics CRM, You might need to get the Entity Column List. This article explains how to get the list of entity columns from CRM in C# application.

You need to download the Microsoft Dynamics CRM SDK package and install it, the installation process will extract sdk files and folders.

Microsoft Dynamics Integration

Create Class Library project.

Create CRMHelper.cs then create instance ConnectToServer and paste ServerConnection class which you get from the CRM SDK.

So, CRMHelper.cs looks like this.


class CRMHelper
{
public static ServerConnection.Configuration ConnectToServer()
{
// Obtain the target organization's Web address and client logon 
// credentials from the user.
ServerConnection serverConnect = new ServerConnection();
ServerConnection.Configuration config = serverConnect.GetServerConfiguration();

return config;
}
}
public class ServerConnection
{
	//you will get this class from CRM SDK => \samplecode\cs\helpercode\crmservicehelpers.cs.
	//copy ServerConnection class from above location of CRM SDK and paste it here.


	//in GetServerConfiguration() set following values getting from webconfig file.
	//config.ServerAddress = string.Format("{0}", ConfigurationSettings.AppSettings["CRMConnection.Domain"]);
	//config.DiscoveryUri = new Uri(string.Format("{0}", ConfigurationSettings.AppSettings["CRMWS.URL"]));
	//config.OrganizationUri = new Uri(string.Format("{0}", ConfigurationSettings.AppSettings["CRMWS.URLORG"]));
}

Configure app.config/web.config file as follow to connect application with CRM in appSettings section.


      <add key="CRMWS.URL" value="https:// <domain of your CRM Account>/XRMServices/2011/Organization.svc" />
      <add key="CRMWS.URLORG" value="https://<domain of your CRM Account>/XRMServices/2011/Organization.svc" />
      <add key="CRMConnection.Domain" value="<domain of your CRM Account>" />
      <add key="CRMConnection.UserName" value="<username of your CRM Account>" />
      <add key="CRMConnection.Password" value="<password of your CRM Account>" />

Add a class to this project named "CRMAPI.cs". Make this class as partial class as below.

- public partial class CRMAPI

We are getting a list of entity column so; create a class with following properties


public class CRMField
{
      public Guid Id { get; set; }
      public string Name { get; set; }
      public string SchemaName { get; set; }
      public string LogicalName { get; set; }
      public string Type { get; set; }
      public FilterType FilterType { get; set; }
      public List<Option> Options { get; set; }
      public ControlType ControlType { get; set; }
}

Also add following classes and enum.


public enum ControlType
{
None,
Textbox,
MultiLineText,
NumTextbox,
DecimalTextbox,
Dropdown,
Datepicker
}
public class Option
{
       public int Value { get; set; }
       public string Text { get; set; }
}

public enum FilterType
{
       [Display(Name = "Equals")]
       EqualsTo = 1,
       [Display(Name = "Contains")]
       Contains = 2,
       [Display(Name = "Starts With")]
       Starts_With = 3,
       [Display(Name = "Contains Data")]
       Contains_data = 4,
       [Display(Name = "No Data")]
       No_data = 5
}


Also add Account class with all fields same as in CRM Solution. In following, I just take common fields.


public class Account
{
public Guid AccountID { get; set; }
public string Name { get; set; }
public Contact Primary_Contact { get; set; }
public string AccountNumber { get; set; }
public Account ParentAccount { get; set; }
public string Email { get; set; }
public string Main_Phone { get; set; }
public string Other_Phone { get; set; }
public string Fax { get; set; }
public string WebSite { get; set; }
public string Address { get; set; }
public AddressType AddressType { get; set; }
public string Address_Name { get; set; }
public string Street_1 { get; set; }
public string Street_2 { get; set; }
public string City { get; set; }
public string StateProvince { get; set; }
public string PostalCode { get; set; }
public string Country { get; set; }
}

Now, following is the method to return list of column from CRM.


public List<CRMField> GetAccountFields()
{
   List<CRMField> fields = null;
   //Connect to CRM
   ServerConnection.Configuration serverConfig = CRMHelper.ConnectToServer();
   OrganizationServiceProxy service = ServerConnection.GetOrganizationProxy(serverConfig);
   OrganizationServiceContext orgSvcContext = new OrganizationServiceContext(service);

   //Get all fields of any entity
   // Create the request
   RetrieveEntityRequest req = new RetrieveEntityRequest();

   req.RetrieveAsIfPublished = true;

   req.LogicalName = "account";

   req.EntityFilters = EntityFilters.Attributes;

   RetrieveEntityResponse resp = (RetrieveEntityResponse)service.Execute(req);
   EntityMetadata em = resp.EntityMetadata;
  	   fields = new List<CRMField>();
   foreach (AttributeMetadata a in em.Attributes)
   {
if (a.AttributeType != AttributeTypeCode.Virtual)
{
   CRMField column = new CRMField();
   column.Type = a.AttributeType.HasValue ? a.AttributeType.Value.ToString() : string.Empty;
   column.Name = a.DisplayName.UserLocalizedLabel != null ? a.DisplayName.UserLocalizedLabel.Label : string.Empty;
   column.LogicalName = a.LogicalName != null ? a.LogicalName : string.Empty;

 		   if (!string.IsNullOrEmpty(column.Name))
   {
foreach (string str in fieldnames)
{
   if (column.LogicalName == str)
   {
column.Id = a.MetadataId.Value;
column.LogicalName = a.LogicalName;
column.SchemaName = a.SchemaName;
column.ControlType = CRMUtility.GetControlType(a.AttributeType);
                          if (a.AttributeType.HasValue && a.AttributeType.Value == AttributeTypeCode.Picklist)
                          {
                              PicklistAttributeMetadata pickList = (PicklistAttributeMetadata)a;
                              column.Options = CRMUtility.GetOptions(pickList.OptionSet);
                          }
                          else if (a.AttributeType.HasValue && a.AttributeType.Value == AttributeTypeCode.Boolean)
                          {
                              column.Options = CRMUtility.GetBooleanOptions();
                          }
                          fields.Add(column);
                      }
                   }
                }
             }
   }
          fields = fields.OrderBy(s => s.Name).ToList();
          return fields;
}

You can see that there are other three methods (GetControlType, GetOptions, GetBooleanOptions) called from it are as below. Create CRMUtility class and add following classes to it. The CRMUtility.cs looks likeā€¦


public class CRMUtility
{
   public static List<Option> GetOptions(OptionSetMetadata optionset)
   {
List<Option> options = null;
if (optionset.Options != null && optionset.Options.Count > 0)
{
   options = new List<Option>();
   foreach (var item in optionset.Options)
   {
Option _option = new Option();
                   	_option.Text = item.Label.UserLocalizedLabel != null ? item.Label.UserLocalizedLabel.Label : string.Empty;
                   	_option.Value = item.Value.HasValue ? item.Value.Value : 0;
                   	options.Add(_option);
                }
            }
            return options;
}

internal static ControlType GetControlType(AttributeTypeCode? attrType)
{
ControlType ctrl = ControlType.None;
if (attrType.HasValue)
{
   switch (attrType)
   {
case AttributeTypeCode.BigInt:
case AttributeTypeCode.Integer:
                        ctrl = ControlType.NumTextbox;
                        break;
case AttributeTypeCode.Boolean:
case AttributeTypeCode.Picklist:
case AttributeTypeCode.Lookup:
case AttributeTypeCode.Status:
                        ctrl = ControlType.Dropdown;
                        break;
case AttributeTypeCode.DateTime:
                        ctrl = ControlType.Datepicker;
                        break;
case AttributeTypeCode.Decimal:
case AttributeTypeCode.Double:
case AttributeTypeCode.Money:
                        ctrl = ControlType.DecimalTextbox;
                        break;
case AttributeTypeCode.String:
case AttributeTypeCode.Uniqueidentifier:
                        ctrl = ControlType.Textbox;
                        break;
case AttributeTypeCode.Memo:
                        ctrl = ControlType.MultiLineText;
                        break;
   }
}
return ctrl;
}

internal static List<Option> GetBooleanOptions()
{
List<Option> options = new List<Option>();
options.Add(new Option { Text = "True", Value = 1 });
options.Add(new Option { Text = "False", Value = 0 });
return options;
}
}

The above method "GetAccountFields" will return list of fields from CRM Account entity. And "GetOptions" method will return all the options with their value available in CRM OptionSet field.

We hope you have understood all the points. In case there is any confusion, write in comments section. Our experts will answer your query as soon as possible. To learn more about Microsoft Dynamics CRM integration, keep visiting this space in future.

Author Bio:

This article has been shared by Ethan Millar working with Aegis SoftTech as senior developer from last five years. The objective of writing this post is to learn how to get the entity column list from Microsoft Dynamics CRM integration.