DBPro for unit testing has been a PITA for the following reasons:
1. Roll back of initial test state is difficult
There is no easy way to begin a transaction in the setup and rollback in the tear down without modifying the generated code to use transaction scope. The setup/tear down methods cannot be used because they run on a separate connection than the test is executed. This is important for security testing but not all that applicable to the work that we perform. I don’t think it’s reasonable for the tear down methods to have to reverse all the work of the test setup methods. Database rollback is the appropriate approach. Therefore, we have reverted to not using anything but the body of the test.
2. Inability to share T-SQL code to setup common state across tests
This is forcing us write T-SQL setup code that is duplicated across multiple tests or we resort to putting all the testing for a given state into single test. These tests are then testing a single unit (stored proc, trigger, view, etc.) but are testing multiple conditions. If the first condition fails, all subsequent tests will not execute. Also, using state that was modified by a previous test can be very problematic and creates interdependencies between tests.
3. Test designer for specifying test conditions is difficult
The point/click interface for editing many test conditions becomes laborious for large numbers of result sets and columns. Managing inline T-SQL with RAISERROR statements is easier and allows a script to be reviewed without the designer surface.
4. T-SQL test editing designer is not friendly for debugging your test
Most people find that using SQL Management Studio to write/debug your unit tests is easier. In order to use SMS you must copy-paste your code in and out of the DBPro test designer. This process is error prone and laborious.
5. DBPro is slow at executing tests
6. Data Generation Plans are not useful
I may be wrong but I don’t find the Data Plan Generation facility very useful for most unit tests. I see how it would be useful for performance or load testing but these types of tests are much higher level.
7. Integrating the DBPro tests into the build process is not easy
This has not been attempted yet but it does not appear on the surface to be easy.
