post by  Brajendra Singh, MSDN

Recently, I have been working on an integration project where few exiting systems were getting integrated with Microsoft CRM System 3.0. We architected to use BizTalk CRM adapter to synchronize and integrate data with CRM system. We could not locate a good documentation about adapter usage. We had to do lots of research, hit-&-try POCs to understand CRM adapter usage. And this encouraged me to come up with set of articles which can facilities developers to understand and use adapter easy and fast.
I am thinking to cover some basic operations with BizTalk CRM adapter such Query Data, Create Data, Update Data, Delete Data, Execute Operations, Retrieve Data etc. I will also cover dealing with complex data types (Pick List, Lookup) and custom entities/attributes.
I am expecting reader to have basic understanding of schemas, maps, orchestration, ports and adapters.
How to Install BizTalk CRM Adapter
Adapter installation is simple. You can download installation bits and usage guide fromhttp://www.microsoft.com/downloads/details.aspx?FamilyID=4628fca6-388d-45bc-a154-453b920dbcb8&displaylang=en.
Following are key points of installation and usage guide –
1.    Installation is simple and setup based.

2.    CRM system uses AD based security schema. BizTalk uses send or solicit-response ports with CRM adapter to talk to CRM system. BizTalk ports run under host. If your BizTalk host service account does not have adequate access on CRM system then you are required to create another host with account which has sufficient access permission on CRM system. And then you construct a new CRM send handler which runs under new host and then configure CRM related ports to run new CRM send handler.

3.    Installation guide also talks about creating port and generating schema which I will also cover in coming sections.

4.    I recommend readers to install CRM SDK which holds good quantity of code samples and information. You can find SDK @http://www.microsoft.com/downloads/details.aspx?familyid=9C178B68-3A06-4898-BC83-BD14B74308C5&displaylang=en.

5.    It is useful to take look at overall installation and usage document. Especially known issues section.

Basics of CRM System
Before we chat about using CRM adapter, we will cover some nuts and bolts which are essential for any BizTalk developer to work with CRM. CRM application can be access using following URL (though it depends on configuration of CRM site) - http://<CRM-Sever>/loader.aspx.
You will find that CRM system is divided into multiple sub-systems such Sales, Marketing, Service etc. With extension of CRM GP, accounting sub-systems also come in picture. Each of these sub-systems maintains a range of information like “Service” sub-system maintains Cases (customer escalations), Accounts, Contacts, Products etc.
In technical vocabulary, information is hold by various entities like we have one entity each for case, account, contact etc. These entities generally hold association/relationship among themselves. For example one account can have multiple contacts, one contact can have more than one case, there is many-to-many relationship between accounts and products etc.
Each of these entities follows a schema. Schema defines attributes hold by these entities and their type information. For example contact has Last Name (string), First Name (string), Gender (Pick List) etc as attributes. If you require then you can also create new custom entities or new custom attributes. When working with CRM system, we generally deal with these entities and their attributes. We create, update or delete instances of these entities.
Before we start integration with CRM system we need to understand entities, their schema, relationship with other entities etc. You can go to “Settings >> Customization >> Customize Entities” to see list of entities available in CRM system. List shows name, schema name, type and description. Here you will find option to create new custom entity or modify existing entity.
If you open (double-click) any existing entity, it will show you details of it. You will see details of attributes, forms/views to populate these entities in CRM system, relationship with other entities etc. When you check “Attributes” of entity, you will see attribute schema name, display name, type and description. Attribute schema name and type is very important information because BizTalk CRM adapter generates schema based on schema name and type information inside CRM system. You will also find provision to create new custom attributes.
CRM sub systems, forms, entities, schemas and customization are self explanatory. I suggest readers to spend some time with it; play around and get comfort. 
It is time to get into coding now.
How to perform Query Data with CRM Adapter
Dynamics CRM provides a web service which can be used by developers to do operations against CRM system. CRM Adapter internally uses this web service for operations. Generally, you will find web service URL as –
http://<CRM-Server>/MSCRMServices/2006/CrmService.asmx.
Without documentation, it was hard for me to perform POCs with BizTalk CRM adapter. To make my job simpler, I played around with Dynamics CRM web service to manipulate entities. And this gave me adequate idea when dealing with generated CRM schemas in BizTalk. I am planning to follow same trick in these articles also - .Net code first and then BizTalk implementation.
We are going to see how query can be performed against CRM system. I am going to query case details using case guid. Case is complaints or service requests registered by customers and it comes under “Service” sub-system.
1.    Perform Query using .net code (calling CRM web service)
I added CRM web service as web reference and named it “CRMServer”. Following is code to query case using case guid.
 CRMServer.CrmService service = new CRMWSTest.CRMServer.CrmService();
 service.Credentials = System.Net.CredentialCache.DefaultCredentials;
 string fetchXml = @"<fetch mapping=""logical"">
                                  <entity name=""incident"">
                                     <attribute name=""ticketnumber""/>
                                     <attribute name=""title""/>
                                     <filter type=""and"">
                                          <condition attribute=""incidentid"" operator=""eq"" value=""7c89ec34-6828-410b-9718-185b5a39a8ba""/>
                                      </filter>
                                    </entity>
                                 </fetch>";
 string result = service.Fetch(fetchXml);
