FetchXML into a DataSet

Once upon a time in the Netherlands there was this developer who was working on MSCRM for quite some time. He started to notice that he had to search again and again for the same questions. Luckily he heard about the term 'blogging' and so he started to post the most frequent questions on a blog. The goal was to be able to quickly retrieve the information that he had found earlier. The fact that other people also read his blog and save themselves time to figure everything out themselves is just an added value to the blog.

Now with this knowledge you will understand why I post this piece of code:


private DataSet FetchDataSet(string fetchXml) {
CrmService service = new CrmService();
service.Credentials = System.Net.CredentialCache.DefaultCredentials;
//service.UnsafeAuthenticatedConnectionSharing = true; //enable in secure migration programs

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

I'm just typing this way to often and I prefer to use copy and paste.

Furthermore a link to a previous post on how to quickly create a fetchXML:
http://ronaldlemmen.blogspot.com/2006/11/using-advanced-find-for-fetchxml.html

And a link to another post on how to make sure that you do fetch all records and not only the first 5000:
http://ronaldlemmen.blogspot.com/2006/08/fetch-all-records.html

22 comments:

Maria said...

I'm pretty new to Microsoft CRM so I was hoping you could help me. I hope I'm not overstepping the line by asking here, but you seem to know all about CRM, C# and fetchxml.

Is there any way to convert fetchxml to sql? Is there any build-in function that will do this? I know it's possible to convert it into a queryexpression, but is it possible to convert it into sql without having to implement the converting procedure myself?

Thank you for your time.
Maria

Ronald Lemmen said...

Hi Maria,

Welcome to the world of MSCRM.

Could you please explain why you would want to convert fetchxml into sql? If you send the fetchxml query to the webservice its fetch function, then the webservice will take care of the database connection. You should not want to contact the database directly.

If you do want to know what the query is that gets executed by your fetchxml query, then you can start a database profiler trace. Then fire the query and find it in the trace.

Hope this helps,

Ronald

Maria said...

Wow! Thanks for the quick reply :-)

Basically I need to take the fetchxml fired by an advanced search in CRM and - assuming I had it converted to sql - manipulate the sql to join on other tables.

Eg. the user makes an advanced search of contacts but also wants the address of the company the contact works for, so I need to join company and contacts on companyId.

I was told that advanced search doesn't join other tables by itself, and that it's very tedious to do this in fetchxml so therefore it would be easiest to convert it into sql and manipulate that query instead. Of course, knowing nothing about fetchxml myself, this information may very well be faulty.

Hope that explains my problem a bit better.
Maria

Ronald Lemmen said...

You indeed cannot let the advanced find query return data from more then one entity. The fetchxml does allow you to do that. When you do fetch the data via fetchxml, you will need to find a way to show the data to the user. Microsoft CRM does not offer you a way to use the controls they use (like the grid).

Another approach would be to copy the address data from the account to the child contacts via a callout. Then all the data including the address data is known within the entity contact and you can let the users select data of only contact via advanced find.

Hope this is clear,

Ronald

Maria said...

It does make it clear. Thanks. The idea was to show the data in an excel-file, since I can't show it in MSCRM.

I'll have to see whether using fetchxml or a callout is most appropriate for the specific problem. My guess is it would be the former as we'd otherwise need a LOT of callouts, depending on which information the user is interested in joining.

Thanks for all your help :-)
Maria

Ronald Lemmen said...

Hi maria,

You're welcome!

When going the excel route, also make sure that you have read this article: http://blogs.msdn.com/crm/archive/2006/05/26/607470.aspx.
It shows how to work with datasets and selecting extra, related data in excel.

Kind regards,

Ronald

Maria said...

Thanks for the heads-up! That's really kind of you :-)

Maria said...

Hi again - a new question as I'm almost through. I only have one small issue left regarding getting the modified fetchxml out to excel.

