Schema and Stored Procedure tests using Nunit

9
votes

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

2
.nettestingnunitmstest

2 Answers

1
votes

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.

0
votes

Maybe AnyDbTest could be something for you regarding the unit tests.

For verification of schema and DB code (SPs etc.), use a DB diff tool of your liking. Or maybe have a look at the bsn ModuleStore, which tries to address the versioning, updating and modularization of SQL Server databases.