Friday, January 29, 2010

Using DataForm and stored procedures in Silverlight

The DataForm in Silverlight is a tremendously powerful tool for allowing users to do CRUD (Create, Read, Update, Delete) operations on an object (data). It is very much like Dyanmic Data in ASP.NET. It takes all the tedious binding, validation, UI layout, etc off your plate and makes it fast and easy to do create a CRUD interface for an entity. Below is not everything you would need, but it is enough to get you going.

Create a Silverlight solution

If you have not done so already you should have a Silverlight solution. The solution should have two parts. The first is the Silverlight application itself. The other is a web site that will host the silverlight application and the WCF service or web service.

Create an Entity

In our example, we are working with Companies, so we will create a company entity. This will be used on the server and client side. When you consume your web service in your Silverlight application it will bring in your Company Entity and create a very nice Company object for use in your Silverlight application.

Below is an example of what the Company entity might look like

public class Company
{
public string Name {set;get;}
public string Phone {set;get;}
public string Region {set;get;}
}

Create your Data Access Layer

You can create your data access layer anyway you want. Below is approximately what your DAL should have in it. The important thing is that you define the CRUD operations that your web service or WCF service will call. In this example, I elude to using stored procedures, but I assume you know how to implement stored procedures and read and fill objects from/to Company Objects.

public class DAL
{

public int AddCompany(Company company)
{
    // call stored proc here
}

public void UpdateCompany(Company company)
{
    // call stored proc here
}

public Company GetCompanyByID(int CompanyID)
{
    // call stored proc here
}

public List<Company> GetAllCompany()
{
    // call stored proc here
}

public void DeleteCompanyByID(int CompanyID)
{
    // call stored proc here
}

}

 

Create a web service or WCF service

Since your Silverlight application will run in a browser on the end user’s pc we cannot connect directly to the database for security restrictions (good ones) that Silverlight imposes, we need to have a web service or WCF service to get data. WCF requires a bit more explanation and setup, but web services are a bit easier to talk about and more people know about them so I will use a web service in this example. You will need CRUD operations defined. Here are some that I use for my Company Entity.

[WebMethod]
public int AddCompany(Company company)
{
    DAL dal = new DAL();
    return dal.AddCompany(company);
}

[WebMethod]
public void UpdateCompany(Company company)
{
    DAL dal = new DAL();
    dal.UpdateCompany(company);
}

[WebMethod]
public Company GetCompanyByID(int CompanyID)
{
    DAL dal = new DAL();
    return dal.GetCompanyByID(CompanyID);
}

[WebMethod]
public List<Company> GetAllCompany()
{
    DAL dal = new DAL();
    return dal.GetAllCompany();
}

[WebMethod]
public void DeleteCompanyByID(int CompanyID)
{
    DAL dal = new DAL();
    dal.DeleteCompanyByID(CompanyID);
}

Add DataForm

The easiest way to add a DataForm to your Silverlight application is to use Blend Expression. Simply drag it from your Assets to the layout. The name space will automatically be added. I have added a couple of event handlers (You add them like you would in ASP.NET by double-clicking the event on the event tab of the property tab in Expression Blend or just manually typing them in) that we will use later. I have also set the last three properties the way that I like them or at least like to think about how I want them. The most important part is that the ItemsSource property is set as shown below (at least for this example).

<dataFormToolkit:DataForm
x:Name="dfCompany"
ItemsSource="{Binding}"
AddingNewItem="dfCompany_AddingNewItem"
EditEnded="dfCompany_EditEnded"
CommandButtonsVisibility="All"
AutoCommit="False"
AutoEdit="False"
>

Databinding

The data will be coming from our web service or WCF service. We need to call the web service (or WCF service). In this example, the DataForm is a control that I put in a ChildWindow (popup window). The constructor takes the company id as a parameter. If it is specified then I show the record in the DataForm, otherwise, I assume we are adding a new company.

It is important to realize that all the calls to the web service will be asynchronous so you need to have event handlers that get called when the call is done. This is when/where you check for exceptions and do something with the data. That is why there are so many event handlers.

One of the most important things is the constructor. We bind our results to the DataContext of the ChildWindow. This means when we set the ItemsSource property on our DataForm earlier to {Binding} that means that the DataContext and the ItemsSource will be linked with a two-way databinding.

