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.

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

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

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

  7. 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).
  8. 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.

  9. 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:
  10. 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.

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read