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