Writing Database Objects in CLR

My previous article covered the basics of CLR integration in SQL Server 2005. It compared CLR inside SQL Server 2005 with TSQL and extended stored procedures, and discussed the judicious use of CLR in lieu of other available choices. This article takes the discussion further, discussing how to write various common database objects in CLR.

You can author a number of objects using CLR and host them in the database:

1. UDT: User-defined types (UDTs) allow you to store object representation of your data directly in the database. For instance, if you had map coordinates such as (X,Y), instead of storing X and Y in two different columns or as a concatenated string, you could create a new data type with a name of your choice, and store and work with the struct or class that represents that data type directly in SQL Server.
2. UDF: User-defined functions (UDFs) are simply functions in a database that return a scalar value. A scalar value, unlike a table value, is identified by a number, floating point, Boolean, or a string instead of a tabular resultset.
3. TVF: Table value functions (TVFs) are functions in a database that return their results in the form of a table.
4. Triggers: Triggers written in CLR behave just like normal TSQL triggers do. What makes CLR triggers so attractive is their ability to leverage System.Transactions to either latch on with the current running transaction or simply break away from a currently running transaction by spawning a new non-context connection (context connections are explained later).
5. Stored procedures: CLR stored procedures, like regular TSQL stored procedures, have the ability to encapsulate logic and a bunch of operations within themselves and return one or more results. CLR stored procedures present an attractive alternative to TSQL stored procedures because, like any other CLR code, they have the ability to leverage System.Transactions.

Wheres describing every detail about each object mentioned above probably is beyond the scope of one article, a canonical example will help explain the main steps involved in using and creating a CLR object inside SQL Server 2005. This article demonstrates a TVF that accepts a number and then returns all numbers that the passed-in number is divisible by. Thus, if you pass in 2, you should get 1 and 2, and if you pass in 20, you should get 1, 2, 4, 5, and 10. Along the way, the article also demonstrates the debugging of SQLCLR code using both SQL Server projects and a simple class library project (click here to download the code).

Table Valued Functions

A TVF, as the name suggests, is a function stored inside SQL Server that returns its results in a tabular form.

To author a CLR object for the database, you need to be able to create a class or struct that holds the logic for you. Visual Studio 2005 Professional and Visual Studio 2005 Team System come with database projects that make authoring and deploying CLR objects rather easy. But, even if you do not have Visual Studio 2005 Professional or Team System installed, you still can author and debug CLR code hosted inside SQL Server 2005. The only difference is that you have to write the deployment scripts yourself and debug by attaching to the SQL Server process.

In an enterprise development environment, you probably will have to write deployment scripts instead of a point-and-click deployment anyway, so this article describes both approaches. First, you must set up a guinea pig database that you will write and run all your code against. Do this with the following simple TSQL command:

Create Database Test

With the database set up, take the following steps:

1. Start up Visual Studio 2005. If you are using Visual Studio Professional or Team System, create a new SQL Server project or just create a class library project. Call the project SQLServerTVF.
2. If you are using a Class Library project, skip directly to Step 4. If you are using a SQL Server project, you will be prompted to choose or create a database reference. If you do not already have a reference to the test database you created, click the Add New Reference button and fill in the relevant details as shown in Figure 1.

   

   Figure 1. New Database Reference Settings

   Note that "homepc" is the name of the computer my database resides on. In your case, it may be different. Finally, your "Add Data Reference" dialog box should look like the one shown in Figure 2.

   

   Figure 2. Add Database Reference Dialog Box

3. For a SQL Server project, right click on the project and choose Add --> User-Defined Function (see Figure 3). Name it "Divisor.cs".

   

   Figure 3. Add User-Defined Function

4. Now, because you are writing a TVF, which returns results in a tabular form, you need two things:
   1. A method that returns an IEnumerator—If you return an IEnumerator, the underlying framework understands that each enumerated item is a row.
   2. Another method that splits each enumerated row into columns.

   Thus, modify the auto-generated code to look as shown below:

   public partial class UserDefinedFunctions
   {
      [Microsoft.SqlServer.Server.SqlFunction(
         FillRowMethodName="RowFiller",
         TableDefinition="Divisor int")]
      public static IEnumerable Divisor(Int32 input)
      {
         if (input < 1)
         {
            throw new ArgumentOutOfRangeException("input",
               "Please pass in a value greater than 0") ;
         }
         for (int i = 1; i < input; i++)
         {
            // Is i a perfect divisor of input?
         if(input%i == 0)
         {
            yield return i ;
         }
         }
      }
      public static void RowFiller(object row,
         out int Divisor)
      {
         Divisor = (int)row ;
      }
   }
   

   Note: The partial keyword is standard for all auto generated code, and is not necessary. It may be omitted in the case of a class library project.

5. With the code modified and compiled as above, go ahead and deploy it to the SQL Server. This can be done by using the menu option in a SQL Server project (see Figure 4).

   

   Figure 4. Deploy Auto-Generated Code in SQL Server

   If you are using a class library project, or if you want to deploy the DLL generated out of the SQL Server project by hand (say, in an enterprise development environment), you may instead use the following TSQL script:

   from
   'C:\SqlServerTVF\SqlServerTVF.dll'
   GO
   Create Function Divisor
   (
      @Input int
   )
   Returns Table
   (
      Divisor int
   )
   As
   External Name
   SqlServerTVF.[UserDefinedFunctions].Divisor
   Go
   

   You may need to modify the location of the DLL depending on where you created your project.

6. With the TVF deployed, the next step is to test it. You can simply go to SQL Server Management Studio and execute the following T-SQL query to test your newly written TVF:

   Select * from dbo.Divisor(100)

   This should produce the output in Figure 5.

   

   Figure 5. Test Result Output

If CLR is not enabled on your database, you may get the following error message instead:

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled.
Use sp_configure "clr enabled" to enable execution of user code
in the .NET Framework.

You can easily fix this problem by running the following line of code against your database to enable CLR:

sp_configure 'clr enabled', 1

The above will prompt you to run RECONFIGURE. Run RECONFIGURE and then test your newly written TVF.

For any reasonably complex code, you absolutely must be able to debug it. Thus, it makes sense to look at debugging your TVF next.

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.

Figure 6. Set the Default Debug Script

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 '[976] 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).

Click here for a larger image.

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".

Conclusion

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.