Dynamics CRM as development platform: transparent substitution of CrmService with Filtered Views.

In my last post I suggested not to directly use, in your code, the CrmService proxy class. I quickly mentioned some enhancements you can gain from this, let’s focus on substituting web service calls with database access on filtered views. Even if limited to read operations, this can increase a lot the performances of your system.

As we want the callers not to be aware of the way we retrieve data, we have to passing back data to them in the form they expect it: “BusinessEntity” derived classes or collection of them. This may seems hard, but it isn’t. You can build the statement in way that it will produce data in XML, in a format that can be directly deserialized in entity instances.

So, let’s view how we can:

  • Compose the SQL statements in such a way the will produce the xml we need
  • Deserialize the data from xml to entities

Take a look at this query:

WITH XMLNAMESPACES 
( 
  'http://www.w3.org/2001/XMLSchema' as xsd 
, 'http://www.w3.org/2001/XMLSchema-instance' as xsi 
, 'http://schemas.microsoft.com/crm/2006/WebServices' as crm 
) 
SELECT name as 'crm:name' 
     , accountid as 'crm:accountid' 
     , donotphonename as 'crm:donotphone/@name' 
     , donotphone as 'crm:donotphone' 
     , address1_shippingmethodcodename as 'crm:address1_shippingmethodcode/@name' 
     , address1_shippingmethodcode as 'crm:address1_shippingmethodcode' 
     , modifiedbyname as 'crm:modifiedby/@name' 
     , modifiedbydsc as 'crm:modifiedby/@dsc' 
     , modifiedby as 'crm:modifiedby' 
     , createdbyname as 'crm:createdby/@name' 
     , createdbydsc as 'crm:createdby/@dsc' 
     , createdby as 'crm:createdby' 
     , statuscodename as 'crm:statuscode/@name' 
     , statuscode as 'crm:statuscode' 
     , statecodename as 'crm:statecode/@formattedValue' 
     , statecode as 'crm:statecode' 
  FROM Filteredaccount 
   FOR XML PATH ('account')

The WITH XMLNAMESPACES, the field aliases and the FOR XML PATH do the magic, and we get data in this format:

<account xmlns:crm="http://schemas.microsoft.com/crm/2006/WebServices" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
  <crm:name>Adventure Works Ltd.</crm:name> 
  <crm:accountid>E85D1177-EE17-DB11-87E4-0000E2998A6B</crm:accountid> 
  <crm:modifiedby name="CRM Administrator" dsc="0">89A611D4-C7BD-DB11-A4E3-005056A80A71</crm:modifiedby> 
  <crm:createdby name="CRM Administrator" dsc="0">89A611D4-C7BD-DB11-A4E3-005056A80A71</crm:createdby> 
  <crm:statuscode name="Active">1</crm:statuscode> 
  <crm:statecode formattedValue="Active">Active</crm:statecode> 
</account> 
<account xmlns:crm="http://schemas.microsoft.com/crm/2006/WebServices" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
  <crm:name>Contoso Inc.</crm:name> 
  <crm:accountid>A00ED7BB-9D0D-DB11-9073-000C293F9D57</crm:accountid> 
  <crm:donotphone name="Allow">0</crm:donotphone> 
  <crm:modifiedby name="CRM Administrator" dsc="0">89A611D4-C7BD-DB11-A4E3-005056A80A71</crm:modifiedby> 
  <crm:createdby name="CRM Administrator" dsc="0">89A611D4-C7BD-DB11-A4E3-005056A80A71</crm:createdby> 
  <crm:statuscode name="Active">1</crm:statuscode> 
  <crm:statecode formattedValue="Active">Active</crm:statecode> 
</account>

That is exactly what you need to obtain, with the help of XmlSerializer, a collection of accounts:

XmlReader xmlReader = cmd.ExecuteXmlReader(); 
xmlReader.Read(); 

while (xmlReader.ReadState != ReadState.EndOfFile) 
{ 
    xml = xmlReader.ReadOuterXml(); 
    StringReader reader = new StringReader(xml); 

    account acc = serializer.Deserialize(reader) as account; 
    accounts.Add(acc); 
} 

// convert accounts in a BusinessEntitesCollection...

Note that:
– the standard SQL syntax for a property is:

, property ascrm:property

– properties expressed by CRM object model in form of “Lookup” are splitted, on the views, in three columns:

, propertyname ascrm:property/@name
, propertydsc ascrm:property/@dsc
, property ascrm:property

– properties expressed by CRM object model in form of “Picklist” and “CrmBoolean” are splitted, on the views, in two columns:

, propertyname ascrm:property/@name
, property ascrm:property

– StatusCode and StateCode are the only further singularity.

Given these rules isn’t hard to build a class able to generate statements for any entity type. For an hard-core solution, add something able to translate a QueryExpression in SQL statements…

Advertisements

~ by Matteo on April 6, 2007.

3 Responses to “Dynamics CRM as development platform: transparent substitution of CrmService with Filtered Views.”

  1. Hi, i’am looking for help and I wonder if someone of you could help me. The stuff you publish looks quite complex… so maybe you have a tip for me.

    I try to prefilter a report. nothing special only standard. I use VS 2005. It wont work… and i have no idea:

    The setting of a alias for the table is quite simple (CRMAF_Filtered…..). so I’ am sure i’ve done it right. Does anyone know these problems, pls let me know by mreich@infogate.ch. Thanks very much

  2. мне кажется: отлично..

  3. Thankfulness to my father who stated to me concerning this website, this blog is really remarkable.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: