Introduction to SQL Common Language Runtime (CLR) in SQL Server

Present in the SQL Server database since the 2005 release, CLR is the heart of the Microsoft .NET Framework platform languages. Code that runs inside the CLR is known as managed code. The CLR provides several functions and services required for program execution, including just-in-time (JIT) compilation, memory allocation and management, type security enforcement, exception handling, thread management, and security.

In other words, CLR allows you to be able to create routines (stored prodecures, functions, triggers, etc.) written in C #, F # and VB.NET, compile and execute them in the database natively, extending DBMS capabilities. , because you can create a multitude of things that wouldn't be possible using just Transact-SQL, such as file manipulation, FTP file upload and download, aggregate functions, Webservices integration, and more.

As with any technology and tool, there are always strengths and weaknesses where you should or should not use the CLR. I will list some:

SQL CLR Advantages

• Possibility of creating new features that would not be possible using only T-SQL
• Ability to work with regular expressions (RegExp) in the database
• Performance Optimization: The same function written in C # in CLR is usually performed much faster than a T-SQL function, especially in cases of looping and calculations, since the .NET compiler is specialized for this type of operation, while T-SQL is for working with collections. In my work, I've seen several cases where the same CLR function executed 5x, 10x and some even 60x faster than the T-SQL function.
• Webservices integration via database
• Security: A nice thing I like about the CLR is that we can define a fixed user to connect to the database. In this way, we can free access for him to execute SP's and consult views and system tables and create functions and SP's in the CLR for that. When an analyst needs to use these system SP's, simply release access to the CLR's SP / View / Function that will have indirect access to the system object, without having to release access to it in the source object or create objects in system banks
• Development Tools: The tool used to develop CLR routines is Visual Studio. Management Studio is a very good IDE for creating Transact-SQL routines, especially as SQL Prompt is installed, but it doesn't compare to powerful Visual Studio, especially ReSharper. Programming is much faster and practical.
• Source Code Versioning: Because we are using Visual Studio, source code can be easily controlled and managed by Team Foundation Server (TFS), giving full control to the code created, unlike Stored Procedures in the database, which does not have controls like merge, diff, etc.
• Xp_cmdshell Override: Although disabled by default, many people and businesses end up enabling cmdshell on their instances, even in production, as some operations cannot be performed using only Transact-SQL, such as file manipulation. , for example. This feature is a great danger since it simply executes any command that is sent to it without any filter or restriction. To do so, I recommend disabling this feature and using CLR procedures designed solely for each purpose, be it a copy of files or even upload an instance.
• OLE Automation replacement: This feature is still widely used and is also disabled by default, OLE Automation procedures are C ++ libraries that allow you to use Windows APIs to perform various operations, such as file manipulation, etc. The big problem is that by enabling this feature, any user can create anything with this, coupled with the fact that the commands are not managed and are executed within the SQL Server process. In case of failure, the instance is shut down, as the SQL Server process is closed automatically by the operating system (!!!!)
• Automation: With CLR procedures, you can automate a multitude of day-to-day processes, which previously could only be automated using Integration Services, which is a great tool, but ends up becoming somewhat limited in the face of the world of CLR possibilities , since in MSIS you only have the resources that tools provide you, while in the CLR you can create anything that the .NET platform allows. In addition, the results of the CLR are database objects, whether Stored Prodecures, Functions, Triggers, etc., which can be used freely in other SP's, Jobs, and any other object in the bank, while Packages can only be executed by the tool or Jobs. (Note: CLR and MSIS are tools with different objectives, I just compared it because some Integration Services tasks can be easily replaced by the CLR)
• Connectivity: Possibility to use the .NET Framework connectors and access other DBMS's and other instances with a direct link, without the need for LinkedServer, which executes the command remotely

Disadvantages of SQL CLR