NOTE: You should NOT bind the ItemsSource (and thus the DataContext in this case) to a single object. It should be bound to a collection of objects even if there is only one object in that collection. Otherwise you will not get the intended result. The DataForm will most likely be blank with no fields on it. In our example, we are only binding to one object that is wrapped in a collection. However, the DataForm supports previous, next, first, and last operations if you pass it more than one record. That may be very useful depending on your application. Try calling the GetAllCompany method instead of GetCompanyByID (and don’t wrap it in a collection since it is already) and you will see this very cool functionality.

I added an adding flag also. This allows me to call either the Add or Update method on our web service when the Save button is called. There are other ways of doing this, but this is pretty straight forward.

public partial class CompanyEditor : ChildWindow
{
bool adding = false;

Example.CompanyWS.CompanyWSSoapClient db;

public CompanyEditor(int? companyID)
{
InitializeComponent();

db = new Example.CompanyWS.CompanyWSSoapClient();
db.GetCompanyByIDCompleted += new EventHandler<Example.CompanyWS.GetCompanyByIDCompletedEventArgs>(db_GetCompanyByIDCompleted);
db.AddCompanyCompleted += new EventHandler<AddCompanyCompletedEventArgs>(db_AddCompanyCompleted);
db.UpdateCompanyCompleted += new EventHandler<System.ComponentModel.AsyncCompletedEventArgs>(db_UpdateCompanyCompleted);

if (companyID.HasValue)
{
db.GetCompanyByIDAsync(companyID.Value);
}
else
{
List<Company> newOne = new List<Company>();
newOne.Add(new Company());
DataContext = newOne;
}
}



void db_GetCompanyByIDCompleted(object sender, Example.CompanyWS.GetCompanyByIDCompletedEventArgs e)
{
if (e.Error == null)
{
List<Company> results = new List<Company>();
results.Add(e.Result);
DataContext = results;

}
else
{
MessageBox.Show("Error loading record");
}
}

private void OKButton_Click(object sender, RoutedEventArgs e)
{
this.DialogResult = true;
}

private void CancelButton_Click(object sender, RoutedEventArgs e)
{
this.DialogResult = false;
}

private void dfCompany_EditEnded(object sender, System.Windows.Controls.DataFormEditEndedEventArgs e)
{
Company company = ((DataForm)sender).CurrentItem as Company;

if (e.EditAction == DataFormEditAction.Commit)
{
if (adding)
{
db.AddCompanyAsync(company);
}
else
{
db.UpdateCompanyAsync(company);
}

}
else if (e.EditAction == DataFormEditAction.Cancel)
{

}
else
throw new Exception("Unknown action: " + e.EditAction);

adding = false;

}

void db_UpdateCompanyCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e)
{
if (e.Error != null)
{
MessageBox.Show("Error Saving. The error has been logged. Please try again.");
}
}

void db_AddCompanyCompleted(object sender, AddCompanyCompletedEventArgs e)
{
if (e.Error != null)
{
MessageBox.Show("Error Saving. The error has been logged. Please try again.");
}
}


private void dfCompany_AddingNewItem(object sender, System.Windows.Controls.DataFormAddingNewItemEventArgs e)
{
adding = true;
}
}

Controlling the DataForm

The DataForm knows what to display by looking at the entities you pass it at runtime. In fact, if you pass it a collection that contains different types of objects it will change the User Interface as you move through each record. Very impressive!

So where does it get all this information? It does so by looking at the annotations on each public property of the entity that you pass it. Since the object that we pass it is generated automatically from the web service we consume we don’t really have much chance to add annotations to it (at least not very easily).

It is recommended to reduce server roundtrips, and improve user experience that validation be done on the client and server when possible. This means in Silverlight and in our web service. I don’t know if I totally agree with that or not. I see the need for both, but it seems like we are back to the same issue we had with ASP.NET and that is writing the business logic twice. If we are careful about what gets duplicated I think it can be a great benefit. I try to limit the business logic that is duplicated to be just annotations, or at least use the ones that are only available in Silverlight.

One way to do this is to wrap all our entities in another entity and add the annotations to that wrapping entity. That way the DataForm can be controlled much better. Dino Esposito talks about this here. This wrapping can be time consuming and a maintenance nightmare, so use only when appropriate.

Working with multiple table

