locked
How to retrieve more than 5000 records in crm 2011 online using fetchxml RRS feed

  • Question

  • Hi All,

    I need to retrieve more than 5000 records in crm 2011 online. I am using fetchxml and I have to use this.(can't use query expression).

    I am also using paging in this, but not able to get all the records. 

    It is throwing exception "Paging cookie is required when trying to retrieve a set of records on any high pages. "

    I tried with Convert Queries Between Fetch and Query Expression but not succeeded.

    I followed this post

    http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/02fb5868-2bca-4ebd-9629-fd683a044519/

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

    below is my code 

     public EntityCollection Perform(string fetchXML, IOrganizationService service)

    {          

    EntityCollection AllRecords = new EntityCollection();

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

                    try
                    {
                        while (true)
                        {

                            string xml = CreateXml(fetchXML, pagingCookie, pageNumber, fetchCount);


                            RetrieveMultipleRequest fetchRequest1 = new RetrieveMultipleRequest
                            {
                                Query = new FetchExpression(xml)
                            };

                            EntityCollection fetXMLRecord = ((RetrieveMultipleResponse)service.Execute(fetchRequest1)).EntityCollection;

                            // Convert the FetchXML into a query expression.  here did Convert Queries Between Fetch and Query Expression (commented above code)
                           // EntityCollection fetXMLRecord = ConversionFromFetchXMLToQueryExpression(xml, service);

                            foreach (Entity ChildRecord in fetXMLRecord.Entities)
                            {

    AllRecords.Entities.Add(ChildRecord );

                             }

                          

                           if (fetXMLRecord.MoreRecords)
                            {
                                pageNumber = pageNumber + 1;                           

                            }
                            else
                            {
                                break;
                            }

    }

     catch (Exception ex)
                    {

    }

    return AllRecords ;

    }

     public 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();
             }

            public 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 EntityCollection ConversionFromFetchXMLToQueryExpression(string fetchXml, IOrganizationService service)
            {
                var conversionRequest = new FetchXmlToQueryExpressionRequest
                {
                    FetchXml = fetchXml
                };

                var conversionResponse =
                    (FetchXmlToQueryExpressionResponse)service.Execute(conversionRequest);

                // Use the newly converted query expression to make a retrieve multiple
                // request to Microsoft Dynamics CRM.
                QueryExpression queryExpression = conversionResponse.Query;

                EntityCollection result = service.RetrieveMultiple(queryExpression);
                return result;
            }

    Any Help much appreciated.

    Thanks 

    Tuesday, January 8, 2013 1:08 PM

Answers

  • I think the problem is that the code never updates the null value you initially set for the paging cookie. Try changing this block:

    if (fetXMLRecord.MoreRecords)
    {
    	pageNumber = pageNumber + 1;
    }
    else
    {
    	break;
    }

    to this:

    if (fetXMLRecord.MoreRecords)
    {
    	pageNumber = pageNumber + 1;
    	pagingCookie = fetXMLRecord.PagingCookie;
    }
    else
    {
    	break;
    }

    Lucas Alexander http://twitter.com/lucas_is

    • Proposed as answer by Andreas Buchinger Tuesday, January 8, 2013 2:35 PM
    • Marked as answer by Manuj Gupta Wednesday, January 9, 2013 12:29 PM
    Tuesday, January 8, 2013 2:09 PM

All replies

  • I think the problem is that the code never updates the null value you initially set for the paging cookie. Try changing this block:

    if (fetXMLRecord.MoreRecords)
    {
    	pageNumber = pageNumber + 1;
    }
    else
    {
    	break;
    }

    to this:

    if (fetXMLRecord.MoreRecords)
    {
    	pageNumber = pageNumber + 1;
    	pagingCookie = fetXMLRecord.PagingCookie;
    }
    else
    {
    	break;
    }

    Lucas Alexander http://twitter.com/lucas_is

    • Proposed as answer by Andreas Buchinger Tuesday, January 8, 2013 2:35 PM
    • Marked as answer by Manuj Gupta Wednesday, January 9, 2013 12:29 PM
    Tuesday, January 8, 2013 2:09 PM
  • Thanks Lucas. 

    Now it is working fine.

    Wednesday, January 9, 2013 12:29 PM