• Need knowledge in SQL and programming language (C #, F # or VB.net)
• Little documentation and knowledgeable people
• When you publish a new version, objects are removed and recreated, losing permissions and making objects unavailable during publication.
• If it is poorly developed and implemented, it may present a risk to the DBMS.
• Some functions may require a high CPU volume for processing.
• There are no optional parameters for procedures. All must be completed.

By default, CLR is disabled on SQL Server instances. To enable this feature is quite simple, just use the command sp_configure:

And we will have the following result:

Otherwise, you will come across this error message when trying to use the CLR:

Msg 6263, Level 16, State 1, Line 2
Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option.

The first step in building our CLR project is of course, downloading and installing Visual Studio compatible with your version of SQL Server (I recommend Visual Studio 2015 Community Editionas it is compatible with SQL Server 2005 through 2014 and free for developers).

After that, open Visual Studio and access the File> New> Project menu. Select Project Type SQL Server> SQL Server Database Project

And it should look like this initially:

Now let's add a new class library project that will contain our C # codes. To do this, right-click on Solution and select Add> New Project. Select Visual C # Category> Windows> Class Library

Once created, I usually create directories by object type in the Class Library for better code organization. This is recommended, but optional. You may want to organize your code by subject or as needed.

Some references can be removed, as they will not be used in the examples. Right click on “References” of the CLR project and select the option “Add Reference…”. On the screen that will open, select the Projects> Solutions category and check the Class Library project checkbox:

Be sure to set the permission of the imported project as shown in the figure below:

Solution Explorer should look like the following:

Creating a stored procedure with no return

• Right click on the “Procedures” directory of the Libraries project (Class Library) and select the option Add> Class… On the screen that opened, type the name of the file that will be created. I usually put the same name as the object that will be created in the database. In this example, I will create the file stpCopia_Arquivo.cs
• Copy and paste the code below:
  C#

  using System.IO; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpCopia_Arquivo(string origem, string destino, bool sobrescrever) { File.Copy(origem, destino, sobrescrever); } }

  1 2 3 4 5 6 7 8 9 10

  using System.IO;   public partial class StoredProcedures {     [Microsoft.SqlServer.Server.SqlProcedure]     public static void stpCopia_Arquivo(string origem, string destino, bool sobrescrever)     {         File.Copy(origem, destino, sobrescrever);     } }

Creating a stored procedure with return of a select

