My preference is to use DB4objects or to SqlLite in memory and normally either having a shared static connection or writing my tests in such a way that i don’t need persistence across boundaries means this is sufficient. However there are circumstances were you do need to persist data across boundaries and where a shared connection is not desirable i recently had such a situation and felt it might be worth while sharing the code.
So what did i want to do ?
I wanted to set up some pre-test data into a data container and i then wanted to kick off a multi threaded program in a different process to transform that data and i wanted to do it from a NBehave style test using the extended NBehave Dll i also wanted to use separate steps files.
Setting up the Data Container Configuration
So the first thing i needed to do was to set up some config so hares a whistle stop tour of that code
The test specs
using NBehave.Narrator.Framework;
using NBehave.Spec.NUnit;
using NeilMartin.DataGateway.Configuration;
using Context = NUnit.Framework.TestFixtureAttribute;
using Concerning = NUnit.Framework.CategoryAttribute;
using Specification = NUnit.Framework.TestAttribute;
namespace NeilMartin.DataGateway.Tests.Configure_application_tests
{
[Theme, Context, Concerning("Application, Configuration")]
public class Can_configure_application_spec
{
[Specification]
public void Should_be_able_to_read_the_output_file_path_from_the_application_configuration_file()
{
const string expectedFilePath = "testFilePath";
var dataGatewayConfigurationSection= DataGatewayConfigurationSection.GetConfig();
dataGatewayConfigurationSection.DataGatewaySettings[0].FilePath.ShouldEqual(expectedFilePath);
}
[Specification]
public void Should_be_able_to_read_the_connection_string_from_the_application_configuration_file()
{
const string expectedConnectionString = "testConnectionString";
var dataGatewayConfigurationSection = DataGatewayConfigurationSection.GetConfig();
dataGatewayConfigurationSection.DataGatewaySettings[0].connectionString.ShouldEqual(expectedConnectionString);
}
}
}
The config section,collection and element
public class DataGatewayConfigurationSection : ConfigurationSection
{
private const string ConfigurationSectionName = "DataGatewayConfiguration";
public static DataGatewayConfigurationSection GetConfig()
{
return (DataGatewayConfigurationSection)ConfigurationManager.GetSection(ConfigurationSectionName);
}
[ConfigurationProperty("DataGatewaySettings")]
public DataGatewaySettingsCollection DataGatewaySettings
{
get
{
return (DataGatewaySettingsCollection)this["DataGatewaySettings"];
}
}
}
public class DataGatewaySettingsCollection : ConfigurationElementCollection
{
protected override ConfigurationElement CreateNewElement()
{
return new DataGatewaySettingsElement();
}
protected override object GetElementKey(ConfigurationElement element)
{
return ((DataGatewaySettingsElement)element).Key;
}
public DataGatewaySettingsElement this[int index]
{
get
{
return BaseGet(index) as DataGatewaySettingsElement;
}
set
{
if (BaseGet(index) != null)
{
BaseRemoveAt(index);
}
BaseAdd(index, value);
}
}
}
public class DataGatewaySettingsElement:ConfigurationElement
{
[ConfigurationProperty("key", IsRequired = true)]
public string Key
{
get
{
return this["key"] as string;
}
}
[ConfigurationProperty("FilePath", IsRequired = true)]
public string FilePath
{
get
{
return this["FilePath"] as string;
}
}
[ConfigurationProperty("ConnectionString", IsRequired = true)]
public string connectionString
{
get
{
return this["ConnectionString"] as string;
}
}
}
An example resultant configuration file
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="DataGatewayConfiguration" type="NeilMartin.DataGateway.Configuration.DataGatewayConfigurationSection, NeilMartin.DataGateway" />
</configSections>
<DataGatewayConfiguration>
<DataGatewaySettings>
<add key="AcceptanceTesting" FilePath="c:\testoutput.file" ConnectionString="Data Source=c:\testDatabaseSqllite.file;Version=3;Pooling=true;Max Pool Size=1;Synchronous=Full" />
</DataGatewaySettings>
</DataGatewayConfiguration>
</configuration>
Setting up the Data Container SpecBase
So this was the code i ended up with after i had used (DRY) to push it down into a spec base form my original Steps file as i built a further Steps file which required the same functionality.
using System;
using System.Data.SQLite;
using System.IO;
using NBehave.Spec.NUnit;
using NeilMartin.DataGateway.Configuration;
namespace NeilMartin.DataGateway.Features.Process_text_records_feature
{
public abstract class DatabaseSpecBase:SpecBase
{
private static DataGatewaySettingsElement settings;
private const string outputFilePath = @"c:\testoutput.file";
private const string databaseFilePath=@"c:\testDatabaseSqllite.file";
private const string databaseScheamaStatement = @"CREATE TABLE messages(id INTEGER PRIMARY KEY ASC,input_reference int NOT NULL,text varchar(50) NOT NULL)";
protected SQLiteConnection connection { get; private set;}
public override void MainSetup()
{
settings = DataGatewayConfigurationSection.GetConfig().DataGatewaySettings[0];
connection = new SQLiteConnection
{
ConnectionString =settings.connectionString
};
connection.Open();
SetupDatabase();
base.MainSetup();
}
private void SetupDatabase()
{
using (var command = connection.CreateCommand())
{
const string createTableCommandText = databaseScheamaStatement;
command.CommandText =createTableCommandText;
command.ExecuteNonQuery();
}
}
public override void MainTeardown()
{
try
{
connection.Close();
connection.Dispose();
GC.Collect();
}
finally
{
File.Delete(databaseFilePath);
File.Delete(outputFilePath);
}
base.MainTeardown();
}
}
}
Their are 2 operations taking place here the first is to set up my final output file for the sake of what were discussing here we can ignor this the other is to set up and teardown the sql database which is the stuff we are interested in here.
Step 1
Get the DLL ‘s for SqlLite and the .net connector they can be downloaded from these sites :
http://www.sqlite.org/download.html
http://sourceforge.net/project/showfiles.php?group_id=132486&package_id=145568
After you have these we then set up a connection like so :
settings = DataGatewayConfigurationSection.GetConfig().DataGatewaySettings[0]; connection = new SQLiteConnection {
ConnectionString =settings.connectionString
};
Next we execute the database setup by running a command to create the table im interested in
private const string databaseScheamaStatement = @”CREATE TABLE messages(id INTEGER PRIMARY KEY ASC,input_reference int NOT NULL,text varchar(50) NOT NULL)”;
using (var command = connection.CreateCommand())
{
const string createTableCommandText = databaseScheamaStatement;
command.CommandText =createTableCommandText;
command.ExecuteNonQuery();
}
Then we run our features and there associated steps and then we teardown
connection.Close();
connection.Dispose();
GC.Collect(); // Required because of file locks
File.Delete(databaseFilePath);
The Repository
Inside the application code to allow use to configure a repository for the test we use the repository pattern
The interface :
using NeilMartin.DataGateway.Types;
namespace NeilMartin.DataGateway.Repositories
{
public interface IMessagesRepository
{
IMessagesCollection GetAll();
IMessageRepositoryBatchOptions GetBatchOf(int batchSize);
int GetCount();
}
public interface IMessageRepositoryBatchOptions
{
IMessagesCollection From(int index);
}
The SqlLite Repository :
using System;
using System.Data.SQLite;
using NeilMartin.DataGateway.Types;
namespace NeilMartin.DataGateway.Repositories
{
public class SqlLiteMessagesRepository : IMessagesRepository, IMessageRepositoryBatchOptions
{
private readonly string connectionString;
private readonly SQLiteConnection connection;
private int batchSize = 1;
public SqlLiteMessagesRepository(string ConnectionString)
{
connectionString = ConnectionString;
this.connection = new SQLiteConnection
{
ConnectionString = ConnectionString
};
}
public IMessagesCollection GetAll()
{
var collection = new MessagesCollection();
try
{
connection.Open();
using (var command=connection.CreateCommand())
{
command.CommandText ="select * from messages";
SQLiteDataReader reader = command.ExecuteReader();
while(reader.Read())
{
collection.Add(new Message(reader.GetInt32(1).ToString() ,reader.GetString(2)));
}
}
}
finally
{
connection.Close();
}
return collection;
}
public IMessageRepositoryBatchOptions GetBatchOf(int requiredBatchSize)
{
this.batchSize = requiredBatchSize;
return this;
}
public int GetCount()
{
var count = 0;
try
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format("select count(*) from messages");
count=Convert.ToInt32(command.ExecuteScalar());
}
}
finally
{
connection.Close();
}
return count;
}
IMessagesCollection IMessageRepositoryBatchOptions.From(int index)
{
var collection = new MessagesCollection();
try
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format("select * from messages where id >{1} LIMIT {0} ", batchSize, index);
var reader = command.ExecuteReader();
while (reader.Read())
{
collection.Add(new Message(reader.GetInt32(1).ToString(), reader.GetString(2)));
}
}
}
finally
{
connection.Close();
}
return collection;
}
}
public class Message : IMessage
{
public string InputReference { get; private set; }
public string Body { get; private set; }
public Message(string inputReference, string body)
{
InputReference = inputReference;
Body = body;
}
}
}
I hope this has been of help.