It seems to be a fairly common question on the internet. The question is, the DataForm works great for an entity that maps to a single table, but how to I use this when I have multiple tables. The answer is quite simple if we follow the design presented here. Whether there is one or two tables is not a UI decision in fact, it is not even at the business logic level, it is solely at the data access or database level. Please note, I did not implement the business logic layer by the way since this was such a simple example, but you may consider putting it between the web service and the data access layer. In that case, the web service would call the business logic layer which would call the data access layer.

I am happy to say that this design allows for this quite nicely. The change does not take place in the UI or Business Logic layer, but instead the change is at the data access layer. You could do it at the business logic layer or in the web service if you want also, but that is entirely up to you. The key is that interface between the web service and the Silverlight application doesn’t change just because columns are split between two or more tables instead of being in one table. We encapsulate that in the web service (or some layer below it) and the Silverlight application and thus the DataForm don’t know the difference. In my opinion, that is how it should be, or at least a reasonable approach, I’m sure there are better and definitely different solutions. I would be interested to hear of different approaches.

Tuesday, January 26, 2010

Using T-SQL to remove unwanted characters

Let’s pretend you have a column (in a table) in MS SQL Server and it has some stuff that you don’t want to be there. For example if you only wanted a column to have alpha or only numeric data you could essentially do a search a replace on that column using  a simplified regular expression syntax that the patindex in T-SQL provides.

Below is an example of the solution.

select top 1 * from Person

while @@rowcount > 0
update Person
set LName = replace(LName, substring(LName, patindex('%[^a-zA-Z. ]%', LName), 1), '')
where patindex('%[^a-zA-Z. ]%', LName) <> 0
The first line is only necessary for the @@rowcount to be set to something greater than 0. If you have an update on that table you need to do, you could do that instead. You could also add some more logic, add variables, etc.

The update statement is called multiple times until no more updates are needed. It is really pretty awesome the way that it keeps affecting less and less rows until no more are affected ad the while loop exits. It feels kind of recursive.

You don’t really have to understand it to make use of this. The important thing to note here is that the first parameter of patindex is the pattern it is matching on. In this case, I am matching (accepting) only lowercase a-z and uppercase A-Z and period and space (notice the space at the end before the ]). You can change this pattern to any valid pattern that patindex accepts.

References:
Docs for patindex
Pattern syntax
I got the idea from here.

Thursday, January 7, 2010

How to query Microsoft Dynamics CRM

There are several choices for querying Microsoft Dynamics CRM. Other than direct database (SQL Server) access, they all go through the web service that is provided by MS Dynamics CRM. Below I provide what I learned when I tried to pull data from MS Dynamics CRM.

Assumptions

I assume you are using Microsoft Dynamics CRM 4.0 (though I think most will apply to 3.0 also). I assume you already have a MS Dynamics CRM installed and working. If you are like me, someone else manages and installed it, but you need to pull or update data in it.

You will need an account to the system. It doesn’t have to be a particularly powerful user, but it does have to have the proper permissions to read or write or create or delete or whatever you want your program to do. Your program will be logging into the system with those credentials.

I assume you want to use the web service instead of direct database access. If you are lucky enough to have direct database access and you are doing read only operations, then this will definitely be the fastest. I was not so lucky, so I have to use the web service, which is not bad really.

What data is available?

The first thing you need is the url of the user interface to the MS Dynamics CRM. For example, https://crm or http://crm. For our examples, I’ll assume https is being used, but http should be the same. Once you have that you can go to the url

http://crm/sdk/list.aspx

This will give you a list of all entities that are available for query. It also provides metadata about each of the entities; including columns and their metadata. This is extremely useful.

How to find the web service

There are actually two web services. One that I never use, but you may want or need to, and another that I use all the time.

The first web service is a discovery web service. Its purpose is to get information about the environment that you are in. The url for this web service is

https://crm/mscrmservices/2007/AD/CrmDiscoveryService.asmx

The second (and most used) web service is the main web service and can be found at

https://crm/mscrmservices/2007/CrmServiceWSDL.aspx

You can use those urls in Visual Studio to add a web reference to the appropriate web service(s). This will import a WSDL and convert the WSDL to C# code for you. I recommend you do it this way. However, if you really want to download the WSDL and work with it you can do that also. You can also download the WSDL from the UI.

If you show all files in your solution you can expand your web service reference and look at the Reference.cs. These are the c# datatypes for the entities and properties that we saw when you looked at http://crm/sdk/list.aspx.

Working with the web service

