VistaDB 6
VistaDB / Developer's Guide / CLR Stored Procedures and Functions / Loading CLR Assemblies into VistaDB
In This Topic
    Loading CLR Assemblies into VistaDB
    In This Topic


    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.

    Copy Code
    -- 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.

    Load CLR Assembly With DDA
    Copy Code
    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.

    See Also