loading and calling C# (assembly) from a sql stored proc

Question

suppose I have simple C# code to print HELLO WORLD as shown in here

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class HelloWorldProc
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void HelloWorld()
    {
        SqlContext.Pipe.Send("HELLO WORLD!\n");
    }
}

So they say in that page:

This topic provides an overview of the namespaces and libraries required to compile database objects using the Microsoft SQL Server integration with the Microsoft .NET Framework common language runtime (CLR). The topic also shows you how to write, compile, and run a simple CLR stored procedure written in Microsoft Visual C#.

CLR integration functionality is exposed in an assembly called system.data.dll, which is part of the .NET Framework. This assembly can be found in the Global Assembly Cache (GAC) as well as in the .NET Framework directory. A reference to this assembly is typically added automatically by both command line tools and Microsoft Visual Studio, so there is no need to add it manually.

After create, (compiling it and add to path dll's) you can use that code as a Stored Procedure like:

CREATE ASSEMBLY helloworld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE

Once the assembly has been created, we can now access our HelloWorld method by using the create procedure statement. We will call our stored procedure "hello":

CREATE PROCEDURE hello
AS
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld

Once the procedure has been created, it can be run just like a normal stored procedure written in Transact-SQL. Execute the following command:

EXEC hello

To drop that proc:

drop procedure hello

Once the procedure has been dropped, you can remove the assembly containing your sample code.

drop assembly helloworld

After this introduction:

I need to use a C++ code that also uses an extern dll, and my question is the following,

How could I use my C++ code (that also uses extern dll) inside that C# like in the example, so, after creating assembly at the moment I exec the stored proc, the following is transparant:

1. SQL creates the assembly and then calls the C# code inside a dll.
2. C# code calls C++ code (inside a dll)
3. The C++ code calls extern dll.

So I only do

EXEC hello

and all that happens (SQL SERVER -> C# -> C++ -> extern dll).

• How to make all this dll party go on and coexist, so I only execute a Stored proc in SQL Server like EXEC hello

I was thinking in

[DllImport("cCode.dll")]

but if the c++ code depends on an extern dll... I am getting lost

Answer 1

Well, if you're going to go down this path, you might skip over using C# as a wrapper for your C++ dll (since it sounds like you're just trying to use the SQL Server CLR integration as a means to call into a C++ routine).

Instead, you could create and register a C++ COM+ Application (example) on the server and invoke this from SQL Server using sp_OACreate and related procedures. FYI: You could also create the COM+ Application in C# if you wanted, but it might be more work to interface with the C++ dll.

I think you will have better luck with this approach because a COM+ Application can be configured to run in a dedicated server process instead of within the SQL Server process.

Maybe your situation dictates otherwise, but as other people have commented, it's best to leave this kind of "integration" outside of the database and put it into a separate application layer.