TurboSQL Guide

Exchanging Parameters with .NET Assemblies

Previous  Top  Next

Assemblies that are used for external functions, procedures or aggregates must be located in the same directory as the database file.

When calling methods from .NET assemblies, parameters are mapped from TurboSQL to CLR according to the table below. Regarding this table, there is a difference between functions on one hand and procedures and aggregates on the other hand.

Functions have only input parameters and are evaluated to NULL, if one of its arguments is NULL. In this case, the CLR method is not executed at all. Therefore, NULL will never be passed to user-defined CLR functions and the CLR method must be declared with the non-nullable CLR types only. For example,

CREATE FUNCTION Log2(:x DOUBLE NOT NULL) RETURNS DOUBLE NOT NULL AS EXTERNAL NAME [MyNamespace.MyClass].MyMethod,MyAssembly

corresponds to this definition in C#:

public class MyClass {
       static public double MyMethod(double x) {...}
}

Because the argument is not nullable, Log2 can never be used on nullable arguments. However, if declared like this

CREATE FUNCTION Log2(:x DOUBLE) RETURNS DOUBLE AS EXTERNAL NAME [MyNamespace.MyClass].MyMethod,MyAssembly

the same definition as above is valid in C#. When Log2 is called with a NULL argument, the execution engine will return NULL without calling the CLR method.

This rule only holds for functions; CLR procedures and CLR aggregates are always called, even if one of the arguments is NULL. Therefore, the parameters and the return type of the CLR definition must be able to transport the NULL value. TurboSQL does this by passing a null value (Nothing in Visual Basic), which is obvious for CHAR types and array types like LONGVARBINARY. In order to be able to do this with value types like Int64 or DateTime, the nullable wrapper must be used.

CREATE PROCEDURE TestProc(:x DOUBLE) AS EXTERNAL NAME [MyNamespace.MyClass].MyMethod,MyAssembly

is therefore mapped by

public class MyClass {
       static public void MyMethod(Nullable<double> x) {...}
}

or

public class MyClass {
       static public void MyMethod(double? x) {...}
}

or in Visual Basic:

Public Class MyClass
       Public Shared Sub MyMethod(X As Nullable(Of Double))
               ...
       End Sub
End Class

For more information on nullable wrappers, search MSDN for the Nullable class.

TurboSQL type

Non-Nullable CLR type

Nullable CLR type

BYTE

System.Int64

Nullable<System.Int64>

SMALLINT

System.Int64

Nullable<System.Int64>

INTEGER

System.Int64

Nullable<System.Int64>

BIGINT

System.Int64

Nullable<System.Int64>

FLOAT

System.Double

Nullable<System.Double>

AUTOINC

System.Int64

Nullable<System.Int64>

BOOLEAN

System.Int64
(0 corresponds to false, all other values to true)

Nullable<System.Int64>

ENUM

System.Int64

Nullable<System.Int64>

GUID

System.Guid

Nullable<System.Guid>

LINK

System.Int64

Nullable<System.Int64>

RELATION

System.Int64[]

System.Int64[]

CHAR(X), VARCHAR(X)

System.String

System.String

WCHAR(X), VARWCHAR(X)

System.String

System.String

LONGVARCHAR

System.String

System.String

LONGVARWCHAR

System.String

System.String

LONGVARBINARY

System.Byte[]

System.Byte[]

TIME

System.DateTime

Nullable<System.DateTime>

DATE

System.DateTime

Nullable<System.DateTime>

TIMESTAMP

System.DateTime

Nullable<System.DateTime>

Compatibility Information

This statement is only available in TurboDB Managed.