Linq to CRM 0.2.0

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.

Interacting with the Dynamics CRM Web Service through WCF

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:

Using SQL Server Native Web Services

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.

Concise code

Yesterday when porting LinqtoCRM to VS 2008 Beta 2, I had to get an array of the types of the properties of some generic type. This array would be passed to Type.GetConstructor(). I had an array of PropertyInfos which contain the type of the property, and I could have new’ed up an array and looped the types into that. Instead I did this:

PropertyInfo[] props = typeof(T).GetProperties();
Type[] types = props.Select(p => p.PropertyType).ToArray();
ConstructorInfo cInf = typeof(T).GetConstructor(types);

Visual Studio/.Net changes in Beta 2

A new beta of Visual Studio was released yesterday, you can read up on the general stuff at ScottGu’s Blog.
The release broke the various projects I’m working on in weird and wonderful ways, here are some of them:

  • The expression tree grammar has changed, some types have disappeared only for new ones to show up.
  • Anonymous types no longer have a zero-argument constructors (from the reflection API), but have to be initialized with their full complement of properties. The properties are read-only, even through the reflection API.
  • The old expression-tree visualizer doesn’t work with the new expression trees, get the new samples.
  • The IQueryable interface has been expanded to include an IQueryProvider-property. Depending on your implementation, you can get around this be returning this and implementing IQueryProvider in your IQueryable (as a side note, it would be great if someone could document the thinking behind the various interfaces floating around in System.Linq, even if it’s still subject to change).

I haven’t really pondered the deeper implications and/or motivations of these changes, merely dealt with them.

UPDATE: Matt Warren has written a coherent description of the IQueryable and associated interfaces, parts 1, 2, 3, 4 and 5.