By Roy
Osherove
Summary:
In this article I?ll show what problems we
encounter today when we perform unit tests against database related component,
and the various ways used to solve these problems. I?ll also show a new method
to solve these problems, which is far easier and simpler than any of the others.
At the end of this article you will have the tools necessary to do simple and
easy unit tests against database related components without the need to write
messy cleanup code after your tests.
Introduction
Test Driven Development makes life easier. Unit
tests are the lifeblood of a good Test Driven Development approach (and thus,
are also the lifeblood of most of the agile methodologies out there such as XP,
Scrum and others). Therefore it stands to reason that one should strive to
create unit tests with the least amount of work and maintenance, which will in
turn allow the developer to focus on the business problem at hand and the
various design and algorithms needed to create a working
product/component.
For the past couple of years I?ve been
struggling with Unit tests, That is, I?ve used them in major production projects
and have seen the good and the bad that can come from a fully test Driven
approach. I?ll let the ?good? part be taken care of by the numerous other
articles out there that talk about the benefits you get from Unit Tests and TDD,
and will focus on one of the most problematic parts of Unit tests in a real
world project ? testing against the database.
Testing Data Access layer components (DALs for
short) is problematic in a number of ways. The number one reason f is
this:
Testing the various CRUD(Create, Retrieve,
Update, Delete) operations on a single class can result in a massive amount of
?garbage? data residing on the test database (You are using a test
database, aren?t you?). This creates several problems:
1.
Problem: Garbage
Data
Your database will eventually be full of junk
data which you?ll need to get rid of sooner or later
2.
Problem:
Affecting other tests
Worst, you may break one of the golden rules of
unit testing: No test should be dependent on another test to perform
correctly. That is, you should be able to run your tests in any order
you choose or maybe just one of them at a time, and they should all still work.
That means that every test needs to start with a known state on which it will
act. For DALs this usually means a known state of records in the database.
But guess what? When you test CRUD operations
you?re actually changing the state of the database, so the next test that will
be run that might depend on a specific record being there might fail because you
just deleted that record in your current test. Anything can happen.
3.
Problem:
starting tests from a known state
This is the other side of the previous problem:
My test needs to have something in the database in order to perform correctly.
For example ? it needs a specific ?category? entity in the Category tables in
order to test inserting a child ?Product? into the Products table. I need to
make sure I insert these values into the database before I perform the test, but
I also want to make sure that data is no longer there after I finish my
test.
Dealing with the
problems
So how do you deal with all this garbage data
that?s making your database and tests unstable? Obviously, you need to make sure
that before and after each test you leave the database in the same state that
you got it in. That is ? you need to ?undo? your CRUD operations after testing
them. Here are some of the most prevalent methods used to achieve
this:
- Remove it
manually
This is the most obvious and in simple cases
one of the simplest to achieve. To do this, after each test (at the end of it)
you execute the opposite actions than the ones you took in the test. For
example, if you inserted a record in the test ? you delete it at the
end.
Some problems and questions that arise from
this approach:
o
If I inserted the
record into the Database using my objects? Insert() method which I was testing ?
should I remove it from the database using the object?s Delete() method (which
might have not been tested yet or may not even exist yet!) or should I do it
against the database using direct calling of stored procedures or direct ADO.Net
classes?
o
If I choose to remove
the records I inserted directly from the DB ? this involves some serious amounts
of extra code, residing in my test! And that code can have bugs as
well.
o
If I choose to use my
object to delete and Insert as well - what do I test first? (Chicken and the egg
kind of question..)
o
I can also choose to
remove all the garbage data at the end of all the tests run
(TestFixureTearDown method). But then I?d need lots of data and custom
code and ?. It?s just complicated.
o
I can ignore all my
garbage data and restore my database from backup before running all the unit
tests. This might solve the problem partially but may still leave a few unit
tests that mess with other test?s data. A big no-no.
- Use the transaction object in
ADO.Net
In his very interesting and insightful book,
?Test
Driven Development with Microsoft.Net?, James Newkirk addresses this same
problem with a seemingly simply solution: Execute all your actions inside a
transaction and rollback that transaction when you?re done with each test. This
is a great solution and can definitely be implemented, but also has its
problems:
- You need to worry about components
that manage their own inner transaction but still will need to use outside
transactions initiated by the unit tests.
- To workaround this problem James
came up with an implementation of the Command pattern inside the unit tests (the
book has a good example). However ?even if it accomplishes the task nicely,
after using this pattern we are left with more complicated unit tests that we
should have, and all this just to be able to support rollback
functionality.
A simpler
way
The previous two approaches are the most used,
and if you?ve used either of them you?d know that they are still hard to achieve
with massive amount of custom work just to make the database data more reliable.
When I was reading Jame?s book, in the chapter
that discusses implementing transactions, I came across this little sentence
(abbreviated):
?We want to achieve the same functionality as
?RequiresTransaction? for each tested component, just like in
COM+?
This sentence triggered an interesting thought
: why work so hard? Why not let COM+(Or, ?Enterprise Services? in .Net) do the
work of coordinating the transactions for us? Thus ? I came up with another way
of implementing database rollback functionality :
Using Enterprise
Services
What we want is to be able to achieve the
transaction functionality without all the overhead of writing our own custom
Transaction manager class as prescribed in James? book. How do we do that? With
COM+ Enterprise services. Here is the technical gist of the
method:
Our simple efforts start with this simple base
class
using System;
using NUnit.Framework;
using System.EnterpriseServices;
namespace TransactionTesting
{
[TestFixture]
[Transaction(TransactionOption.Required)]
public class DatabaseFixture:ServicedComponent
{
[TearDown]
public void
TransactionTearDown()
{
if(ContextUtil.IsInTransaction)
{
ContextUtil.SetAbort();
}
}
}
}
Here are some important pointers about this
class:
- This class will be the base class
that your DAL unit tests will inherit from. It allows for automatic transaction
enlistment using the COM+ architecture through the EnterpriseServices namespace
in .Net.
for that end :
- It inherits from
ServicedComponent
- It has the following attributes on
it:
- [TestFixture]
- [Transaction(TransactionOption.Required)]
The second attribute specifies that whenever
this class is instantiated or invoked, COM+ will automatically create a
transaction context for it to run under, or enlist it into an existing
transaction context.
- It has a simple TearDown
method which will call ContextUtil.SetAbort(). This causes the following
flow to occur:
1.
Before each test a
transaction context is opened (or enlisted into an existing
transaction)
2.
The test is performed
3.
TearDown is called and
the transaction is rolled back thus
We get the following benefits from this
approach:
1.
it is dead simple and
easy to implement
2.
You need no code
whatsoever to roll back any database changes your components have made
throughout the test. They will be rolled back automatically.
3.
You do not need to
manage transaction by yourself.
A few more settings and we?re
done
We?re still not finished but we?re awfully
close to the finish line.
All serviced components need to have strong
names. This one is no exception.
- Use the SN.exe tool with the ?k
option to create a key file.
- In your test project open
AssemblyInfo.cs and set the following attribute (add it if it is not
there):
[assembly: AssemblyKeyFile(@"..\..\..\test.snk")]
Notice that I?ve put here
a relative file path. This is the file I generated from sn.exe and it is located
in the same directory as my .sln file. That way I can put this same attribute on
all the other projects that need it as well.
- Put
a static Version attribute in your test project:
- Look for this in the
AssemblyInfo.cs file :[assembly: AssemblyVersion("1.0.*")]
- And change it into [assembly: AssemblyVersion("1.0.0")] or whatever version you
like.
This step is important
because by default when the attribute is at its default state Visual Stuido.Net
will automatically increment the assembly version with each build. That?s
a bad practice to have on any project, but when you?re dealing with a
COM+ component (A ServicedComponent is just that) you need to
realize that for every separate version of the test assembly that you build, you
will have created a different COM+ component in the COM+ catalogue of the
testing machine. SO ? after you?ve run the tests 50 times a day your COM+
catalog might turn out to look pretty scary and we don?t want that. Setting the
version to a fixed version saves us this little headache.
- Since
your test assembly now has a strong name, every assembly that it
references must have a strong name as well. This is easily done: put this same
attribute in each of the referenced projects set in your test project.
That?s basically it.
You?re done.
Here is a simple example of a test fixture that
derives from this class to achieve a perfectly transparent data rollback after
each test. This class tests a simple class that does category insert and delete
against the Northwind database:
using System;
using NUnit.Framework;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using NorthwindDAL;
namespace TransactionTesting
{
public class
CategoryTests:DatabaseFixture
{
string CONN
= @"Server=localhost;Database=Northwind;Trusted_Connection=True";
[Test]
public void
Insert()
{
CategoryManager mgr = new CategoryManager();
int newID =
mgr.Insert("Test
category");
VerifyRowExists(CONN, newID,true);
}
[Test]
public void
Delete()
{
CategoryManager mgr = new CategoryManager();
int newID =
mgr.Insert("Test
category");
bool result
= mgr.Delete(newID);
Assert.IsTrue(result);
VerifyRowExists(CONN, newID,false);
}
private void
VerifyRowExists(string connectionString, int existingRowID,bool
shouldExist)
{
string SELECT = "Select * from
Categories where categoryID=" + existingRowID.ToString();
SqlDataReader dr = SqlHelper.ExecuteReader(connectionString,CommandType.Text,SELECT);
Assert.AreEqual(shouldExist,dr.HasRows);
dr.Close();
}
}
}
As you can see there is no clean up code
anywhere. That?s how simple this technique really is.
Just so you can see there are no tricks up my
sleeve ? here is the CategoryManager tested class (in VB.Net ? just for the heck
of it):
Public Class CategoryManager
Private CONN As String = "Server=localhost;Database=Northwind;Trusted_Connection=True"
Public Function
Insert(ByVal
CategoryName As String) As Integer
Dim sql
As String =
"Insert into categories (categoryName)
values('" + CategoryName + "');Select scope_identity()"
Dim result
As Integer =
NorthwindDAL.Data.SqlHelper.ExecuteScalar(CONN, CommandType.Text, sql)
Return result
End Function
Public Function
Delete(ByVal
id As Integer) As Boolean
Dim sql
As String =
"delete from categories where categoryID=" +
id.ToString()
Dim result
As Integer =
_
NorthwindDAL.Data.SqlHelper.ExecuteNonQuery(CONN, CommandType.Text, sql)
Return (result = 1)
End Function
End Class
No tricks, no hidden cards. The database
actually gets updated, only the updates are rolled back.
Conclusion
In this article I?ve show a way to define
simpler, more maintainable unit tests against the database access layer
components using the powerful and easy to use services of COM+. Hopefully
using this technique database unit testing can become a much less painful
experience, and thus allow more developers do more tests on their database code
? and find those bugs as soon as possible.