My code is almost identical to the one you have listed here:
protected void TransferToExcel(string fetchXml)
{
CrmService service = new CrmService();
service.Credentials = System.Net.CredentialCache.DefaultCredentials;
try
{
xmlString = service.Fetch(fetchXml);

XmlReader reader = XmlReader.Create(new StringReader(xmlString));
DataSet ds = new DataSet();
ds.ReadXml(reader);
}
}

however, when I try to call the 'service.Fetch' method I get the very undescriptive error: "0x80040216 An unexpected error occurred. Platform".

I have been unable to find *any* useful information on google, and was hoping you might be able to help me?

Thanks!
Maria

Maria said...

(oops, forgot to include the catch part when I copy-pasted. It's merely:
catch (SoapException e)
{
result.Text = "InnerXml: "+e.Detail.InnerXml;
}
)

Maria said...

Never mind, I discovered the error myself. For some reason the fetchxml string wasn't defined properly. Once I managed that, it worked fine.
Maria

Ronald Lemmen said...

Hi Maria,

Good that you have solved this yourself. Thanks for the update as well.

Kind regards,

Ronald

binoos said...

hi ronald,

i am doing my first assignment with CRM. i have a question maybe stupid or not possible. Please help me....

i have a saved view (created through Advanced Find). can i connect this view to a navigation item in the left side.

i have the same question if i have a saved report too. beacuse i can get the url of saved view or saved report. so can i specify this same url to a navigation item in the left pane to work.

Cheers
Binu | South India

Ronald Lemmen said...

Hi Binu,

You can use the ISV.Config to add elements to the navigation bar. You can point the new navigation bar items to any url, including your custom URL. Look in the SDK for more information on how to work with the ISV.Config.

Hope this helps,
Ronald

binoos said...

thanks for the reply ronald...will try and let you know.....

Another clarification i need is, Is it posiible to hide/disable the Icons/menu items on the top of each form/view? What i am talking about is the NEW button, Excel export Image, Delete button etc just above the record grids in all views

Microsoft CRM 3.0 Customization said...

Nice code explanation... thanks for the links...

Ronald Lemmen said...

Binu,

You can only remove those by changing the security rights. This will impact all the grids in the whole application for users with that role though.

Kind regards,
Ronald

Anurag said...

Hey Ronald,

This is a hypothetical question, is there a tool that takes a SQL statement as input and returns a FetchXML query. If so where can I find it.

Thank you for your time.

Regards,
Anurag

Ronald Lemmen said...

Hi Anurag,

There is no such tool as of this moment. I also don't expect such a tool to be made because you can do a lot more within SQL as compared to FetchXML. If you do make such a tool and decide to publish it for the community I would be glad to blog about it!

Kind regards,
Ronald

shilpa said...

Hi Roland,

Could you pls let me know if we can use filtered views with in a fecth XML query?

Our requirement is we have to get records from fetch XML query for which user has access to?

Thanks in advance

Ronald Lemmen said...

Hi Shilpa,

The fetchXML queries are executed against the Webservice. The webservice will respect the security settings of the user which is used to call the webservice. This means that if user x has no rights to view specifici records, then these records will not be returned by the webservice even though the records would match the requirements of the fetchxml query.

Ronald

hero said...

Hi Ronald,
I tried your code to find out the resulting dataset's result table only has 4 columns no matter how many are returned by strResult, I had a work around of this with the following modification in your code :

DataSet ds = new DataSet();
System.IO.StringReader reader = new System.IO.StringReader(strResult);
System.Xml.XmlTextReader readerXML = new System.Xml.XmlTextReader(new System.IO.MemoryStream(System.Text.ASCIIEncoding.Default.GetBytes(strResult)));
XmlDocument xd = new XmlDocument();
xd.LoadXml(strResult);

ds.ReadXml(new XmlNodeReader(xd));
return ds;

Weifeng said...

I think the readers of this post might interested in writing FetchXML, here is a web tool convert from SQL to FetchXML, available at http://www.sql2fetchxml.com