September 24, 2008
Retrieving All Records with Fetch XML
You may or may not be aware that when retrieving records from the CRM service using the Fetch() method, CRM will limit the results to the first 5000 records by default. I found a nice post by Ronald Lemmen on fetching all records, but I wanted to take his concept a step further by having a function do the following:
- Accept fetch xml in a string format
- Add the paging functionality to fetch all records
- Merge the results together
- Return the merged results in a string format
I like this approach because it uses no extraneous data types, the fetch xml is passed as a parameter (no hard-coding of your fetch unless you want to!), and the returned xml is identical to what the Fetch() method would return (except more records are there, of course).
The Standard Fetch
The normal usage of the Fetch() method would be like this:
string sFetchXml = "<fetch xml goes here>";
string sResultsXml = service.Fetch(sFetchXml);
You would probably just load your results xml into an XmlDocument object and work with the data in that fashion. If there are more than 5000 records, you'd have to perform another fetch to retrieve additional records 5000 at a time.
Let's instead create a private function that handles all of this for us.
First things first, let's declare the function. We want it to accept the fetch xml and return the results xml.
private string fetchAll(string sFetchXml)
Logic within the function will handle the paging. We do it by utilizing a boolean flag to determine whether we have to perform any more fetches. Also we'll create an XmlDocument object to store our results that will eventually be returned:
bool bComplete = false;
int iPage = 1;
XmlDocument oResults = new XmlDocument();
So, while bComplete evaluates to false, we're first going to load the fetch xml into an XmlDocument object. Then we'll grab the <fetch> node and see if it has a page attribute. If it doesn't, we'll add it. We set the page attribute to the value of iPage.
XmlDocument oFetchXml = new XmlDocument();
XmlNode oFetchNode = oFetchXml.SelectSingleNode("/fetch");
if (oFetchNode.Attributes["page"] == null)
XmlAttribute oPageAttribute = oFetchXml.CreateAttribute("page");
oPageAttribute.Value = iPage.ToString();
oFetchNode.Attributes["page"].Value = iPage.ToString();
The above code automatically tweaks our fetch xml to enable us to perform subsequent fetches. Next, we'll perform the fetch and load our results into a temporary XmlDocument object (I'm assuming you already have a CrmService object created and have named it oService):
string sResultsXml = oService.Fetch(oFetchXml.InnerXml);
XmlDocument oTempXml = new XmlDocument();
Next, we've got to add our fetch results to the oResults xml object:
if (iPage == 1)
XmlNodeList oNodes = oTempXml.SelectNodes("/resultset/result");
XmlNode oResultsetNode = oResults.SelectSingleNode("/resultset");
foreach(XmlNode oNode in oNodes)
XmlNode oNewNode = oResults.ImportNode(oNode,true);
Now we have handled our fetch xml and merging the resulting records. Now we need to figure out if we have any additional fetches to perform by checking the morerecords attribute of the resultset node. If we have more records, we just incrcement the iPage variable, otherwise, set bComplete to true.
XmlNode oMore = oTempXml.SelectSingleNode("/resultset");
if (oMore.Attributes["morerecords"].Value == "0")
bComplete = true;
That's the meat of the function right there. All we have to do now is return the data in our oResults object (remember, this would be after our while loop):
Hopefully, you'll find this function to be extremely useful in situations where you have a lot of records. Best of all, it's easily re-used.
TrackBack URL for this entry:
Listed below are links to weblogs that reference Retrieving All Records with Fetch XML: