A coworker asked this question about our automated integration test suite, recently:

"with the RowTest feature of NUnit, does the test data being used need to be hard coded into my code or can it be a variable that gets defined based on some rule?"

Here is my response, outlining the need to know the state of the database (know every value of every field, in every table) before and after the test suite is run:

The short answer is that the tests will have data hard coded into them.

This may be a change from how you have looked at integration testing, previously – where the data is changed on each run, so as not to duplicate the data in the system. Rather than changing the data that is used during each test, we need to ensure that the data is exactly what we expect, before and after each test. If the data is not what we expect before the test is run, the test cannot produce the data that we expect to have after it is run. If the data is not what we expect after a test is run, the test fails.

At a very high level, automated tests against the database require three things:

1. A known, state of ALL data in the database
2. A known, suite of unit tests that manipulate said data in predictable ways
3. An expected, verifiable suite of data returned by the software and manipulated in the database

The data is not necessarily "perfect" in that it has no flaws in it – rather, every last character in every last field of every last table is known, and all of the ramifications of that data is known with the explicit purpose of that data being there to support the automated tests. The automated tests expect this "perfect" data to produce the desired results and verify that the system works as expected. Any data that is manipulated in the system is expected to be in a predictable state, so the tests can verify that the system manipulated the data correctly.

When it comes to implementation of this, there are some fairly strict requirements for it to work:

1. Before the first test is run from the test suite, the data must be exactly what the tests expect
2. After the tests have run, the data must be exactly what the tests expect
3. Repeat for each run of the test suite

What this really means is that we cannot add or modify data between two test runs, without changing the tests that work with the data. In order for the tests to run multiple times throughout the day and/or on-demand, we have to ensure that the data being manipulated is what we expect it to be. Therefore, the first step of any run of the test suite is to revert the database to the known state, through sql scripts or code that are automatically run before the tests are run.