Code is easy to understand. I first created an instance of CRM web service client and then create query (fetch) XML. Fetch xml is straightforward to understand.
  <entity name=""incident""> 
Means entity which I wish to query is “incident”. Well, case is the display name and schema name for case is “incident”. You can find this in customization section as mentioned in previous section.
<attribute name=""ticketnumber""/>
 <attribute name=""title""/>
I am querying “Case Number” and “Title” attributes of case. Schema name of these two attributes are “ticketnumber” and “title”. You can add more attributes as required.

 <filter type=""and"">
       <condition attribute=""incidentid"" operator=""eq"" value=""7c89ec34-6828-410b-9718-185b5a39a8ba""/>
</filter>
This is filter condition to query. Type is “and” as logical operator. It can also accept “or”. “attribute” is entity attribute which we are using for query. Again this is schema name not display name. “operator” is assignment operator and it can accept values as “lt” (less than), “gt” (greater than), “le” (less than or equal), “ge” (greater than or equal), “eq” (equal), “ne” (not equal), etc. Finally “value” holds value of attribute to be queried. If required, you can use more attributes with “and/or” options. I suggest readers to refer CRM SDK for more details about fetch xml.
Finally we call “Fetch” method passing “fetchXml” as parameter.
Query result comes in following format.
<resultset morerecords="0" paging-cookie="$1$$incident$incidentid$1$0$38${7C89EC34-6828-410B-9718-185B5A39A8BA}$!$incident$incidentid$1$0$38${7C89EC34-6828-410B-9718-185B5A39A8BA}$">
  <result>
    <ticketnumber>CAS-01006-ZBEJ0W</ticketnumber>
    <title>This is test case1</title>
  </result>
</resultset>
Result is self illustrative. You can see ticket number and title returned for query.

2.    Perform Query BizTalk code (using Dynamics CRM Adapter)
Above was query implementation in .net code. Now we are going to implement same logic in BizTalk orchestration. Following are the steps to perform same query operation in orchestration using BizTalk Dynamics CRM adapter.
·         In query we send Fetch XML and return result. We need solicit-response send port for this purpose. Create a new static solicit-response send port in BizTalk Administration Console. Give a name to it (say “CRMSendPort”) and select type as “Microsoft Dynamics CRM”. Go to “Configure” and put “Web Service URL” as http://<CRM-Server>/mscrmservices/2006.
In send handler, select send handler created as per CRM specific host in above section. Configure “Send Pipeline” as “Xml Transmit” and “Receive Pipeline” as “Xml Receive”. Save and close things.

