How to call the .NET runtime in SQL Server Language Extensions
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.
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.
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.
नोट
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.
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.
नोट
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;
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.