CLR SQL Stored Procedures Testing with Unit Test Project

Question

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:

1. debug the stored procedure in the visual studio debugger
2. 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.

I hope I can have two projects in my solution, one for my CLR stored procedures and one for my unit test project. When I want to run my tests in my unit test project I hope that all of the changes made to my CLR stored procedure project will be published to the server, the test project will start executing, and if I set a break point in the CLR stored procedure when the unit test begins to test that stored procedure it will break on the server and I can then step through the code.

The closest solution I have found so far is by Alex Kuznetsov and Alex Styler, however using this solution I can not step through to the SQL server.

****UPDATE 2****

This is more of a 'bump' to bring this question back up...I've still had no luck stepping to the SQL server from the unit tests. Any other thoughts?

Answer 1

You have to enabled SQL/CLR debugging on the connection before being able to debug your code. in order to do that, follow the instructions here http://msdn.microsoft.com/en-us/library/ms165039(VS.80).aspx

Note that when you debug your C# SP, all the managed threads in the SQL server will suspend during your debugging

Hope this helps.

Answer 2

When create a project i have a treeview node with database and an item named SQL Server Project.

alt text http://img514.imageshack.us/img514/8523/81345560.jpg

Next when project template is created i get Test Scripts (Test.sql) where i can test all my SQL Server CLR classes, using common sql procedures and commands.

alt text http://img17.imageshack.us/img17/7826/38591345.jpg

With this you can have an development interface for CLR with debug. Test.sql is like an Unit Class for CLR classes.

PS. Don't forget active CLR in your SQL Server Configuration.