How To Unit Test – Interacting with the Database

One of the problems I have found with unit testing is there are a lot of people writing about the theory of TDD and Unit Testing and a lot of people writing about how to do simple Unit Testings (Assert.Equals(accountA.Balance, accountC.Balance).  But not so many people are talking about how to actually unit test real world objects and scenarios (or they are and i’m just completely missing them).  Over a series of blog posts, I hope to change that 🙂 All the posts will be tagged with MbUnit, TDD and Testing if you want to follow them easily.

In this first post, I will discuss unit testing with code which involves interacting with a database.  People have spoken about this before, but it is the most common scenario so I thought I would start here.  I have already wrote about mocking a database in Part 2 of my Rhino Mocks series on ASP Alliance. In this post, I will focus on unit testing and interacting directly with the database.

Why would you not want to interact with the database?

I thought I would start with saying why interacting with a database isn’t recommend during unit tests.  The main reason is because a unit should be a single, isolated block of code with no dependencies on other parts of the application.  Having a database breaks this as you need to ensure that the database is setup, populated and accessible at runtime (think from build servers).  By mocking out the database, we remove this dependency and our tests become more encapsulate and easier to maintain.

Another problem is that database tests are slow.  For every test you need to setup data, perform queries and tear down data, it all takes time and effort and distracts from the intent of the test. If you are running your tests every time you build, then this additional overhead definitely makes the process more painful.

Finally, if you use a central server to execute the tests against, you might have problems if other developers or testers are also testing the tests at the same time.

However, as I mentioned in the article, while mocking out the database is great when your testing your higher layers (API/Business Logic) you still have to test your actual data access against a database – otherwise how else will you know it works?  Most people refer to this as a integration test instead of a unit test as its breaking the machine boundary.

Scenario

For this sample, I will create a Customer Database where we can insert, update and delete Customer records.  Very simple application, but we have all had a similar requirement.  I’ll write the test code inline to demonstrate my thought process, if you want to know how I actually implemented the solution then the solution is downloadable at the end.

TDD/Unit Tests

The first thing we want to do is be able to do is connect to the database using a connection string from the configuration file.  For this, we need to obtain the connection string from the App.Config.

[Test]
public void GetConnStringFromAppConfig()
{
     DataAccess da = new DataAccess();
     string actualString = da.ConnectionString;
     string expectedString = System.Configuration.ConfigurationManager.ConnectionStrings[“DatabaseConnection”].ConnectionString;
     Assert.AreEqual(expectedString, actualString);
}

This is a test just to make sure that when we construct the DataAccess layer, the connection string property is populated correctly.  Once we have a passing test, we can move on.

The next thing we need to be able to do is be able to connect and disconnect from the database.

[Test]
public void ConnectAndDisconnectFromDatabase()
{
    DataAccess da = new DataAccess();

    bool connected = da.Connect();

    bool disconnected = da.Disconnect();

    Assert.IsTrue(connected);

    Assert.IsTrue(disconnected);
}

While this test does touch two different items, its important to disconnect from the database during the test so we might as well test both items. When executing this test, it fails due to a problem logging in to the database.  As all this does it try to connet to the database, we know its either because the server is down, your login credentials are wrong or the database doesn’t exist. As we know the server is up and we are using integration security it must be the database. For this solution I have created a database called HowToUnitTest and using Integrated Security as the login mode.

Next we need a way to actually execute tests on the server. For this I’m going to have a method which returns a SqlCommand which can execute a stored procedure.

[Test]
public void GetSqlCommand()
{
    string spoName = “spoTest”;
    DataAccess da = new DataAccess();
    SqlCommand response = da.GetSqlCommand(spoName);

    Assert.IsNotNull(response);
    Assert.AreEqual(spoName, response.CommandText);
}

This method will only be used internally and other methods will be accessible on the DataAcccess object to execute the SqlCommand.  That can be our next test.

[Test]
public void ExecuteSqlCommand()
{
    string spoName = “sp_who2”;
    DataAccess da = new DataAccess();
    SqlCommand response = da.GetSqlCommand(spoName);
    da.Connect();
    DataTable ds = da.ExecuteSqlCommand(response);
    da.Disconnect();
    Assert.IsTrue(ds.Rows.Count > 0);
}

Great!, we have a set of tests to execute a command on the database (even if all it does is return who is logged in). Now, lets actually do something involving data!  I want to be able to insert a customer into the database.

[Test]
public void InsertCustomerIntoDatabase()
{
    string spoName = “InsertCustomer”;
    DataAccess da = new DataAccess();
    da.Connect();
    SqlCommand response = da.GetSqlCommand(spoName);
    response.Parameters.Add(“Name”, SqlDbType.NVarChar).Value = “Customer Test 1”;
    response.Parameters.Add(“Email”, SqlDbType.NVarChar).Value = “[email protected]”;

    int rows = response.ExecuteNonQuery();
    Assert.AreEqual(1, rows);
}

This command will call InsertCustomer with Name and Email as a parameter.  However, this isn’t a great way to do it as it involves our calling code doing a lot of the leg work some i’m going to refactor the code to be a little bit easier to understand.  First, we need a customer object.

[TestFixture]
class CustomerTests
{
    [Test]
    public void CreateCustomer()
    {
        string name =  “Customer Test”;
        string email = “[email protected]”;
        Customer c = new Customer(name, email);
        Assert.AreEqual(name, c.Name);
        Assert.AreEqual(email, c.Email);
    }
}

Next, we want to call a method on the DataAccess to insert the customer which returns true if it inserted correctly.

[Test]
public void InsertCustomerIntoDatabase()
{
    string name = “Customer Test”;
    string email = “[email protected]”;

    DataAccess da = new DataAccess();
    bool inserted = da.InsertCustomer(new Customer(name, email));
    Assert.IsTrue(inserted);
}

This is great, we have changed our code to make more sense.  Now we have a customer object, we can do a lot more based on this.  For example, we could Delete a Customer or Update a Customer just by creating and calling the method on the data access object.  The problem with implementing those methods is that first we need some way to get a customer out of the database.  So let’s write a test for that.

[Test]
public void GetCustomer()
{
    string name = “Customer Test”;

    DataAccess da = new DataAccess();
    da.Connect();

    Customer c = da.GetCustomer(name);

    da.Disconnect();

    Assert.IsNotNull(c);
    Assert.AreEqual(name, c.Name);
    StringAssert.IsNonEmpty(c.Email);
}

Now we can get customers from the database, we can update customers.

[Test]
public void UpdateCustomer()
{
    string name = “Customer Test”;
    string updatedName = “Updated Customer”;
    DataAccess da = new DataAccess();
    da.Connect();

    Customer c = da.GetCustomer(name);

    c.Name = updatedName;
    da.UpdateCustomer(c);

    Customer c2 = da.GetCustomer(updatedName);

    da.Disconnect();

    Assert.AreEqual(updatedName, c2.Name);
    Assert.AreEqual(c.Email, c2.Email);
}

In this test, we are getting a customer, updating their name, requerying for the customer again and asserting that the details are correct.  This test looks fine, the first time we run the test it passes, however the next it cannot find the “Customer Test” customer and fails.  This is because that customer is no longer in our database, it is “Updated Customer”, we have created dependencies within our tests! Not great as we cannot easily isolated the tests from each other and tests cannot be run independently of each other.  What we really need to do is have a known state for the database before we start and clean up after ourselves once we finish.

MbUnit has a cool attribute called [Rollback] which wraps the test inside of a transaction and once it has finished, automatically rolls it back.  This solves our problem of needing to clean up after ourselves as MbUnit can do it for us. Just add the attribute under [Test] and the framework will do the rest for you.

The only problem then is having a known good state for the database.

Our UpdateCustomer test now looks like this:

[Test]
[RollBack]
public void UpdateCustomer()
{
    string name = “Customer Test”;
    string updatedName = “Updated Customer”;
    DataAccess da = new DataAccess();
    da.Connect();

    da.InsertCustomer(new Customer(name, “[email protected]”));

    Customer c = da.GetCustomer(name);

    c.Name = updatedName;
    da.UpdateCustomer(c);

    Customer c2 = da.GetCustomer(updatedName);

    da.Disconnect();

    Assert.AreEqual(updatedName, c2.Name);
    Assert.AreEqual(c.Email, c2.Email);
}

We insert a known customer in the database, and then try and return it.  InsertCustomer is being tested by another test so if that has a problem, the other test will fail as well to make debugging easier.  Next, we want to delete a customer.

[Test]
[RollBack]
public void DeleteCustomer()
{
    string name = “Customer Test”;
    string email = “[email protected]”;
    DataAccess da = new DataAccess();
    da.Connect();

    da.InsertCustomer(new Customer(name, email));

    Customer c = da.GetCustomer(name);

    da.DeleteCustomer(c);

    Customer c2 = da.GetCustomer(name);

    da.Disconnect();

    Assert.IsNull(c2);
}

You may notice that we have said that if a customer doesn’t exist, it should return null however, we haven’t got a test for this so lets write one.

[Test]
public void IfCustomerNotFoundReturnNull()
{
    da.Connect();
    Customer c = da.GetCustomer(“Unknown”);
    da.Disconnect();
    Assert.IsNull(c);
}

We don’t need a rollback attribute as we are not making any changes to the database. The only method left now is to return all the customers from the database.

[Test]
[RollBack]
public void GetAllCustomers()
{
    string name = “Customer Test”;
    string email = “[email protected]”;
    int insertCount = 5;

    DataAccess da = new DataAccess();
    da.Connect();

    for (int i = 0; i < insertCount; i++)
    {
        da.InsertCustomer(new Customer(name + i, email));

    }

    List customers = da.GetAllCustomers();

    da.Disconnect();

    Assert.IsNotNull(customers);
    Assert.IsTrue(customers.Count == insertCount);
}

One thing left to do is add a TestCategoryAttribute to all the tests to say they are Database related. By using TestCategory, we can target which tests you want to run.  If you know your integration server doesn’t have access to a database then you can setup the tests to run all but the Database category.  This way, the tests aren’t executed and the failing tests are because of a problem and not database errors.  This is also really useful if you know you quickly want to execute your tests and are sure the database code hasn’t changed. 

We now have a set of passing tests for our DataAccess object, however there is a lot of repeating code within our database tests which could be removed. By using the Setup and Teardown we can remove a lot of this duplicated code and make our tests more readable.

DataAccess da = null;

[SetUp]
public void TestSetup()
{
    da = new DataAccess();
}

[TearDown]
public void TearDown()
{
    da = null;
}

That is now requirement complete.  Some refactoring on the internals of the actual code should be performed but now we have a full suite of tests we can easily perform this would fear of breaking anything.

One thing I haven’t coded yet is validation. This should also be tested.

[Test]
[ExpectedException(typeof(ArgumentNullException))]
public void IfCustomerNameNullThrowArgumentException()
{
    string name = null;
    string email = “[email protected]”;

    Customer c = new Customer(name, email);

    Assert.Fail(“If we get here, an exception hasn’t been thrown”);
}

When it comes to writing high level tests, we have two choices. We can either mock out the DataAccess object as that has been fully tested and is what I would recommend, or we can simply write more tests like GetAllCustomers() which insert all the data into the database, do some processing, and assert the response cleaning up after itself. However, as mentioned at the beginning we are already stretching the definition of unit when testing and creating the Data Access so API/BI tests interacting with the database is definitely not advised.

Download Sample

Download the solution, including the tests and the implementation code.

http://blog.benhall.me.uk/Code/HowToUnitTest/HowToUnitTest_Databases1.zip

In summary, I hope you have found this post useful. I hope you can see what we are aiming from when writing tests and how we can go about keeping them small and isolated but still interacting with a database.  If we were using mocks, we would mock out the SqlCommand section which actually executed the tests against the database and replace this with our own internal code. See my article for more on this. 

Any feedback on this post would be most welcome, if you want me to write how to unit test anything, then please let me know.