Skip Navigation LinksTech Blogs

Simple Database-level Unit Test Process

by Marc Moore | Jan 10, 2012

Unit testing of database-related code has long been problematic for the ESI team.  Using Entity Framework Code First, EF Migrations, and SQL Server Compact Edition we may be able to do a much better job in upcoming projects.

Essentially the demonstrated in this article scheme is this: When a set of tests is executed, a SQL CE database is created and populated with the data needed to drive those tests.  Then the tests are executed and evaluated and the database is discarded. 


This post assumes you have Visual Studio 2010 installed on your machine and have the NuGet extension installed.  If the latter is not true, see the instructions and get that done.

Also, you must have SQL Server Compact Edition 4.0 installed.

Given that, let’s proceed.

Data Layer and NuGet

The first step is to create a data layer project in your solution.  This is just a simple class library like the ones found in other ESI projects.

Let’s use NuGet to bring Microsoft Entity Framework (EF) 4.2 into the solution.  Right-click on your data project’s References node and pull up the NuGet Package Manager:


Find the Entity Framework project and install it.


Next, find and install the Beta version of Entity Framework Migrations:


The last NuGet package we want to install is SQL Server Compact Edition:


Core Shared Modules

We’re going to create a simple Employee table in the EF “Code First” style, so we will have neither a database project with a SQL table object or an EF object model with generated class definitions.

Instead, we’re going to have a plain-old-CLR-object (POCO) class of our own to act as the model for the table.  Since this class will be used to move data around layers of the solution, let’s create another new class library to contain it.  Let’s call this the “core” library.

Then add a class called EmployeeDTO to the core library:


(Yes, putting this class in a separate library is overkill for this example, but in a production-grade application, you’ll want to have a core library for these and other classes. Think of extension methods, etc., as well.)


Data Layer Functionality

With our POCO in place we can create our application’s data context object:


(In the DataContext class, the OnModelCreating method is used to map the context’s Employees property to the Employee table in our database.)

Next, we’ll create an “employee migration” class in our data project’s Migrations folder (which was created by NuGet when we installed the Migrations package):


This class is used by EF Migrations to create the actual database table in the SQL CE database.  In this simple example, pay particular attention to the Id column, which is declared as a SQL Identity.  This will be used later in the testing process.

A Basic Test

Let’s create a 3rd project in our solution for testing purposes, then add a basic unit test class like this one:


The “Can_insert” test is perfectly clear, but where does the DataContext object come from?  To instantiate our one-off SQL CE database instance, we need to create a custom database factory.

Creating a Database Instance

In an EF application, the database context acts as a wrapper around a connection to the database.  Before we can get a connection in our test library, we need to migrate our database schema to SQL CE. 

Our test data context factory does that, then returns a data context to the test:


Dissecting the Test

The unit test we wrote a moment ago is no different than any other.  It arranges, acts, and asserts as usual.  The only difference is that the test class’ initialization method has created a scratch database for us to work with.


When the test(s) are run, the database is created and made available to the test via the context object, then the data operation is invoked.

It’s easy to imagine a more complex test doing additional arrangement to set up a particular testing scenario, then executing the required business rule code and evaluating the results.

It’s also easy to imagine using the database generation to create development and test databases, particularly during the early phases of a project.

This project is in TFS under the $/DeveloperTools/ProofOfConcept/DbUnitTesting folder.

Primary Reference

Other References