积极答复者
关于CRM WebService的Fetch 方法

问题
答案
-
多过5000, 这个不太清楚, 但你可设定返回的纪录如果是少于5000。
为什么还用 FetchXml 呢? 可以用 QueryExpression, 返回的是 DynamicEntities, 容易用多了。
Darren Liu | 刘嘉鸿 | MS CRM MVP | English Blog: http://msdynamicscrm-e.blogspot.com | Chinese Blog: http://liudarren.spaces.live.com- 已标记为答案 Jim Wang (Microsoft)Microsoft employee, Moderator 2010年1月5日 19:10
-
FetchXml的转用SQL语句Microsoft会加Top 5001这样子,(top 5000,还是top 5001记不太清了,你可以跟一下). 超过100最好用page
- 已标记为答案 Jim Wang (Microsoft)Microsoft employee, Moderator 2010年1月5日 19:10
-
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- 已标记为答案 Jim Wang (Microsoft)Microsoft employee, Moderator 2010年1月5日 19:10
- 取消答案标记 JackJiang 2010年1月12日 7:55
- 已标记为答案 JackJiang 2010年1月12日 7:55
全部回复
-
多过5000, 这个不太清楚, 但你可设定返回的纪录如果是少于5000。
为什么还用 FetchXml 呢? 可以用 QueryExpression, 返回的是 DynamicEntities, 容易用多了。
Darren Liu | 刘嘉鸿 | MS CRM MVP | English Blog: http://msdynamicscrm-e.blogspot.com | Chinese Blog: http://liudarren.spaces.live.com- 已标记为答案 Jim Wang (Microsoft)Microsoft employee, Moderator 2010年1月5日 19:10
-
FetchXml的转用SQL语句Microsoft会加Top 5001这样子,(top 5000,还是top 5001记不太清了,你可以跟一下). 超过100最好用page
- 已标记为答案 Jim Wang (Microsoft)Microsoft employee, Moderator 2010年1月5日 19:10
-
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- 已标记为答案 Jim Wang (Microsoft)Microsoft employee, Moderator 2010年1月5日 19:10
- 取消答案标记 JackJiang 2010年1月12日 7:55
- 已标记为答案 JackJiang 2010年1月12日 7:55