Sample code to retrieve more than 5000 records using FetchXML in CRM

Hi,

Sharing a sample code to retrieve more than 5000 records using the Fetch XML.

Version 1 :


private List<Entity> GetTotalRecordsfromFetch(string fetchXML, IOrganizationService orgService)
{
List<Entity> lstEntity = new List<Entity>();

int fetchCount = 5000;
int pageNumber = 1;
string pagingCookie = null;

while (true)
{
// Build fetchXml string with the placeholders.
string xml = this.CreateXml(fetchXML, pagingCookie, pageNumber, fetchCount);
RetrieveMultipleRequest fetchRequest = new RetrieveMultipleRequest
{
Query = new FetchExpression(xml)
};

var returnCollections = ((RetrieveMultipleResponse)orgService.Execute(fetchRequest)).EntityCollection;

if (returnCollections.Entities.Count >= 1)
{
lstEntity.AddRange(returnCollections.Entities);
}

if (returnCollections.MoreRecords)
{
pageNumber++;

results.pagingCookie = returnCollections.PagingCookie;
}
else
{
// If no more records in the result nodes, exit the loop.
break;
}
}

return lstEntity;
}

public string CreateXml(string xml, string cookie, int page, int count)
{
StringReader stringReader = new StringReader(xml);
XmlTextReader reader = new XmlTextReader(stringReader);

XmlDocument doc = new XmlDocument();
doc.Load(reader);

XmlAttributeCollection attrs = doc.DocumentElement.Attributes;

if (cookie != null)
{
XmlAttribute pagingAttr = doc.CreateAttribute("paging-cookie");
pagingAttr.Value = cookie;
attrs.Append(pagingAttr);
}

XmlAttribute pageAttr = doc.CreateAttribute("page");
pageAttr.Value = System.Convert.ToString(page);
attrs.Append(pageAttr);

XmlAttribute countAttr = doc.CreateAttribute("count");
countAttr.Value = System.Convert.ToString(count);
attrs.Append(countAttr);

StringBuilder sb = new StringBuilder(1024);
StringWriter stringWriter = new StringWriter(sb);

XmlTextWriter writer = new XmlTextWriter(stringWriter);
doc.WriteTo(writer);
writer.Close();

return sb.ToString();
}

Version 2 :


private static List<Entity> GetTotalRecordsFetchXML(OrganizationServiceProxy orgProxy, string fetchXML)
{
XDocument xDocument = XDocument.Parse(fetchXML);
var fetchXmlEntity = xDocument.Root.Element("entity").ToString();

EntityCollection entityColl = new EntityCollection();
List<Entity> lstEntity = new List<Entity>();
int page = 1;
do
{

entityColl = orgProxy.RetrieveMultiple(new FetchExpression(
string.Format("<fetch version='1.0' page='{1}' paging-cookie='{0}'>" + fetchXmlEntity + "</fetch>",
SecurityElement.Escape(entityColl.PagingCookie), page++)));

lstEntity.AddRange(entityColl.Entities);
}
while (entityColl.MoreRecords);

return lstEntity;
}

Hope it helps..

Advertisements

System.Security.SecurityException: That assembly does not allow partially trusted callers while using HttpUtility.UrlEncode in CRM

Hi,

We got the below error in one of our Sandboxed Plugins

“System.Security.SecurityException: That assembly does not allow partially trusted callers”.

We realised it was because of using System.Web.HttpUtility class


We replaced it with EscapeDataString method and this fixed the issue.

System.Uri.EscapeDataString()

Helpful thread

https://stackoverflow.com/questions/3840762/how-do-you-urlencode-without-using-system-web

Hope it helps..

Using WebClient to call external service from CRM Online Sandboxed Plugin (HTTP POST and JSON)

Sharing a sample code that can be used to send JSON data using POST to an external service inside CRM Online Plugin. As plugin runs in sandbox mode we cannot reference Newtonsoft’s JSON.NET library http://www.newtonsoft.com/json. The other option could be to use ILMerge, which isn’t that elegant.

Here, basically we are passing some information related to lead to an external service on Post Create of it.


using Microsoft.Xrm.Sdk;
using System;
using System.IO;
using System.Net;
using System.Runtime.Serialization.Json;
using System.Text;

namespace MyTestPlugin
{

 public class Lead
 {
 public string Topic { get; set; }
 public string FullName { get; set; }
 public string Email { get; set; }
 }

