The very first version of Footy Links is now available for download from the Android Market. You can get the app by opening the following website on your android device and press the ‘Install’ button: Footy Links on the Android Market

What is Footy Links?

Footy links is a game designed to test your football knowledge by guessing clubs connecting premiership football players. Make your guess by clicking on the blue text then selecting the club badge matching the answer.

There is a basic scoring system following these rules:

• +1 point for every correct guess of a ‘top six’ club
• +2 points for a correct guess of any other club
• -1 point each time you skip to another question without answering the previous one

Can I play it?

If you have an Android based mobile device I hope so, it should work on any device with Android OS version 2.2 or above. If you have any problems running the game feel free to contact me here on my blog or through twitter

Is that it?

As you may be able to tell from the title, in the spirit of agile development (or maybe just because I’m lazy), this is an early version with the absolute minimum set of features. I really wanted to just get a version out there in the wild to see if it will work on anyone’s phone other than my own!

But do not fear, more versions are planned with lots of exciting extra features, possibly including:

• Harder difficulty levels - more links to guess between each of the players
• More advanced scoring system
• Multi-player games
• Online high score board
• Inclusion of all English leagues
• Inclusion of European league clubs

If you have any suggestions about Footy Links then please feel free to get in contact with me here on my blog or through twitter

The Techy Bit

If you’re interested in how the app works behind the scenes, the source is freely available for viewing on my github account here: Footy Links on github

The source consists of two applications

• A .NET application written to import the football club and player data into a Sqlite database
• An android application for the game itself

This post was drafted some time ago, so some of the details are lost in the mists of time. But I’ll provide links to the source so anyone interested in doing something similar can start from there.

The android app I’m developing needs to be able to connect to an existing SQLite database, there are not many examples of how to achieve this in the Android tutorials, however I was able to find some guidance in a post from another Android developer on the reigndesign blog

The key is that the database needs to be copied internally to the system directory on the device running the application before it can be accessed. This doesn’t seem particularly efficient but I’m sure Android had their reasons! My modified version of the code to do this is available from my github repository here:

FootyLinksSQLLiteHelper.java

I’ve also encountered a couple of other snags since then while working with the SQLite database.

No password support

Passwords do not appear to be supported at all for the Android applications. I’d automatically added password protection to the database when creating it and it took me a while to figure out why eclipse was refusing to read it!

Manually delete after schema changes

I’ve added some additional fields to the database over time. However initially they were not being copied into the version of the database in my device emulator. I found it was necessary to browse the emulator files themselves, using the DDMS perspective in Eclipse, and delete the old database file directly in the emulator file system.

I have recently been working on a new feature request for the ability to generate excel reports dynamically from a web application. This kind of feature can add a lot of value to a system as it enables businesses to analyse crucial trends in data on demand.

However it can be challenging to generate reports in an Excel spreadsheet format from a web application. Firstly you need to have the program itself installed on the web server, which can be difficult if you are using third party hosting. Also coding the solution itself is difficult as you either need intimate knowledge of the Excel API or, for versions of office after 2007, in depth knowledge of the OpenXML file format. But fear not there is a third option, open source to the rescue! Some clever chaps have developed the EPPlus library which abstracts away all the nasty low level OpenXML calls and provides a nice object oriented API for dealing with directly the spreadsheet.

Modelling the report data

I won’t go into too much detail on the features of the library as you can read all about it on the EPPlus FAQ page and download the samples. However one of the methods that I found really useful was the LoadFromArrays method. This enables a table of data to be output to the worksheet from a list of object arrays. So you can construct a model of the data you want to display then feed it straight to the worksheet through the list. It may sound quite complicated but here is a simple example that can be easily expanded with more columns / rows:

string firstName = "Joe";
string lastName = "Bloggs";
var dateOfBirth = new DateTime(2000, 1, 1);
var testData = new List<object[]>()
                   {
                       new object[] {"First name", firstName},
                       new object[] {"Last name", lastName},
                       new object[] {"Date of birth", dateOfBirth}
                   };

using (var excelPackage = new ExcelPackage())
{
    ExcelWorksheet excelWorksheet = excelPackage.Workbook.Worksheets.Add("Test worksheet");
    //Loads the summary data into the sheet, starting from cell A1. Prints the column names on row 1
    excelWorksheet.Cells["A1"].LoadFromArrays(testData);
}

Don’t get duped

I generally found that the EPPlus library was reliable, however I did hit a snag during the development. The file would get generated OK but when I tried to open it in Excel a nasty message was shown:

Excel found unreadable content in ‘Test export.xlsx’. Do you want to recover the contents of this workbook?

The file did open OK if you chose to recover, however this obviously isn’t ideal and not acceptable for a client release. The tricky part was that no error was being thrown so I had to use a bit of guess work to track down the issue. Initially there wasn’t much to go on, however after running some more tests on sample data I noticed that the export always worked fine if the file only included a single worksheet. However sometimes multiple worksheets would cause the ‘unreadable content’ error. From there I examined each of the properties that were being set on the worksheets and narrowed the problem down to be the worksheet name itself.

It turns out that excel requires each worksheet to have a unique name. This is backed up in excel itself if you try and manually name to worksheets the same you get a reasonably friendly error message saying:

Cannot rename a sheet to the same name as another sheet

After ensuring my worksheets were uniquely named the reports always opened up without a problem. As an aside I just tried to replicate this bug through EPPlus and this time it returned a much more helpful error message, I wish it had told me this when I was originally working on the feature!

