As part of the twelve days of code, I’m building a Pomodoro style task tracking application and blogging about it. This post is the sixth in this series. Today I’ll be adding some persistence logic to the Pomodoro application.
I should point I’ve never been a huge fan of object-relational mapping tools, and while I’ve done a few tours with SQL Server I haven’t played much with SQLite. I’ve heard good things about the upcoming version of the Entity Framework in .NET 4.0, so this post gives me a chance to play with both.
Getting Ready
As SQLite isn’t one of the default providers supported by Visual Studio 2010 (Beta 2), I downloaded and installed SQLite. The installation adds the SQLite libraries to the Global Assembly Cache, and adds Designer Support for connecting to a SQLite database through the Server Explorer. The installation and GAC’d assemblies may prove to be an issue later when we want to deploy the application, but we’ll worry about that later.
Creating a Session Repository
So far, the project structure has a “Core” and “Shell” project where the “Core” project contains the central interfaces for the application. Since the ITaskSessionController already has the responsibility of handling starting and stopping of sessions, it is the ideal candidate to interact with a ISessionRepository for recording these activities against a persistent store.
To handle auditing of sessions, I created a ISessionRepository interface which lives in the Core library:
interface ISessionRepository
{
void RecordCompletion(ITaskSession session);
void RecordCancellation(ITaskSession session);
}
Although we don't have an implementation for this interface, we do know how that an object with this signature will be used by the TaskSessionController. In anticipation of these changes, we add tests to the TaskSessionController that verify it communicates with its dependency:
public TaskSessionControllerSpecs : SpecFor<TaskSessionController>
{
// ... initial context setup with Mock ISessionRepository
// omitted for clarity
public class when_a_session_completes : TaskSessionControllerSpecs
{
// omitted for clarity
[TestMethod]
public void ensure_activity_is_recorded_in_repository()
{
repositoryMock.Verify(
r => r.RecordCompletion(
It.Is<ITaskSession>( (s) => s == session )
);
}
}
}
To ensure the tests pass, we extend the TaskSessionController to take a ISessionRepository in its constructor and add the appropriate implementation. Naturally, because the constructor of the TaskSessionController has changed, we adjust the fixture setup so that the code will compile. Below is a snippet of modified TaskSessionController:
public class TaskSessionController : ITaskSessionController
{
public TaskSessionController(ISessionRepository repository)
{
SessionRepository = repository;
}
protected ISessionRepository SessionRepository
{
get; set;
}
public void Finish(ITaskSession session)
{
session.Stop()
SessionRepository.RecordCompletion(session);
}
// ...omitted for clarity
}
Adding ADO.NET Entity Framework to the Project
While we could add the implementation of the ISessionRepository into the Core library, I’m going to add a new library Pomodoro.Data where we’ll add the Entity Framework model. This strategy allows us to extend the Core model and provides us with some freedom to create alternate persistence strategies simply by swapping out the Pomodoro.Data assembly.
Once the project is created, we add the Entity Framework to the project using the Add New “ADO.NET Entity Data Model” and follow the wizard:
Note, that the wizard adds the appropriate references to the project automatically.
Since we don’t have a working database, we’ll choose to create an Empty Model. Later on, we’ll generate the database from our defined model.
Creating a Data Model
One of the new features of the Entity Framework 4.0 is that it allows you to bind to an existing data model. Although the TaskSession could be considered as a candidate for an existing model, it doesn’t fit the bill cleanly – Sessions represent countdown timers and they don’t track the final outcome. Instead, we’ll use the default behavior of the framework and manually generate a model class, TaskSessionRecord:
For our auditing purposes, we only need to record an ID, Start and End Times and whether the session was completed or cancelled.
Creating the Database from the Model
After the model is complete, we generate the database from the model:
- Right click the designer and choose “Model Browser”
- In the Data Store, choose “Generate Database from Model”
- Create a new database connection. In our case, we specify SQLite provider
- Finish our the wizard by clicking Next and Finish.
The wizard produces the Database and the matching DDL file to generate the tables. Note that SQLite must be installed in order to have it appear as an option in the wizard.
Unfortunately, I wasn’t able to create the SQLite database using any of the tools with Visual Studio. Instead, I cheated and manually created the TaskSessionRecord table. We’ll hang onto the generated ddl file because we may want to programmatically generate the database at some point. For the time being, I’ll cheat and copy the database to the bin\Debug folder.
Implementing the Repository
The repository implementation is fairly straightforward. We simply instantiate the object context (we specified part of the name when we added the ADO.NET Entity Data Model to the project), add a new TaskSessionRecord to the EntitySet and then save the changes to commit the transaction:
namespace Pomodoro.Data
{
public class TaskSessionRepository : ISessionRepository
{
public void RecordCompletion(ITaskSession session)
{
using (var context = new PomodoroDataContainer())
{
context.TaskSessionRecords.AddObject(new TaskSessionRecord(session, true));
context.SaveChanges();
}
}
public void RecordCancellation(ITaskSession session)
{
using (var context = new PomodoroDataContainer())
{
context.TaskSessionRecords.AddObject(new TaskSessionRecord(session, false));
context.SaveChanges();
}
}
}
}
Note that to simplify the code, I extended the auto generated TaskSessionRecord class to provide a convenience constructor. Since the auto generated class is marked as partial, the convenience constructor is placed in its own file. As some of the existing generated code requires the presence of an empty constructor (which is implicitly defined by the compiler if not present), we must also include a default constructor.
public partial class TaskSessionRecord
{
// needed to satisfy some of the existing generated code
public TaskSessionRecord()
{
}
public TaskSessionRecord(ITaskSession session, bool complete)
{
Id = session.Id;
StartTime = session.StartTime;
EndTime = session.EndTime;
Complete = complete;
}
}
Integrating into the Shell
To integrate the new SessionRepository into the Pomodoro application we need to add the database, Pomodoro.Data assembly and the appropriate configuration settings. For the time being, we’ll add a reference to Pomodoro.Data library to the Shell application – this strategy may change if we introduce a composite application pattern such as Prism or MEF. For brevity sake, I’ll manually copy the database into the bin\Debug folder.
The connection string settings appear in the app.config like so:
<connectionStrings>
<-- formatted for readibiliy -->
<add name="PomodoroDataContainer"
connectionString="metadata=res://*/PomodoroData.csdl|res://*/PomodoroData.ssdl|res://*/PomodoroData.msl;
provider=System.Data.SQLite;
provider connection string="data source=Pomodoro.sqlite""
providerName="System.Data.EntityClient" />
</connectionStrings>
One Last Gotcha
As the solution is compiled against .NET Framework 4.0 and our Sqlite assemblies are compiled against .NET 2.0, we receive a really nasty error when the System.Data.SQLite assembly loads into the AppDomain:
Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information
We solve this problem by adding useLegacyV2RuntimeActivationPolicy="true" to the app.config:
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
</startup>
Next Steps
In the next post, we’ll look at adding Unity as a dependency injection container to the application.
