Fetch It Part 2
In the first blog on using Fetch XML, I introduced a tool that I had created that would allow you to execute Fetch XML statements to retrieve CRM data. In this blog, I want to discuss some of the inner workings to executing Fetch XML statements to retrieve more than a single page of records. Here’s the link to the updated FetchIt tool
The mechanism is a paging cookie that tells the CRM system were to start the retrieve from. The paging cookie is returned on a fetch execute statement in the data set. The first step is to execute a fetch xml request. This is pretty simple and documented well in the SDK.
string QueryString = @"<fetch mapping='logical'><entity name='new_test'><all-attributes/></entity></fetch>";
Result = this.m_crmService.Fetch(QueryString);
This will return a string of xml data.
<resultset morerecords="1" paging-cookie="<cookie page="1"><new_testid last="{C855BD56-D955-DE11-90B3-001E0B5E0BF6}" first="{37F4AB96-D755-DE11-90B3-001E0B5E0BF6}" /></cookie>"><result>……
Notice in the root element are the attributes morerecords and paging-cookie.
The morerecords indicates there is more data from the query. The limit per retrieve is 5000 records. To get more, you simply need to add the paging cookie to the next request.
string QueryString = @"<fetch mapping='logical' page='2' count='5000' " + Cookie + "><entity name='new_test'><all-attributes/></entity></fetch>";
To accomplish this you’ll need to do a bit of parsing and string manipulation. Make sure you grab the quote on the end.
string Cookie= paging-cookie="<cookie page="1"><new_testid last="{C855BD56-D955-DE11-90B3-001E0B5E0BF6}" first="{37F4AB96-D755-DE11-90B3-001E0B5E0BF6}" /></cookie>”
then insert into the query along with a paging attribute.
string QueryString2 = @"<fetch mapping='logical' page='2' count='5000'" + Cookie + "’><entity name='new_test'><all-attributes/></entity></fetch>";
Next, all you need to is execute the fetch request with the new string. On each call, increase the page number from the last until morerecords = 0.
Here’s some code snippets. Make sure you add appropriate error handling. I put this together as a conceptual example.
string GetCookie(int idx,string DataChunk)
{
int end = DataChunk.IndexOf(">", idx);
int start = DataChunk.IndexOf("paging-cookie=");
int nCnt = end - start;
string Cookie = DataChunk.Substring(start, nCnt);
return Cookie;
}
public static string InsertCookie(string fetchXml,
int PageNum, string Cookie)
{
string Root = "<fetch mapping='logical' page='"
+ PageNum.ToString() + "' count='5000' "
+ Cookie + ">";
int end = fetchXml.IndexOf(">");
if (end < 0)
{
return “”; // need to handle empty string
}
StringBuilder builder
= new StringBuilder(fetchXml);
int RootEnd = fetchXml.IndexOf(“>”);
if (RootEnd > -1)
{
int length = RootEnd + 1;
// Remove old root attributes.
builder.Remove(0, length);
// Insert new root element attributes
builder.Insert(0, Root);
}
return builder.ToString();
}
cheers
-jonw