Assemblies for use as SQL CLR Procs

Overview

SQL CLR assemblies are Class Libraries (dlls) written in either C# or VB.Net.    Visual Studio 2008 or Visual Studio 2010 may be used to build the assembly.

Assembly and Namespaces

Ensure that you know the assembly and namespace of your dll.  These are both required when you register the assembly in the database.  For C# users right click the assembly project and look at the first panel.

By default these two will match, they have been changed in our sample to illustrate the naming when you go to register the assembly and functions.  Make sure you assign a namespace to all your classes.

Loading an Assembly into the database

Assemblies can be loaded using SQL Code, DDA, or using the Data Builder UI.

Load using SQL Code

The loading of an assembly into the database is done through the following SQL Commands.

-- Add the assembly
CREATE ASSEMBLY [MyClrProcExportAssembly] FROM 'MyClrProcExportAssembly.dll';

-- Update the assembly
ALTER ASSEMBLY [MyClrProcExportAssembly] FROM 'MyClrProcExportAssembly.dll';

-- Drop the assembly
DROP ASSEMBLY MyClrProcExportAssembly;

ALTER an assembly will reload the assembly from disk without requiring all the registered functions be dropped first.  This is very handy during the development cycle. Any methods that are not present in the new assembly are dropped.

The Assembly name will be used when you want to update or drop it from the database later.  A best practice is to use the name of the base dll without the extension.  This will load from the local path (paths are also valid).

Look in Data Builder after running this command and you will see the assembly under the Programmability / Assemblies tree item. 

Note: Multiple assemblies may be loaded into a single database.  The screen shot shows CLR Procs that have been registered using the scripts included in the sample.  These will not be present until your register them.

 

Load CLR Assembly using DDA Code

DDA cannot call CLR Procs or Functions, but it can load their assemblies and register the methods.

using (IVistaDBDatabase db = DDAObj.OpenDatabase(dbName, 
        VistaDBDatabaseOpenMode.NonexclusiveReadWrite, null))
{
    // Load the assembly from disk
    db.AddAssembly("MyClrProcExportAssembly", 
        "C:\MyClrProcExportAssembly.dll", 
        "My CLR Description");

    // Remove the assembly FORCE unload
    db.DropAssembly("MyClrProcExportAssembly", true);

    // Get the list of assemblies in a database
    IVistaDBAssemblyCollection registeredAssemblies = db.GetAssemblies();
}

The loaded assembly will now be shown in Data Builder.

Not shown in the code above is an UpdateAssembly function that can be used to update an assembly from disk without the need to drop all the registered methods first.  Any methods that are not present in the new assembly are dropped.

Load CLR Assembly using Data Builder

Data Builder also has a UI to do this under CLR Procs - Add Assembly.

Notice that each Method shows up with the complete namespace.class.method.  Checking the boxes next to these functions will register them with the database to be eligible for calling.  All methods in an assembly are not automatically added for you, you must register each method you want to call.  See the page on registering CLR Procs and Functions for more information.

 

Assembly Targeting Guidelines

Target the .Net 2 framework as your common base, unless you know all your usage scenarios will include whatever other framework you target.  You can target .Net 3.5 SP1 for example, but then if the database is run on a machine without .Net 3.5 SP1 you would end up with runtime errors when the engine attempts to load the assembly.

Sample Project

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