This example is based on the tutorial HO_S_10. we start by obtaining a C# library compile from the following code (included in the tutorial):

using System;

[Serializable]
public class Point_10
{
  public Double x;
  public Double y;

  public Point_10 ()
    {
      x = 0;
      y = 0;
    }
  public Point_10 (Double new_x, Double new_y)
    {
      x = new_x;
      y = new_y;
    }

  public Double distance (Point_10 p)
    {
      Double ret;

      ret =  Math.Sqrt ((p.x - this.x) * (p.x - this.x) + (p.y - this.y) * (p.y - this.y));

      return ret;
    }
}

This gives us the Point_10 class with two constructors and one method for finding the distance between two points.

Now we must create the library reference in Virtuoso using the following:

DROP ASSEMBLY "myPoint";

CREATE ASSEMBLY "myPoint" as concat (http_root() , '\\tutorial\\hosting\\ho_s_10\\Point_ho_s_10.dll')
  WITH PERMISSION_SET = SAFE WITH AUTOREGISTER;

Now for a quick test, we will find the distance between two points:

SQL> select new Point_10(0,0).distance(Point_10(3,4));
callret
DOUBLE PRECISION
_______________________________________________________

               5

Now we will create a table with a column of type Point_10 and then insert some test data:

drop table CLR..Supplier_ho_s_10;

create table CLR..Supplier_ho_s_10 (id integer primary key, name varchar (20), location Point_10);

insert into CLR..Supplier_ho_s_10 (id, name, location) values (1, 'S1', new Point_10 (1, 1));
insert into CLR..Supplier_ho_s_10 (id, name, location) values (2, 'S2', new Point_10 (3, 3));
insert into CLR..Supplier_ho_s_10 (id, name, location) values (3, 'S3', new Point_10 (5, 5));

Now we will demonstrate how this assembly's class can be used in SQL by showing some queries on the sample data:

SQL> select name, s.location.x from CLR..Supplier_ho_s_10 s;
name                  callret
VARCHAR               DOUBLE PRECISION
_______________________________________________________________________________

S1                                   1
S2                                   3
S3                                   5

The distances from (0, 0):

SQL> select name, s.location.distance(Point_10(0,0)) from CLR..Supplier_ho_s_10 s ;
name                  callret
VARCHAR               DOUBLE PRECISION
_______________________________________________________________________________

S1                    1.414213562373095
S2                    4.242640687119285
S3                    7.071067811865476

Now, the points that are more than 3 units away from it:

SQL> select name from CLR..Supplier_ho_s_10 s where s.location.distance(Point_10(0,0)) > 3;
name
VARCHAR
_______________________________________________________________________________

S2
S3

This example demonstrates the creation of trivial CLR classes and referencing them from Virtuoso.

These examples will use an assembly called test.dll, whose source code is:

using System;
using System.IO;

public class Sample
{
   public static String GetEnv ()
     {
       return Environment.GetEnvironmentVariable("PATH");
     }
}

The assembly will be registered using:

CREATE ASSEMBLY "test" from 'test.dll' WITH PERMISSION_SET = SAFE WITH AUTOREGISTER;

and subsequently called using:

SQL> select Sample::GetEnv ();

returning the following error for attempting to exceed the SAFE permission set.

*** Error 42000: [Virtuoso Driver][Virtuoso Server]CLR05: Request for the permission of type System.Security.Permissions.EnvironmentPermission,
      mscorlib, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
in
__udt_method_call:(BIF),
<Top Level>
at line 4 of Top-Level:
select Sample::GetEnv ()

Now we can try the same sample using PERMISSION_SET = UNRESTRICTED.

drop ASSEMBLY "test";

CREATE ASSEMBLY "test" from 'test.dll' WITH PERMISSION_SET = UNRESTRICTED WITH AUTOREGISTER;

SQL> select Sample::GetEnv ();
callret
VARCHAR
_______________________________________________________________________________

D:\Virtuoso\bin...;

1 Rows. -- 32 msec.
<

Unrestricted assemblies do not have any restrictions on usage.