WARNING — unsupported CRM hacking below!

A client wanted signatures to be inserted automatically in emails sent from CRM. I wanted to use the built-in template-feature, but the button on the email form launches a modal dialog which makes it hard to script. I was just about to give up and do a callout, when my manager suggested eavesdropping on the dialog to see what it did to merge the template.

Wireshark revealed that no postback is involved, the dialog merely calls an undocumented web service called /AppWebServices/EmailTemplateService.asmx. The service has an operation called GetInstantiatedEmailTemplate (see pic) which returns a bunch of XML. Not wanting to deal with this myself, I poked around in the email-form source and found that Microsoft has been kind enough to provide a function that’ll insert the result of the template instantiation for you. It’s called InsertValue() and resides on the crmForm.all.description element.

While this method is obviously unsupported, I’m reliably informed that the web services in /AppWebServices are also to be found in Titan.

Here’s the code in all it’s glory, note that JavaScript SOAP Client is used for the web service call.

var template_url = 'http://foo/AppWebServices/EmailTemplateService.asmx';

var soap_js = IncludeJsByDom('http://foo/js/soapclient.js');
soap_js.attachEvent("onreadystatechange", check_load);
function Get_Text()
{
    var params = new SOAPClientParameters();
    params.add("templateId", '{5623E3DE-1175-DC11-A465-001B78E16CCE}');
    params.add("objectId", crmForm.all.to.DataValue[0].id);
    params.add("objectTypeCode", 2);
    SOAPClient.invoke(
        template_url,
        "GetInstantiatedEmailTemplate",
        params,
        true,
        Template_Callback);
}
function Template_Callback(vol){
xmlDoc=new ActiveXObject("Microsoft.XMLDOM");
xmlDoc.async=false;
xmlDoc.loadXML(vol);
var body = xmlDoc.getElementsByTagName("body");
crmForm.all.description.InsertValue(body.item(0).text);
}
var check_load = function()
{
    if (event.srcElement.readyState == 'loaded'
    || event.srcElement.readyState == 'complete')
    {
        Get_Text();
    }
}

I’ve implemented some rather hefty Reporting Services (RS) reports lately, with some of the requirements including recursive summing af attribute values through hierarchies of CRM accounts. This sort of aggregation may be possible in RS, but I dislike that tool with a vengeance and prefer doing math in the database.

My first attempt (and the one that is now running at the client) used recursive SQL-functions. The scoping rules for recursive functions are highly non-intuitive however and when I look at the functions now, I have only a vague idea of why — let alone how – they work. Further, functions have to be created in the database independently of uploading the reports that use them, a minor deployment headache. Clearly there must be a better way…

Enter Common Table Expressions, a new feature introduced in SQL Server 2005. The syntax and exactly how the recursion works is somewhat opaque, the best explanation I’ve found is this article. You’ll probably need to read it if you want to grok this bit of SQL that computes the total number of employees in the parent/child hierarchy of each account:

with num_employees as
(
	select fa.accountid,
		accountid as original_id,
		fa.[name] as original_name,
		isnull(fa.numberofemployees,0) as numberofemployees
	from filteredaccount fa

	union all

	select fa.accountid,
		ne.original_id,
		ne.original_name,
		isnull(fa.numberofemployees,0) as numberofemployees
	from filteredaccount fa
		inner join num_employees ne on fa.parentaccountid = ne.accountid
)

select sum(numberofemployees) as numberofemployees, original_name
from num_employees
group by original_id,original_name

Here’s similar code for a prefilterable report dataset:

with num_employees as
(
	select accountid,
		accountid as original_id,
		name as original_name,
		isnull(numberofemployees, 0) as numberofemployees
	from filteredAccount as CRMAF_fa

	union all

	select fa.accountid,
		ne.original_id,
		ne.original_name,
		isnull(fa.numberofemployees, 0) as numberofemployees
        from filteredAccount as fa
		inner join num_employees as ne on fa.parentaccountid = ne.accountid
)
select sum(numberofemployees) as numberofemployees, original_name
from num_employees as num_empl_1
group by original_id, original_name

The most elegant formulation I’ve come up with is based on this example however:

