Like many of you, I'm being dragged into the land of managed code in order to achieve things that I've had oodles of JScript code samples to do for a long time. The InfoPath Toolkit for VS.NET allows you to write code in either VB or C#; since C# looked more like JScript, and I had both JScript and C# code samples for the easy part of what I wanted to do, I decided to go with C#.
So the task at hand that I needed to do was to pick up the username attribute from the local environment and populate that into a field on the form, and then use that field as a lookup value into a secondary datasource and populate a "friendly" username and office location number and friendly name for the office location as well. Oh by the way I needed to do it all in the OnLoad event of the form. This sounds easy - and in fact the username part was super-easy. Getting the right syntax though to address the secondary datasource and pluck values out was a major pain in the butt. To make matters worse, my form was tied to a database, so the namespaces are all "dfs" and "d" instead of "my". It gets confusing since all datasources that are SQL-based follow the same basic nomenclature, and you have to figure out how to navigate between them. The only thing that might be hard to follow below is the fact that I don't store the friendly office location name in the destination table that the form is tied to, so in my schema I just created a node outside of the dataFields to hold the friendly office location name for purposes of display.
//This variable declaration looked very foreign to me. Chalk that up to being new to C#
DOMDocument50 objPeopleStuff = (DOMDocument50)thisXDocument.GetDOM("LookupTable");
ADOAdapterObject myAdapter = (ADOAdapterObject)thisXDocument.DataAdapters["LookupTable"];
//This method below seemed to be clunky to me at first, but turns out to be helpful later, as you'll want
//to set the query string back to nominal later on.
string origCommand = "";
myAdapter.Command=origCommand + " WHERE username = '"+ nodeEmployee.text +"'";
//Sets friendly name value
Unfortunately, this code does need the form to have full trust and be signed with a Level 2 code signing certificate to run. That's a bummer, but I see no way around it for now.