Muokkaa

Jaa

How to call the .NET runtime in SQL Server Language Extensions

  • Artikkeli

Applies to: SQL Server 2019 (15.x) and later versions

The SQL Server Language Extensions feature uses the sp_execute_external_script system stored procedure as the interface to call the .NET runtime.

This how-to article explains implementation details for C# code that executes on SQL Server.

Where to place C# classes

You call C# code in SQL Server by uploading compiled .NET libraries (DLLs) and other dependencies into the database using the external library DDL. For more information, see Create a .NET DLL from a C# project.

Basic principles

The following are some basic principles when executing C# on SQL Server.

  • Compiled custom .NET classes must exist in DLL files.

  • The C# method you're calling must be provided in the script parameter on the stored procedure.

  • If the class belongs to a package, the packageName must be provided.

  • params is used to pass parameters to a C# class. Calling a method that requires arguments isn't supported. Therefore, parameters are the only way to pass argument values to your method.

Note

This note restates supported and unsupported operations specific to C# in SQL Server 2019 (15.x) and later versions. On the stored procedure, input parameters are supported, while output parameters aren't supported.

Call C# code

The sp_execute_external_script system stored procedure is the interface used to call the .NET runtime. The following example shows an sp_execute_external_script using the .NET extension, and parameters for specifying path, script, and your custom code.

Note

You don't need to define which method to call. By default, a method called Execute is called. This means that you need to follow the Microsoft Extensibility SDK for C# for SQL Server and implement an Execute method in your C# class.

DECLARE @param1 INT;

SET @param1 = 3;

EXEC sp_execute_external_script
    @language = N'dotnet',
    @script = N'<PackageName>.<ClassName>',
    @input_data_1 = N'<Input Query>',
    @param1 = @param1;

Use external library

In SQL Server 2019 (15.x) and later versions, you can use external libraries for the C# language on Windows. You can compile your classes into a DLL file and upload the DLL and other dependencies into the database using the CREATE EXTERNAL LIBRARY DDL.

Example of how to upload a DLL file with external library:

CREATE EXTERNAL LIBRARY [dotnetlibrary]
FROM (CONTENT = '<local path to .dll file>')
WITH (LANGUAGE = 'dotnet');
GO

When it creates an external library, SQL Server automatically has access to the C# classes, and you don't need to set any special permissions to the path.

The following code is an example of calling the Execute method in class MyClass from a package MyPackage, uploaded as an external library:

EXEC sp_execute_external_script
    @language = N'dotnet',
    @script = N'MyPackage.MyClass',
    @input_data_1 = N'SELECT * FROM MYTABLE'
WITH RESULT SETS((column1 INT));

For more information, see CREATE EXTERNAL LIBRARY.

Related content