 public class MyPluginClass : IPlugin
 {
 public void Execute(IServiceProvider serviceProvider)
 {
 try
 {
 // Obtain the execution context from the service provider.
 IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

 // The InputParameters collection contains all the data passed in the message request.
 if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
 {
 // Obtain the target entity from the input parameters.
 Entity entity = (Entity)context.InputParameters["Target"];

 using (WebClient client = new WebClient())
 {
 var myLead = new Lead();
 myLead.Topic = entity.Attributes["subject"].ToString();
 myLead.FullName = entity.Attributes["fullname"].ToString();
 myLead.Email = entity.Attributes["emailaddress1"].ToString();

 DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(Lead));
 MemoryStream memoryStream = new MemoryStream();
 serializer.WriteObject(memoryStream, myLead);
 var jsonObject = Encoding.Default.GetString(memoryStream.ToArray());


 var webClient = new WebClient();
 webClient.Headers[HttpRequestHeader.ContentType] = "application/json";
 var code = "key";
 var serviceUrl = "https://xyz.azurewebsites.net/api/mylead?code=" + code;

 // upload the data using Post mehtod
 string response = webClient.UploadString(serviceUrl, jsonObject);
 }
 }
 }
 catch (Exception ex)
 {
 throw new InvalidPluginExecutionException(ex.Message);
 }
 }
 }
}

Hope it helps..


Using MediaWiki API for Showing content of Wikipedia page inside Dynamics 365 form

Hi,

Recently we had a requirement to show content of Wikipedia page and display it inside CRM in one of the entities form.

For this purpose, we can make use of “MediaWiki web service API.”

https://www.mediawiki.org/wiki/API:Main_page

To create the query, we can use the below tool

https://en.wikipedia.org/wiki/Special%3aApiSandbox

The html source code


<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<script type="text/javascript">

