Call dll function from sql stored procedure using the current connection

Question

Can I call a dll from a stored procedure using the open connection?

I have a dll that gets data from SQL Server and I don't want to open a new connection when I call it from the stored procedure.

Thank you

Here is an exemple

public class Class1
{
    public static SqlString GetName(SqlString str)
    {
        SqlCommand cmd = new SqlCommand(str.ToString());
        cmd.CommandType = System.Data.CommandType.Text;

        string name = cmd.ExecuteScalar().ToString();
        return name;
    }
}

and this is the SQL code

CREATE FUNCTION fn_TestConnection
(
    @str nvarchar(255)
)
RETURNS nvarchar(max)
AS EXTERNAL NAME TestConnection.[TestConnection.Class1].GetName
GO

SELECT dbo.fn_TestConnection('SELECT FName FROM Clients WHERE Id = 1' )

Solution

These instructions are for Microsoft SQL Server Management Studio 2014.

Import the Assembly

First of all you need to import that assembly into your database inside SQL Server Management Studio by navigating to the New Assembly dialog window:

DatabaseName -> Programmability -> Assemblies -> (Right Click) 'New Assembly...'

Inside the 'New Assembly' dialog window, chose Browse under the Path to assembly field and select the assembly you want to import. Adjust Permissions and click ok.

Wrap Assembly Methods in a SQL Function

Next you need to create sql function to wrap your assembly method like this:

CREATE FUNCTION [dbo].[fn_funcName](@str [varchar](max))
RETURNS 
   varchar(max) 
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [YourSqlAssemblyName].[YourAssemblyName.Class1].[GetName]

If you want to return table from your function read about SqlFunctionAttribute in .NET.

Comments

[Minh Tran]

What is the convention for naming your SQL function so that you know which assembly it came from? Or is there a way to check in SQL Server Management Studio the assembly associated with a given procedure?

[Gleb]

@MinhTran As I remember there is no naming convention. You have to keep it in mind. But I'm now sure, that was ages ago.