Retrieve records using Query Expression & C# SDK in CRM

Let us see a simple example to retrieve records using Query Expression,

Task: Get a Contact record with the following values, where Full Name = Arun Potti

Retrieve Multiple - FetchXML SDK

Solution: Follow the below Steps,

Step 1: Include the below References in your project, you can get the same from Latest SDK.

Goto the path, SDK -> Bin for the dlls

Microsoft.Crm.Sdk.Proxy
Microsoft.Xrm.Sdk

Step 2: Include the below Framework Assemblies in your project,

Reference Manager - ConnectToCRM

Step 3: Add the below namespaces in your class file,

using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using System.ServiceModel.Description;
using Microsoft.Xrm.Sdk.Query;

Step 4: First we have to connect to CRM, for details Click Here

Step 5: User the below method to retrieve record, and it accepts the below parameters

Query Expression Retrieve Record Parameters

private static EntityCollection GetEntityCollection(IOrganizationService service, string entityName, string attributeName, string attributeValue, ColumnSet cols)
{
QueryExpression query = new QueryExpression
{
EntityName = entityName,
ColumnSet = cols,
Criteria = new FilterExpression
{
Conditions =
{
new ConditionExpression
{
AttributeName = attributeName,
Operator = ConditionOperator.Equal,
Values = { attributeValue }
}
}
}
};
return service.RetrieveMultiple(query);
}

Final usage of the function is as follows,

EntityCollection ec = GetEntityCollection(_service, "contact", "fullname", "Arun Potti", new ColumnSet("fullname", "parentcustomerid", "gendercode", "birthdate", "creditlimit", "donotsendmm"));

Step 6: Final Code is here,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using System.ServiceModel.Description;
using Microsoft.Xrm.Sdk.Query;

namespace Retrieve_Record
{
class Program
{
static IOrganizationService _service;
static void Main(string[] args)
{
EntityCollection ec = null;
ConnectToMSCRM("arunpotti@XXXXXX.onmicrosoft.com", " XXXXXX", "https:// XXXXXX.api.crm5.dynamics.com/XRMServices/2011/Organization.svc");
Guid userid = ((WhoAmIResponse)_service.Execute(new WhoAmIRequest())).UserId;

if (userid == Guid.Empty) return; //Check for CRM Connection Establishment. If Not return, other wise will proceed to next step

ec = GetEntityCollection(_service, "contact", "fullname", "Arun Potti", new ColumnSet("fullname", "parentcustomerid", "gendercode", "birthdate", "creditlimit", "donotsendmm"));

if(ec.Entities.Count > 0) //Check for EntityCollection count
{
string output = string.Empty;
foreach (var item in ec.Entities)
{
//String
if (item.Attributes.Contains("fullname")) //Check for fullname value exists or not in Entity Collection
output += "Full Name : " + item.Attributes["fullname"] + "\n";

//Lookup
if (item.Attributes.Contains("parentcustomerid")) //Check for parentcustomerid exists or not in Entity Collection
output += "Company : " + ((EntityReference)item.Attributes["parentcustomerid"]).Name + "\n";

//OptionSet
if (item.Attributes.Contains("gendercode")) //Check for gendercode exists or not in Entity Collection
output += "Gender : Name - " + item.FormattedValues["gendercode"] + ", Value - " + ((OptionSetValue)item.Attributes["gendercode"]).Value + "\n";

//Date
if (item.Attributes.Contains("birthdate")) //Check for birthdate exists or not in Entity Collection
output += "Birthday : " + ((DateTime)item.Attributes["birthdate"]).ToLocalTime().ToShortDateString().ToString() + "\n";

//Currency
if (item.Attributes.Contains("creditlimit")) //Check for creditlimit exists or not in Entity Collection
output += "Credit Limit : " + ((Money)item.Attributes["creditlimit"]).Value + "\n";

//Two Options
if (item.Attributes.Contains("donotsendmm")) //Check for donotsendmm exists or not in Entity Collection
output += "Send Marketing Materials : Name - " + item.FormattedValues["donotsendmm"] + ", Value - " + ((Boolean)item.Attributes["donotsendmm"]).ToString();
}
Console.WriteLine(output);
Console.ReadKey();
}
}

private static void ConnectToMSCRM(string UserName, string Password, string SoapOrgServiceUri)
{
try
{
ClientCredentials credentials = new ClientCredentials();
credentials.UserName.UserName = UserName;
credentials.UserName.Password = Password;
Uri serviceUri = new Uri(SoapOrgServiceUri);
OrganizationServiceProxy proxy = new OrganizationServiceProxy(serviceUri, null, credentials, null);
proxy.EnableProxyTypes();
_service = (IOrganizationService)proxy;
}
catch (Exception ex)
{
Console.WriteLine("Error while connecting to CRM " + ex.Message);
Console.ReadKey();
}
}

private static EntityCollection GetEntityCollection(IOrganizationService service, string entityName, string attributeName, string attributeValue, ColumnSet cols)
{
QueryExpression query = new QueryExpression
{
EntityName = entityName,
ColumnSet = cols,
Criteria = new FilterExpression
{
Conditions =
{
new ConditionExpression
{
AttributeName = attributeName,
Operator = ConditionOperator.Equal,
Values = { attributeValue }
}
}
}
};
return service.RetrieveMultiple(query);
}
}
}

Step 7:  You can see the below output,

Retrieve Multiple - FetchXML SDK Output

Please provide your valuable feedback.

Advertisements

One thought on “Retrieve records using Query Expression & C# SDK in CRM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s