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

What are you testing?

There are three possibilities, off the top of my head:

A. You're testing the DAO (data access object) class, making sure it's correctly marshaling the values/parameters being passed to the database,, and correctly marshaling/transforming/packaging results gotten frm the database.

In this case, you don't need to connect to the database at all; you just need a unit test that replaces the database (or intermediate layer, eg., JDBC, (N)Hibernate, iBatis) with a mock.

B. You're testing the syntactic correctness of (generated) SQL.

In this case, because SQL dialects differ, you want to run the (possibly generated) SQL against the correct version of your RDBMS, rather than attempting to mock all quirks of your RDBMS (and so that any RDBMS upgrades that change functionality are caught by your tests).

C. You're testing the semantic correctness of your SQL, i.e, that for a given baseline dataset, your operations (accesses/selects and mutations/inserts and updates) produce the expected new dataset.

For that, you want to use something like dbunit (which allows you to set up a baseline and compare a result set to an expected result set), or possibly do your testing wholly in the database, using the technique I outline here: Best way to test SQL queries.