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 Java 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 a method in a class from a package, 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
Σχόλια
https://aka.ms/ContentUserFeedback.
Σύντομα διαθέσιμα: Καθ' όλη τη διάρκεια του 2024 θα καταργήσουμε σταδιακά τα ζητήματα GitHub ως μηχανισμό ανάδρασης για το περιεχόμενο και θα το αντικαταστήσουμε με ένα νέο σύστημα ανάδρασης. Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα:Υποβολή και προβολή σχολίων για