function SetWikiContent() {

// get country name from crm form
if (Xrm.Page.getAttribute("new_name") != null) {

var title = Xrm.Page.getAttribute("new_name").getValue();

// set the url
var url = "https://en.wikipedia.org/w/api.php?format=json&action=parse&page=" + title + "&prop=text&section=0&callback=?";

$.getJSON(url, function (data) {
for (text in data.parse.text) {
var text = data.parse.text[text].split("
");
var pText = "";

for (p in text) {
//Remove html comment
text[p] = text[p].split("<!--");
if (text[p].length > 1) {
text[p][0] = text[p][0].split(/\r\n|\r|\n/);
text[p][0] = text[p][0][0];
text[p][0] += "
 ";
}
text[p] = text[p][0];

//Construct a string from paragraphs
if (text[p].indexOf("
") == text[p].length - 5) {
var htmlStrip = text[p].replace(/<(?:.|\n)*?>/gm, '') //Remove HTML
var splitNewline = htmlStrip.split(/\r\n|\r|\n/); //Split on newlines
for (newline in splitNewline) {
if (splitNewline[newline].substring(0, 11) != "Cite error:") {
pText += splitNewline[newline];
pText += "\n";
}
}
}
}
pText = pText.substring(0, pText.length - 2); //Remove extra newline
pText = pText.replace(/\[\d+\]/g, ""); //Remove reference tags (e.x. [1], [4], etc)

Xrm.Page.getAttribute('new_wikiinfo').setValue(pText);
}
});
}
}

</script>
</head>
<body onload="SetWikiContent();">
</body>
</html>

The helpful article

http://stackoverflow.com/questions/8555320/is-there-a-clean-wikipedia-api-just-for-retrieve-content-summary

Hope it helps..

Simple Bing Map HTML Web Resource to show Country location in Dynamics 365

Just sharing a HTML Web resource code to show Country location inside the map using name of the country.

Source Code: –


<!DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>My Bing Map</title>
<script charset="UTF-8" type="text/javascript" src="https://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=7.0&s=1"></script>
<script type="text/javascript">
var map = null;

// get the bing map key https://msdn.microsoft.com/en-us/library/ff428642.aspx
var credentials = "AvYH87t36Tv3ybyYagU9HUZKjsrWO2Hcup3nu1fmNhpenHKO_xxxxx";

function GetMap() {

// countryName - name of the country from the CRM's form
var countryName = parent.Xrm.Page.getAttribute("new_name").getValue();

// calling virtual earth api
var geocodeRequest = "https://dev.virtualearth.net/REST/v1/Locations?countryRegion=" + countryName + "&key=" + credentials + "&jsonp=GeocodeCallback";

CallRestService(geocodeRequest);
}

function GeocodeCallback(result) {

if (result && result.resourceSets && result.resourceSets.length > 0 && result.resourceSets[0].resources && result.resourceSets[0].resources.length > 0) {

var coordinates = result.resourceSets[0].resources[0].point.coordinates;
var centerPoint = new Microsoft.Maps.Location(coordinates[0], coordinates[1]);

map = new Microsoft.Maps.Map(document.getElementById("mapDiv"),
{
credentials: credentials,
center: centerPoint,
mapTypeId: Microsoft.Maps.MapTypeId.road,
zoom: 5
});

var pushpin = new Microsoft.Maps.Pushpin(map.getCenter());
map.entities.push(pushpin);
}
}

function CallRestService(request) {
var script = document.createElement("script");
script.setAttribute("type", "text/javascript");
script.setAttribute("src", request);
document.body.appendChild(script);
}
</script>
</head>
<body onload="GetMap();">
<div id='mapDiv' style="position: relative; width: 600px; height: 300px;"></div>
</body>
</html>

Check this helpful post as well

https://blogs.bing.com/maps/2015/03/05/accessing-the-bing-maps-rest-services-from-various-javascript-frameworks/

Hope it helps..

Deployment Manager – To create a new organization, the wizard must remove the current organization in Dynamics 365 message.

While creating a new organization using Deployment Manager we got the below message

It was because we had entered the Workgroup Product key which restricts it to only organization.

https://technet.microsoft.com/en-us/library/hh699677.aspx

Changing the product key to the server one, allowed creating the new organizations.

Hope it helps..

SSRS Fetch XML based CRM Online Report performance in Dynamics 365

Hi,

Recently we were analyzing the performance of a fetch xml based online report.

The report was running against opportunity, and had 4 data set in it. Custom entity A and custom entity B were n – 1 related to opportunity.

Data Set Opportunity 13000 records
Data Set for Custom Entity A used for Lookup 100 records
Data Set for Custom Entity B used for Lookup 8000
Data Set for Tasks Activity used for Lookup 130000 records

More on Lookup Function in SSRS

Running without filter 107 seconds
Filter Last 6 months 94 seconds

We then updated the report’s design, to include only one Dataset by using link entities in the main dataset’s fetch xml. We got the below result

Running without filter 280 seconds
Filter Last 6 months 10 seconds

Another variation we tried was having two Dataset, one for opportunity and another one for tasks to be used for lookup.

Running without filter 50 seconds
Filter Last 6 months 50 seconds

The last variation we tried was using only one Dataset for Opportunity in the main report and the drill down report for Tasks

Running without filter 34 seconds
Filter Last 6 months 7 seconds

It clearly shows that the most efficient way of implementing is through the usage of drill down report, only glitch here is user needs to click on the main report to get the details.

Using single dataset is efficient for subset of records, however it can take long time if it runs on huge number of records in which case lookup performs better.

Basically,

g

Hope it helps..

Using Lookup, LookupSet and Multilookup function in SSRS (Dynamics 365)

Using the Lookup, LookupSet and Multilookup functions we can basically combine values from 2 different data set.

The definition of them are

Lookup/LookupSet/Multilookup(source_expression, destination_expression, result_expression, dataset)

source expression – the field from the source data set (for e.g. primary key)

destination expression – the field to be matched from the destination dataset (sort of foreign key)

result expression – the field whose value has to be returned from the destination dataset

dataset – destination dataset name.

For e.g. we have custom entity Team and Team Members (1 à n)

We have following two DataSet one for Team and one for Team Members.

  1. DSTeam

  1. DSPlayer

The sample report has the Tablix which is bound to DSTeam and has two columns one which is bound new_name field of DSTeam dataset and the second column which gets the player name from DSPlayer dataset.

Output:

Lookup function picks the “first matching value” from the second dataset DSPlayer.

To get all the matching we need to use the LookupSet function (along with Join)

Output:

To understand Multilookup, let us create one more entity Tournament that will have a text attribute which stores comma separated team name.

Let us create one more dataset DSTournament and bound it to a new table. Which have one column which lists the Tournament name and the other column that does the multilookup to DSPlayer to get the player name.

It brings the first matching value, by searching (doing lookup) on multiple value i.e. India and South Africa

Hope it helps..

The ‘distinct’ attribute is not declared error while trying to import solution in CRM 2016 (and earlier)

Hi,

Got this error while trying to import a managed solution in CRM 2016 Online.

During analysis we figured out that it was pointing to one of the charts created for System User Entity. It seemed like chart’s xml was modified after export and then imported. The xml definition of the chart contained distinct keyword.

Removing the distinct keyword from the Chart definition and importing it back and then importing the managed solution with this updated chart got imported without any error.

Hope it helps..

IPluginExecutionContext Depth is 2 in Create Plugin during Import of records in CRM 2015 (and earlier)

We had one of our plugins on Create of Opportunity however during the import of the Opportunity record it was not working properly. We had the Depth check in our plugin code to check for infinite loop as the same plugin was being used for Update also.

After some debugging we realized that Depth value is 2 instead of 1 during import.

To test this, we created a sample plugin for create of Test entity record and then imported the Test entity records.

As expected the import failed and we got our custom exception in it.

Hope this helps..