Ways to Register SQL CLR Methods

Methods must be public static

This page discusses how to register methods in a CLR Assembly for use as CLR Functions, CLR Procedures, and CLR Triggers (the process is almost identical).  All of these methods must be public statics in a CLR assembly in order to be used from SQL.  The methods can instantiate other classes that are not static, but the entry point must be a public static.

See the CLR Proc Overview for a high level overview of CLR Procs in general.

Load CLR Assembly First

Make sure you have loaded the CLR Assembly into the database before doing the steps below. 

Create CLR Functions Procedures in VistaDB

You can do each of these steps using direct SQL, using DDA, or using Data Builder.  We will briefly cover each way to allocate a method in the database.

Adding CLR Functions using direct SQL

The way you tell the engine you want a CLR method to be used as a SQL Procedure or Function is by using the CREATE FUNCTION or CREATE PROCEDURE SQL Commands.

Note the method signature must match the CLR method.  The AS EXTERNAL tells the SQL engine to go look for this fully named method signature.  Since the assembly has already been loaded, it should be able to find it.

The FULL method name must be used.  There are no using statements or ways to alias a namespace.  You have to use the full AssemblyName.AssemblyNamespace.ClassName.MethodName.  In C# the namespace and the assembly name and namespace are often the same.  This can lead to confusing names like Sample.Sample.MyClass1.Method1 for the full method signature.

-- We want a friendly name ExportSchemaAndData rather 
-- than the full namespace name. Note the way we have
-- to specify the Assembly.Namespace.Class.Function
-- Even if the assembly and namespaces are the same
-- you MUST specify each

-- NOTE how the NVARCHAR(4000) is used for a string. 
-- The max length of a string is 8000 bytes, but
-- all strings in .Net are unicode, so we need 
-- to use NVARCHAR and that takes 2 bytes per character
CREATE FUNCTION ExportSchemaAndData ( @value NVARCHAR(4000) ) 
RETURNS BIT
AS EXTERNAL NAME 
MyClrProcExportAssembly.MyClrProcExportNamespace.CLRProcedures.ExportSchemaAndData;

-- Add GetDatabaseVersionProcedure
-- If you were to add it using the Data Builder UI your 
-- name would be CLRProcedures_GetDatabaseVersionProcedure.
-- The default is to use the assemblies default namespace, 
-- but the class_method is the name of the procedure.
-- By specifying a name here we are overriding the default. 
-- It does not have to match the actual fuction name 
-- (The actual function is GetDatabaseVersionProcedure).
CREATE PROCEDURE GetVersionProcedure( @versionString NVARCHAR(4000) OUTPUT ) 
AS EXTERNAL NAME 
MyClrProcExportAssembly.MyClrProcExportNamespace.CLRProcedures.GetDatabaseVersionProcedure;

You will notice that procedures do not normally have a return type.  An integer is implied to indicate the number of rows affected by the procedure.

Adding CLR Functions using DDA

Add functions in DDA is quite easy, but a little different than SQL.  Using the above SQL we don't know what assembly the method is supposed to be found in, but using the DDA routine we pass in the assembly.  As a result you don't need to specify the assemblyname as a part of the method.

public static void RegisterAllMethodsDDA()
{
    using (IVistaDBDatabase db = SampleRunner.DDAObj.OpenDatabase
        (SampleRunner.DatabaseFilename, 
        VistaDBDatabaseOpenMode.NonexclusiveReadWrite, 
        null))
    {
        // NOTE: There is a difference in how you register the 
        // CLR Proc in DDA than through SQL. The ClrHostedMethod must

        // just be the Namespace.Class.Method. The assembly is added 
        // for you from the assemblyName parameter (3rd param)
        // In SQL during an add function or add procedure we don't 
        // know which assembly name the method belongs to, so it must
        // be specified in the full naming, but through DDA we have the

        // assembly through the assemblyName param.

        // procedureName = the name we want to use to call the procedure

        // or function (the attribute on the method tells us which type it
            is)
        // ClrHostedMethod = Namespace.Class.Method of the method to call
        
        // assemblyName = The friendly assembly name used when registering
        // the assembly into the database description = The text description
        db.RegisterClrProcedure("ExportSchemaAndData",
            "MyClrProcExportNamespace.CLRProcedures.ExportSchemaAndData", 
            SampleRunner.AssemblyName, 
            null);

        db.RegisterClrProcedure("GetVersionFunction", 
            "MyClrProcExportNamespace.CLRProcedures.GetDatabaseVersionFunction", 
            SampleRunner.AssemblyName, 
            null);

        db.RegisterClrProcedure("GetVersionProcedure", 
            "MyClrProcExportNamespace.CLRProcedures.GetDatabaseVersionProcedure", 
            SampleRunner.AssemblyName, 
            null);
    }
}

public static void ShowRegisteredCLRMethods()
{
    using (IVistaDBDatabase db = SampleRunner.DDAObj.OpenDatabase(
        SampleRunner.DatabaseFilename, 
        VistaDBDatabaseOpenMode.NonexclusiveReadWrite, 
        null))
    {
        IVistaDBClrProcedureCollection registeredCLRProcs = db.GetClrProcedures();

        foreach (IVistaDBClrProcedureInformation info in registeredCLRProcs)
        {
            Console.WriteLine(string.Format("Method:
                {0} - {1}", 
                info.Name, info.FullHostedName));
        }
    }
}

You can call GetClrProcedures() to get a collection of all the CLR Functions and Procedures in the database.  I know the naming is a little bit wrong, but the two are identical from the engine standpoint.  We only included both types to be compatible with SQL Server.

Adding CLR Functions using Data Builder

During the loading of the assembly you may check the boxes next to the functions you want to be registered in the database.  The naming by default will be Assembly_Class_Method, it cannot be changed through Data Builder at this time.  Checking the box next to the method name will register it in the database.

Another way to register the methods after installing the assembly is to check the method in the Programmability pane of Data Builder.  Again, the methods cannot be renamed, but you can see the full name and signature in the UI.

Unchecking the boxes will unregister the method.  In order to drop an assembly from the database using Data Builder you must first unregister all of the methods.

Sample Project

We have a sample CLR Proc project page for more detailed information and links to the current project download.