Stored procedures are one of the most common database objects that you'll want to create using one of the managed .NET languages. One of the best uses for CLR stored procedures is to replace existing extended stored procedures. T-SQL is only able to access database resources. In order to access external system resources, Microsoft has provided support in SQL Server for a feature known as extended stored procedures. Extended stored procedures are unmanaged DLLs that run in the SQL Server process space and can basically do anything a standard executable program can do, includingaccessing system resources that are external to the database, such as reading and writing to the file system, reading and writing to the Registry, and accessing the network.
However, because extended stored procedures run in the same process space as the SQL Server database engine, bugs, memory violations, and memory leaks in the extended stored procedure could potentially affect the SQL Server database engine. CLR stored procedures solve this problem because they are implemented as managed code and run within the confines of the CLR. Another good candidate for CLR stored procedures is to replace existing T-SQL stored procedures that contain complex logic and embody business rules that are difficult to express in T-SQL.
CLR stored procedures can take advantage of the built-in functionality provided by the classes in the .NET Framework, making it relatively easy to add functionality such as complex mathematical expressions or data encryption. Plus, since CLR stored procedure are compiled rather than interpreted like T-SQL, they can provide a significant performance advantage for code that's executed multiple times. However, CLR stored procedures are not intended to be used as a replacement for T-SQL stored procedures. T-SQL stored procedures are still best for data-centric procedures.
To create a CLR stored procedure in Visual Studio 2005, first select the New | Project option and then select the SQL Server Project template as is shown in Figure 3-3.
Figure 3-3: Creating a new SQL Server stored procedure project
Give your project a name and click OK to create the project. In this example you can see that I've used the name usp_ImportFile for my stored procedure. This stored procedure shows how you can replace an extended stored procedure with a CLR stored procedure. In this case the CLR stored procedure will read the contents of a file and store it in a SQL Server column. After naming the project, click OK. Before Visual Studio generates the project code, it displays the New Database Reference dialog that you can see in Figure 3-4.
Figure 3-4: The New Database Reference dialog
Visual Studio 2005 uses the New Database Reference dialog to create a connection to your SQL Server 2005 system. That connection will be used to both debug and deploy the finished project. Drop down the Server Name box and select the name of the SQL Server that you want to use with this project. Then select the type of authentication that you want to use and the database where the CLR stored procedure will be deployed. In Figure 3-4 you can see that I've selected the SQL Server system named SQL2005. The project will connect using Windows authentication, and the stored procedure will be deployed to the AdventureWorks database. You can verify the connection properties by clicking the Test Connection button. Once the connection properties are set up the way you want, click OK. All of the required references will automatically be added to your SQL Server project, and Visual Studio 2005 will generate a SQL Server starter project.
Next, to create the CLR stored procedure, you can select the Project | Add Stored Procedure option to display the Visual Studio installed templates dialog that's shown in Figure 3-5.
Figure 3-5: Adding a CLR stored procedure
From the Add New Item dialog, select the Stored Procedure option from the list of templates displayed in the Templates list and then provide the name of the stored procedure in the Name field that you can see at the bottom of the screen. Here you can see that the stored procedure will be created using the source file usp_ImportFile.vb. Visual Studio 2005 will add a new class to your project for the stored procedure. The generated class file is named after your stored procedure name and will include all of the required import directives as well as the starter code for the stored procedure. You can see the SQL Server CLR stored procedure template in Figure 3-6.
Figure 3-6: The CLR stored procedure template
By default the SQL Server .NET Data Provider is added as a reference, along with an include statement for its System.Data.SqlServer namespace. Plus, you can see the System.Data reference, which provides support for ADO.NET and its data-oriented objects such as the DataSet and the System.Data.SqlTypes namespace that provides support for the SQL Server data types.
It's up to you to fill in the rest of the code that makes the stored procedure work. The following example illustrates the source code required to create a simple CLR stored procedure that imports the contents of a file into a varchar or text column:
Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.IO Partial Public Class StoredProcedures
_ Public Shared Sub usp_ImportFile _ (ByVal sInputFile As String, ByRef sColumn As String) Dim sContents As String Try Dim stmReader As New StreamReader(sInputFile) sContents = stmReader.ReadToEnd() stmReader.Close() sColumn = sContents Catch ex As Exception Dim sp As SqlPipe = SqlContext.Pipe() sp.Send(ex.Message) End Try End Sub End Class
The first important point to note in this code is the directive that imports the Microsoft.SqlServer.Server namespace. This enables the usp_ImportFile project to use the SQL Server .NET Data Provider without always needing to reference the fully qualified name. The second thing to notice is the
Inside the usp_ImportFile method, a new string object named sContents is declared that will contain the contents of the file. Next, a Try-Catch loop is used to capture any errors that may occur during the file import process. Within the Try-Catch loop a new StreamReader named stmReader is created that will be used to read the file from the operating system. The name of the file that will be read is passed into the StreamReader's instantiation call. Then the stmReader's ReadToEnd method is used to read the entire contents of the file into the sContent string variable. After the contents of the file have been read, the stmReader StreamReader is closed and the contents of the sContents variable are assigned to the SQL Server column.
If any errors occur while the input file is being read, then the code in the Catch portion of the Try-Catch structure is executed. Within the Catch block a SqlPipe object named sp is created and then used to send those errors back to the caller of the stored procedure. This code block uses the SqlPipe object, which represents a conduit that passes information between the CLR and the calling code. Here, the SqlPipe object enables the stored procedure to pass error information to the external caller.
Setting the stored procedure security
At this point the code is finished for the stored procedure, but because of security concerns, it still can't execute. By default SQL Server CLR objects can only access database resources, and they cannot access external resources. In the case of the usp_ImportFile example, the stored procedure needs to access the file system, so the default security settings need to be changed. To enable external access, you need to open the project's properties and click the Database tab. Then in the Permissions Level drop-down you need to change the value from Safe to External. More information about the CLR security options is presented later in this chapter.
Deploying the stored procedure
After the CLR stored procedure source code has been compiled into an assembly, you can then add that assembly to the database and create the CLR stored procedure. You can do this in two ways. If you're using Visual Studio 2005 to create the SQL Server CLR database objects, then you can interactively deploy the CLR stored procedure directly from Visual Studio. To deploy the stored procedure to SQL Server, select the Build | Deploy Solution option from the Visual Studio menu.
You can perform the deployment manually as was shown in the earlier section "Creating CLR Database Objects". To do this, you essentially need to move the compiled DLL to a directory or file share where it can be accessed by SQL Server. Then run the CREATE ASSEMBLY statement to register the DLL and copy it into the database.
create assembly usp_ImportFile from 'C:tempusp_ImportFile.dll' WITH PERMISSION_SET = EXTERNAL
The CREATE ASSEMBLY statement copies the contents of the usp_ImportFile.dll file in the c:temp directory into the SQL Server database. The WITH PERMISSION SET clause is used to specify that this assembly can access resources that are external to the SQL Server database. That's needed here because the stored procedure reads an external file.
CREATE PROCEDURE usp_ImportFile @filename nvarchar(1024), @columnname nvarchar(1024) OUT AS EXTERNAL NAME usp_ImportFile.[usp_ImportFile.StoredProcedures] .usp_ImportFile
The CREATE PROCEDURE statement is used to create a new SQL Server stored procedure that uses the CLR assembly. This CLR stored procedure uses two parameters. The first is an input parameter, and the second is an output parameter. The EXTERNAL NAME clause uses a three-part name to identify the target method in the DLL. The first part of the name refers to the assembly name. The second part refers to the class. If the class is part of a namespace, as is the case here, then the namespace must preface the class name and both should be enclosed in brackets. Finally, the third part of the name identifies the method that will be executed.
Using the stored procedure
After the CLR stored procedure has been created, it can be called exactly like any T-SQL stored procedure, as the following example illustrates:
DECLARE @myColumn ntext EXEC usp_ImportFile 'c:temptestfile.txt' @myColumn
The previous tip is from "Developing CLR database objects: 10 tips in 10 minutes," excerpted from Chapter 3 of the book "Microsoft SQL Server 2005: A Developer's Guide" written by Michael Otey and Denielle Otey, courtesy of McGraw-Hill Publishing.
Developing CLR database objects
Tip 1: CLR architecture
Tip 2: CLR assemblies in SQL Server 2005
Tip 3: Creating CLR database objects
Tip 4: CLR stored procedures
Tip 5: User-Defined Functions
Tip 6: CLR triggers
Tip 7: User-Defined Types
Tip 8: CLR aggregates
Tip 9: Debugging CLR database objects
Tip 10: .NET database object security