none
关于CRM WebService的Fetch 方法 RRS feed

  • 问题

  • 使用Crm webservice 的fetch 方法取一个Entity的所有记录,发现在返回的Resultset  xml 最多包含5000条记录,请问这个是可以设置的参数还是CRM固定的?

    2010年1月5日 3:44

答案

  • 多过5000, 这个不太清楚, 但你可设定返回的纪录如果是少于5000。

    为什么还用 FetchXml 呢? 可以用 QueryExpression, 返回的是 DynamicEntities, 容易用多了。
     


    Darren Liu | 刘嘉鸿 | MS CRM MVP | English Blog: http://msdynamicscrm-e.blogspot.com | Chinese Blog: http://liudarren.spaces.live.com
    2010年1月5日 3:58
    版主
  • FetchXml的转用SQL语句Microsoft会加Top 5001这样子,(top 5000,还是top 5001记不太清了,你可以跟一下). 超过100最好用page
    2010年1月5日 6:44
  • Jack
        默认是返回5000条记录.  MVP Ronald的blog上提供了2个解决方法可以参考:

    Fetch all records

    Have you ever tried to write a code which will get you all records from a specific entity? It's harder then you think it is! Everybody who is a bit aware of the CRM SDK thinks it should be a fetch statement like this:

    <fetch mapping='logical'><entity name='account'><attribute name='accountid'/></entity>

    WRONG!
    This would only give you the first 5000 records in the database! It is written down in the SDK with small letters, but it could drive you crazy..

    There are two solutions for this issue.
    1) Add a registery setting to specify not to implement MaxRowsPerPage
    2) Modify the fetch statement and merge several results

    Here are the details for each solution
    1st solution
    Search in the SDK for the word "TurnOffFetchThrottling". You should add this as DWORD registery setting to HKLM\Software\Microsoft\MSCRM. Set the value to 1. You will now not have the 5000 records limit.

    2nd solution
    Modify your fetch statement to include paging and count numbers. Store all the data in an DataSet and perform that series of code over and over again as long as there is data coming.

    Here's the script you should use to get all accountid's (for clarity and the ease of use I have added a function called "FetchDataSet").


    private DataSet FetchAllAccountIds(){
    int i=1;
    bool bFinished = false;
    DataSet dsAllData = new DataSet();
    while (bFinished == false)
    {
    StringBuilder sbFetch = new StringBuilder();
    sbFetch.AppendFormat("<fetch mapping='logical' page='{0}' count='5000'>", i);
    sbFetch.Append("<entity name='account'>");
    sbFetch.Append("<attribute name='accountid'/>");
    sbFetch.Append("<attribute name='new_12_accountid'/>");
    sbFetch.Append("</entity>");
    sbFetch.Append("</fetch>");
    DataSet dsTempResult = FetchDataSet(sbFetch.ToString());
    dsAllData.Merge(dsTempResult);
    if (dsTempResult.Tables[0].Rows[0]["morerecords"].ToString() == "0")
    {
    bFinished = true;
    }
    else
    {
    i++;
    }
    }
    return dsAllData;
    }

    private DataSet FetchDataSet(string fetchXml)
    {
    string strResult = service.Fetch(fetchXml);
    DataSet ds = new DataSet();
    System.IO.StringReader reader = new System.IO.StringReader(strResult);
    ds.ReadXml(reader);
    return ds;
    }


    I hope this saves you some time!

    Thanks to Andrew Krivosheyenko for the Regedit solution!

    Batistuta Cai-刀客 | 蔡敏生 | MS CRM MVP | Blog:http://caims.cnblogs.com
    2010年1月5日 8:22
    版主

全部回复

  • 多过5000, 这个不太清楚, 但你可设定返回的纪录如果是少于5000。

    为什么还用 FetchXml 呢? 可以用 QueryExpression, 返回的是 DynamicEntities, 容易用多了。
     


    Darren Liu | 刘嘉鸿 | MS CRM MVP | English Blog: http://msdynamicscrm-e.blogspot.com | Chinese Blog: http://liudarren.spaces.live.com
    2010年1月5日 3:58
    版主
  • FetchXml的转用SQL语句Microsoft会加Top 5001这样子,(top 5000,还是top 5001记不太清了,你可以跟一下). 超过100最好用page
    2010年1月5日 6:44
  • thanks 各位。
    2010年1月5日 7:31
  • Jack
        默认是返回5000条记录.  MVP Ronald的blog上提供了2个解决方法可以参考:

    Fetch all records

    Have you ever tried to write a code which will get you all records from a specific entity? It's harder then you think it is! Everybody who is a bit aware of the CRM SDK thinks it should be a fetch statement like this:

    <fetch mapping='logical'><entity name='account'><attribute name='accountid'/></entity>

    WRONG!
    This would only give you the first 5000 records in the database! It is written down in the SDK with small letters, but it could drive you crazy..

    There are two solutions for this issue.
    1) Add a registery setting to specify not to implement MaxRowsPerPage
    2) Modify the fetch statement and merge several results

    Here are the details for each solution
    1st solution
    Search in the SDK for the word "TurnOffFetchThrottling". You should add this as DWORD registery setting to HKLM\Software\Microsoft\MSCRM. Set the value to 1. You will now not have the 5000 records limit.

    2nd solution
    Modify your fetch statement to include paging and count numbers. Store all the data in an DataSet and perform that series of code over and over again as long as there is data coming.

    Here's the script you should use to get all accountid's (for clarity and the ease of use I have added a function called "FetchDataSet").


    private DataSet FetchAllAccountIds(){
    int i=1;
    bool bFinished = false;
    DataSet dsAllData = new DataSet();
    while (bFinished == false)
    {
    StringBuilder sbFetch = new StringBuilder();
    sbFetch.AppendFormat("<fetch mapping='logical' page='{0}' count='5000'>", i);
    sbFetch.Append("<entity name='account'>");
    sbFetch.Append("<attribute name='accountid'/>");
    sbFetch.Append("<attribute name='new_12_accountid'/>");
    sbFetch.Append("</entity>");
    sbFetch.Append("</fetch>");
    DataSet dsTempResult = FetchDataSet(sbFetch.ToString());
    dsAllData.Merge(dsTempResult);
    if (dsTempResult.Tables[0].Rows[0]["morerecords"].ToString() == "0")
    {
    bFinished = true;
    }
    else
    {
    i++;
    }
    }
    return dsAllData;
    }

    private DataSet FetchDataSet(string fetchXml)
    {
    string strResult = service.Fetch(fetchXml);
    DataSet ds = new DataSet();
    System.IO.StringReader reader = new System.IO.StringReader(strResult);
    ds.ReadXml(reader);
    return ds;
    }


    I hope this saves you some time!

    Thanks to Andrew Krivosheyenko for the Regedit solution!

    Batistuta Cai-刀客 | 蔡敏生 | MS CRM MVP | Blog:http://caims.cnblogs.com
    2010年1月5日 8:22
    版主
  • thansk 老大
    2010年1月12日 7:54