Depending on your security, environment, etc you may need to specify more about the web service than many of the simple examples on the web show. In my case, I had to specify my own Authentication Token that specified my organization name in the CRM.

Below is a method that I created to call instead of calling the standard new CrmService() constructor.

private static CrmService GetWebService()
{
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = AuthenticationType.AD;
token.OrganizationName = "YourCompanyNameHere";

CrmService crmService = new CrmService();
crmService.CrmAuthenticationTokenValue = token;
crmService.Credentials = System.Net.CredentialCache.DefaultCredentials;

// big performance improvement for subsequent calls to web service
crmService.PreAuthenticate = true;

return crmService;
}

Where are all the methods on the web service?

When I first looked at the CrmService web service I was shocked. I thought I would see tons of methods that I can call, but instead I found a seven methods. Each one has a different use and each has its own advantages and disadvantages. Click on one of the links below to go to the docs that also contain good examples on using the methods.

Create - Creates an entity instance. Use this to create a new record in the database.

Delete - Deletes an entity instance. Use this to delete an existing record from the database.

Retrieve - Retrieves an entity instance using the specified ID. Use this when you only want one entity and you know its ID. This is the fastest for returning a single row for a given entity. This returns a strongly typed object. Only the properties you specify to be returned are populated in the object though. You can use intellisense on these objects.

RetrieveMultiple - Retrieves a collection of entity instances based on the specified query criteria. This returns a collection of strongly typed objects. Only the properties you specify to be returned are populated in the objects though. You can use intellisense on these objects. Can only operate on one entity, and gives Object Oriented filtering, paging, etc. This is the fastest for returning multiple rows for a given entity.

Update - Updates an entity instance. This uses a strongly typed object. You can use intellisense on these objects.

Fetch - Retrieves entity instances in XML format based on the specified query expressed in the FetchXML query language. This does NOT use strongly typed objects, and intellisense doesn’t work so you will have to lookup everything or use a saved query. Both the query and the results are xml based. This is by far faster than RetrieveMultiple or Execute (assuming you don’t need most of the properties in an entity to be brought back). This is also the only choice when you need to left outer join multiple tables. In general, this is your best choice for reporting because of all the joining, and the selecting properties from multiple entities. It is as close to SQL as you are going to get without being SQL. You have to work a little harder to work with this, but performance and flexibility are maximized. NOTE: Retrieve and RetrieveMultiple are technically faster according to the docs, but I think it depends on the query.

Execute - Executes business logic and special operations using messages. A ton of things (Anything that inherits from Request) can be passed to this method and the results are returned in a Response object. Search the Reference.cs for “ : Request” and you will see all objects that inherit from Request. You can join (link) entities, but only for the filter, not for selecting properties.

Samples

For starters download the SDK, or look on MSDN for the same samples, but on the web.

Execute Example

Below is an example of how you can use the Execute method to return multiple rows (entities) for a given query. You can build complex filters, but you can only select from the entity that the query is for. You specify what properties you want to bring back, and when you access the objects in the response you should only access the properties you specified in the request. Paging is built in and easy to do.

private void GetUsersUsingExecute()
{
RetrieveMultipleResponse results = null;
int pageNum = 1;
while (results == null || (results != null && results.BusinessEntityCollection.MoreRecords))
{

// specify the columns we want to return
ColumnSet cols = new ColumnSet();
cols.Attributes = new string[] { "domainname", "systemuserid" };

// Create the FilterExpression.
FilterExpression filter = new FilterExpression();

PagingInfo pageInfo = new PagingInfo();
pageInfo.Count = 1000; // the number of rows in each batch
pageInfo.PageNumber = pageNum;

// Create the QueryExpression.
QueryExpression query = new QueryExpression();

// Set the properties of the QueryExpression.
query.EntityName = EntityName.systemuser.ToString();
query.ColumnSet = cols;
query.Criteria = filter;
query.PageInfo = pageInfo;
query.Distinct = true;

// Create the request object.
RetrieveMultipleRequest request = new RetrieveMultipleRequest();

// Set the properties of the request object.
request.Query = query;

// Execute the request.
results = (RetrieveMultipleResponse)GetWebService().Execute(request);


BusinessEntity[] users = results.BusinessEntityCollection.BusinessEntities;
foreach (systemuser user in users)
{
Console.WriteLine(user.systemuserid.Value);
Console.WriteLine(user.domainname);
}


pageNum++;
} // end while

}


