Trying to test strongly typed datatables with sqlite...

Following on from my previous post about wrapping an auto-generated strongly typed set of data-tables behind a service interface, in this post I want to show how I attempted to unit this service and failed!

The service was created for the sole purpose of abstracting away the complexity I believe an ORM would have removed (see previous post), if this functionality had been implemented by an ORM then this interface would not have existed and no testing would be required.

I was trying to perform unit testing - I wanted to test the code in isolation without any external dependencies. At this stage I didn't want to write any integration tests, since the development was deon off site and the client owned and managed the system infrastructure we believe they can take the opportunity to write integration tests after our delivery - this would allow them to prove the code base we have developed.

The system uses a Sql Server 2008 back-end - standard 2 tier stuff. So the first stage was to convert this to a Sqlite database - this would provide the database as a single file which could be included in the unit test project, thus removing the external dependency. This was easy enough using this tool hosted up on CodeProject - all done in 5 minutes.

Next stage was to modify the connection string used by the strongly typed data-tables - the client has specific requirements how this connection string was built - they inject particular connection string parameters from the app.config file. The client specific requirements wasn't the issue, the issue was the settings class containing the connection string, it was marked with sealed & internal modifiers. It was also a singleton used by all data adapters defined in the DAL.

As you can see from above no way to easily set the singleton. This wasn't difficult to get round, a bit of reflection and we have the settings class with connection string we want:

var type = Type.GetType("XXXX.RMS.DAL.My.MySettings, DAL, Version=2.0.2.0, Culture=neutral, PublicKeyToken=null");
var parameterlessCtor = (from c in type.GetConstructors(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)
                         where c.GetParameters().Length == 0 select c).FirstOrDefault();

var instance = parameterlessCtor.Invoke(null);
var setting = (ApplicationSettingsBase) instance;
setting["XXXXConnectionString"] = "Data Source=XXXX_sqlite.db;";

I used this code in the set-up for a unit test to access the sqlite database via the strongly typed database. This is when things went wrong or more specifically my assumption was wrong - all I needed to do was change the database connection string from a Sql Server to a Sqlite version.

[Test]
public void get_package_assemblies()
{
    // Given we can manipulate the settings class and set the connection string...
    SetConnectionStringToSqlite();

    // Given we can create a assemblies table adapter...
    var adapter = new AssembliesTableAdapter();

    // When we request an assembly by id...
    var dataTable = adapter.GetByAssemblyId(13231);

    // Then we expect some results to be returned...
    Assert.IsNotNull(dataTable);
    Assert.IsNotEmpty(dataTable.Rows);
}

private void SetConnectionStringToSqlite()
{
    var type = Type.GetType("FBS.RMS.DAL.My.MySettings, DAL, Version=2.0.2.0, Culture=neutral, PublicKeyToken=null");
    var parameterlessCtor = (from c in type.GetConstructors(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)
                                where c.GetParameters().Length == 0
                                select c).FirstOrDefault();

    var instance = parameterlessCtor.Invoke(null);
    var settings = (ApplicationSettingsBase)instance;
    settings["FBSConnectionString"] = "Data Source=FBS_sqlite.db;";

    // Set the static field - singleton used by all data table adapters...
    var tmp = settings.GetType().GetField("defaultInstance", BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.SetField);
    tmp.SetValue(settings, settings);
}

The test blew at the first assert - it failed because the assumption I made about how the data adapter had been generated was wrong. I was expecting the data adapter class to have member variables for the adapter, connection & command as interfaces not as implementations of the interface, in other words instead of IConnection for the variable type it was SqlConnection.

This completely scuppered my attempt to test the service in isolation.

It's at this point I gave up trying...