Accessing .NET Components From SQL Server

In the articles titled Using COM Objects in SQL Server and Extending SQL Server with COM Objects, we saw the basics of how to access and use COM objects from SQL Server. With the advent of .NET, it is but natural for us to see if SQL Server can call into .NET objects. Unfortunately, you cannot, as described in the following article. But don't loose heart!! You can access .NET objects in an indirect way using the COM Interop facility provided by .NET. Note that Microsoft makes no promise for supporting your problems under this method, but anyways, it is good to know that you can access .NET components if need be. In this example, we will see how to exploit this feature.

The steps for accessing .NET objects from SQL Server is quite straight-forward. The following are the steps:

Create the .NET class by defining an interface and having a .NET class implement the interface.
Generate a strong name for the assembly using the SN tool and add the relevant assembly attributes. An alternative to this is to place the .NET DLL under the SQL Server BINN folder. This is more-or-less similar to a private assembly, wherein .NET will search for the assembly in the startup folder of the application (SQL Server in this case).
Compile the assembly and use the REGASM tool to generate the type library for the assembly and register the same.
Run the GACUTIL program to register this assembly in the GAC (you will do this step only if you decide to create a strong name for the assembly)
Write the SQL Server calls for this DLL. The way the interop works will ensure that your .NET component is loaded by the CLR and the appropriate functions are called.

This does look like a lot of steps, but in reality, it is quite simple :-) Let's explore each step in more detail by writing a simple .NET component that returns some information about the AppDomin in which it is running. This value is packaged as a string and sent back to the caller.

Step - 1: Creating the .NET Class

The first step is to create the .NET class. Since this component will ultimately interact with a COM component, it is better to define an interface and implement a class from this interface. Implementing functionality through interfaces has a major benefit for COM clients. .NET keeps interfaces consistent with previous versions when generating the COM Callable Wrapper (CCW). This helps keeps changes from breaking the COM clients. Here is the code for the .NET class.


' Interface that specifies the contract
Public Interface IHelloNET
    Function SayHello() As String
End Interface

' Concrete implementation of the interface
Public Class HelloNET
    Implements IHelloNET

    Public Sub New()
        ' Empty constructor required by COM
    End Sub

    Public Function SayHello() As String Implements IHelloNET.SayHello
        ' Return attributes from the AppDomain namespace
        Dim oDomain As System.AppDomain
        Dim strRetVal As String

        oDomain = AppDomain.CurrentDomain
        strRetVal = "Base Directory: " & oDomain.BaseDirectory & ", Friendly Name: " & _
            oDomain.FriendlyName & ", Current Thread ID: " & oDomain.GetCurrentThreadId.ToString()

        Return (strRetVal)
    End Function
End Class

I've called this project HelloNET.

Step - 2: Creating a strong name for the assembly

The next step is to sign the assembly so that we can deploy it in the GAC. For this, we need to create a strong name using the SN.EXE tool and reference it inside the assembly. Modify the AssemblyInfo.vb file as shown.


Imports System
Imports System.Reflection
Imports System.Runtime.InteropServices

' General Information about an assembly is controlled through the following 
' set of attributes. Change these attribute values to modify the information
' associated with an assembly.

' Review the values of the assembly attributes

<Assembly: AssemblyTitle("HelloNET")> 
<Assembly: AssemblyDescription("Hello World .NET")> 
<Assembly: AssemblyCompany("")> 
<Assembly: AssemblyProduct("")> 
<Assembly: AssemblyCopyright("")> 
<Assembly: AssemblyTrademark("")> 
<Assembly: CLSCompliant(True)> 

'The following GUID is for the ID of the typelib if this project is exposed to COM
<Assembly: Guid("77B3F76A-2CBE-437F-BFA1-81A0DFBC0469")> 

' Version information for an assembly consists of the following four values:
'
'      Major Version
'      Minor Version 
'      Build Number
'      Revision
'
' You can specify all the values or you can default the Build and Revision Numbers 
' by using the '*' as shown below:

<Assembly: AssemblyVersion("1.0.*")> 
<Assembly: AssemblyKeyFile("..\..\HelloWorld.snk")>

The highlighted portions of the assembly information file are the changes that I've made.

Step - 3: Generate a type library

In order to make a .NET component visible to a COM client, we need to register the assembly using the REGASM utility. This command line tool adds information about the class to the system registry so that COM clients can use the .NET class transparently. To register the assembly, execute the following command in the project directory.


regasm /tlb:HelloNET bin\HelloNET.dll

Step - 4: Registering the assembly in the GAC

In order for any COM client to see the .NET assembly, we need to register the assembly in the Global Assembly Cache (GAC). Assemblies registered in the GAC are called shared assemblies. If you want only one COM client to work with this assembly, rather than registering the same in the GAC, you can copy the assembly into the application directory. Such assemblies are called private assemblies. To run a private assembly from the SQL Server environment, the assembly must be in the BINN directory of SQL Server. To register an assembly in the GAC, execute the following command in the project directory.


gacutil /i bin\HelloNET.dll

Step - 5: Calling the .NET Assembly from SQL Server

Here is the SQL Server code to access the VB DLL.


DECLARE @comHandle INT
DECLARE @retVal INT
DECLARE @errorSource VARCHAR(100)
DECLARE @errorDescription VARCHAR(100)
DECLARE @helloString VARCHAR(200)

EXEC @retVal = sp_OACreate 'HelloWorld.HelloNET', @comHandle OUTPUT
IF (@retVal <> 0)
BEGIN
	EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
	SELECT @errorSource, @errorDescription
END

EXEC @retVal = sp_OAMethod @comHandle, 'SayHello', @helloString OUTPUT
IF (@retVal <> 0)
BEGIN
	EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
	SELECT @errorSource, @errorDescription
END

EXEC @retVal = sp_OADestroy @comHandle
IF (@retVal <> 0)
BEGIN
	EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT

	SELECT @errorSource, @errorDescription
END

SELECT @helloString

The mode of calling is quite simple. We simply create the .NET object by referring to its ProgID (solution name and project name) and then call the method in it. This in turn calls the .NET assembly and generates the following output.


--------------------------------------------------------------------------------------------------------- 
Base Directory: C:\PROGRA~1\MICROS~4\MSSQL\binn\, Friendly Name: DefaultDomain, Current Thread ID: 3780

(1 row(s) affected)

You can wrap these calls in a stored procedure and then call this stored procedure. Another interesting concept is to wrap these calls inside an extended stored procedure. Here is an article that exposes a .NET component as an extended stored procedure written by Kumar Gaurav Khanna.

OK, that was quite easy and interesting! Being able to access .NET code from SQL Server opens the door to writing lot of interesting code that uses .NET objects. Keep in mind though that the performance of your application cannot be guaranteed to be fast. Note that there is an interop call involved apart from the SQL Server to COM call. So do not write complex .NET code that might interfere with the actual usage of SQL Server, which is to provide relational access to data.

But, have fun with this capability!!

Home