with num_employees as
(
        select          accountid,
                        sum(numberofemployees) as numberofemployees
        from            filteredaccount
        group by        accountid

        union all

        select          fa.parentaccountid,
                        y.numberofemployees
        from            filteredaccount as fa
			inner join num_employees as y on y.accountid = fa.accountid
)
select sum(numberofemployees),accountid
from num_employees
group by accountid

I challenge you, the reader, to come up with a more concise example :-).

More resources:

• The Zen of Recursion
• CS145 Lecture Notes (14) — Recursion in SQL
• SQL Server 2005 Recursion and WITH Clause

Got a new version of LinqtoCRM running, but it is not nearly as big an improvement as I had hoped. While Matt Warren’s posts proved a big boost, some of the stuff is not applicable to CRM (eg. constructing expression trees so they can be StringBuilded sequentially to SQL) and some of it I just have to think about some more.

Anyway, go get it while it’s hot.

Before I could get started on LinqtoCRM, I had to get Visual Studio 2008/WCF and CRM to agree on a common mode of interaction. I must confess that, in the past, I’ve only picked up just enough web services knowledge to get things humming (which was almost nothing in VS 2003/2005). WCF, with its notions of “endpoints” and other newfangled stuff, seems a bit more configuration heavy. Here’s what I did to get it to work. In “app.config”, you need this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.serviceModel>
    <bindings>
      <basicHttpBinding>
        <binding name="CrmServiceSoap" closeTimeout="00:01:00" openTimeout="00:01:00"
        receiveTimeout="00:10:00" sendTimeout="00:01:00" allowCookies="false"
        bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
        maxBufferSize="100000000" maxBufferPoolSize="100000000" maxReceivedMessageSize="100000000"
        messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
        useDefaultWebProxy="true">
          <readerQuotas maxDepth="32" maxStringContentLength="100000000" maxArrayLength="100000000"
          maxBytesPerRead="4096" maxNameTableCharCount="100000000" />
          <security mode="TransportCredentialOnly">
            <transport clientCredentialType="Windows" />
          </security>
        </binding>
      </basicHttpBinding>
    </bindings>
    <client>
      <endpoint address="http://foo/MSCRMServices/2006/CrmService.asmx"
      binding="basicHttpBinding" bindingConfiguration="CrmServiceSoap"
      contract="ServiceReference.CrmServiceSoap" name="CrmServiceSoap" />
    </client>
  </system.serviceModel>
</configuration>

And in code, do something like this:

CrmServiceSoapClient client = new CrmServiceSoapClient();
client.ClientCredentials.Windows.ClientCredential.Domain = "foo";
client.ClientCredentials.Windows.ClientCredential.UserName = "bar";
client.ClientCredentials.Windows.ClientCredential.Password = "foo";
client.ClientCredentials.Windows.AllowedImpersonationLevel =
	System.Security.Principal.TokenImpersonationLevel.Impersonation;

Addendum: I just noticed that Visual Studio still lets you create a traditional web reference (as opposed to a service reference). This may be a lower friction approach:

This week I spent some time implementing an integration-scenario for a client. There were some complicating factors that we managed to overcome, and in case it may have wider applicability, I’ve written up our experiences.

The client has a successful e-commerce solution, and wanted updated information on their customers and their orders available in Dynamics CRM, so that complaints and other service requests could be handled consistently. For this sort of scenario, we would typically use ImportManager Studio, a superb piece if kit from from CRM Extensions. Given an ODBC connection to a data source and access to the CRM web service, it will merrily integrate data to almost all parts of the CRM data model.

Unfortunately, the e-commerce database was not hosted at the client’s premises. A rickety VPN-line was available, but deemed unsuitable and the hosting company was unwilling to open port 1433 directly to the SQL-server — a reasonable stand. We decided that a web service extracting information from the database and deployed somewhere on the e-commerce website would provide a solution acceptable to all, and I set to work.

First up, I wrote stored procedures to extract the required data. The database layout was very… different, but the forces of good prevailed. Next up was the implementation of the web services themselves. The group architect was assigned to the project, and figuring I had better do it right, I set about creating business-entity classes with associated data-accessors. This involved lots of tedious copy-paste boilerplate-coding but after a while, I was serving out tidy XML:

