The Joy, Blood, Sweat And Tears Of InfoPath 2007

I recently completed a project based on InfoPath 2007 (Office client version) and Microsoft Office SharePoint Services 2007 (MOSS 2007). Looking back I can say that InfoPath has its uses, but before you build a solution around it you have to be very sure about its limitations. InfoPath has a number of limitations, especially with regards to submitting data, that aren’t that apparent at first sight. If you don’t watch out, you can quickly get caught up in what feels like a never ending maize of dead ends.

InfoPath is often pitched as a solution that doesn’t require writing custom code. This project was no different, and its time lines were made accordingly. In the end we had to write a fair amount of custom code, which was fun, but took more time.

The Many Limitations Of InfoPath DataBase Data Source

InfoPath generally works well viewing standard enterprise data sources such as database tables or SharePoint lists. The limitations become apparent when you attempt to submit to a database using an InfoPath SQL connection, or perform advanced queries. There are a number of limitations when you work with an InfoPath SQL database data source/connection:

1. Only submit to a single table. This excludes database data sources such as views, and queries with joins. You cannot submit to views or SQL DataSources with joins.
2. To submit to a database you can only use the main data connection. In other words you can’t have a database-view as the main data source, and setup another simple single table select to submit to.
3. Range queries are not possible. You can only use a field once in a query’s WHERE clause with an equality operator.
4. SQL data source dependent on table schema. If a SQL data source’s underlying table is modified, even just adding a column (in other words InfoPath’s SELECT statement doesn’t actually change), the data source will break.

Data Connection Wizard

With SharePoint lists you cannot query the data source with queryfields like relational data sources.

The above limitations, especially regarding relational data sources, mean one thing: Web services are mandatory for working with your relational data. Using web services allows you to overcome all the limitations of the standard InfoPath SQL data source, and work with a consistent schema.

Another thing to watch out for is that InfoPath’s performance deteriorates quickly when you have more than 50 rows in your result set. Sometimes this figure is much lower. In the project I worked on the data was coming lightning fast from the data base through the web service. But when the data hits the form, and InfoPath starts parsing the XML document, it completely froze for quite a while. I have decided not to torture myself trying to page my form data, so I haven’t looked into this yet (and I believe InfoPath is not meant to be used in this manner). The quickest and most effective solution I could come up with is to allow users to load data into their form incrementally. How this works is that you’ll do a normal retrieve of your data from the data source, but instead of clearing the form, you’ll just add the new result set to the rest of the form’s data. The big drawback of this is that you need to write custom code to modify the XML document directly using XmlWriter: Not a too pleasant exercise.

