Schema and Stored Procedure tests using Nunit

Question

We have various systems where the majority of our business logic is within stored procedures. We need to write some tests that basically check data has changed in the expected way when a stored proc has been run. I'd also like some basic schema tests, input and output params etc as well.

I've been looking at DBfit and MSTest database tests and currently, i'm not sold on either. I've used Nunit to test c# code and was wondering if using Nunit would be a good alternative approach to testing stored procs from within .net (maybe using EF4)

Something like

• Setup data
• Check data
• Execute proc
• Check data has changed

Would this be a suitable method of testing stored procs? Are there other, better methods?

Opinions and feedback would be appreciated :-)

EDIT: i'd also like to integrate this into our CI process using TeamCity

Answer 1

Yes you can do the testing within VS and the unit testing environment. However you might also want to look into tSQLt a unit testing solution for SQL Server. http://tsqlt.org/user-guide/

Red-gate provide an SSMS addin to help support that approach. http://www.red-gate.com/products/sql-development/sql-test/

I find that unit testing and performance tunning SQL parts can be done independently of .NET unit tests. I do agree that integration with your CI server and reporting success/failure through the CI server reporting mechanism is a worthy objective.