<ArrayOfOrder>
    <Order>
        <OrderNum>123</OrderNum>
        <OrderDate>03-08-2007</OrderDate>
        .
        .
        .
        <OrderLines>
            <OrderLine>
                <Linenr>1</Linenr>
                <Productcode>321</Productcode>
                .
                .
                .
            </OrderLine>
        </OrderLines>
    </Order>
</ArrayOfOrder>

Before moving onto the customers, I paused and looked at the code. While I was happy with the output, the associated effort and complexity seemed excessive. Indeed, each order-attribute exposed through the web service was named, renamed, referenced or manhandled in some other way no less than 10 times: twice in the stored procedure, twice in the data-access class, twice again in the entity-class constructor and four times around the property with associated getters and setters. While Visual Studio or SQL Server checked the validity of most af these occurrences (and their number can be significantly reduced in .Net 3.5), several where just free-floating strings. Worse, there was the risk of similar-looking attributes being cross-assigned due to some cut-‘n-paste error — I had found several of these already. Further, if the customer decided they needed more attributes migrated, adding them would be significant hassle.

Leaning back, unhappy with this state of affairs, I tried to come up with better, simpler solution. It was then I remembered something about SQL Server having it’s own web service thingamabob, and — after some bumbling around — lo and behold: Native XML Web Services for Microsoft SQL Server 2005. The introduction has it pretty well:

Microsoft SQL Server 2005 provides a standard mechanism for accessing the database engine using SOAP via HTTP. Using this mechanism, you can send SOAP/HTTP requests to SQL Server to execute:

• Transact-SQL batch statements, with or without parameters.
• Stored procedures, extended stored procedures, and scalar-valued user-defined functions.

Neat huh? SQL Server seems to accomplish this feat by registering itself directly with the OS(through http.sys), independent of any IIS, ensuring that relevant requests comes its way. The documentation on the “create endpoint” statement is terse and I couldn’t find any non-trivial examples. After some trying, I managed to concoct a statement palatable to the sophisticated tastes of SQL Server:

create endpoint Orders
	state = started
as http
(
	path='/sql/orders',
	authentication = (integrated),
	ports = (clear),
	clear_port = 8080,
	site = 'db-server'
)
for soap
(
	webmethod 'GetOrdersModifiedAfter'
		(name = 'Orderdb.dbo.GetOrdersModifiedAfter'),
	batches = disabled,
	wsdl = default,
	database = 'Orderdb',
	namespace = 'foospace',
	schema = standard
)

Obviously these web services can only be made accessible from the server running SQL Server (i.e. deploying them to the web-server running the e-commerce stuff was not an option). So — having validated the approach — I had to check with the hosting guys that it was OK to poke a hole in the firewall to the SQL server on port 8080. Security for this approach should be solid: Requests are authenticated at the http-level before being checked again by SQL Server. As a further measure at the host-site, only requests from the CRM-server IP are allowed through the firewall.

The downside to this approach was that my web service now disgorged object-arrays containing ADO DataSets (that is, if you’ve been a good boy…). The slightly added complexity in the code interacting with the web service was well worth the elimination of the entire middle tier on the database-end though. The DataSet can be found with this code (more than one may be returned, so watch out):

private static DataSet GetDataSet(object[] objectArray)
{
	DataSet ds = null;
	foreach (object o in objectArray)
	{
		if (o.GetType().ToString() == "System.Data.DataSet")
		{
			ds = (DataSet)o;
		}
	}
	return ds;
}

So there you have it: Found a off-site SQL Server with interesting data to be had? Native Web Services provides quick and low-maintenance access to it.

I’ve uploaded an initial version of LinqtoCRM to CodePlex. It’s a proof-of-concept more than anything else, but it will handle some non-trivial queries (like joins). The goal of the project is to abstract away CRM’s (necessarily) clunky web service interface and unleash the amazing application platform underneath. While I’ve worked with Linq as part of my thesis for the last couple of months, I managed to throw this code together over the weekend. If you have good examples and a bit of compiler-knowledge, creating your own queryprovider is not overly hard.