Version 0.2.3 is now up and I’ve made a “getting started” video for your amusement. There are now two downloads. The first one is the entire solution for you to hack on, the second is the minimal amount of code you need to write Linq queries in you own CRM project. Get it here, demo after the jump.
The demo can be found here: http://www.itu.dk/~friism/files/linqtocrm800.swf
UPDATE: New screencast here
It all started at the annual ITU party of 2007. Named the “Programmer Party” by the members of (Blank)Banquet, this was the last chance to find a programmer before the Junge Hunde 2007 festival and a strong snatch team was sent. I was there, of course, and happily drunk (of course) and without many protestations, I surrendered my card. From amongst the many contacts gathered on that fateful night, I was contacted a few weeks later by Cameline.
Map_of_You is a performance installation consisting of several “hubs” representing rooms in a house. I won’t elaborate on the artistic side of the project lest I get it wrong, follow the link if you want all the words. I worked with two of the “rooms”, the first one being the hall (choreographed by Tonje), featuring a slow motion mirror. The mirror was implemented using a DV-cam, a projector and some Resolume effects.
I spent most of my time in the living room however. This room is split into two parts by a large projection-screen. One side of the screen has a projector and the audience between the projector and the screen, casting shadows (right in my sketch). The other side has two performers and some ten pieces of all-black furniture. The screen shows icons representing the pieces of furniture. If the camera detects shadows intersecting with the icons, the icons are (ideally) moved out of the way, enabling the audience to move around the icons with their shadows. The performers can see this and will move the furniture to match the layout of the icons on screen. Further, the individual pieces of furniture have little narratives attached to them and the performers use these narratives and the interaction of the audience through the screen, to improvise a non-verbal play. I didn’t come up with this of course, Cameline did. But I did write the code that makes the camera-projector-screen part happen.
Technicalities
The interaction is inspired-by/copied-from Zack Booth Simpsons “Front Shadow, Translucent screen” setup. On the off chance that Zack doesn’t claim prior art, I think it should be named the “H.C. Andersen method of interaction”. Faced with the challenge of implementing the orchestration of the shadows, the icons, the projector and the camera, I first had to choose a platform and there’s plenty of choice: Processing, Pure Data, vvvv, MaxMsp with Jitter, EyesWeb and RoboRealm to name but a few. I gave each of these all of 15 minutes to prove their worth and most failed by not immediately working with my crummy Logitech webcam. Admittedly this was rather harsh, but at this point we where some ten days from the curtain call and I didn’t really have time to implement drivers or driver-bridges to cater to the exotic tastes that these frameworks tend to have (most seem to prefer fancy firewire-cameras). What did work out of the box though was Adobe Flash.
Before this project, I considered Flash a tool for lamers making annoying web-animations, sometimes rising to the the level of semi-interesting games. I figured Flash would have decent graphics manipulation libraries though and I could quickly identify a handful of tutorials demonstrating how to manipulate the web cam-input I was seeing on my screen. In the end, it turned out to be a pretty good decision — my thoughts on the matter are summarised here.
The code (click to download – let me know if you need more than the actionscript) is subtly broken in a wide variety of wonderful ways and has more magic numbers than a good-length fairy-tale. It took me the better part of 10 days to write, embarrassing considering the relative simplicity, but maybe not too bad when factoring in that I had to learn the idiosyncrasies of Flash and that I hadn’t really written any image analysing or graphics manipulating code before. When implementing the toggle for the control-UI, it occurred to me that I hadn’t handled a keyboard click-event on my own since Caltech, some three years ago.
As mentioned, the code is pretty simple. There’s a constructor that inits various bits of UI, draws a grid (used by the performers to gauge the relative positioning of furniture) and loads the furniture icons. The meat is in onEnterFrame which gets called every frame. First the web cam feed is drawn unto a BitmapData object, being flipped in the process (the camera was mounted upside down in the ceiling). BitmapData has a lot of nifty, if opaquely documented methods, reportedly written in C and much faster than whatever pixel-pushing you’re liable to implement in Actionscript yourself. One thing BitmapData cannot do however, is warp the image in a trapezoid way. The camera is mounted in the ceiling and thus won’t see the screen face on and to compensate, the image should ideally be warped. It turns that this is a minor problem for even quite oblique camera angles and since the warping code (from here) is rather slow and creates artifacts, it was discarded in the final version.
At this point, the image consists of both shadows projected by the audience and icons displayed by the projecter (along with various other cruft like irregularities on the projection screen). The software doesn’t do the intersection-analysis on this image (this would add the complication of identifying from the image the proper icon to move), but instead filters out the icons and does the hittest on just the shadows and its own internal representation of the icons. The filtering is done with a call to threshold that also transforms the various colour-values to a pure alpha-channel signal for easy hit detection using the built-in hitTest method. For this to work, the icons need to be displayed in a colour that’s lighter (or different in some other way) than how the audience shadows appear on screen.
The fact that the hittest is done on the internal state of the icons, as opposed to what is seen on screen, means that if the internal representation is not perfectly aligned with what the camera sees (and it isn’t), the interaction is gonna be wonky, ie. the systems perception of where your shadow is vis-a-vis the icons is wrong. After a bit of calibration and fiddling, the mis-alignment wasn’t too bad for most parts of the screen though and the system was pretty usable.
The rest of the code handles the moving of “hit” icons and makes sure that only icon is moved at a time (to give the performers time to relate to the new scene) and also changes the colour of the active icon to help the audience figure out which icon is currently moveable. Note that I’m using this hack to include flash UI components in the Flex project.
You’ll need a web cam to mess around with with application.
[kml_flashembed movie=”http://www.itu.dk/~friism/files/MoY/Webcam.swf” height=”240″ width=”320″ /]
So there you have it! A simple shadow-interaction system implemented in Flash/Actionscript 3.0. I’m sure my DIKU, game programming brother-in-arms Kristian is fueling his flamethrower, heading over to torch my sorry ass even before finishing reading this post, but hey!, it did the job :-). Future upgrades would include: Running the web cam feed through Resolume for fast and accurate distortion, getting proper fire wire cameras (you should use DV-cams as a minimum, they are better than web cams at correcting for poor lighting etc.) and perhaps porting the entire application to something faster than flash (to enable higher resolutions and better frame rates).
Closing Thoughts
Coding for interactive art installations is very much a question of extreme user interface design. When people go to experience art or theater it mustn’t feel like another annoying PC application they have to learn. There can be no mouse or keyboard (looks too much like “work”) and certainly no manual — the application must be immediately usable and have a very shallow learning curve. Remember that people will probably not spend more than 15 minutes looking at any given piece, severely limiting how much they can “learn”. Also remember that the audience will want to look cool and in control when interacting with the piece: People don’t to art galleries to be reminded of how bad computer interfaces are, they get plenty of that on their job.
Having said that, your code can have any number of bugs and glitches as long as they can be plausibly brushed aside with a “it’s supposed to that”. This makes for pleasant programming: You can hammer out the main concepts and get them working, leaving in all the annoying little bugs (that take hours to hunt down) for charm. In general, the audience is probably gonna be much more focused on what the installation is doing as opposed to how it’s doing it or whether it’s doing it “right”. Believe me, this seems strange when one’s spent the last few weeks poring over how to get it working and getting it working right.
Btw, if you know of a venue in Copenhagen or anywhere else for that matter, that might host the Map_of_You exhibition, please drop me a line!
Other Ressources:
I haven’t updated the project for a while, but Michael Höhne sent me some fixes that I’ve merged. I’ve also compiled the project with Visual Studio 2008 RTM and run it against CRM 4.0 RC0 (partnersource link). Get it here.
Michael H. has hinted that he might contribute more, and I expect to pour some time into the project too.
When I tell people that I once took the ferry from Copenhagen to Świnoujście and rode my bike across northern Poland, many go “I wanna do that, please tell me how!”. It’s rather simple really: If you live in Copenhagen, you just pack your bags, check the ferry schedule, ride down to Langelinje in time to buy a ticket, get on the ferry and you’re off. For those wanting a slightly more elaborate guide, I present — by popular demand… “Frytki means French Fries – Świnoujście to Gdansk on a pushbike”. I’ll start with some general observations about traveling by bike and about Poland before moving on to the day-to-day itinerary.
Riding from Świnoujście to Gdansk, will take you trough the old German lands of Pomerania and West-Prussia. The Germans were booted out (or killed) after World War II and the land taken over by the Poles (who themselves had been kicked out of Eastern Poland by the Soviets). So while Świnoujście used to be “Swinemünde” and Gdansk “Danzig”, all modern maps use Polish names and so will I. Some twenty years after the fall of the Iron Curtain, Poland still has a distinctly communist feel. The traveller in Northern Poland will see rusting machinery on abandoned communal farms, gray, dilapidated residential housing and Polski Fiats. The countryside is very pleasant however, generally flat or with gently rolling hills (good for biking), pine forests and nice, if narrow, beaches — rather like Denmark actually. Poles seem an easygoing and talkative bunch. Knowledge of English alas, is sporadic and German won’t get you much farther.
Preparations and Info
This being a biking-holiday, your bike will obviously take center stage. I took my sturdy-framed, plenty-of-gears city-bike (since stolen) which worked pretty well. In retrospect, I would probably sacrifice a bit of speed and go for a good, old-school touring-bike. Modern bikes have you leaning over the handle-bars, putting a lot of weight on your wrists and hands. This is fine for zipping around town for a few minutes, but after half a day on an imperfectly paved road, your hands tend to go numb and your wrists get sore. This can be greatly alleviated by a pair of padded biking-gloves, but the human body is just much more comfortable with it’s weight resting on it’s bum instead of the hands. Other than gloves, biking-tights are recommended — at least for guys. They will make you look singularly silly but,…. do it for the kids. Martin, a friend, was good enough to lend me his — note that lending of tights between men infer a very special bond.
Other than the bike, I brought along the following: tent, sleeping mat, sleeping bag, bike tools and spares, towel and toiletries, compass (for crude navigation), some nuts and raisins for breaks and clothes to wear when not in the saddle. This was carried in two saddlebags (also kindly lent be Martin) and on the cycle rack. Do be sparing when packing, you will hate any unnecessary weight as soon as you hit the first dent in the landscape.
I generally camped in camp sites which are to be found everywhere along the Baltic Coast, a major tourist destination for Poles. Most campgrounds seem unfamiliar with itinerant travellers and getting them to understand that you wish to stay for just one night — as opposed to hanging around at the beach for a week — might take some hand waving. It’s entirely feasible to sleep in the countryside but since campgrounds are cheap and plentiful, I didn’t find it to be worth the trouble. If you want to jettison the tent and sleeping bag, I’m sure there are plenty of hostels around.
Polish cuisine is rustic and hearty. Bread and pastries are cheap and good and can be enjoyed with a wide selection of pâtés and sausages. In the mornings, shops carry freshly made cheese called “twaróg”. It’s kinda like cottage cheese, only strained and compacted — I love it. Bottled water can be bought in all shops, but remember to get the “nie gaz” variety — lukewarm fizzy-water is not what you want when hot and thirsty. In the evenings I would usually have a two course meal of soup or gulasch followed by some kind of meat with gravy and potatoes. Variations of this theme exists, but vegetarians would have a hard time I fear. This, along with a beer, will set you back about kr. 50. You could certainly bring you own cooker, but with restaurant food at these prices, I wouldn’t bother.
From Świnoujście to Gdansk, going by the small roads near the coast is a distance of some 400-500 km. When going by bike, you should definitely try to stick to small roads as the bigger ones carry heavy, inconsiderate traffic. Riding 100 km in a day is not a problem, with 150 km possible if you get going early and stick to good roads. The trip can thus be completed comfortably in a week, including a day of sightseeing in Gdansk. If you want a longer trip, you can continue on from Gdansk to the supposedly nice “Lakes region” to the south-east.
Itinerary
In the summer of 2004 I was mooning around, waiting to go to the US in the fall to buy a Jaguar and (less importantly) study at Caltech. Usually these summers would be productively spent playing Heroes of Might and Magic III and eating fish-sticks with my flatmate Jon, with an occasional guest appearance by Gabriel. This summer Jon had defected however, and was in Thailand with his girlfriend. Single-player Heroes, it turns out, blows. I debated the merits of the Camino de Santiago with Susanne, but ended up telling her that I was going biking in Poland. Note to self: If you can’t summon the resolve to do something, tell someone else you’ll do it — no chickening out then.
So, on July 15, 2004 I went by Martin to get his bike-gear and then packed my stuff. In the afternoon I biked down to Langelinje, bought a ticket (kr. 520 open-ended return, including bike) and rode onto the ferry, the good ship “Pomerania” (the name in Danish is nothing short of hilarious: “Det gode skib Pommern”). Sailing out of Copenhagen harbour in the sunset is very nice. Going south, you sail all the way around Amager and get to see airplanes taking off from the airport right above your head. The ferry reeks of oil, both the lubricating and the cooking kind. I didn’t taste the french fries so I can’t say whether the lanky Polish cooks got the two reversed: try them at your own peril. If you don’t have a cabin (I didn’t), you sleep in uncomfortable air-plane like seats or on the floor.
Once off the ferry in Świnoujście, I adjusted my packs in the shade of a tree. The interesting parts of town are on the opposite (western) side of the Swina river so unless you really want to see it (it was thoroughly flattened by the Americans during WWII and then used by the Soviets as a naval base — don’t bother), you might as well head east out of town. I had neither map nor money at this point, luckily there’s only one road going east. In Międzyzdroje I withdrew the equivalent of kr. 1000 in złoty from an ATM (these lasted me thought the week) and purchased a 1:200.000 tourist map of the entire Polish Baltic Coast. For reasons of morale, I recommend maps with fairly large scales as they give the impression of covering great distances :-). Międzyzdroje, by the way, is a beautiful, genteel resort-town in Wolin National Park.
Heading out of Rewal, I encountered the first of a string of army bases that line the coast. During the Cold War, these bases where used by Polish marines training for the invasion of Falster and Sealand so that we Danes could also have shared the wonders of socialism. The bases are not marked on maps, nor by signs on the road so I had to double back some 5 km after having been pointed at with an AK by some dude in a guard tower. In general I found that the absence of towns/beaches/roads on my map suggested an abundance of sullen conscripts in real life, but it’s not a reliable measure. I’ll warn you of the ones I encountered, ask the locals for directions if in doubt.
Army bases not withstanding, the Baltic coast is absolutely wonderful. On this the first day alone, I rode through dozens of small resort villages perched in dunes behind the beaches. The coastal areas also feature another communist relic (other than military bases): Giant holiday-camps for the youth of the industrial heartland, replete with dorms and communal kitchens. The camps are still run by someone it seems, so young, tan and happy people on vacation abound. My first day of riding left me in a forest east of Kołobrzeg, some 100 km from Świnoujście (not counting the diversion).
The second day took my as far as Jarosławiec with another army base impeding progress in the Sarbinowo-area. On the way I stopped in Darłowo, an old Hansestadt. The town features a castle with a lot of old junk which I duly inspected. Eric of Pommerania (who succeeded Margaret I as regent of Denmark and the Kalmar Union) is buried there, you should go say hi. Fed up with having to turn back, I asked the lady at the restaurant where I had dinner whether I might encounter obstacles going east from Jarosławiec. And “bingo”, this time an airbase no less. You can even see it on Google Maps. I wonder why they have to put these installations right on the beach where people will want to bask and frolic and ride their bikes…?
From Jarosławiec I rode through Słowiński National Park to Leba, a somewhat larger resort town. The next day, there was a slight drizzle and I decided to just stay in town and read Don Quixote. The weather cleared somewhat in the afternoon so I biked out to climb a giant wind-mobile sand dune west of town. I was staying at a very small campground (someones backyard really) and the other guest were interested in my expedition. One of them — a friendly engineering student — even spoke some English and we drank Bruderschaft (and then some) before the five of us went out on the town. Good times.
Rising later than usual, I continued on to Hel (watch out for some sandy roads east of Leba), a 35 km sand dune sticking out into the sea. During WWII, the Germans managed to hold on to the tip right until the end of the war and military installations remain to this day. The most common sight during the summer however, are kite-surfers taking advantage of the shielded bay. On the very tip, you’ll find a small village with a dodgy campground (no shower) and a lighthouse that affords a nice view.
Unless you wanna go all the way back along the peninsula, you should grab one of the ferries running to Gdansk. There’s a fast hydrofoil, but it was a windy morning so I had to contend with a lumbering 3.5h trip on an older ferry, including stop-overs in Gdynia and Sopot. The ferry will take you right into the center of Gdansk, past the giant cranes of what used to be the Lenin Shipyards. This was where Solidarity, with Lech Wałęsa in front, laboured tirelessly for freedom and democracy before finally overthrowing the communists in 1989.
Gdansk is a tourist destination on it’s own, and you could easily spend a weekend there. I deposited my luggage at the station and explored the city center which has been restored to it’s former hanseatic splendor. The Church of St. Mary is the worlds largest brick church, and is indeed huge. When standing in the entrance, the people near the alter look positively tiny. Remember to climb the tower to get a view of the town.
Back at the station, I got a ticket for Świnoujście for the night train with a change in Poznań. The timetables for Polish Rail haven’t been digitised (as of 2004) it appears. I was unsure of how to get to my destination, so I presented it along with the required time of arrival to a serious-looking guy in a glass box who then, from memory and after much furrowing of brows, produced an itinerary for me. Polish trains are old as dirt and not made with bikes in mind so getting on and off involves much pushing and clambering. After my middle-of-the-night change in Poznań, I was bruised and sweaty and ready for a shower, but I still had to endure the cruise back to Copenhagen on M/F Pomerania which looks even more depressing in daylight. Back in Copenhagen, seven days after I left, I rolled gently home, unpacked and took a looong shower before dozing off.
So there you have it: Want a cheap, unplanned and cheerful holiday, just pack your bike and hop on the ferry to Poland :-).
Some time last year, I got my hands on a draft of the Ecmascript 4 (Javascript to you and me) specification, thinking that it would be interesting to implement a reference-implementation for the evolving language (or rather, a non-boring way to get credit towards my degree). I remember being puzzled by the fact that, aside from a few Microsoftees and Mozilla-people, almost all the committee participants are from Adobe. The reason, it turns out, is that Actionscript — the scripting-language used in Flash — is based on Ecmascript, and Adobe apparently wants to drive innovation for the language.
Why do I care? Earlier this month, I wrote a non-trivial Flash application (details of the project to follow in another post), and I rather liked it. The typical place to start is the Flash authoring tool, which — compared to regular IDEs like Visual Studio (VS) — seems turned on it’s head: Whereas VS is a code editor with some incidental design-modes, Flash is a graphical design-tool with an incidental code-editing feature. Trying to figure whether an object dragged on to the stage should be declared, merely initialised or just used from your code gets old pretty quickly, Javascript has enough scoping ambiguities on it’s own.
Instead, go get Flex Builder 3. It’s still in beta, but freely available and built on Eclipse with all the IDE-niceness that comes with that platform. Refactoring is a bit sketchy, and probably difficult to get right in a language like Actionscript, but otherwise it’s very well rounded. The best part is that Actionscript 3 comes with most of the good stuff from Ecmascript 4 (could someone sync the versions please?), features like packages, proper classes (as opposed to the prototype-voodoo) and optional, strong typing.
What I’m getting is that if you wanna see what’s new in Javascript, go check out Actionscript! Through Ecmascript 4 it’s becomming an interesting language, there’s a nice IDE, rich API’s, interesting code (check this out) and a community that’s more focused on building compelling apps than on perfecting their Tower of Hanoi implementations.
Resources:
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:
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.