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

Views: 4.960
Reading Time: 14 minutes

Hello people,
Good Morning!

In this post, which will be the blog's 100 number, I'd like to talk about something I particularly like about SQL Server, which is the creation of .NET routines within the SQL Server database. Yes, we are talking about the common language runtime (CLR).

Introduction

Click here to view
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.

Advantages and disadvantages

Click here to view
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: One nice thing I like about CLR is that we can set a fixed user to connect to the database. This way, we can allow him access to execute SP's and consult views and system tables and create functions and SP's in CLR for that. When an analyst needs to use these system SPs, simply grant access to the CLR SP / View / Function that will have indirect access to the system object, without having to grant access to it on 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 Override: A widely used feature that 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 from it, coupled with the fact that the commands are unmanaged and run within the SQL Server process. On failure, the instance shuts down because the SQL Server process is automatically closed by the operating system (!!!!)
  • Automation: With CLR procedures, you can automate a multitude of day-to-day processes that previously could only be automated using Integration Services, which is a great tool, but becomes somewhat limited in the face of CLR's world of possibilities. , since in MSIS you only have the features that tools make available, while in CLR you can create anything that the .NET platform makes possible. In addition, the result of the CLR is database objects, such as Stored Prodecures, Functions, Triggers, etc., which can be used freely in other SPs, Jobs, and any other database objects, while Packages can only be executed by the tool. or by Jobs. (Note: CLR and MSIS are tools with different purposes, I just compared because some Integration Services tasks can be easily replaced by CLR)
  • Connectivity: Possibility to use .NET Framework connectors and access other DBMS's and other instances with direct link, without the need for LinkedServer, which runs 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.

Enabling CLR on Your SQL Server Instance

Click here to view
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:
Enabling CLR on SQL Server

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.

Creating Your First SQL CLR Project in Visual Studio

Click here to view
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

New CLR Project

And it should look like this initially:
CLR new project - Solution explorer

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

CLR New 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 may be removed as they will not be used in the examples. Right-click on “References” from the CLR project and select the “Add Reference…” option. In the screen that will open, select the Projects> Solutions category and check the Class Library project checkbox:

CLR Add Reference

Be sure to set the permission of the imported project as shown in the figure below:
CLR Assembly Permission Set 2

Solution Explorer should look like the following:
CLR new project with Class Library - Solution explorer

Creating a stored procedure with no return

  • Right click on the “Procedures” directory of the Class Library project and select the option Add> Class… In the screen that opened, type the name of the file to 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_Archive.cs
  • Copy and paste the code below:

Creating a stored procedure with return of a select

  • Right click on the “Procedures” directory of the Class Library project and select the option Add> Class… In the screen that opened, type the name of the file to 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:

Creating a scalar function

  • Right-click on the “Functions”> “Scalar Function” directory of the Class Library project and select Add> Class… In the screen that opened, type the name of the file to 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:
  • 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 Class Library project and select the option Add> Class… In the screen that opened, type the name of the file to 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 fncArchive_Ler.cs
  • Copy and paste the code below:
  • 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.

string or SqlString?

Click here to view
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).

Which version of the .NET Framework should I use?

Click here to view
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 even .NET Framework 3.5
- SQL Server 2012 and 2014: Support even the .NET Framework 4.6.1

You can set the version of the .NET Framework by 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 ability to set the database version:

CLR - Target Framework

CLR - Target Platform

CLR Class Library - .NET Framework Version

Compiling your project and publishing to the database

Click here to view
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 option “Publish…”. The “Build” and “Rebuild” buttons are only for compiling the source code and already validating for source syntax errors, while the “Clean” button eliminates the generated files and the Visual Studio cache.

CLR - Publish

In the screen that will open, you must click the "Edit…" button to enter the server and the way to connect to the database.

CLR - Publish Database 2

You can create / load profiles for easy publishing in more than one environment. The “Generate Scripts” button will generate a SQL script, which must be executed by SQLCMD for CLR publishing, and the “Publish” button will generate the script and already execute in the database.

CLR Publish completed successfully

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:

CLR - Object Explorer

SQLCLR

Assembly Restrictions: Supported and Unsupported DLL's

Click here to view
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)

Catalog Views and SP's

Click here to view

Video - Introduction to SQLCLR

Want to chat with us about SQLCLR?
- Telegram: https://t.me/sqlclr
- Whatsapp: https://chat.whatsapp.com/71JS49CiD12Ct6pR3fjfLu

And that's it folks!
Until the next post!

SQL Server CLR SQLCLR how to enable enable how to use how to enable enable how to create sp how to create procedure how to program how to get started getting started where to how to code coding programming procedures table-valued functions scalar c # csharp programming database database programming

SQL Server CLR SQLCLR how to enable enable how to use how to enable enable how to create sp how to create procedure how to program how to get started getting started where to how to code coding programming procedures table-valued functions scalar c # csharp programming database database programming