For the past week or so I've been going over requirements for the next iteration of the system I am working on, so I have not really looked at much code. Now that is going to change and I am finally going to post about unit testing the database. Note: All of this has been tested with v1.0 of the .NET Framework. I will be upgrading shortly (like maybe next week), but looking over the change list for v1.1, I think the code presented here should continue to work without modification.
There is not much out there on unit-testing databases, but here is the best of what I have seen so far:
The gap that I seek to fill through this blog is how to unit test a database in .NET, with code. As the Agile Manifesto states: Working software over comprehensive documentation.
One of the first things I ran into while unit-testing the Database Access Layer (DAL) is comparing one DataRow to another. I like to stick with typed DataSets as much as possible. Compile-time type checking by the compiler makes it so I do not have to write unit tests to make sure assignments will not fail during run-time for type conversion reasons (I'm lazy). See Bruce Eckel's post on Strong Typing vs. Strong Testing for a thorough discussion on this tradeoff.
Ok, so I would have 2 DataRows to compare. One would contain the values I expected and the other would contain the actual values retrieved from the database through the DAL. (As a side note, I have found no reason to duplicate a lot of work by writing separate unit tests for the stored procs and the DAL, since the two are so tightly coupled. If you are using inline SQL statements (please don't), then there is no separation anyway.) So I would end up with a lot of code like this:
Assertion.AssertEquals(
string.Format( "Should be {0}", expected.CHARGE_NBR_ID ),
expected.CHARGE_NBR_ID, actual.CHARGE_NBR_ID );
Assertion.AssertEquals( string.Format( "Should be {0}", expected.DBS_NME ),
expected.DBS_NME, actual.DBS_NME );
Assertion.AssertEquals( string.Format( "Should be {0}", expected.ISR_REQUEST_ID ),
expected.ISR_REQUEST_ID, actual.ISR_REQUEST_ID );
Assertion.AssertEquals( string.Format( "Should be {0}", expected.REQUEST_CHARGE_NBR_ID ),
expected.REQUEST_CHARGE_NBR_ID, actual.REQUEST_CHARGE_NBR_ID );
Assertion.AssertEquals( string.Format( "Should be {0}", expected.REQUEST_CHARGE_PL_CDE ),
expected.REQUEST_CHARGE_PL_CDE, actual.REQUEST_CHARGE_PL_CDE );
Don't pick on the naming conventions, I am not responsible for them.
I pulled the above lines of code out of several test methods and put them into a separate method and just called this method from various unit tests, but I still needed one method for every DataSet I needed to test. The problem with generalization is checking any AutoNumber columns will always fail (unless your database happens to have the exact same number that your expected values have, which might work for Selects but does not work for Inserts). I also avoided checks which were problematic, such as DateTime fields. It wasn't a great solution, but it worked.
It would be easy to skip a check on an AutoNumber column, but gaining access to a strongly-typed DataRow's DataColumn's properties is impossible. So I had to go back to plain-vanilla DataRows. I could now access DataColumn properties, but needed a way to do comparisons of DataRow values without knowing the types. Luckily, I remembered reading somewhere that NUnit does all comparisons after calling ToString() on an object's value. And this led to the following method in my MasterDataUnitTest class, from which all my unit test classes inherit from.
///
/// This compares all columns between two DataRows.
///
/// The row representing expected values
/// The row representing actual values
public static void CompareDataRows( DataRow expected, DataRow actual )
{
for( int currentIndex = 0; currentIndex < expected.ItemArray.Length; currentIndex++ )
{
// Check all columns except autoincrement columns
if( expected.Table.Columns[currentIndex].AutoIncrement == false )
{
Assertion.AssertEquals( string.Format("DataRow comparison failed on column {0}",
expected.Table.Columns[currentIndex].ToString()),
expected[currentIndex].ToString(),
actual[currentIndex].ToString() );
}
}
}
The beauty of making the method static and placing it in a class I inherit from is now I can call it like this after I have my two rows:
CompareDataRows( expectedRow, actualRow );
This removed many lines of code and reduced the dependency on the actual structure of the strongly-typed DataRow. I can add columns to a DataTable, the DataRow will be updated automatically (after rebuilding), and the comparisons will automatically work after I update the expectedRow values.