FetchXML Example

FetchXML is in my opinion the preferred method to retrieve data from the CRM due to its optimal performance and flexibility.

Helper Methods

You will want to use these methods when you are using FetchXML. You can just copy and paste them. They basically make working with the XML much easier.

private string ExtractNodeValue(XmlNode node, string childNodeName)
{
XmlNode childNode = node.SelectSingleNode(childNodeName);
return (childNode == null) ? string.Empty : childNode.InnerText;
}

private string ExtractNodeAttributeValue(XmlNode node, string childNodeName, string childNodeAttributeName)
{
XmlNode childNode = node.SelectSingleNode(childNodeName);
return (node == null) ? string.Empty : GetAttriubteValue(childNode, childNodeAttributeName);
}

private bool HasMoreResults(XmlDocument doc)
{
return GetAttriubteValue(doc.DocumentElement, "morerecords") == "1";
}

private string GetPagingCookie(XmlDocument doc)
{

XmlAttribute attribute = doc.DocumentElement.Attributes["paging-cookie"];
return (attribute != null) ? attribute.InnerXml : string.Empty;
}

private string GetAttriubteValue(XmlNode node, string attributeName)
{
XmlAttribute attribute = node.Attributes[attributeName];
return (attribute != null) ? attribute.Value : string.Empty;
}



private string SetPagingInfo(string fetchXml, string pagingCookie, int pageNumber, int fetchCount)
{
return string.Format(fetchXml, pageNumber, fetchCount, pagingCookie);
}

private string FormatXmlQuery(string xmlQuery)
{
// add some white space for readability
xmlQuery = xmlQuery.Replace("<", "\r\n<");
xmlQuery = xmlQuery.Replace("<link-entity", "\r\n<link-entity");
xmlQuery = xmlQuery.Replace("<filter", "\r\n<filter");
xmlQuery = xmlQuery.Replace("<order", "\r\n<order");
return xmlQuery;
}


XML Query

I store this in an XML file and read it in when I need it. Otherwise, trying to do this inline in code is messy and error prone. I STRONGLY recommend this method or at least not putting inline in your code. This example is complex for several reasons. To start with, there are several entities that are linked together and I am selecting attributes from each of them. There are even some outer joins here. Anything with mycustom_ in the name are properties that are only in my CRM; they are custom properties setup in the CRM. The alias attribute of the link-entity tag is ugly here, but is actually what is generated by the UI. The important thing to know is I can change it here, but it also has to be changed in my code. This xml also has c# placeholders for supporting paging. Also, note that I am not filtering the rows. I am selecting all rows (paged). I could have added a filter though.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" page="{0}" count="{1}" paging-cookie='{2}'>


<entity name="opportunity">

<attribute name="mycustom_opportunity_revenue"/>
<attribute name="mycustom_customer_part_number"/>

<link-entity name="mycustom_project" from="mycustom_projectid" to="mycustom_projectid" visible="false" link-type="outer" alias="opportunitymycustom_projectidmycustom_projectmycustom_projectid">
<attribute name="mycustom_customer_description"/>
<attribute name="mycustom_name"/>
</link-entity>

<link-entity name="systemuser" from="systemuserid" to="owninguser" visible="false" link-type="outer" alias="opportunityowningusersystemusersystemuserid">
<attribute name="domainname"/>
</link-entity>

<link-entity name="account" from="accountid" to="customerid" visible="false" link-type="outer" alias="opportunitycustomeridaccountaccountid">
<attribute name="name"/>
<attribute name="mycustom_baseregion"/>
</link-entity>

<link-entity name="mycustom_mag" from="mycustom_magid" to="mycustom_magid" visible="false" link-type="outer" alias="opportunitymycustom_magidmycustom_magmycustom_magid">
<attribute name="mycustom_name"/>
</link-entity>

<link-entity name="product" from="productid" to="mycustom_12ncid" visible="false" link-type="outer" alias="opportunitymycustom_12ncidproductproductid">
<attribute name="name"/>
</link-entity>



</entity>

</fetch>

The FetchXML code

The constants below MUST match EXACTLY those that are in the XML Query file that is read in the method below. The code below supports paging and reading the XML query from a file. This code requires the Helper methods shown above. There are no strongly typed properties or anything like that. If you need your data as anything except string, you will need to convert it. This is actually a small price to pay for having the most flexibility and performance possible when using the web service (obviously direct database access would be the fastest).