• Right click on the “Procedures” directory of the Libraries project (Class Library) and select the option Add> Class… On the screen that opened, type the name of the file that will be created. I usually put the same name as the object that will be created in the database. In this example, I will create the file stpImporta_Txt.cs
• Copy and paste the code below:
  C#

  using System.Data; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.IO; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpImporta_Txt(SqlString caminho) { if (caminho.IsNull) return; using (var sr = new StreamReader(caminho.Value)) { // Define um novo objeto de metadados com 2 colunas var colunas = new SqlMetaData[2]; // Define as colunas de retorno colunas[0] = new SqlMetaData("Nr_Linha", SqlDbType.Int); colunas[1] = new SqlMetaData("Ds_Linha", SqlDbType.NVarChar, 1024); var rec = new SqlDataRecord(colunas); // inicia o envio dos dados SqlContext.Pipe.SendResultsStart(rec); var contador = 1; while (sr.Peek() >= 0) { // define as colunas rec.SetInt32(0, contador); rec.SetString(1, sr.ReadLine()); // Envia o registro para o banco de dados SqlContext.Pipe.SendResultsRow(rec); contador++; } // finaliza o envio dos dados SqlContext.Pipe.SendResultsEnd(); } } }

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

  using System.Data; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.IO;   public partial class StoredProcedures {     [Microsoft.SqlServer.Server.SqlProcedure]     public static void stpImporta_Txt(SqlString caminho)     {           if (caminho.IsNull) return;           using (var sr = new StreamReader(caminho.Value))         {               // Define um novo objeto de metadados com 2 colunas             var colunas = new SqlMetaData[2];               // Define as colunas de retorno             colunas[0] = new SqlMetaData("Nr_Linha", SqlDbType.Int);             colunas[1] = new SqlMetaData("Ds_Linha", SqlDbType.NVarChar, 1024);               var rec = new SqlDataRecord(colunas);               // inicia o envio dos dados             SqlContext.Pipe.SendResultsStart(rec);               var contador = 1;             while (sr.Peek() >= 0)             {                   // define as colunas                 rec.SetInt32(0, contador);                 rec.SetString(1, sr.ReadLine());                   // Envia o registro para o banco de dados                 SqlContext.Pipe.SendResultsRow(rec);                   contador++;             }               // finaliza o envio dos dados             SqlContext.Pipe.SendResultsEnd();           }     } }

Creating a scalar function

• Right click on the “Functions”> “Scalar Function” directory of the Libraries project (Class Library) and select the option Add> Class… On the screen that opened, type the name of the file that will be created. I usually put the same name as the object that will be created in the database. In this example, I will create the file fncArquivo_Existe.cs
• Copy and paste the code below:
  
  C#

  using System.Data.SqlTypes; using System.IO; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlBoolean fncArquivo_Existe(SqlString Ds_Arquivo) { return (File.Exists(Ds_Arquivo.ToString())); } }

  1 2 3 4 5 6 7 8 9 10 11

  using System.Data.SqlTypes; using System.IO;   public partial class UserDefinedFunctions {     [Microsoft.SqlServer.Server.SqlFunction]     public static SqlBoolean fncArquivo_Existe(SqlString Ds_Arquivo)     {         return (File.Exists(Ds_Arquivo.ToString()));     } }

• Note that, unlike procedures that always return void, functions must return data. In the example case, return data of type SqlBoolean (bit = true / false)

Creating a table-valued function

• Right click on the “Procedures” directory of the Libraries project (Class Library) and select the option Add> Class… On the screen that opens, type the name of the file that will be created. I usually put the same name as the object that will be created in the database. In this example, I will create the file fncArquivo_Ler.cs
• Copy and paste the code below:
  
  C#

  using System.IO; using System.Collections; using System.Data.SqlTypes; public partial class UserDefinedFunctions { // Classe que irá receber os dados processados através do construtor da Classe private class ArquivoLer { public SqlInt32 Nr_Linha; public SqlString Ds_Texto; public ArquivoLer(SqlInt32 nrLinha, SqlString dsTexto) { Nr_Linha = nrLinha; Ds_Texto = dsTexto; } } // Definição da função, contendo o método de preenchimento dos registros (FillRow) // e o retorno para o banco de dados [Microsoft.SqlServer.Server.SqlFunction( FillRowMethodName = "FillRow_Arquivo_Ler", TableDefinition = "Nr_Linha INT, Ds_Texto NVARCHAR(MAX)" /*, DataAccess = DataAccessKind.Read // Caso a função realize consultas no banco */ )] public static IEnumerable fncArquivo_Ler(string Ds_Caminho) { var ArquivoLerCollection = new ArrayList(); if (string.IsNullOrEmpty(Ds_Caminho)) return ArquivoLerCollection; var contador = 1; using (var sr = new StreamReader(Ds_Caminho)) { while (sr.Peek() >= 0) { ArquivoLerCollection.Add(new ArquivoLer( contador, sr.ReadLine() )); contador++; } sr.Close(); } return ArquivoLerCollection; } /* Método de preenchimento dos dados. O parâmetro de entrada é sempre o objeto da classe criada no início do código e as variáveis que serão retornadas, todas como OUTPUT, pois seus valores originais serão alterados para o método IEnumerable que controla a função. */ public static void FillRow_Arquivo_Ler(object objArquivoLer, out SqlInt32 nrLinha, out SqlString dsTexto) { var ArquivoLer = (ArquivoLer)objArquivoLer; nrLinha = ArquivoLer.Nr_Linha; dsTexto = ArquivoLer.Ds_Texto; } }

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85

  using System.IO; using System.Collections; using System.Data.SqlTypes;   public partial class UserDefinedFunctions {       // Classe que irá receber os dados processados através do construtor da Classe     private class ArquivoLer     {           public SqlInt32 Nr_Linha;         public SqlString Ds_Texto;           public ArquivoLer(SqlInt32 nrLinha, SqlString dsTexto)         {               Nr_Linha = nrLinha;             Ds_Texto = dsTexto;           }       }       // Definição da função, contendo o método de preenchimento dos registros (FillRow)     // e o retorno para o banco de dados     [Microsoft.SqlServer.Server.SqlFunction(         FillRowMethodName = "FillRow_Arquivo_Ler",         TableDefinition = "Nr_Linha INT, Ds_Texto NVARCHAR(MAX)" /*,         DataAccess = DataAccessKind.Read // Caso a função realize consultas no banco         */     )]     public static IEnumerable fncArquivo_Ler(string Ds_Caminho)     {           var ArquivoLerCollection = new ArrayList();           if (string.IsNullOrEmpty(Ds_Caminho))             return ArquivoLerCollection;           var contador = 1;           using (var sr = new StreamReader(Ds_Caminho))         {               while (sr.Peek() >= 0)             {                   ArquivoLerCollection.Add(new ArquivoLer(                     contador,                     sr.ReadLine()                 ));                   contador++;               }               sr.Close();           }           return ArquivoLerCollection;       }         /*       Método de preenchimento dos dados. O parâmetro de entrada é sempre o objeto da classe     criada no início do código e as variáveis que serão retornadas, todas como OUTPUT, pois     seus valores originais serão alterados para o método IEnumerable que controla a função.       */       public static void FillRow_Arquivo_Ler(object objArquivoLer, out SqlInt32 nrLinha, out SqlString dsTexto)     {           var ArquivoLer = (ArquivoLer)objArquivoLer;           nrLinha = ArquivoLer.Nr_Linha;         dsTexto = ArquivoLer.Ds_Texto;       }   }

• This type of object is probably the most laborious to create in the CLR, since its result is a table and to populate that table we need to create a class with sets / gets with the data to be returned, set the output of data to the bank and programming to populate / calculate the data.

As you may have noticed from the examples above, in some cases I use string, bool, int, etc .. and in others I use SqlString, SqlBoolean, SqlInt32, etc. This difference is that the first cases are data types from C #, while others are database-driven and have more controls and ways to work.

Examples:
The SqlString type has the IsNull check method to determine if a NULL value has been passed by parameter. This check is faster than using the string.IsNullOrEmpty. However, using a string variable as a parameter, if the function or SP is called with a NULL value, it will be executed normally, whether or not it will be treated in the future in its source code. If you enter a NULL value for a variable of type SqlString and you have not handled it using the variable.IsNull method, retrieving the value entered using the variable.Value attribute or the variable.ToString () method will raise an exception. of ERROR in your routine.

Another nice example to illustrate the difference is between DateTime and SqlDateTime. If you pass a NULL date to DateTime, an ERROR exception is raised in your routine. This data type does not accept NULL. If you need to use something like this, you should use the MinValue (1 / 1 / 0001 12: 00: 00 AM) or MaxValue (31 / 12 / 9999 23: 59: 59) method. Already the data type SqlDateTime accepts null values ​​and has the variable.Null method to define null values.

My recommendation is to always use database data types (SqlString, SqlInt32, etc.) and ALWAYS remember to perform the necessary treatments (especially the variable.IsNull).

Identifying and deciding which version of the .NET Framework to use is quite a simple task, but it has caused me a lot of headache in the past due to poor documentation on the subject. Therefore, I will try to help you by simplifying:
- SQL Server 2005: You can only use the .NET Framework 1.0 and 2.0
- SQL Server 2008: Supports up to .NET Framework 3.5
- SQL Server 2012 and 2014: Support up to .NET Framework 4.6.1

You can define the .NET Framework version when creating the project or by right-clicking on the Class Library project and selecting the “Properties” option. You need to do the same procedure for the CLR project as well, which still gives you the possibility to define the database version:

Once the code has been developed, we will publish the generated assemblies in the database to start using the objects that will be created. You can set Visual Studio itself to create the target database (if it doesn't exist) or you can manually create the database first (I recommend this option) and then publish the assemblies.

Once the database has been created, right-click on the CLR project and select the “Publish…” option. The "Build" and "Rebuild" buttons are used to only compile the source code and already validate if there are syntax errors in the source, while the "Clean" button eliminates the generated files and the Visual Studio cache.

On the screen that will open, you must click on the “Edit…” button to enter the server and how to connect to the database.

You can create / upload profiles to facilitate publication in more than one environment. The “Generate Scripts” button will generate an SQL script, which must be executed by SQLCMD to publish the CLR and the “Publish” button will generate the script and already run in the database.

Assembly Permission Level

In SQLCLR, there are 3 permission levels of created assemblies:

• SAFE: Assembly methods can do more than Transact-SQL methods with the same logic and are executed with the credentials of the calling user.
• EXTERNAL ACCESS: Methods can perform file manipulation and I / O operations over the network. Methods are executed using the SQL Server service account, inheriting their Active Directory privileges.
• UNSAFE / UNRESTRICTED: Extends EXTERNAL ACCESS privileges, allowing CLR to execute commands without any restrictions

If the assembly needs to use EXTERNAL ACCESS or UNRESTRICTED permission levels, you must set the database to which it will be published as TRUSTWORTY:

Required Permissions to Compile the CLR

In order for a user to have permission to publish a CLR to the database, they must meet one of the following requirements:

• Role Member sysadmin
• If the assembly permission level is SAFE, then the user will need CREATE ASSEMBLY and DROP ASSEMBLY permission.
• If the assembly permission level is EXTERNAL ACCESS, then the user will need CREATE ASSEMBLY, DROP ASSEMBLY, and EXTERNAL ACCESS ASSEMBLY permission.
• If the assembly permission level is UNRESTRICTED, then the user will need CREATE ASSEMBLY, DROP ASSEMBLY, and UNSAFE ASSEMBLY permission.

Final result:

SQL Server places certain constraints on managed code in assemblies to verify that they can be performed in a safe and scalable manner. This means that certain operations that may compromise server robustness in SAFE and EXTERNAL_ACCESS assemblies are not allowed.

Custom attributes not allowed
Assemblies cannot be annotated with the following custom attributes:
- System.ContextStaticAttribute
- System.MTAThreadAttribute
- System.Runtime.CompilerServices.MethodImplAttribute
- System.Runtime.CompilerServices.CompilationRelaxationsAttribute
- System.Runtime.Remoting.Contexts.ContextAttribute
- System.Runtime.Remoting.Contexts.SynchronizationAttribute
- System.Runtime.InteropServices.DllImportAttribute
- System.Security.Permissions.CodeAccessSecurityAttribute
- System.STAThreadAttribute
- System.ThreadStaticAttribute

Additionally, SAFE and EXTERNAL_ACCESS assemblies with the following custom attributes cannot be annotated:
- System.Security.SuppressUnmanagedCodeSecurityAttribute
- System.Security.UnverifiableCodeAttribute

.NET Framework disallowed APIs
Any Microsoft .NET Framework API noted with one of the HostProtectionAttributes bans cannot be called from SAFE and EXTERNAL_ACCESS assemblies.
- eSelfAffectingProcessMgmt
- eSelfAffectingThreading
- eSynchronization
- eSharedState
- eExternalProcessMgmt
- eExternalThreading
- eSecurityInfrastructure
- eMayLeakOnAbort
- eUI

Supported .NET Framework Assemblies
Any assembly referenced by your custom assembly must be loaded into SQL Server using CREATE ASSEMBLY. The following .NET Framework assemblies are already loaded in SQL Server and therefore can be queried by custom assemblies without having to use CREATE ASSEMBLY.
- CustomMarshalers.dll
- Microsoft.VisualBasic.dll
- Microsoft.VisualC.dll
- mscorlib.dll
- System.dll
- System.Configuration
- System.Core.dll (supported from SQL Server 2008)
- System.Data.dll
- System.Data.OracleClient
- System.Data.SqlXml.dll
- System.Deployment
- System.Security.dll
- System.Transactions
- System.Web.Services.dll
- System.Xml.dll
- System.Xml.Linq.dll (supported from SQL Server 2008)