SQL CLR Integration in SQL Server 2008

Introduction

Google is a great tool for finding information on the Internet. For developers Google (or Bing) are great tools for finding blogs that describe how to employ technical solutions. The biggest challenge is finding the correct and a complete solution. It is pretty straight forward to find partial solutions; it is much harder to find complete solutions.

Part of the difficulty in finding a complete solution is that there are so many environments, configurations, and configuration settings one small variable can make your environment different and the solution (found on the Internet) not quite right for your environment. Perhaps an answer is to try to clearly articulate the environment(s) the solution was tested in and document as complete a solution as possible. Since a lot of blogs are unpaid volunteer efforts, there is no way to control the quality of posts. As a result sometimes you have to put a solution together from several posts.

In this column I try to incorporate as many details as possible, describe some scenarios, and talk about the environment the solution was tested in. (Even I can't test all environments and configurations.)

This article talks about SQL/CLR (Common Language Runtime, or .NET Framework) Integration. That is, calling .NET CLR code from SQL. The solution was implemented in Microsoft Visual Studio 2010, SQL Server Express 2008, and uses .NET Framework 3.5. The integration code is trivial, but the solution shows you how to configure the database server and define wrapper stored procedures and functions for the CLR methods. In the name of completeness, the end of the article talks about attaching to the SQL Server instance and debugging the .NET framework CLR code.

After reading this article you will know how to enable the CLR in SQL Server, check the SQL Server compatibility level, create a key file for signing a CLR Class Library, defining methods that can be called from SQL Server, registering the assembly in SQL Server, defining procedure and function wrappers for the CLR code, and attaching the Microsoft Visual Studio debugger to the SQL Server instance and stepping into your CLR code.

Checking the SQL Server Compatibility Level

So this article doesn't turn into a book I am going to keep the short stuff short, or at least relatively short.

SQL Server and CLR integration was introduced for SQL Server 2005 and 2008. It is a relatively new capability in the grand scheme of things so check your compatibility level. You can do this in Microsoft Visual Studio or Microsoft SQL Server Management Studio by creating a new query and running.

  exec sp_dbcmptlevel <database_name>

For example, the sample database for this article is ClrIntegration so the command to check the current compatibility level would be:

  exec sp_dbcmptlevel ClrIntegration

To set the compatibility level to SQL Server 2005 revise the command to:

  exec sp_dbcmptlevel ClrInteration, 90

The next step is to enable CLR integration.

Enabling CLR Integration in SQL Server (2008)

SQL is a bunch of tables and stored procedures that contain housekeeping behaviors and schemas. Add a new database to a server instance or new table to an existing database and the pre-installed stored procedures and tables somewhere are updated to contain the definition of these new elements.

One such stored procedure is sp_configure. One of the settings of sp_configure is 'clr enabled' which is 0 by default. To enable CLR integration type in a query window in Microsoft Visual Studio or SQL Server Management Studio.

  exec sp_configure  'clr enabled', 1
  RECONFIGURE;
  GO

The RECONFIGURE statement updates currently configured values of a configuration option and performs a SQL stop and restart if necessary.

Creating a Key File for the CLR Class Library

You can create a key file-a .snk file-by opening the Microsoft Visual Studio command prompt and running the sn.exe utility with the -k option. I thought this file might be needed for CLR assemblies with the PERMISSION_SET = UNSAFE option, but it doesn't appear if you need a key file.

If you determine that you need a key file for your project then select Start|Programs|Microsoft Visual Studio (2010)|Visual Studio Tools|Visual Studio Command Prompt (2010) and enter sn -k <keyfile.snk> name at the command prompt, copy the .snk file to your project.

  To associate the strong key file with your project select Project|<project name> Properties, click the Signing tab and use the drop down "Choose a strong name key file" to associate your key file with the project.

Keep in mind that precise instructions like menu options may vary slightly between different versions of Windows and Microsoft Visual Studio.

Create the Class Library in Microsoft Visual Studio 2010

If is worth noting that if you use the New Project|Database|SQL Server|Visual Basic SQL CLR Database Project option to create your CLR integration project then a key file is not created for you and the Deploy option associated with projects of that type will register and create the assembly associations automatically. The material in this section walks you through using a Class Library project and all of the steps for CLR integration; use the SQL Database Project type if you are starting from scratch.

To manage SQL/CLR integration with regular class library a VB CLR SQL Integration project follow these steps:

  1. Assuming you have a class library project handy or have created one, add a class to it using a meaningful name.
  2. Make sure that the target framework is 3.5 by selecting Project|Properties|Compile (tab)|Advanced Compile Options|Target framework (all configurations) .NET Framework 3.5--see the note.
  3. VB doesn't have a namespace around classes, but if you do happen to have one then remove it. SQL CLR Integration doesn't manage namespaces well (for some reason that escapes me), but if you want to use the fully qualified namespaces and class names then see the example in the next section
  4. Add an Imports System.Runtime.InteropServices and Microsoft.SqlServer.Server statements to the top of the class file. Add a shared subroutine with the <SqlProcedureAttribute()> on the shared method and the OutAttribute() on the second parameter; here is the subroutine definition:

      <sqlprocedure()>
      Public Shared Sub GetUserName(ByVal dummy As Integer, <out()> ByVal name As String)
      SqlContext.Pipe.Send("In CLR Code")
         name = "Paul Kimmel"
         SqlContext.Pipe.Send("Exiting CLR Code")
      
      End Sub
    

  5. Compile the class library

Note: If you are using Microsoft Visual Studio 2010 as I am for this demo then the default target framework is .NET framework 4.0. Unfortunately, SQL Server doesn't seem to want to register .NET frameowrk 4.0 assemblies. This is problematic as the framework will age. Ether I am missing something about using .NET 4.0 for CLR to SQL Integration or support going forward may not continue. Until there is a definitive answer future support is a consideration when using this technology.

The entire listing (so far) is provided in Listing 1. SqlProcedureAttribute is used by convention. You use the method from SQL without it, but it is a convenient tag and let's you provide a SQL-like name if you are into that sort of thing.

The Out() attribute marks the second parameter as an output only parameter. If you want a "stored procedure" then use subroutines with out parameters and define SQL Server stored procedures in SQL; if you want a function then use a Function in VB and a SQL Server Function. Here is Listing 1.

  A VB class that is designed to be called from SQL Server using CLR integration.
  
  Imports System.Data.SqlClient
  Imports System.Runtime.InteropServices
  Imports Microsoft.SqlServer.Server
  
  Public Class CallFromSql
  
      <SqlProcedure()>
      Public Shared Sub GetUserName(ByVal dummy As Integer, <Out()> ByRef name As String)
  
          SqlContext.Pipe.Send("In CLR Code")
          name = "Paul kimmel"
          SqlContext.Pipe.Send("Exiting CLR Code")
  
      End Sub
  
  End Class
Listing 1



SQL CLR Integration in SQL Server 2008

Registering the Assembly in SQL Server (2008)

The next step in the process is to register the .NET framework assembly with SQL Server. I am using 2008 but this works with 2005 too. (Just remember that the target framework for the .DLL needs to be 3.5.)

You can try these steps from Microsoft Visual Studio's integrated query feature, but you get weird feedback on SQL statements that don't return result sets, so I use Microsoft's SQL Server Management Studio. Follow these steps to register the CLR assembly in SQL Server:

  1. Open Microsoft SQL Server Management Studio
  2. Connect to your target database which can be any SQL Server 2005 or greater database (and you can create an empty one just for testing integration)
  3. Click the New Query button in SSMS
  4. Copying the output path of your assembly enter the following query sequence in the new query Editor where the file path is the desired location of your .NET framwework Assembly.

      CREATE ASSEMBLY CallFromSql FROM   'C:\Temp\Exercises\CallFromSql\CallFromSql_VB\bin\Release\CallFromSql_VB.dll' WITH PERMISSION_SET = UNSAFE
      GO
    

  5. In SSMS prss F5 to run the query. You should get a message that the "Command(s) completed successfully"
  6. To verify the existence of the assembly expand the Databases|[ClrIntegration|<your database name>]Programmability Assemblies folder in SSMS' Object Explorer or run the following SQL statement.

      SELECT  * FROM sys.assemblies
    

  7. Optional: If you want to debug from SQL to Microsoft Visual Studio register the debug (.pdb) file too, using the ALTER ASSEMBLY command as demonstrated:

      ALTER ASSEMBLY  CallFromSql ADD FILE FROM 'C:\Temp\Exercises\CallFromSql\CallFromSql_VB\bin\Release\CallFromSql_VB.pdb' 
      Go
    

If you want to step into Microsoft Visual Studio from SQL Server calls then you will need to complete step 7, registering the debug (.pdb) file. You can perform DROPS-DROP ASSEMBLY-on these assemblies if they are re-compiled or modified and you need to re-register them.

Defining SQL Procedure and Function Wrappers

The next step is to define SQL wrappers for your procedures and functions. Remember think "from a SQL perspective", so internally other SQL code will be calling your CLR codes, so a wrapper procedure makes this look seamless.

To wrap the CLR method I use a SQL stored procedure name and signature that matches my CLR method as closely as possible. The following SQL statement creates a stored procedure that maps to the method defined in Listing 1 and contained in the assembly registered in the last section.

  CREATE PROCEDURE GetUserName
  	@dummy int,
  	@name nvarchar(100) OUTPUT
  AS 
  EXTERNAL NAME CallFromSql_VB.[CallFromSql_VB.CallFromSql].GetUserName
  
  GO
  
  SELECT  assembly_id
         ,assembly_class
         ,assembly_method
  FROM sys.assembly_modules 
  WHERE object_id=OBJECT_ID('GetUserNameProc')

The first CREATE PROCEDURE statement maps a SQL stored procedure name to GetUserName and the external assembly method GetUserName. The first CallFromSql_VB is the assembly name defined in the CREATE ASSEMBLY statement. The part in the brackets contains the namespace and class name, which is stored as the assembly class in sys.assembly_modules. Use the [] around the assembly class, keeping in mind that VB namespaces are the project names by default and their use is implicit in VB.NET code. The last bit is the function to map to. The SELECT statement shows you how SQL Server sees your mapped function.

Executing the CLR Wrapper Methods

You can execute the stored procedure right in SSMS by declaring parameters and executing the stored procedure which in turn calls the wrapped CLR method. Here is a fragment that calls GetUserName; remember to use the OUTPUT modifier on the out parameter.

  DECLARE @n1 AS NVARCHAR(100)
  SET @n1 = ''
  EXEC GetUserName 5, @n1 OUTPUT
  PRINT @n1

Listing 2 contains all of the helper/worker SQL I have been using to develop this example.

  DROP PROCEDURE getusername
  go
  
  ALTER ASSEMBLY CallFromSql_VB DROP FILE 'C:\Temp\Exercises\CallFromSql\CallFromSql_VB\bin\Release\CallFromSql_VB.pdb' 
  go
  
  DROP ASSEMBLY CallFromSql_VB
  go
  
  CREATE ASSEMBLY CallFromSql_VB FROM   'C:\Temp\Exercises\CallFromSql\CallFromSql_VB\bin\Release\CallFromSql_VB.dll' WITH permission_set = UNSAFE
  go
  
  ALTER ASSEMBLY  CallFromSql_VB ADD FILE FROM 'C:\Temp\Exercises\CallFromSql\CallFromSql_VB\bin\Release\CallFromSql_VB.pdb' 
  Go

SELECT  *
FROM sys.assemblies


DROP PROCEDURE GetUserNameProc

CREATE PROCEDURE GetUserName
	@dummy int,
	@name nvarchar(100) OUTPUT
AS 
EXTERNAL NAME CallFromSql_VB.[CallFromSql_VB.CallFromSql].GetUserName

GO

SELECT  assembly_id
       ,assembly_class
       ,assembly_method
FROM sys.assembly_modules 
WHERE object_id=OBJECT_ID('GetUserName')

declare @n1 AS nvarchar(100)
SET @n1 = ''
EXEC GetUserName 5, @n1 OUTPUT
PRINT @n1
Listing 2: helper SQL statements for SQL/CLR Integration.





SQL CLR Integration in SQL Server 2008

Attaching the Microsoft Visual Studio Debugger and Stepping Through Your CLR Code

Now suppose that you want to step into your CLR code from a SSMS call or while you are testing elements of your database. You can do this by making sure the .pdb file is registered with your assembly, that the class library is open in Microsoft Visual Studio, and attaching Microsoft Visual Studio to your SQL Server process. Follow these steps to debug GetUserName:

  1. Make sure SSMS is open and attached to your integration database
  2. Open a New Query
  3. Open Microsoft Visual Studio 2010 (or earlier versions that support CLR integration)
  4. Load the class library that implements GetUserName
  5. Place a breakpoint somewhere in the GetUserName method
  6. Select Tools|Options|Debugging and uncheck Enable Just My Code (Managed Only)-see Figure 1
  7. Click OK
  8. In Microsoft Visual Studio click Tools|Attach to Process
  9. Check Show all processes from all users
  10. Using Figure 2 as a guide navigate ot the sqlserver.exe process and click Attach
  11. Microsoft Visual Studio should switch to the running state
  12. Switch to SSMS and run the test statements that invoke GetUserName
  13. Run the SQL from SSMS

    [options1.jpg]
    Figure 1: Uncheck Enable Just My Code in Tools|Options|Debugging.

[sql2.jpg]
Figure 2: Attach to the sqlservr.exe process to respond when SQL Server CLR hits your code.

When SQL Server transitions into your code Microsoft Visual Studio should respond and stop on the breakpoint you set in step 5 above. (Sometimes this takes a few tries. For some reason I had to attach, detach, and re-attach a couple of times before Microsoft Visual Studio started breaking when my CLR code was hit--see Figure 3.

[clr3.jpg]
Figure 3: The CLR code stopped when it was called from SQL Server.

If SSMS seems to hang and you stop the debugging process in Microsoft Visual Studio you might end up stopping the SQL process--at least SQLEXPRESS. You can click Start|Run and type services.msc to open the services MMC console and restart the SQLXPRESS process--hey we are really working here.

If you see a hollow red brakpoint with an exclamation point next to your .NET framework breakpoint then right click the breakpoint icon, click Location and check Allow the source code to be different from the original version--see Figure 4.

[breakpoint4.jpg]
Figure 4: You don't need to re-load the CLR code everytime you make a little tweak, like adding a comment; this checkbox will let the version in SQL Server and the version in Microsoft Visual Studio to be slightly different.

Using ADO.NET From CLR Integrated Methods

Notice that the sample code is using something called SqlContext and a method called Pipe. This is your pipline back to the running SQL Server process. This is one way to get a handle to and get data back to the running SQL Server process.

Listing 3 is based on a sample from Microsoft at http://msdn.microsoft.com/en-us/library/ms345136(SQL.90).aspx that shows you how to use an existing context and send data back through the pipe from .NET framework using ADO.NET code. The sample assumes you created a table named TEST with a DATA and ID column. The found ID I sent back to the running process using pipe.Send.

  [SqlProcedure]
  public static void Report(string data)
  {
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
      connection.Open();                       // open the connection
      SqlPipe pipe = SqlContext.Pipe;    // get the pipe
      SqlCommand cmd = new SqlCommand(
        string.Format("SELECT * FROM TEST WHERE Data = '{0}'", data), connection);
      SqlDataReader reader = cmd.ExecuteReader();
      if(reader.Read())
        pipe.Send(reader["ID"].ToString());
    }
  }
Listing 3: A CLR/SQL procedure that uses ADO.NET and the caller's context to connect to the database and return data.



Summary

I am pretty lucky. Several times a week someone asks me how to do something and then I have an excuse to go learn as many details as I can about that something and write it down. Other people run into problems that I might not encounter for months in years in the course of my daily work. So helping others ultimately helps me a lot too.

I Hope this long and comprehensive example helps you figure out how to use SQL/CLR integration without a tremendous amount of frustration. The gist of it is to define a class library with shared methods. Add the SqlProcedure attribute to those methods. Compile the library, register the assembly, and define a wrapper for your CLR methods. Those wrappers are called by other SQL or by other .NET framework code and consequently the wrappers call the CLR/.NET code.

You can define functions and types that can be represented in SQL Server. You can debug your .NET code by attaching to the SQL Server process, and your .NET code can talk back to the SQL context via the SqlContext class.

Related Articles





About the Author

Paul Kimmel

Paul Kimmel is the VB Today columnist for CodeGuru and has written several books on object-oriented programming and .NET. Check out his upcoming book Professional DevExpress ASP.NET Controls (from Wiley) now available on Amazon.com and fine bookstores everywhere. Look for his upcoming book Teach Yourself the ADO.NET Entity Framework in 24 Hours (from Sams). You may contact him for technology questions at pkimmel@softconcepts .com. Paul Kimmel is a Technical Evangelist for Developer Express, Inc, and you can ask him about Developer Express at paulk@devexpress.com and read his DX blog at http:// community.devexpress.com/blogs/paulk.

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • 10 Rules that Make or Break Enterprise App Development Projects In today's app-driven world, application development is a top priority. Even so, 68% of enterprise application delivery projects fail. Designing and building applications that pay for themselves and adapt to future needs is incredibly difficult. Executing one successful project is lucky, but making it a repeatable process and strategic advantage? That's where the money is. With help from our most experienced project leads and software engineers, …

  • The first phase of API management was about realizing the business value of APIs. This next wave of API management enables the hyper-connected enterprise to drive and scale their businesses as API models become more complex and sophisticated. Today, real world product launches begin with an API program and strategy in mind. This API-first approach to development will only continue to increase, driven by an increasingly interconnected web of devices, organizations, and people. To support this rapid growth, …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds