How to write unit tests for database calls

Question

I'm near the beginning of a new project and (gasp!) for the first time ever I'm trying to include unit tests in a project of mine.

I'm having trouble devising some of the unit tests themselves. I have a few methods which have been easy enough to test (pass in two values and check for an expected output). I've got other parts of the code which are doing more complex things like running queries against the database and I'm not sure how to test them.

public DataTable ExecuteQuery(SqlConnection ActiveConnection, string Query, SqlParameterCollection Parameters)
{
    DataTable resultSet = new DataTable();
    SqlCommand queryCommand = new SqlCommand();
    try
    {
        queryCommand.Connection = ActiveConnection;
        queryCommand.CommandText = Query;

        if (Parameters != null)
        {
            foreach (SqlParameter param in Parameters)
            {
                 queryCommand.Parameters.Add(param);
            }
        }

        SqlDataAdapter queryDA = new SqlDataAdapter(queryCommand);
        queryDA.Fill(resultSet);
    }
    catch (Exception ex)
    {
        //TODO: Improve error handling
        Console.WriteLine(ex.Message);
    }

    return resultSet;
}

This method essentially takes in all the necessary bits and pieces to extract some data from the database, and returns the data in a DataTable object.

The first question is probably the most complex: What should I even test in a situation like this?

Once that's settled comes the question of whether or not to mock out the database components or try to test against the actual DB.

Answer 1

In order to do this properly though you would should use some dependency injection (DI), and for .NET there are several. I am currently using the Unity Framework but there are others that are easier.

Here is one link from this site on this subject, but there are others: Dependency Injection in .NET with examples?

This would enable you to more easily mock out other parts of your application, by just having a mock class implement the interface, so you can control how it will respond. But, this also means designing to an interface.

Since you asked about best practices this would be one, IMO.

Then, not going to the db unless you need to, as suggested is another.

If you need to test certain behaviors, such as foreign key relationships with cascade delete then you may want to write database tests for that, but generally not going to a real database is best, esp since more than one person may run a unit test at a time and if they are going to the same database tests may fail as the expected data may change.

Edit: By database unit test I mean this, as it is designed to just use t-sql to do some setup, test and teardown. http://msdn.microsoft.com/en-us/library/aa833233%28VS.80%29.aspx

Answer 2

On JDBC based project, JDBC connection can be mocked, so that tests can be executed without live RDBMS, with each test case isolated (no data conflict).

It allow to verify, persistence code passes proper queries/parameters (e.g. https://github.com/playframework/playframework/blob/master/framework/src/anorm/src/test/scala/anorm/ParameterSpec.scala) and handle JDBC results (parsing/mapping) as expected ("takes in all the necessary bits and pieces to extract some data from the database, and returns the data in a DataTable object").

Framework like jOOQ or my framework Acolyte can be used for: https://github.com/cchantep/acolyte .

Answer 3

For unit tests I usually mock or fake the database. Then use your mock or fake implementation via dependency injection to test your method. You'd also probably have some integration tests that will test constraints, foreign key relationships, etc. in your database.

As to what you would test, you'd make sure that the method is using the connection from the parameters, that the query string is assigned to the command, and that your result set returned is the same as that you are providing via an expectation on the Fill method. Note -- it's probably easier to test a Get method that returns a value than a Fill method the modifies a parameter.