I'm just getting into using VS2008 to write clr stored procedures for SQL 2008. When writing c# code I am used to having a separate 'Test Project' where I would place all my unit testing code, however it appears at first blush that I can't have the same setup with a clr sql project with stored procedures. It 'feels' like this can be done, and I'm missing a couple of configuration parameters, but I'm not sure what those might be.
I am using
- Visual Studio 2008
- MS Test
- MS SQL 2008
My requirements are:
- debug the stored procedure in the visual studio debugger
- have a bunch of unit test to test the stored procedures
Does anyone know how I can have the unit test project properly depoly the stored procedures to the server, connect up to the sql server, and allow me to step through the unit test to the stored procedures that are sitting on the server?
****UPDATE****
Thank you to everyone for the answers so far, however they are not excatly what I'm looking for.
Mark Seemann's answer below is an interesting approach that I did not know about, and I will certainly use when it comes to resetting my database to a known state. However, I am looking to debug CLR stored procedures, and it doesn't appear Mark's method will allow me to step from my unit test project, through to the SQL server, and debug the code sitting on the server.
I am looking to actually debug the C# sitting on the server, much like the solution pho3nix listed below. However, using this standard approach you need to write your test scripts using a 'Test.sql' file and not using a unit test project from within Visual Studio.