// NOTE: These need to match the XML Query
private readonly string OWNER_ALIAS = "opportunityowningusersystemusersystemuserid";
private readonly string PROJECT_ALIAS = "opportunitymycustom_projectidmycustom_projectmycustom_projectid";
private readonly string CUSTOMER_ALIAS = "opportunitycustomeridaccountaccountid";
private readonly string MAG_ALIAS = "opportunitymycustom_magidmycustom_magmycustom_magid";
private readonly string MYCUSTOM12NC_ALIAS = "opportunitymycustom_12ncidproductproductid";

public void GetOpportunitiesSampleUsingFetchXML()
{
// Set up the CRM Service.
CrmService service = GetWebService();

// Define the fetch attributes.
// The number of records per page to retrieve.
int fetchCount = 500;

// Initialize the page number.
int pageNumber = 1;

// The current paging cookie. For retrieving the first page,
// pagingCookie should be null.
string pagingCookie = null;

// read the query from file
string xmlQueryText = File.ReadAllText("Query.xml");
string fetchXml = xmlQueryText;


while (true)
{
// Build fetchXml string with the placeholders.
string xml = SetPagingInfo(fetchXml, pagingCookie, pageNumber, fetchCount);

// Execute the fetch query and get the xml result.
string fetchResult = service.Fetch(xml);

// Load the fetch result into XMLDocument to parse its cotents.
XmlDocument doc = new XmlDocument();
doc.LoadXml(fetchResult);

// The paging-cookie attribute holds the paging cookie to pass in the next query.
pagingCookie = GetPagingCookie(doc);

// Retrieve the result nodes.
XmlNodeList resultNodes = doc.DocumentElement.SelectNodes("result");

// Retrieve all records from the result set.
foreach (XmlNode resultNode in resultNodes)
{
string region = ExtractNodeValue(resultNode, CUSTOMER_ALIAS + ".mycustom_baseregion");
string mycustom_customer_description = ExtractNodeValue(resultNode, PROJECT_ALIAS + ".mycustom_customer_description");
string mycustom_project_name = ExtractNodeValue(resultNode, PROJECT_ALIAS + ".mycustom_name");
string mycustom_customer_part_number = ExtractNodeValue(resultNode, "mycustom_customer_part_number");
string mycustom_opportunity_revenue = Convert.ToDecimal(ExtractNodeValue(resultNode, "mycustom_opportunity_revenue"));
string ownerAccount = ExtractNodeValue(resultNode, OWNER_ALIAS + ".domainname");
string dwaccount = ExtractNodeValue(resultNode, CUSTOMER_ALIAS + ".name");
string mycustom_mag_name = ExtractNodeValue(resultNode, MAG_ALIAS + ".mycustom_name");
string mycustom_12nc_name = ExtractNodeValue(resultNode, MYCUSTOM12NC_ALIAS + ".name");

string opportunityid = ExtractNodeValue(resultNode, "opportunityid");

}

// Check for morerecords, if it returns 1.
if (HasMoreResults(doc))
{
// Increment the page number to retrieve the next page.
pageNumber++;
}
else
{
// If no more records in the result nodes, exit the loop.
break;
}

} // end while

}


Other Resources for FetchXML

Fetch XML Schema - Docs, Examples, Syntax, etc

Intro to FetchXML and see Other Resources on it for great blogs

Retrieve Example

Here is a tweaked example from the docs for the Retrieve method. This works very similarly to the Execute Example with regards to the QueryExpression.

CrmService service = GetWebService();

// Create the column set object that indicates the properties to be retrieved.
ColumnSet cols = new ColumnSet();

// Set the properties of the column set.
cols.Attributes = new string [] {"fullname"};

// contactGuid is the GUID of the record being retrieved.
Guid contactGuid = new Guid("4D507FFE-ED25-447B-80DE-00AE3EB18B84");

// Retrieve the contact.
// The EntityName indicates the EntityType of the object being retrieved.
contact contact = (contact)service.Retrieve(EntityName.contact.ToString(), contactGuid, cols);

Retrieve Multiple Example

Here is a tweaked example from the docs for the Retrieve method. This works very similarly to the Execute Example with regards to the QueryExpression.

CrmService service = GetWebService();

// Create the ColumnSet that indicates the properties to be retrieved.
ColumnSet cols = new ColumnSet();