public void Load_Clicked(object sender, ClickedEventArgs e)
{
  // Call the web service of the secondary DataSource, which will populate it         
  DataSources["ClientWS2"].QueryConnection.Execute();
  var clients = DataSources["ClientWS2"].CreateNavigator().Select("/dfs:myFields/dfs:dataFields/tns:GetClientsResponse/tns:GetClientsResult/tns:Client", NamespaceManager);

  // The 1st time rows are added GetClientsResult might not exist, only GetClientsResponse
  var main = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:dataFields/tns:GetClientsResponse/tns:GetClientsResult", NamespaceManager);
  if (main == null) main = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:dataFields/tns:GetClientsResponse", NamespaceManager);

  using (XmlWriter writer = main.AppendChild())
  {
    // Make sure we are adding Client elements to /dfs:myFields/dfs:dataFields/tns:GetClientsResponse/tns:GetClientsResult and not, /dfs:myFields/dfs:dataFields/tns:GetClientsResponse
    if (main.LocalName == "GetClientsResponse")
    {
      // So if it doesn't exist, create it first
      writer.WriteStartElement("GetClientsResult", "http://sh.inobido.com/CRM/Service");
    }

    while (clients.MoveNext())
    {
      writer.WriteStartElement("Client", "http://sh.inobido.com/CRM/Service");

      // Select all the client element's child elements
      var fields = proposals.Current.Select("*", NamespaceManager);
      while (fields.MoveNext())
      {
        // Write each element and value to the Main DataSource
        writer.WriteStartElement(fields.Current.Name, "http://sh.inobido.com/CRM/Service");
        writer.WriteString(fields.Current.Value);
        writer.WriteEndElement();
      }

      writer.WriteEndElement();
    }

    if (main.LocalName == "GetClientsResponse)
    {
      writer.WriteEndElement();
    }
    writer.Close();
  }
}

The above event fires when a user clicks the Load button. The trick to load data incrementally is that you need a second DataSource exactly the same as the Main DataSource (they should point to the same data store). Whenever you call DataSource.QueryConnection.Execute() InfoPath will wipe any previous data from that DataSource, and reload it with the new data. That’s why you need a separate second DataSource that you call Execute on, and then copy that data to the Main DataSource. The end result is the Main DataSource doesn’t lose its data, but data gets added to it on each query.

Just another side note on InfoPath: Pivot tables are not possible, because you have to know exactly which columns your binding to at design time, and cannot create columns dynamically at runtime. This shouldn’t be a show stopper to most projects, but I’m just mentioning it. All the InfoPath forms we had to do came from Excel spreadsheets, and the one spreadsheet was a monster pivot table.

Hacking The DataConnection

It’s possible to query a data connection directly from InfoPath, change the SQL command dynamically, or extract the connection string. The biggest drawback of this hack (apart from being a hack, i.e. not recommended) is that it requires FullTrust and Sql Code Access Security (CAS) permissions. That means you have to certify your InfoPath form, or create an installer so users have to install it locally onto their machines. This doesn’t really work well when the form is made available to users through a SharePoint document library.

Anyways, here is a very unrefined sample to achieve this:

private const string CONNECTION_STRING = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;";

private string GetConnectionString(AdoQueryConnection queryConnection)
{
  var password = GetConnectionStringParameter(queryConnection, "Password");
  var user = GetConnectionStringParameter(queryConnection, "User ID");
  var server = GetConnectionStringParameter(queryConnection, "Data Source");
  var db = GetConnectionStringParameter(queryConnection, "Initial Catalog");
  return string.Format(CONNECTION_STRING, server, db, user, password);
}

// Hmmm, if your using my wonderful hack, then you might want to
// consider rewriting this method to use regular expressions instead 
private string GetConnectionStringParameter(AdoQueryConnection queryConnection, string name)
{
  var paramIndex = queryConnection.Connection.IndexOf(name + "=");
  var parameter = queryConnection.Connection.Substring(paramIndex, queryConnection.Connection.IndexOf(";", paramIndex) - paramIndex);
  return parameter.Substring(parameter.IndexOf("=") + 1);
}

private IDataReader SelectWorksheetItems(SqlConnection connection, int pocketID)
{
  using (var dbCommand = new SqlCommand("WorksheetItemGetByPocket", connection))
  {
    connection.Open();
    dbCommand.Parameters.Add("@pocketID", SqlDbType.Int).Value = pocketID;
    dbCommand.CommandType = CommandType.StoredProcedure;
    return dbCommand.ExecuteReader();
  }
}

worksheetItemCurrentDS = DataSources["WorksheetItemCurrent"];
worksheetItemCurrentCmd = ((AdoQueryConnection)worksheetItemCurrentDS.QueryConnection).Command + " where \"PocketID\"={0}";

using (var connection = new SqlConnection(GetConnectionString((AdoQueryConnection)worksheetItemCurrentDS.QueryConnection)))
{
  using (var reader = SelectWorksheetItems(connection, 24))
  {
    // Do some stuff with the DataReader here...
  }
}

InfoPath’s different data sources each use a specific data connection that inherits from the abstract class Microsoft.Office.InfoPath.DataConnection. The main point of the above example is that you can cast your InfoPath’s DataConnections to its specific implementation. For SQL database data sources InfoPath uses AdoQueryConnection. With AdoQueryConnection you have the ability to extract or manipulate the data source’s command and connection string.

Using SQL Server To Store InfoPath Documents

You cannot call store procedures directly from InfoPath, but if you develop on SQL Server 2005 or later, you can use SQL Web Services to call stored procedures as a web service. The big catch here is that SQL authentication and SQL Web Services don’t really go well together. Therefore when using SQL authentication for your InfoPath DataConnections you will either have to support integrated authentication for calls coming through the SQL Web Service (and SQL authetication for direct calls from the InfoPath form), or you will have to throw open access to your stored procedure to all users. If your using SQL authentication, there’s usually a good reason your doing so, so additionally supporting integrated authentication might not be an option. Giving access to anyone is an even worse idea.

CREATE ENDPOINT ClientInsertEndpoint
STATE = STARTED
AS HTTP
(
  SITE = 'ServerName',
  PATH = '/WebServiceName',
  AUTHENTICATION = (NTLM),
  PORTS = (CLEAR)
)
FOR SOAP
(
  WEBMETHOD 'ClientInsert'
  (
    NAME = 'DataBase.dbo.ClientInsert',
    SCHEMA = DEFAULT,
    FORMAT = ROWSETS_ONLY
  ),
  WSDL = DEFAULT,
  BATCHES = DISABLED,
  DATABASE = 'DataBase'
)
GO

If is possible to support SQL authentication for SQL Server Web Services, but this requires a SSL server certificate. Microsoft also plans to remove this feature from SQL Server in future releases.

“This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.”

Definitely read Microsoft’s best practices for SQL Server Native XML Web Services.

Finally, you can use SQL Server 2005’s XML data type to save an InfoPath Form or query it in a relational format. Here’s a sample stored procedure that takes in the root node of the InfoPath’s XML document, and inserts the items into a table:

ALTER PROCEDURE [dbo].[ClientUpdate]
(
  @clientsXml XML
)
AS
  INSERT INTO  Client (FirstName,
               LastName,
               CellNo,
               TelNo,
               WorkNo)
SELECT         Clients.Client.query('data(@FirstName)').value('.', 'VARCHAR(25)') as FirstName,
               Clients.Client.query('data(@LastName)').value('.', 'VARCHAR(25)') as LastName,
               Clients.Client.query('data(@CellNo)').value('.', 'VARCHAR(25)') as CellNo,
               Clients.Client.query('data(@TelNo)').value('.', 'VARCHAR(25)') as TelNo,
               Clients.Client.query('data(@WorkNo)').value('.', 'VARCHAR(25)') as WorkNo
FROM           @clientsXml.nodes('declare namespace d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"; //d:Client') Clients(Client)

The new SQL XML syntax is a bit tricky, but once you get it right it works wonderfully well.

XPath Expressions Are Your Friend

Conditional Format

Conditional formatting and XPath expressions are very handy to display unique values in a RepeatingTable. For instance say you’ve got a Client object, with multiple Addresses – street, postal, and work. Say you only wanted to show a client’s name once, and list each of his addresses without repeating his name. When you’re using a SQL DataSource, you will do a left join with the address table from the client table. This means you’ll repeat the same client name for each address.

To solve the aforementioned you need to make sure you order by client name, and then hide the textbox with a XPath expression:

tns:ClientName = preceding-sibling::tns:Client/tns:ClientName

What this expression is saying is that if the current Client’s ClientName is the same as the previous Client’s, then do something. That something is the action you’ll check on the Conditional Formatting window, that will be “hide” in our case.

This approach can be extended to multiple fields. All you have to do is make sure your order sequence is correct. So just by ordering your resultset corrrectly and using the right XPath expression, you’ll achieve quite a bit without having to write code.

InfoPath And Visual SourceSafe Does Not Play Well Together

If you’re creating InfoPath forms with Visual Studio Tools for Office and using Visual SourceSafe for source control, you will quickly get a whole range of different and meaningless error messages. Here are the general things to do to resolve them:

1. Make sure all files in your Visual Studio InfoPath project’s “InfoPath Form Template” directory are checked out, before doing any work on manifest.xsf (the InfoPath form).
2. If you’re having trouble checking files out of SourceSafe, from within Visual Studio:a. Close Visual Studio.b. Open the Visual SourceSafe application, and check out the files for the project from there. Once you’ve done this you can close Visual SourceSafe.c. Make the directory “InfoPath Form Template” and all its content writeable, by unchecking the Read-only option from the folder’s Options.d. Reopen Visual Studio, and continue working as usual.

Most Annoying InfoPath Deployment

Another aspect of InfoPath you need to consider is how you’re deploying your forms. To deploy a form you need to manually, that’s right manually, update each DataConnection to point to your production environment. Ouch! If you have say 5 forms, with 5 DataConnections each, then your looking at 25 DataConnections to manually update. Nasty! And if you mess one connection up, you’ve got a problem.

SQL DataConnections are the worst to update. When you want to change to a new DataBase, InfoPath completely clears your original select statement and forgets which table you were using, and you have to reselect the columns/table. Should your DataSource’s schema change (i.e. your select statement is not exactly the same as previously), InfoPath will do you the favor of removing your controls’ databindings. Most of the time you’ll probably use all the table’s columns, but you still have to go and re-select that table.

Web Service DataConnections are the easiest to reconfigure (but still pretty painful). You can just take the URL of the new web service, and copy it into the web service address box, and quickly click Next through the DataConnection wizard. InfoPath doesn’t forget which web method your DataConnection uses, like it does with SQL table DataConnections.

The aforementioned makes it extremely time consuming and error prone to deploy InfoPath forms between development, QA and production environments.

Conclusion

1. Use web services to retrieve and save form data, and plan accordingly. I cannot state this enough. Yes, maybe for the simplest of simple forms you can get away with using InfoPath’s SQL DataConnection (and I mean really simple), but for everything else a web service is an absolute must.
2. Try to avoid large editable, repeating data grids (or referred to as a RepeatingTable in InfoPath lingo). Be extra cautious when you’re planning on editing large result sets, with lots of drop down lists and lookup data. Forms that work best are ones that displays and edits a single entity, and apposed to forms that edit multiple instances of an entity on the same form.
3. Don’t think you’re going to deploy those 5 forms in a few minutes. Give yourself enough time for the deployment, and to test each form afterwards to check that you didn’t mess a DataConnection up.
4. Do a quick prototype of your forms to check whether InfoPath can really handle it. In my case the person who recommended InfoPath for the solution should have checked that it can accommodate pivot tables. This is general good software dev practice, but I think because of all InfoPath’s restrictions, I think one needs to be particularly careful.We’ve gotten so used to having control over every element of the user interface with ASP .NET and Windows Forms that we expect the same of other technologies we use. Remember InfoPath’s controls and their behaviour dictate how your information is displayed. You do not have access to the underlying API that these controls are based on. In other words know what InfoPath’s UI controls can do, because you won’t be able to write your own.

A Minor Battle That Declared The Begining Of British Rule Of South Africa

HMS America, HMS Stately, HMS Rattlesnake and HMS Echo at the Battle of Muizenberg

Soon after the start of the American War of Independence from Britain, tension mounted between Holland and Britain, because of the Dutch’s trade with France and the American revolutionaries. In 1779 the Dutch Republic joined the First League of Armed Neutrality to seek protection from Britain. The British government saw the complications of getting embroiled in a war with Russia, Sweden and Denmark (members of the leage), and declared war on Holland in 1780. This was the beginning of the fourth, and last, Anglo-Dutch War. The Dutch’s naval capacity has been deteriorating since 1712, and their fleet was in dismal shape. With only twenty naval warships at the start of the war, the Dutch fleet was no match for the British Royal Navy. The Fourth Anglo-Dutch War resulted in ruin for the Dutch Republic that was ruled by William V, Prince of Orange. Prince William was pro British and also grandson of king George I of Great Britain. Therefore when Holland lost the war, he was blamed by the pro French elements in Dutch politics for mismanagement. The Patriots formed out of discontent for the Republic’s poor performance and in an attempt to turn around its decline.

Muizenberg Dutch Fort Lower Part

By 1785 the Patriots threatened to overthrow the Stadholder led government, even though most people still gave their full support to the House of Orange. The same year the Patriot revolution commenced. However in 1787 they were forced out by a Prussian led force that supported William’s wife, Princess Wilhelmina daughter of Prince Augustus William of Prussia. Since its formation the Patriots associated themselves with the French revolutionaries, and fled to France.

In 1795  the Patriots returned with the full support of the French revolutionary armies. With force they took control of the Netherlands, and established the new Batavian Republic. Prince William V fled to his ally, Britain, abandoning his country to the French backed revolutionaries.

Battle Of Muizenberg Cannon Mark

Britain now lost a strategic ally and with it access to the Cape of Good Hope. The Cape was a key midway point for ships to replenish crew and equipment on their way to important colonies in the east and far east. The British East India Company realised they had to take control of the Cape to retain access to India. Lord Baring the Chairman of the British East India Company, persuaded the British Government to intervene with a military force.

Muizenberg Dutch Fort Lower Part

A fleet of seven Royal Navy ships was sent to the Cape under command of Vice-Admiral Elphinstone. Five were third-rate ships of the line: HMS Monarch (His/Her Majesty’s Ship, 74 guns), HMS Victorious (74 guns), HMS Arrogant (74 guns); two were 4th rate ships of the line: HMS America (64 guns) and HMS Stately (64 guns); two were 16-gun sloops: HMS Echo and HMS Rattlesnake. The fleet left Britain on the 1st of March, and reached Simon’s Bay in early June 1795. Elphinstone attempted to negotiate with the Dutch governor Sluysken, to handover the Cape Colony to Britain, but was refused. On June 14 the Dutch was unable to scorche Simon’s Towns, when 350 Royal Marines and 450 men of the 78th Highlanders infantry regiment took control of the town.

The Dutch retreated to their fort just outside Muizenberg, where their military force could defend against the British forces with artillery fire. The British assembled another 1,000 sailors from the fleet into two battalions of five hundred men each, commanded by Commander Temple Hardy, captain of Echo, and Commander John William Spranger, captain of Rattlesnake, bringing the total number of soldiers to  1,800 men. The British used carronades, a powerful, short-range anti-ship and anti-crew weapon, from the ships’ launches, to serve as close artillery. The Dutch waited with 800 soldiers at their Muizenberg fort.

Muizenberg Dutch Fort Middle Battery

The Dutch had three days to prepare their defences. Today some of these fortifications are still visible, like the middle battery. The middle battery is a small lookout post halfway up the hillside, on small promontory which gives it an excellent 180 degrees view from Simon’s Town to Muizenberg Beach. At least one gun was mounted at this location, since it was recorded as a battery. The gun was most likely a 4-pounder, of which several were known to have been deployed on the site. A 4-pounder weighed about 900 kg, and was used for signalling or to fire grape and musket-shot at infantry. The little battery is also home to the ruins of a stone hut built using cement, which indicates it was built by the British. The hut is currently demolished.

Battle Of Muizenberg From Boyes Drive

On Sunday 7 August 1795 at 12:00pm the skirmish commenced, when the British marched towards Muizenberg in a column formation, for speed and manoeuvrability, through Fish Hoek and Kalk Bay, supported by the the big 4th rate America and Stately, and the smaller Echo and Rattlesnake from the sea with their carronade cannons

In a hurry the Dutch constructed defences, and assembled the guns from various sources. Two 4-pounder field guns with their large wheels were the easiest to be transported with horses to the site. A couple of days later a 13-inch mortar and a howitzer, a kind of short, heavy cannon, and also a powder wagon, a kind of box on wheels with a steep roof, arrived at the fort. These were used for dropping explosive shells, instead of solid shot, on the British infantry. Next the Dutch sent two big 24-pounder cannons and their gun carriages.

Dutch 4-Pounder Field Cannon

These were meant to be operated from mounted positions as ship or battery guns. Apparently they arrived without their wooden platforms. Weighing 2500 kg, they quickly sank into the sand and impossible to aim. Two more 4-pounders were sent to the fight. In the Dutch’s retreat to Zandvlei they would’ve taken the 4-pounders. However the large 24-pounders, mortar and howitzer were left behind, and captured by the English. The Dutch most likely disabled them before deserting them.

Because the Dutch fort was built so close to the sea, it was within range of the ships guns. The warships anchored close to the rocks and in half an hour fired 800 cannon balls at the defenders. The column of British soldiers and troops then overran the position. Some of the Dutch and especially the Pandouren or native troops fought well, but Dutch morale was low and they abandoned the fight. The Pandouren were Coloured or Khoisan men who were either slaves of the Dutch or free men subject to the authority of the VOC. By 2:00pm the Dutch retreated around the corner to Zandvlei.

The fighting continued for weeks, but the British eventually pushed the Dutch back to Wynberg Hill. On the 14th of September 1795 a fresh assault with reinforcements was made on the Dutch. On 16 September 1795 the Dutch finally surrendered the Cape to Britain.

Muizenberg Dutch Fort Parapet

This first, temporary, British occupation led to the second in 1806, which was permanent, and is the reason we speak English in South Africa rather than French. The consequences of this little skirmish was huge.

Today, next to the Main Road in Muizenberg is a crude rock fort started by the Dutch in 1795 and expanded by the British from 1796 onwards. Higher up the mountainside, below Boyes Drive is a crudely built defensive protective wall in front of a trench (parapet) that was probably built by the Dutch.

Old English Fort And The Most Southern Battle Of The Anglo Boer War

On easter weekend we traveled on the Kogman’s pass (also spelt as Cogman) to Montagu on our way to Stilbaai. From 1875 to 1877 Thomas Charles John Bain (1830 to 1893), son of the pioneer road builder Andrew Geddes Bain, constructed the Kogman’s Kloof Pass. Thomas Bain, was a renowned road builder and designed and built many of the spectacular mountain passes in South Africa. The pass goes through a little tunnel in the mountain. In 1899, during the Anglo Boer War, the English built a small fort on top of it. It’s a very small fort, resembling a blockhouse without a roof, with rugged walls and loopholes (embrasure) on the north and south walls.

Cogmanskloof passes through folded layers of the Table Mountain Group in the Langeberge. Rivers deposited the sediment of which these rocks (mostly sandstone) are composed along the coastline some 400 to 450 milltion years ago. Burial below other deposits compacted the beds and transformed them to stone. About 280 million years ago, compressional forces in the earth’s crust began folding the beds and continued to do so for the next 60 million years. Upon this followed tensional forces, which formed large crustal fractures roughly parallel to the present coastline. One of the largest of these, the Worcester Fault, passes near the southern entrance to the Kloof and displaced beds to the south of it downward by several kilometers. Cogmanskloof is but one of several deep gorges carved into the tough sandstone of the Cape mountains by the erosive action of swiftly flowing rivers over millions of years.

Most Southern Battle Of The Anglo Boer War

About 10km after turning off from the N2 to Stilbaai you’ll find a memorial at the location of the most southern battle of the Anglo Boer War. The battle took place on 12 September 1901. Commandant Jan Theron’s Boer commando clashed with the District Mounted Troops and Riversdale Town Guards of Lieutenant Smalberger and a unit of British troops under the command of Major Kavanagh. The Boers were on the hills North-West, West, South-West and South-East of the valley. Two Boers, field-cornet J.A. Van Biljon from Kroonstad and R.C.H. Tiell of Johannesburg, were injured. They recovered and got banned. The British troops incur numerous incidents, but exact numbers are unknown.

No turnaround confirmed yet

A cornerstone of Dow Theory is confirmation. Dow Jones used industrial and railroad indexes as indicators of the state of the economy and business. Confirmation is the process of comparing two averages, to assert the direction of the primary trend. What this means is that if the one index reaches a new high, yet the other index doesn’t, it means the primary trend is running out of steam. If one index reaches a new high, then the other index should also, around the same time. They don’t need to reach new highs or lows at exactly the same time – a few weeks is acceptable. The primary and secondary indexes should perform together. If not, a divergence suggests a reversal is likely.

The secondary index should complement the primary index. The secondary index should also consist of securities that are completely different from those in the primary index. For instance in Dow’s time he used the industrial and rail indexes. Today the modern version of the rails index is the transportation index. The American transport index is the Dow Jones Transportation Average. On the Johannesburg Stock Exchange (JSE), the equivalent index is the Industrial Transportation Index (INDT – Black Line Chart).

JSE INDT TOPI

If we compare the JSE’s Top 40 Index (TOPI – Blue Line Chart) with INDT, we can clearly see that the Top 40 reached a new high on 20 June 2007, yet INDT failed to reflect this peak. On 6 March 2008 another divergence occurred, when the Top 40 reached another new high, and INDT continued its downward trend. On 22 May 2008 the Top 40 reached its all time high, and the INDT yet again told us otherwise.

What can we say about the past 5 months? Well it looks as if the Top 40 is trending sideways, and INDT is continuing its downward trend. This is yet another divergence. Not as strong as the ones produced during 2007, and 2008, but still suggesting that the Top 40 might fall even further than its lowest point on 20 November 2008. It looks like the Top 40’s current support is around the 16000 to 16800 range. On the 3rd of March 2009 INDT reached an all time low, but TOPI has not yet reached an all time new low. I believe there is a strong likelihood that the TOPI  will continue trending downwards during the next 6 weeks, and reach a new low. It will be interesting to see if this is indeed the case.

What’s so powerful about Dow’s confirmation is that it provides a strong early warning signal that the big trend is changing. In the recent past we were warned almost a year before the JSE finally came crashing down.

Executing Shell Commands From A Visual Studio Deployment Project

Visual Studio’s Deployment Projects are quite limited. To get them to do more complicated setup tasks require writing custom deployment actions in C#. The problem with writing code is that it places an additional maintenance and testing burden on the developer. Often all that’s needed is to perform a few small operating system tasks. In these scenarios it just feels unjustified to revert to writing a bunch of C# setup logic.

I recently ran into such a scenario. As part of the deployment application, SQL Server 2005 Express had to be automatically unpacked. When the SQL Server Express setup utility is runned with the /x argument, it unpacks its contents, instead of installing SQL Server. The trick is how to execute this shell command with the minimum effort? The answer is to write a little Visual Basic script file (.vbs) that uses the WScript.Shell object. The emphasis is on little. I wouldn’t recommend sticking huge complicated installation logic in a script file. When you have lots of setup logic, then it’s time to write those C# custom actions, together with unit tests, and mocks. But for all those 1 liner shell commands, a script file is just the thing you need.

Visual Basic Scripts (.vbs) Are Your Friend

The VB script:

Set WshShell = CreateObject("WScript.Shell")
WshShell.Exec(Property("CustomActionData") & "\SQLEXPR32.EXE /x:""" & Property("CustomActionData") & "\SQLEXPR2005""")
Set WshShell = Nothing

The Windows Script Host (WScript.Shell) object has two candidate methods to execute commands – Run and Exec. According to Microsoft, if you need access to command-line output, you should use the Exec method instead. In this case I did not specifically need access to shell output, but for whatever reason I was unable to get Run to work.

Each custom action has access to the CustomActionData argument. This handy little argument is set on the custom action’s properties. It allows you to pass through the directory where the application was installed, using the global variable [TARGETDIR].

WScript.Shell.Exec allows you to execute any standard command line argument in a manner similar to using the Run… dialog box from the Start menu.

What’s nice about this solution is that it allows you to package SQL Server Express with your solution in one file, and then unpack it during the installation process. The other option is to unpack SQL Server beforehand and add the individual files to your deployment solution. If you’re using Visual SourceSafe, you will run into problems with some of SQL Server’s files that have very long names, and are not DOS compatible.

Content Managment With SharePoint And Kentico

Since I’ve joined Intervate Cape Town, I’ve been assigned to three projects. Two are Microsoft Office SharePoint Server 2007 (MOSS) solutions, and a small web site based on the Kentico Content Management System (CMS). It’s been a while since I worked with MOSS, so I had to relearn a lot of things. The one MOSS solution was an extranet application, that used Windows (Active Directory) and Forms Based Authentication (FBA). Initially I was a bit confused at how SharePoint handles FBA, and to make matters worse there are some overlapping terms between FBA and the rest of SharePoint’s security model.

Here’s briefly what I learned:

• Permissions assigned to SharePoint groups and users are referred to as roles in the SharePoint Object Model (SOM).
• FBA, which is just standard ASP .NET Forms authentication, also has roles. These roles correspond with SharePoint Security’s concept of a group, and has nothing to do with SharePoint permissions.
• SharePoint handles users and roles coming from ASP .NET Membership the same way as those coming from Windows AD. ASP .NET’s Membership becomes just another credential store to SharePoint. You add an ASP .NET user/role in the same manner as you’d add a Windows AD user/group to a SharePoint user/group. When you search for users/groups you’ll notice that those coming from ASP .NET Membership are prefixed with AspNetSqlRoleProvider:, or aspnetsqlmembershipprovider: for ASP .NET roles, and users respectively.
• It’s best to deploy SharePoint customizations using a SharePoint solution file (.wsp). But where’s a good guide to explain how this works? Well, luckily we have good blogs like the The Bonobo Journal to explain it in layman’s terms.
• Make sure you understand the do’s and don’ts of writing optimal code for SharePoint’s Object Model. Andreas Grabner from InfoQ did an excellent post on this subject.

The other project I got assigned to was done in Kentico. Yes, I had the same confused expression when I heard about it. Turns out Kentico is a really neat .NET CMS that doesn’t get in your way. It’s jam packed with features, and has every standard web technology, like blogs and wikis, out of the box (and they are all really well implemented). What I really appreciate about Kentico is that it’s got a really well designed and simple architecture, based on standard ASP .NET components. When you extend Kentico with custom code you literally open the Kentico Visual Studio solution and start creating ASP web pages (modules), and user controls (web parts). To debug something you just run the solution, and debug it as you’d do with any normal .NET web app. This enables developers to use a familiar development and deployment process.

Kentico arranges it’s web sites as a hierarchy of nodes. Each node has a document type (with its own table in the database), a form/data view, and a page/UI view. To retrieve any document is a straightforward matter:

CMS.TreeEngine.TreeProvider tree = new CMS.TreeEngine.TreeProvider();

treeNode = tree.SelectSingleDocument(CMSContext.CurrentDocument.DocumentID);         

var nodeSet = tree.SelectNodes(CMSContext.CurrentSiteName, treeNode.NodeAliasPath + "/%", treeNode.DocumentCulture, true, "cms.file");

The above code finds the node of the current document shown to the user, and then retrieves all its child nodes. This kind of simplicity is found throughout Kentico, from the application itself, right down to the database. It also provides multiple extension points. You can use the application itself to create new document types, and plug your own code in without using Visual Studio. Your other options are to write your own web parts, and modules in Visual Studio. And lastly if you really wanted to, you can directly query the database. Corresponding directly with the database should not be your first choice, but Kentico provides a database API that assists you in this regard.

The deployment process is also as easy as it gets. To install Kentico you can either use the setup file, or just copy your solution over and connect it to the database. From there on you just export your sites, and import them again on the target server. And yes, Kentico even exports/imports any custom classes that you might have added to it’s Visual Studio solution.

And if you think Kentico is only for small web sites, think again. There’s support for load balancing and server farms. Definitely give Kentico’s free version a try next time your client doesn’t demand SharePoint.

Beginning Ruby On Rails: Route A Custom Controller Action

I recently rewrote my pet project in Ruby on Rails: Again! A very long time ago, in a galaxy far far away (aka Johannesburg, since I’m now staying in Cape Town), the project was done in Python, and later I converted it to Java and Google Web Toolkit – ’cause I thought GWT kicked serious ass. It wasn’t going too bad with the Java-GWT affair, but somehow I never felt completely in love with GWT’s philosophy. I like designing HTML pages, and prefer not to have to code my UI in Java. Coding a UI as a bunch of classes, and not laying it out in design medium like HTML, is really not my cup of tea. After a recent project based on ASP .NET MVC and jQuery that turned out as one of my favorite projects ever, I realized that, personally, this is the way to go.

So with a lot of personal reluctance, I thought I’d give one of the most famous MVC web frameworks a try and see how it goes: Ruby On Rails. And man, am I impressed: I really enjoy scaffolding, DB migration, and how you can incrementally generate models, and controllers. The way which model objects can automatically accommodate any query via the method_missing mechanism is also wicked. And the fact that I don’t need to know anything but Ruby to achieve it all, is a real bonus. I have done work in Rails in a couple of minutes, that will take me hours to do in Java and GWT. And I’m only an amateur Ruby developer at this stage. Anyways, just thought I’d give a little background as to how I ended up writing about Ruby.

Routing a call to a custom controller action wasn’t too clear from the official Rails documentation, and I had to do a little more Googling than I thougt is acceptable.

In my case I wanted to add a custom controller action called “pick” that should display web_feeds/pick.html.erb:

class WebFeedsController < ApplicationController

  def pick
  end

end

This will produce the error:

“ActiveRecord::RecordNotFound in WebFeedsController#show
Couldn’t find Post with ID=pick“

From this error message we infer that Rails thinks we are requesting the “show” view with WebFeed ID “pick”, which is not what we really want. Routes are defined in config/routes.rb. To view all the available routes for your application run rake routes in the shell. Routes defined before others get a higher priority.  So you need to define more specific routes before the general/default routes. When you look at routes.rb you’ll notice lines beginning with map.resources :controller_name. Map.resources generates routes for seven default actions (index, show, create, new, edit, update, and destroy). So what we need to do is define a more specific route to our other custom actions, before these default routes:

map.connect "web_feeds/:action", :controller => 'web_feeds', :action => /[a-z_]+/i

This line will map to any custom action on WebFeedsController. The :action argument is a regular expression that accepts any word and underscores. This stops Rails from trying to map an ID for one of the default routes to an action. “i” Makes the regex case insensitive.

Bioinformatics Comes To .NET

My interest in molecular biology grows with each issue of Scientific American and each Technology Review post I read.  For a C# and Java developer, such as myself, Bioinformatics is a natural progression from programming into cell biology. I’m also a big open source proponent. So what do you get when you throw all these together? An open source project that brings the popular BioJava bioinformatics library to .NET, where I’m the latest team addition. BioSharp is the first open source project that I am actively working on.

BioSharp was started by Doug Swisher out of curiosity about how BioJava was implemented. Since .NET lacked an equivalent framework, Doug decided to create a port of BioJava in C# to aid his learning.

Currently I’m converting the classes from the org.biojava.ontology package to the BioSharp.Core.Ontology gene ontology namespace. As a non-native English speaker, the term ontology was new to me, and immediately caught my attention. So I spontaneously started working  on BioSharp’s gene ontology.

It turns out implementing the gene ontology library requires more knowledge about parsing files and building hierarchies of related terms (which could be any term not just biological terms) than knowledge about genes and cells. This suits me very well at this early stage, since my knowledge of cellular biology is extremely limited. I’m definitely working on BioSharp out of enthusiasm for molecular biology, rather than being an expert at anything biology related.

Open Source J# Reborn As Ja.NET

Commercially I develop contracted software for clients based on the .NET platform, and C#. During my ‘free time’ I work on an application based on Java technologies (Java, Spring, Hibernate, and GWT). Sometimes I wish I could use one language for both my .NET and Java development. In the past this lead me to consider using J# for some of my .NET development, but somehow I never got around writing anything in J#. I finally lost all interest when Microsoft dropped J# from the .NET framework. So it was with great interest that I watched the rebirth of an open source J#, called Ja.NET.

Ja.NET is Java 1.5 Standard Edition (J2SE) that compiles MSIL for .NET. Ja.NET is derived from the Apache Harmony JVM. What this means is that all code written for Ja.NET can be compiled to .NET MSIL or Harmony Java bytecode. So far the developers have successfully built and ran JUnit, Xerces, Ant, and other OSS on .NET without any code changes to those distributions. Pretty neat!

An Impersonation Aspect With Spring.NET AOP

One of the most useful features of Spring.NET is its Aspect Oriented Programming (AOP). Object Orientation (OO) decomposes an application into a hierarchy of entities linked through references. AOP on the other hand, decomposes an application into ’service layers’ (referred to as aspects or cross cutting concerns in AOP lingo). This allows us to transfer infrastructure code out of our core business logic and entities. AOP has some really confusing jargon, like advice, weaving, crosscutting concern, joinpoint, pointcut, etc. To get a grip of these basic building blocks of AOP I refer you to Spring’s excellent documentation that will get you started in no time.

This post is for those of you that covered the basic concepts and terms of AOP, and Spring.NET AOP in particular, but are still struggling a bit to put it all together. I’ll give an overview of how all the different parts of AOP come together, and then follow it up with a working example.

Transparently Wrapping Infrastructure Around Objects

Even after going over the fundamentals of AOP can one still be confused. So let’s start of with two very simplified diagrams that contrast OO against AOP. These diagrams are not ment to be technically accurate, just demonstrate the very high level concept of AOP.

Figure 1

The frist diagram of figure 1 shows how all of OO’s operating logic is contained within an object’s methods. In OO the object (or class) is the main construct that distills data (properties) and tasks (methods) into a type of entity. Each class has several methods, and in turn each method consists of business and infrastructure logic. Before we move on, I’d just like to mention that it’s not a case of AOP vs OO, and which one is the better than the other. AOP complements OO, and improves the quality and adaptability of OO.

OO works wonderfully when we only consider data, methods, and statements directly related to the bigger concept they collectively represent. But when we take things like logging, caching, security, and transactions into concideration we get this uneasy feeling that we are adding code that do not have any direct relation to the logical entity. Traditionally we would combine this infrastructure code with our business logic. This results in dirty business logic code, that becomes more complex, confusing, and less maintainable as we increase its coupling with infrastructure code. Not only this, but let’s say you quickly wanted to cache the results of several methods, without modifying each object’s method? You would have to go and manually duplicate the same code in each method were you want to perform the caching. That’s a lot of hard coding labor, and even harder maintenance should you want to change the way you do things.

Along comes AOP that allow you to move the infrastructure code out of our object’s methods into aspects that has specific roles with tasks. So you’ll have an aspect that provides security around methods, and another logging method execution. Our object’s business logic does not have to know anything about the infrastructure code in which it’s embedded. The infrastructure code is dynamically injected before or after a method executes. That is what the second diagram of figure 2 demonstrates: We have our object, and dynamically we specify when, and how infrastructure aspects intercept incoming and outgoing method calls. The result is that we are left with pure business logic code in the object’s methods, which are themselves transparently wrapped in the necessary infrastructure code.

Interfaces And Proxies

Spring.NET’s AOP framework uses proxies and interfaces to transparently wrap objects in aspects. An AOP proxy is an object that intercepts calls to, and results from, our actual business entity objects. The proxy has the exact same public members as our business entity object. When we request an object that we want to wrap in aspects, we actually request its proxy. In our configuration we will tell the proxy which object provides the actual implementation:

<objects xmlns="http://www.springframework.net">

    <object id="crmService" type="Spring.Aop.Framework.ProxyFactoryObject, Spring.Aop">
<property name="proxyInterfaces" value="Shinobido.GhostBlade.Customers.ICRMService" />
<property name="target">

<object type="Shinobido.GhostBlade.Customers.CRMService, Shinobido.GhostBlade">
<property name="Url" value="http://localhost:2491/CRMService.asmx" />
<property name="UseDefaultCredentials" value="true" />

</object>
         </property>
<property name="interceptorNames">
	<list>
                 <value>impersonationAspect</value>

</list>
        </property>

</object>

     <!-- Interception AOP Begin -->

<object id="impersonationAspect" type="Spring.Aop.Support.DefaultPointcutAdvisor, Spring.Aop">
<property name="Pointcut">

<object id="regexPointcut" type="Spring.Aop.Support.SdkRegularExpressionMethodPointcut, Spring.Aop">
<property name="patterns">
	<list>

<value>.*</value>

</list>
                  </property>
               </object>
       </property>
<property name="Advice" ref="impersonationAdvice" />

</object>

     <object id="impersonationAdvice" type="Shinobido.GhostBlade.Security.ImpersonationAdvice, Shinobido.GhostBlade" />

     <object id="Shinobido.GhostBlade.Security.IIdentityProvider"
                    type="Shinobido.GhostBlade.Security.HttpContextIdentityProvider, Shinobido.GhostBlade" />

</objects>

public interface ICRMService
{
    CustomerInformation GetCustomerById( int id ) ;
}

public class CRMService: System.Web.Services.Protocols.SoapHttpClientProtocol, ICRMService
{
    public CustomerInformation GetCustomerById( int id )
    {
            object[] results = this.Invoke( "GetCustomerById", new object[] { id } );
            return ( CustomerInformation )results[0];
    }
}

Figure 2

From the above example we can infer a number of things:

1. ProxyFactoryObject is going to intercept calls to our target object, CRMService. In Spring.NET there are several proxying strategies to choose from like inheritance or composition (interface) based, autoproxies, and so forth. Here we’re using a GUID-based composition ProxyFactoryObject. When you instantiate this proxy through Spring.NET’s object factory, you’ll notice that the object’s type is actually a runtime generated object of a type like “CompositionAopProxy_4f054a8f2cd349479fe1673d4427aca3″ that implements ICRMService.
2. ProxyInterfaces expose the members that will be visible to client code and intercepted by the proxy through interface ICRMService. In other words aspects will only be applied to members specified by this interface. Any members specified by this interface will automatically receive advice based on their pointcuts.
3. Target specifies the business entity object that we’re going to wrap.
4. InterceptorNames specify that the aspect impersonationAspect must be applied to method requests and outputs.
5. impersonationAspect’s Pointcut uses a regualr expression that selects all members of the given class to receive impersonationAdvice.

In point 1 I mentioned composition vs inheritance based AOP proxies. We are using a composition based proxy, because the proxy never inherits from our target object, CRMService. A composition based proxy has a private field called m_targetSourceWrapper of type Spring.Aop.Framework.StaticTargetSourceWrapper that stores the actual business entity class we’re redirecting to. An inheritance based proxy inherits from the target object.

Writing A Custom Impersonation MethodInterceptor

Spring.NET provides a number of out-of-the-box aspects for caching, logging, transactions, and exceptions. What we’re going to do is write custom ‘around advice’, called a MethodInterceptor. Remember, Advice are objects that perform the actual work. So logging advice will actually write to the log file. Pointcuts tell the proxy when to pass control to the specified advice. You get four types of advice:

• Before a target method executes.
• After a target method executes.
• Before and after (around) a method executes.
• When a target method throws an unhandled exception.

The AOP advice we’re going to write, will impersonate the current thread with a WindowsIdentity obtained from an identity provider. This is used when an application runs under a Windows service account, but selected methods need to be called with the current user’s identity, instead of the service account’s. The IIdentityProvider is dynamically injected, and retrieves the WindowsIdentity from the applicable context. For instance when you’re impersonating the current thread in a web application, you need to implement a HTTP IIdentityProvider, that gets the current user’s WindowsIdentity from the HttpContext.

In our specific scenario we are applying impersonationAdvice to all methods (.*) of the ICRMService, implemented by CRMService. In a nutshell, we are impersonating all our calls to CRMService.

Control is passed to an IMethodInterceptor before a target method is entered, and after it returns:

 public class ImpersonationAdvice : IMethodInterceptor
 {   
 #region IMethodInterceptor Members

 public object Invoke( IMethodInvocation invocation )
 {
     using ( IdentityImpersonator.Begin( ObjectManager.New<IIdentityProvider>().Provide() ) )
     {         
         return invocation.Proceed();
     }
 }

 #endregion
 }

ImpersonationAdvice implements IMethodInterceptor. You can almost say that Invoke will replace the target method it’s wrapping. To execute the actual target method, you need to call Proceed() on the supplied IMethodInvocation argument.

Here’s the code for our implementation of IIdentityProvider, IdentityImpersonator, and ObjectManager:

internal class HttpContextIdentityProvider : IIdentityProvider
{
    #region IIdentityProvider Members

    /// <summary>
    /// Provides the identity of the current user.
    /// </summary>
    /// <returns></returns>
    public WindowsIdentity Provide()
    {
        return (WindowsIdentity) HttpContext.Current.User.Identity;
     }

     #endregion
}

public sealed class IdentityImpersonator : IDisposable
{
    #region Globals

    private WindowsImpersonationContext ctx;
    private bool disposed;

    #endregion

    #region Construction       

    /// <summary>
    /// Initializes a new instance of the <see cref="IdentityImpersonator"/> class.
    /// </summary>
    private IdentityImpersonator()
    {
        // Static Construction in Begin().
    }

    #endregion

    #region Public Members

    /// <summary>
    /// Begins this instance.
    /// </summary>
    /// <returns></returns>
    public static IdentityImpersonator Begin(WindowsIdentity identity)
    {
        var impersonator = new IdentityImpersonator {ctx = identity.Impersonate()};
        return impersonator;
    }

    /// <summary>
    /// Undoes this instance.
    /// </summary>
    public void Undo()
    {
        if (ctx != null)
        {
            ctx.Undo();
        }
    }

    #endregion

    #region IDisposable Members

    /// <summary>
    /// Releases unmanaged and - optionally - managed resources
    /// </summary>
    public void Dispose()
    {
        if (!disposed)
        {
            Undo();
            GC.SuppressFinalize(this);
            disposed = true;
        }
    }

    #endregion
}

/// <summary>
/// Instantiates objects based on their name, and provides
/// a standard set of configured utility objects.
/// </summary>
public static class ObjectManager
{
    private static IApplicationContext context;

    /// <summary>
    /// Instantiates a default object with an id/name
    /// that is the same as the class's full name.
    /// </summary>
    public static T New<T>()
    {
        return New<T>(typeof(T).FullName);
    }

    /// <summary>
    /// Instantiates the object with the specified name.
    /// </summary>
    /// <typeparam name="T">The type of object to return.</typeparam>
    ///<param name="name">The name of the object.</param>
    /// <returns>A newly created object with the type specified by the name, or a singleton object if so configured.
    /// </returns>
    public static T New<T>(string name)
    {
        return (T)Context.GetObject(name);
    }

    /// <summary>
    /// Instantiates the object with the specified name, and constructor arguments.
    /// </summary>
    public static T New<T>(string name, object[] arguments)
    {
        return (T)Context.GetObject( name, arguments);
    }

    /// <summary>
    /// Gets the context.
    /// </summary>
    /// <value>The context.</value>
    internal static IApplicationContext Context
    {
        get
        {
            if (context == null)
            {
                lock ( ContextRegistry.SyncRoot )
                {
                    context = ContextRegistry.GetContext();
                }
             }

             return context;
         }
    }
}

ObjectManager serves as the object factory that wraps Spring.NET’s ApplicationContext with some additional functionality. Specifically in this scenario, ObjectManager instantiates our proxied objects.

The important tasks are performed by IdentityImpersonator. HttpContextIdentityProvider supplies IdentityImpersonator.Begin(…) with the current user’s WindowsIdentity from HttpContext.Current.User, which in turn calls WindowsIdentity.Impersonate(). To stop impersonating the current thread, you need to call WindowsIdentity.Undo(). The Dispose method stops the impersonation, so that you need to put code that you want to impersonate, in a using {…} clause. GC.SuppressFinalize(…) tells the garbage collector to ignore IdentityImpersonator’s finalize method, since we already cleaned up the object when it was Disposed.

Usage and Conclusion

So, to impersonate a call to CRMService becomes a completely transparent process. You don’t need to duplicate IdentityImpersonator.Begin() every time you want to impersonate the current thread. The methods that need impersonation is dynamically selected from one place, impersonationAspect’s Pointcut, and the impersonation work is also performed from one place, in ImpersonationAdvice:

var crmService = ObjectManager.New<ICRMService>( "crmService" );
crmService.GetCustomerById( 1 );<

This should clearly demonstrate the power of AOP: Injecting all that functionality into GetCustomerById(…), without adding a single line of code to it, and we can change it all without making any code changes!