Thursday, July 21, 2011

Page Large Result Sets with FetchXML

int fetchCount = 5000;
// Initialize the page number.
int pageNumber = 1;
string pagingCookie = null;
string fetchXml = Common.GetFetchXmlForComponentInfo(((EntityReference)entity.Attributes["neu_whatifmodelid"]).Id.ToString(), fiscalyear.ToString());
while (true)
{
string xml = Common.CreateXml(fetchXml, pagingCookie, pageNumber, fetchCount);
componentinfocollection = service.RetrieveMultiple(new FetchExpression(xml));
foreach (var c in componentinfocollection.Entities)
{
yearlyinfo.Add(
new ComponentYearlyInformation
{
ComponentId = c.Attributes.Contains("neu_componentid") ? ((EntityReference)c.Attributes["neu_componentid"]).Id : Guid.Empty,
name = c.Attributes.Contains("neu_name") ? (string)c.Attributes["neu_name"] : string.Empty,
YearlyExpenditure = c.Attributes.Contains("neu_yearlyexpenditure") ? (decimal)c.Attributes["neu_yearlyexpenditure"] : 0,
FullyFundedAmount = c.Attributes.Contains("neu_fullyfundedamount") ? (decimal)c.Attributes["neu_fullyfundedamount"] : 0
}
);


}

if (componentinfocollection.MoreRecords)
{
// Increment the page number to retrieve the next page.
pageNumber++;
pagingCookie = componentinfocollection.PagingCookie;
}
else
{
// If no more records in the result nodes, exit the loop.
break;
}

}


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

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

return CreateXml(doc, cookie, page, count);
}

public static string CreateXml(XmlDocument doc, string cookie, int page, int count)
{
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();
}


Reference : http://msdn.microsoft.com/en-us/library/gg309717.aspx