// Set the properties of the ColumnSet.
cols.Attributes = new string [] {"fullname", "contactid"};

// Create the ConditionExpression.
ConditionExpression condition = new ConditionExpression();

// Set the condition for the retrieval to be when the contact's address' city is Sammamish.
condition.AttributeName = "address1_city";
condition.Operator = ConditionOperator.Like;
condition.Values = new string [] {"Sammamish"};

// Create the FilterExpression.
FilterExpression filter = new FilterExpression();

// Set the properties of the filter.
filter.FilterOperator = LogicalOperator.And;
filter.Conditions = new ConditionExpression[] {condition};

// Create the QueryExpression object.
QueryExpression query = new QueryExpression();

// Set the properties of the QueryExpression object.
query.EntityName = EntityName.contact.ToString();
query.ColumnSet = cols;
query.Criteria = filter;

// Retrieve the contacts.
BusinessEntityCollection contacts = service.RetrieveMultiple(query);


Generate FetchXML query using UI

If you want to use FetchXML, but are overwhelmed or just don’t want to think that hard you are in luck. The Advanced Find in the user interface allows you to save queries that you build using their ultra cool query builder. Lucky for us they are saved in FetchXML format, so all we have to do is read the saved query from the CRM and tweak it for our uses.

The code to Extract the Saved Query from the CRM

public string GetUserQuery(string queryName)
{
// specify the columns we want to return
ColumnSet cols = new ColumnSet();
cols.Attributes = new string[] { "fetchxml"};

// Create a ConditionExpression.
ConditionExpression nameCond = new ConditionExpression();
nameCond.AttributeName = "name";
nameCond.Operator = ConditionOperator.Equal;
nameCond.Values = new object[] { queryName};

FilterExpression filter = new FilterExpression();
filter.FilterOperator = LogicalOperator.And;
filter.Conditions = new ConditionExpression[] { nameCond };

// Create the QueryExpression.
QueryExpression query = new QueryExpression();
query.EntityName = EntityName.userquery.ToString();
query.ColumnSet = cols;
query.Criteria = filter;
query.Distinct = true;

// Create the request object.
RetrieveMultipleRequest request = new RetrieveMultipleRequest();
request.Query = query;

// Execute the request.
RetrieveMultipleResponse results = (RetrieveMultipleResponse)GetWebService().Execute(request);

if (results.BusinessEntityCollection.BusinessEntities.Length > 0)
{
userquery result = (userquery)results.BusinessEntityCollection.BusinessEntities[0];

string xmlQuery = result.fetchxml;
xmlQuery = FormatXmlQuery(xmlQuery);
return xmlQuery;

}
else
{
return string.Empty;
}



}

Just call the method and write the results to a file or console. Then

string xmlQuery = p.GetUserQuery("My Custom Query");
Console.WriteLine("Saved Query: " + xmlQuery);

Once you have the query, you will need to modify the fetch tag to look like the one in my FetchXML example.

Here is an alternate way to do the same thing. Be careful if your query is complex with outer joins, etc, you will lose some of that when you convert to a QueryExpression. If you have the ID or get it as described in the alternate way link, you can use the code below to get the actual FetchXML stored in the CRM.

public string GetUserQuery(Guid queryId)
{
CrmService ws = GetWebService();
userquery userQuery = (userquery)ws.Retrieve(EntityName.userquery.ToString(), queryId, new AllColumns());
string xmlQuery = userQuery.fetchxml;
xmlQuery = xmlQuery.Replace("<", "\r\n<");

return xmlQuery;
}

Switching Between FetchXML and QueryExpression

There are two methods that all you to switch between FetchXML and QueryExpression. They are FetchXmlToQueryExpression and QueryExpressionToFetchXml. See here for more details on the subject. A word of warning, the documentation does NOT tell you this, but you cannot do outer joins or handle selects from anything but the main entity. If this is possible, someone please let me know. For example, if I try to convert the XML in the FetchXML example to a QueryExpression, I will lose the columns I selected when I look at the results. On the other hand, I can go from any QueryExpression to FetchXML syntax without any problem because FetchXML is much more expressive.

Additional Resources

After you read my entry, you may want to start here for info on how to use queries, etc.

Top most Docs for all versions of Microsoft Dynamics CRM at: http://technet.microsoft.com/en-us/library/bb496810.aspx