Overview

SQL CLR assemblies are Class Libraries (dlls) written in either C# or VB.Net. 

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.

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

As with any library you create, target the lowest level of the runtime that you will need to run in.  It can be a lower version than the VistaDB provider you are using if necessary since .NET 4.0 can load .NET 2.0 and higher assemblies.