Development and Deployment of CLR Database Objects Without Visual Studio 2005 | Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET

You can develop and deploy CLR database objects "manually" without Visual Studio. All you need is .NET Framework 2.0, which contains a compiler that you can use.

The process is done in several basic steps:

Develop code in any ASCII editor.
Compile the .dll.
Load to SQL Server 2005; record assembly metadata using the Create Assembly statement.
Bind to the entry point; execute the Create (Procedure or other appropriate) statement.
Test and use the new CLR database object.

Develop Code

Create a simple program that consists of a single static method in a public class:

 using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures {     [Microsoft.SqlServer.Server.SqlProcedure] public static void ap_MyFirst()     {            SqlContext.Pipe.Send("Calculation completed!\n");     } };

If you are developing in the Visual Basic program, the template should be something like this:

 Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class StoredProcedures     <Microsoft.SqlServer.Server.SqlProcedure()> _     Public Shared Sub  ap_MyFirst ()        SqlContext.Pipe.Send("Calculation completed!\n")    End Sub End Class

Save the file in ap_myfirst.cs or ap_myfirst.vb.

Compile Program

.NET Framework is delivered with the C# (csc.exe) and the Visual Basic .NET (vbc.exe) command line compilers. The compilers are typically installed in the folder with the remainder of the .NET Framework. The folder name is the number of the .NET Framework version (see Figure 11-4).

You should first find out the latest version that is installed on your computer. Go to C:/WINDOWS/Microsoft.NET/Framework and investigate your options. SQL Server 2005 installs .NET Framework v2.0. You will need one of its minor versions. The computer in Figure 11-4 has only one v2.0 folder.

image from book
Figure 11-4: .NET Framework folder

Tip

This folder is not in the Path environment variable, so you will have to include the path in every statement that references compilers. If you plan to use this program often, it makes more sense to include the path in the Path variable.

To compile the C# program, you should use a command such as:

 csc /target:library ap_MyFirst.cs

This command assumes that csc.exe is in the path.

For the Visual Basic program you should use a command such as:

 vbc /target:library ap_myFirst.vb

Setting the /target switch (or alternatively /t) to library notifies the compiler to create a .dll file. Other possible options are console application (exe), Windows application (winexe), and a module that can be added to another module (module). However, for producing assemblies that will be attached to SQL Server database objects, you have to use the /t:library option.

The result of the compilation is a .dll file in the same folder in which the source code was produced (see Figure 11-5). It makes sense to have a dedicated folder on the server to store all assembly files, such as c:\sqlclr\.

image from book
Figure 11-5: Compiled DLL

By default, the name of the .dll file is the same as the name of the source code file. However, you can change it if you wish, using the \out switch in the compiler command (for example, \out:c:/sqlclr/MyFirstUdp.dll).

Load Assembly to SQL Server Database

Before you can use the assembly, you must register it in a database. This is done using the Create Assembly statement:

 CREATE ASSEMBLY MyFirst FROM 'c:\sqlclr\ap_MyFirst.dll' WITH PERMISSION_SET = SAFE;

You must assign it a name that is unique in the current database. SQL Server does not allow registration of more than one version of an assembly with the same name. Exceptions are assemblies designed to work on different languages (cultures, in .NET Framework terminology) and assemblies that have different public keys. Therefore, you must drop the old version of the assembly before you can attach a new one:

 DROP ASSEMBLY MyFirst; CREATE ASSEMBLY MyFirst FROM 'c:\sqlclr\ap_MyFirst.dll' WITH PERMISSION_SET = SAFE;

Alternatively, you can refresh (replace) the old version with the new version of the assembly using:

 ALTER ASSEMBLY MyFirst FROM 'c:\sqlclr\ap_MyFirst.dll' WITH PERMISSION_SET = SAFE;

Assembly External Access

The With Permission_Set clause controls the type of access to external resources from the assembly. There are three options for Permission_Set:

SAFE will prevent code in the assembly from accessing external resources. This is the default option. It should be used for assemblies that perform only local operations such as data management or calculations.
EXTERNAL_RESOUCES will allow assembly code to access some external resources—files, environment variables, registry, and network. By default, assemblies run under a SQL Server service account. This is a potential security problem. Keep that in mind when allowing users access to the assemblies or setting database objects to run in the security context of the current user.
UNSAFE will allow code access to any resource both on the server and outside the server. This option is required to make calls to unmanaged libraries. It can potentially compromise SQL Server security and, therefore, you should only use it on assemblies that you are certain that you can trust.

To be able to add CLR objects that require the External_Resources or Unsafe permission setting to the Asset5 database, or any other database, you will need to set the database to Trustworthy On and to grant the database owner External Access Assembly permission:

 Grant EXTERNAL ACCESS ASSEMBLY to [LG\dsunderic] GO Alter Database Asset5 set TRUSTWORTHY ON

By default, the database Trustworthy option is set to Off. This is one of those options that makes SQL Server "secure by default." The idea is that a DBA has to make a conscious decision to accept this risk.

Your assembly can reference system assemblies otber tban tbose specified in tbe earlier "Supported and Not Supported" section. However, in sucb a case, you need to register tbose otber assemblies in your database as well.

Create SQLCLR Database Objects

Now that the assembly is registered in the database, I can create a database object based on it:

 CREATE PROCEDURE dbo.ap_MyFirst AS EXTERNAL NAME MyFirst.StoredProcedures.ap_MyFirst

The External Name clause contains a reference to the individual assembly, class, and static method in the format assembly_name.class_name.method_name.

After the database object is created you can use it regardless of the way it was designed.