Writing Database Objects in CLR
Debugging CLR Code Using the SQL Server Project
Debugging CLR code in a SQL Server project is rather easy. The SQL Server project already has a debug script added for you. Make this the default debug script for your project, as shown in Figure 6.
With the script set up as your default debug script, edit the script by writing the following code:
Select * from dbo.Divisor(100)
Now, simply set up a breakpoint in your CLR code, and then press F5. If you watch the debug output produced, you will notice the following:
Auto-attach to process ' sqlservr.exe' on machine 'homepc' succeeded.
Also note that your specified breakpoint is hit. This is because the SQL Server project was smart enough to recognize that in debugging the test script, which is basically T-SQL code, you needed to seamlessly jump from the T-SQL code to the CLR TVF.
At this point, you can continue debugging like any other .NET project. However, by attaching to the "sqlserver.exe" process, you have effectively blocked that process from serving anyone else. Thus, you should not do this kind of debugging on a shared database server, such as a development or a production server.
Debugging CLR Code Using a Class Library
A TVF written as a class library is just as easy to debug, although it's done a bit differently. The class library project cannot be invoked by itself. It also has no idea that it needs to connect to a specific process (sqlservr.exe), and of course there is no test script that you can set as default and run. You will have to perform all these steps manually. You run the script through SQL Server Management Studio or otherwise, but before doing that, set up a breakpoint in your TVF code.
Next, you need to go to the Debug --> Attach to Process menu item and select "sqlservr.exe" as the process you want to debug (see Figure 7).
Figure 7. Attach "sqlservr.exe" to Process
Make sure that "Attach to" includes T-SQL Code and Managed Code. Click on Attach, and Visual Studio will monitor the SQL Server process, waiting for some code invocation to trip over the breakpoint you set in your TVF.
Now, simply go to SQL Server Management Studio and execute the following T-SQL statement:
Select * from dbo.Divisor(100)
You'll notice that your breakpoint is hit, and you now can debug your TVF much like any .NET project.
You have a stored procedure accepts a number. It then uses the TVF above to find out all its divisors, and if the number of divisors is only two, the stored procedure includes it in the results as "found prime numbers".
You saw how to write a canonical example of a CLR object hosted in SQL Server 2005. You also learned how to write a TVF (Table Valued Function) using either a class library or a SQL Server project and how to debug such code.
Although the code was hosted inside a database, you really didn't have much to do with the database itself. You never read from or wrote to a table. Also, what about transactions?
The next article will demonstrate a stored procedure written in CLR that leverages the TVF you wrote in this article. The stored procedure will accept a number as an input parameter and find all prime numbers equal to or lesser than the input parameter.
Download the Code
To download the accompanying source code for the demonstration, click here.
About the Author
Sahil Malik has worked for a number of top-notch clients in Microsoft technologies ranging from DOS to .NET. He is the author of Pro ADO.NET 2.0 and co-author of Pro ADO.NET with VB.NET 1.1. Sahil is currently also working on a multimedia series on ADO.NET 2.0 for Keystone Learning. For his community involvement, contributions, and speaking, he has also been awarded the Microsoft MVP award.
Page 2 of 2