Skip to content

Latest commit

 

History

History
88 lines (60 loc) · 4.15 KB

call-c-sharp-from-sql.md

File metadata and controls

88 lines (60 loc) · 4.15 KB
title titleSuffix description author ms.author ms.date ms.service ms.subservice ms.topic monikerRange
Call .NET runtime
SQL Server Language Extensions
Learn how to call C# code from a SQL Server stored procedures using SQL Server Language Extensions.
rwestMSFT
randolphwest
04/29/2024
sql
language-extensions
how-to
>=sql-server-ver15 || >=sql-server-linux-ver15

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

[!INCLUDE sqlserver2019-and-later]

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 [!INCLUDE sssql19-md] 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 [!INCLUDE sssql19-md] 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