Note: One of the common mistakes committed by developers is to put complete web service URL (http://<CRM-Server>/MSCRMServices/2006/CrmService.asmx) in configuration. This generates error during schema generation. Reason is that CRM adapter uses “Metadata.asmx” to generate schema and it just need URL of CRM web service virtual directory.

·         Create a BizTalk project and assign a strong name key to it.

·         We have to generate schema for query operation. Right click Project Name >> Select Add Menu >> Select Add Generated Items Menu>> Select Add Adapter Metadata Option>> Press Add Button >> Select Microsoft Dynamics CRM Option>> Select Port (same port created in above steps say “CRMSendPort”) >> Press Next Button. “Microsoft Dynamics CRM User Credentials” screen opens up. Enter user name and password for CRM system. Remember this user name and password should have adequate access to fetch entity and their schema information from CRM system. Press Ok button after entering credentials.

·         “Microsoft Dynamics CRM Actions and Entities” screen opens up. As we already discussed, CRM sub-systems are made of entities. At this point we select entity (account, contact, case etc.) which we want to deal with and action (add, update, delete, execute etc.) which we want to perform on selected entity. Since we want to perform query on cases (incidents), select “ExecuteFetch” as action and “Incident” as entity. If required, in some implementation, you can also select multiple actions and multiple entities. When selection is done, press next button. It would take few seconds to generate all schemas related to fetch operation in BizTalk project.

·         In generated artifacts, it generates lots of schema and one BizTalk orchestration file. Open orchestration file and delete all port types and multi-part message types generated by default. Well, these are useful but I am asking to clean them so that we can create and understand things from scratch.

·         It generates some 10 schemas in project. These schemas contain type definition and are included in each other. We are interested in two schemas only – one request schema which can send our query/fetch XML and one response schema which can pull result back.

·         “ExecuteFetch_ExecuteFetchRequest.xsd” serves purpose of request schema. You expand to see details of this schema. It has two important elements – “crm_action” and “FetchXml”. We will check these in details in few seconds.

·         Now response is tricky part. Developers think that “ExecuteFetch_ExecuteFetchResponse.xsd” is response schema but it is NOT. Every response using CRM adapter follows a fix and common schema and this schema is not generated. You can find this schema @ “C:\Program Files\BizTalkAdapter\Schemas\Response.xsd” depending upon adapter installation location. You have to include this Response.xsd in project.

·         We are now all set to implement logic. Create two messages of type request and response schemas. Hook these messages to a static solicit-response send logical port using send and receive shape. Use map (with transform shape) or message assignment shapes to create request xml and send it via solicit-response port. When response comes consume as per requirement. I am leaving this logic development part to readers. If you face any issue, please refer sample application attached with this article or feel free to message me.

·          When creating xml request (using map or message assignment), take care of following things. Set “crm_action” attribute value to “execute”. We are doing this because we are executing fetch operation on CRM system. Other possible values are “create”, “update” and “delete”. We will see use of this in coming articles. Set “FetchXml” element value to - "<fetch mapping='logical'><entity name='incident'><attribute name='ticketnumber'/><attribute name='title'/><filter type='and'><condition attribute='incidentid' operator='eq' value='7c89ec34-6828-410b-9718-185b5a39a8ba'/></filter></entity></fetch>"
Remember this is same format which we used in previous .net code sample. In attached sample, I used script functoids in map to prepare request XML. I have implemented in crude way to keep things simple but you can do better and smart job.

·         When you are done with coding, compile project and deploy it. After deployment, bind solicit-response orchestration ports with physical solicit-response port (“CRMSendPort”) created in above steps. Enlist and start orchestration. Finally trigger orchestration to test query operation.

·         You will find that response message comes in following format –

  <Header>
  <ReturnCode>1</ReturnCode>
  <ErrorCode />
  <ErrorString />
  <Retryable />
  </Header>
<Body>
  <Message><prefix:ExecuteFetchResponse xmlns:prefix="http://localhost/schemas.microsoft.com/crm/2006/ExecuteFetchResponse"><FetchXmlResult>&lt;resultset morerecords='0' paging-cookie='$1$$incident$incidentid$1$0$38${7C89EC34-6828-410B-9718-185B5A39A8BA}$!$incident$incidentid$1$0$38${7C89EC34-6828-410B-9718-185B5A39A8BA}$'&gt;&lt;result&gt;&lt;ticketnumber&gt;CAS-01006-ZBEJ0W&lt;/ticketnumber&gt;&lt;title&gt;This is test case1&lt;/title&gt;&lt;/result&gt;&lt;/resultset&gt;</FetchXmlResult></prefix:ExecuteFetchResponse></Message>
</Body>
</ns0:Response>
“Message” element contains response (xml format) and it follows schema type ExecuteFetch_ExecuteFetchResponse.xsd. You need to employ some parsing technique to fetch information out of response xml.
Rest of the return message contains error handling mechanism which can be used as required.
·         That’s it. Make tweaks in request message and orchestration logic to explore query/fetch option thoroughly.
Conclusion
This was a high level walk through and a sample to query CRM system for entities. If required, please download sample project attached with article for further reference. If you face any issue please feel free to ping me on this article. Hope article was useful to you and your comments are always welcome.
Please wait for some time before we meet again with next part of it.