Add worksheet Error: attempting to create worksheet with duplicate name

EPPlus for the win

In conclusion I would recommend EPPlus to anybody who needs to generate an excel report from C# code and doesn’t want to interface with the murky depths of the Excel API. It is a great open source library that does just what you need in the way you would expect it to work.

A common requirement for e-commerce applications is the ability to schedule jobs or tasks. I am currently working on a project which uses EPiServer Commerce and I needed to schedule an order export job.

I was pleasantly surprised to find that EPiServer Commerce is supplied bundled with the Quartz.NET enterprise code library to facilitate these job scheduling requirements.

Gentlemen, choose your weapons

I had three main options available for implementing the export job, a separate windows service, an EPiServer CMS scheduled job or a Quartz job. I decided that the Quartz service was better than the option of a separate windows service because the framework was already there and available so would not increase the complexity of deployments. I also felt it was a better option than a EPiServer CMS scheduled job because Quartz runs independently to the website app pool so should not put the web application under greater load while it is running and potentially degrade the performance of the end user website.

A 1000 foot example

I’m not going to go into a massively detailed explanation of how to implement the jobs as there is a already a good example available in the EPiServer Commerce dev guide along with a detailed tutorial on the Quartz .NET site. However a quick overview demonstrates how intuitive it is to implement.

The first thing you need to do is create your job class which will run when Quartz triggers the execution. The class must inherit from the IJob interface which just has a single Execute method to implement. Here is a sample skeleton class:

    public class CommerceOrderExportJob : IStatefulJob
    {
        private readonly ILog _log;

        public CommerceOrderExportJob()
        {
            _log = LogManager.GetLogger(base.GetType());
            //Any other init code
        }

        public void Execute(JobExecutionContext context)
        {
            //Your export methods
        }
    }

You then need to extend the config file to configure Quartz to call your job. I think all the parameters are pretty self explanatory:

<job>
    <job-detail>
      <name>OrderExportJob</name>
      <group>all</group>
      <description>This job exports orders from commerce</description>
      <job-type>Your.Assembly.CommerceOrderExportJob, Your.Assembly</job-type>
      <volatile>false</volatile>
      <durable>true</durable>
      <recover>false</recover>
    </job-detail>
    <trigger>
      <simple>
        <name>OrderExportJobTrigger</name>
        <group>eCommerceFramework</group>
        <description>Fires Order Export Job</description>
        <misfire-instruction>SmartPolicy</misfire-instruction>
        <volatile>false</volatile>
        <job-name>OrderExportJob</job-name>
        <job-group>eCommerceFramework</job-group>
        <repeat-count>RepeatIndefinitely</repeat-count>
        <repeat-interval>300000</repeat-interval>
      </simple>
    </trigger>
  </job>

Hit run on the service and check the event log for any problems. Make sure to include some useful event/error logging in the job otherwise it will just be a mysterious black box! There are a couple of snags you may run into when running the job against EPiServer Commerce:

Mining for more gems

Ultimately only time will tell if Quartz was the best choice but I feel it has the best chance of high reliability and performance in the long term. I hope that, where appropriate, EPiServer can continue to expand the incorporation of proven open source libraries with their product offerings.

Most recent projects I have worked on have used unit tests to define the requirements of our code and ensure they are met. Typically the tests are written by the developer while they are coding the solution. However this means that the tests are based entirely on the developers understanding of the requirements, which may not always be the same as the stakeholders understanding.

To combat this issue we are introducing behaviour driven development, to agree on a set of requirements for each feature with the stakeholders before starting development. The requirements are captured using the Gherkin syntax (Given, When, Then) based on real world examples. These examples can then be wired up to the system directly as automated tests using Specflow.

OK, show me the example already

Here is a fictionalised scenario which demonstrates the value that BDD adds to a project. An abbreviated version of the initial scenarios drafted by the developer are as follows:

Feature: Process 3D secure authentication response
        As a logged in user
        I would like to place a 3D secure authenticated order
        so that I have assurance of the websites security

Scenario: Handle a 3D secure response of AUTHORISED
    Given a 3D secure response of AUTHORISED
        When  i view the order confirmation page
        Then  the order status will be Complete

Scenario: Handle a 3D secure response of NOT AUTHORISED
    Given a 3D secure response of NOT AUTHORISED
        When  i view the order confirmation page
        Then  the order status will be Cancelled

Scenario: Handle a 3D response of NOT PROCESSED
    Given a 3D secure response of NOT PROCESSED
        When  i view the order confirmation page
        Then  the order status will be Cancelled

Evolving the scenario

The assumptions shown in these scenarios are perfectly reasonable, an order will only be completed if the 3D secure security check returns an authorised response. However it should really be a decision for the stakeholder’s business whether the system should behave in this way. There are several valid scenarios where all 3D responses should actually result in a completed order, for example:

Scenario: Handle a 3D secure response of NOT AUTHORISED
Given a 3D secure response of NOT AUTHORISED
And a total order value order value under the following amount:   25
When  i view the order confirmation page
Then  the order status will be Complete

The stakeholders calculated that the risk of charge-backs was so low on orders under 25 that they could disregard the 3D secure response completely and allow ‘not authorised’ transactions to be completed.

Here we can see a clear benefit in the stakeholder sharing their understanding of the system requirements with the implementation team. Rules which are beneficial to their business can be incorporated into the system, resulting in a more effective solution from the outset.

If you are interested in reading more about using BDD for web applications I can recommend Steven Sanderson’s excellent blog post.