Depth. NET core: Examples of CLR function usage

You can create database objects in a SQL Server instance that can be programmed in assemblies created in the Microsoft. NET Framework Common Language Runtime (CLR). database objects that can take full advantage of the rich programming patterns provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types.

Creating a CLR function in SQL Server is divided into the following steps:

Defines a function as a static method of a class using languages supported by the. NET Framework. The class is then compiled using the appropriate language compiler to generate the assembly in the. NET Framework. Registers an assembly in SQL Server using the CREATE ASSEMBLY statement. Creates a function that references a registered assembly by using the CREATE FUNCTION statement.

Attention:

Deploying the SQL Server project in Microsoft Visual Studio registers the assembly in the database that is specified for the project. When you deploy the project, you also create CLR functions in the database for all methods that use the SqlFunction property annotation.

Attention:

By default, SQL Server's ability to execute CLR code is turned off. You can create, change, and delete database objects that reference managed-code modules, but these references will not be executed in SQL Server unless the CLR enabled option is enabled by using sp_configure (Transact-SQL).

accessing external resources

You can use CLR functions to access external resources, such as files, network resources, Web services, and other databases, including remote instances of SQL Server. This can be accomplished by using various classes in the. NET Framework, such as System.IO, System.webservices, and System.sql. You should at least configure the assembly that contains this type of function to set external_access permissions for this purpose. You can use the SQL client managed access interface to access remote instances of SQL Server. Loopback connections to the originating server are not supported in CLR functions.

Examples are as follows:

First step: