written by:
In this article I will demonstrate how to test this database code by using "unit tests".
Thanks goes to Offbeat Solutions for the idea (NHibernate + SQlite)!
Scenario
My demo consists of the following scenario and technologies. From the bottom up: data layer is SQL Server. I will use Entity Framework Code First to access the database. The Entity Framework code is called from a repository layer which groups together logical operations. The controllers of the application use the repositories to access the data.
I will reference my example code throughout the article. Be sure to take a look on GitHub!
The example uses Entity Framework and Visual Studio Unit Tests (MSunit) but it should not be difficult to apply to other ORMs and tests suits as well.
Why unit tests should not hit the database?
Unit tests should run independently from one another and from other resources, especially external dependencies. If we allow the unit test to go to the real database (even a development database) we hit some big problems. How can we guarantee the data is intact, is always the same? If a test modifies the database, does it affect another test? How can I run the tests on the build server where there are no databases available?
You could of course install a database on the build server and setup some SQL scripts to delete all data prior to tests, etc. But I would argue you are still setting yourself up to trouble, not to mention that now in order for the tests to run those SQL scripts need to be maintained, something that we developers are pretty bad at
If you do not want to go to the database ...
If your unit tests cannot go to the database, then the repository layer is the one you will have to cut off. In my example code, you will find the IProductRepository interface, which describes the interface for the repository. The ProductRepository implements this interface, and uses the Entity Framework to retrieve the needed information.
If I wanted to test my code without accessing the database, I could write another repository implementation called ProductMemoryRepository that implements the interface, but operates in memory. I would put this implementation in the unit test dll and then inject that into the code (into my ProductController for example).
The problem with this approach is that the code in my actual ProductRepository is not tested. There might be LINQ expressions and filters there that are now outside the scope of my test.
Enter SQL Server Compact Edition
How can we test our repository code without going to the actual database? SQL Server Compact Edition is an in-process database server that you can include in your application. You include a DLL, and voila, you have SQL Server inside your application (well, more or less). There is even an Entity Framework binding available. Entity Framework and SQL CE is available through NuGet.
The only thing is, SQL CE requires a file to store the data in. But it can be any file (even a temp file) that can be destroyed as soon as the last connection is closed to the database.
Can we use this in unit tests? Absolutely.
Because Entity Framework works with SQL CE, we do not need to modify our entity framework code at all.
So before our test runs, let's create a new database:
[TestInitialize]
public void Initialize ()
{
currentFile = Path.GetTempFileName();
string connectionString = string.Format(
"Data Source={0};Persist Security Info=False", currentFile);
var conn = DbProviderFactories.GetFactory("System.Data.SqlServerCe.4.0").CreateConnection();
conn.ConnectionString = connectionString;
conn.Open();
entities = new Entities(conn);
entities.Database.CreateIfNotExists();
Seed();
}
What happens here?
First we generate a temporal filename, and construct a connection string using that. We open a new connection using this connection string, and pass the connection to our entities class. We also make sure our database and tables exist. This last step guarantees that our test will always have a fresh copy of the database, and we do not need to maintain our tests to provide a fresh and clean database. This call will be called before every test is run, so every single test will have its own database to work with.
Our code first entities class which I cacled Entities needs a new constructor for this to work:
public class Entities : DbContext
{
public Entities ()
{
}
public Entities(DbConnection connection)
: base(connection, true)
{
}
The default constructor is used normally in the code, and it finds the connection string from the config file. The second constructor receives the connection through parameters. This is how the connection is given to the entities class.
The test initialization method also calls a seed method at the end. This is how some initial data that is required by the test can be added to the database.
protected virtual void Seed()
{ }
At the end of the test we need to destroy the database.
[TestCleanup]
public void Teardown ()
{
entities.Dispose();
entities = null;
File.Delete(currentFile);
}
All this code can go into a base class that the actual test classes will inherit, if they require database access. In my sample code this is called SqlCompactBasedTest. All test classes should inherit this that require their own database for testing.
Now we can create a simple test:
[TestMethod]
public void Test_AddProduct()
{
Product p = new Product() { Name = "Raspberry icecream", Price = 1.99m };
Entities.Products.Add(p);
Entities.SaveChanges();
var cheapThings = Entities.Products.Where(product => product.Price < 10.0m);
Assert.AreEqual(1, cheapThings.Count());
}
Because we know exactly in the test what is in the database BEFORE the test, we can make assertions on the contents after we perform some operations. Please check the example code for more sample tests.
Here I used the Entities class directly, but of course I can also write tests that test other aspects of the code base, but in the end, somewhere deep down the call chain they use the database. In the sample code you can see such a test in the RepositoryTests test class in the test method ProductController_ShowProducts().
Example code
I implemented the concepts demonstrated here in an actual solution. The application is very small and not a real application in the sense that the main program does not do anything. The unit tests however do work and use the technique described above. The sample uses NuGet to pull in those dependencies.
The application has a very basic data model, with some persons, products and orders. It also has a database migration for Entity Framework, so you might create the data in an actual SQL Server database, but the sample program does not really access this. The unit tests use the technique described above and contain the helper class to set up the database.
You can find the code on GitHub
