Is it possible to reuse test conditions or test steps in an SSDT Database Unit Test?

Question

I have recently started using SSDT database unit tests. I find I have many test conditions which are identical, but I can't find a way to reuse them. How many times do I need to say, "and only one row may be returned"?

Similarly, I have begun to find multiple unit tests which need the same data. This seems to mean that I should use the same pre-test step. But I also can't find a way to reuse the pre-test steps.

Is there something I'm missing? Either that there is a way to reuse these components, or that there is some reason why I don't really need to reuse them?

Answer 1

First and foremost there is no way to reuse pre-tests, post-tests or test conditions across multiple tests. Yes this is annoying however I can kind of understand it - ensuring each test is self-contained strikes me as a good idea.

One way I've gotten around the problem of inability to reuse test data in the past is to write stored procedures that create that test data and include those stored procedures in the SSDT project that I am testing, then I just call the stored procedures from Pre-Test.

The downside of that approach is that those stored procedures then end up as part of your deployed database - not a good thing as you don't want stored procs that exist solely for creating test data in your production databases. The way to get around that is to put these "test data" stored procs into a dedicated SSDT project and create a "Same database" database reference from that project to the SSDT project that contains all your code to be tested. Deploying the second project will also deploy the first one. This technique is better known as Composite Projects.

So that's one way of solving the "sharing test data" problem. I don't know of a way of sharing test conditions other than going and hacking the underlying C# code-behind but that's not something I'd be comfortable in recommending.

Answer 2

In a unit test class there is a section for (Common scripts) found in the same drop down as the individual tests you add.

This section has "Test initialize" and "Test cleanup" where you can add code that runs before and after every test in that class.

Your general test structure would look like this;

1. "Common scripts > Test initialize" : Setup generic data (and check any test initialize conditions).
2. "My Test > Pre-test" : make any test specific data adjustments (and check any pre-test conditions).
3. "My Test > Test" : Execute the testable code and check any test specific conditions.
4. "My Test > Post-test" : clean up any test specific data (and check any post-test conditions).
5. "Common scripts > Test cleanup" : clean up any generic data (and check and cleanup conditions).

That is half the battle. Unfortunately there doesn't appear to be a way to run the same test conditions for a number of tests.

However! Test initialize and Test cleanup can have their own test conditions, so if each of your unit tests for a particular stored procedure should result in the same output, e.g. always one row in a table (like an audit entry), then you could add this test condition to the Test cleanup instead; while keeping specific test conditions in the test where they belong.

It could be a little misleading that a core test is found in the Test cleanup section so it is a bit of a work around.

You would have to ensure that the Test cleanup section doesn't remove any data you want to check in your general test conditions as it would be executed first. I work around this by doing my cleanup in the initialize section just before setting up the data, in effect the clean up always puts the database into a known state regardless of what test had run before.

For the audit example, step 5. might confirm that [dbo].[Audit] has one record while step 1. will delete from [dbo].[Audit] returning the row count to 0.

Answer 3

A similar problem is duplication of setup/teardown code. I like having a test class per stored procedure. Within a test class I often have many tests that look at the side effects and data changes caused by running the associated stored procedure.

I don't like that each class has >80% the same setup and teardown code. That means I have dozens of classes to change if my setup or teardown changes.

On the other hand, this post suggests that is just the way things should be: What does “DAMP not DRY” mean when talking about unit tests?